Combobox goes empty on new record

[TheJaks]

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?

[#2]

@TheJaks 

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?

[#3]

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

[#4]

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.

[#5]

hmm do you mean creating a "lookup wizard" in the table design for that column? If so, not sure that works with a dataverse back end?

[#6]

@TheJaks 

No, I mean a second table called "Risk_Name". It is a "Lookup Table" because it has only the values you need for "Risk_Name". You use Lookup tables to provide a closed list of possible values for "Risk_Name" which you control. It limits users to only those values provided in that table. What you are doing actually allows for any previously entered value to be repeated, whether it is appropriate or not because it is based on the values already in that field in the Policies table. Once a value is entered, it continues to be offered. 

Lookup tables are standard Access design. Dataverse is designed to make it more opaque what is going on, but the same concept is available there.

[#7]

ah I see, you mean simply a related table with those unique risk name as values? Yes that would be better from a design perspective. Initially this field started live as a sort of free type field , later on with some grouped functionality on the risk name.

If that's the only way around it I may have to do that indeed. Bigger implementation job than planned. There's no other hack you can think of?

[#8 · Accepted solution]

It's not the only way to handle the situation. However, it is the appropriate way for a relational database application.

[#9]

You're absolutely right. I was implementing a short cut that would have resulted in long term pain. I created a table for the risk names.