Forum Discussion
Combobox goes empty on new record
Hi all - silly issue that somehow I can't get the answer to. Any help is appreciated.
I have an Access database (dataverse backend). I'm using a data entry form, which works well.
There are a variety of input field, some are comboboxes all working well, except 1 issue I have. One of these comboboxes is linked to the same table as I'm editing with the form. It has a row source query "Select RiskNames from blurb" and linked to the RiskNames as control source.
That all works as expected and the combobox shows results and auto expands, until I create a new record (DoCmd.GoToRecord , , acNewRec). Initially the combobox still has values (clicking the drop down shows them) , but as soon as I enter any data in any of the other columns, the combobox values are gone (drop down empty). I've used a cmbbox.requery event (in the cmbbox.gotfocus event) to try and reset the row source- this doesnt help. also with VBA I tried to change the rowsource to the query, also to no effect.
I suspect somehow the table is locked or some while a new record is being added, blocking the combobox to requery the original table.
The behaviour is as expected when just editing an existing record.
Any ideas how to solve?
- It's not the only way to handle the situation. However, it is the appropriate way for a relational database application.
8 Replies
Show us the SQL from the row source for the combo box.
Show us the SQL from the record source for the form.
Is the form in Datasheet view, Continuous view or Single Form view?thanks for your reply.
The form recordsource is: SELECT * From Policies
The rowsource for the combobox is:
SELECT Policies.Risk_Name
FROM Policies
GROUP BY Policies.Risk_Name
ORDER BY Policies.Risk_Name;
It's a Single Form.Ps the control is linked to Risk_Name too
- Do you not have a look up table for "Risk_Names"? That would be the normal approach. This approach sort of works, but has the unfortunate side effect observed.
