Updating data in "Find Record" combo box when current record on a form is changed (1 Viewer)

[Mel**]

Updating data in "Find Record" combo box when current record on a form is changed

I am very new to programming with VBA Access and am trying something which I believe should be very simple but I can't make work.

I am practising on a basic recipe database at home, but this is related to changes I would like to make in some existing databases at work.

I have a table full of recipes. I have a form which shows alll the recipes, and a combo box called cmbFindRecipe from which I can call up the appropriate record based on the recipe name - easy -a basic wizard function, but... when I change the record eg paging up/down, the recipe name in the combo box stays the same as the original choice. I want it to synchronise which whatever the current record is (to prevent confusion).

I have tried attaching some code to the "On Change" event for the recipe name field.

I have tried:
Me.txtName.Value = CmdFindRecipe.Value
txtName.Value = CmdFindRecipe.Value
CmdFindRecipe.Value = Me.txtName.Value
CmdFindRecipe.Value = txtName.Value

I can't imagine anything else to try.

Surely it is simple and a common need, but when I searched the forum for an answer I couldn't seem to find the right keywords to bring up anything like what I am looking for.

Any help would be appreciated.

 

[philosofe]

Re: Updating data in "Find Record" combo box when current record on a form is changed

Hi Mel,

Does the combobox actually have 2 columns, the PK and the name? In that case you can't pass the name to the combobox, but the AutoNumber value instead:

Private Sub Form_Current()
    Me.CmdFindRecipe = Me.intAutoNumberField
End Sub

Hope this helps

 

[Mel**]

Re: Updating data in "Find Record" combo box when current record on a form is changed

Thanks. I checked but it doesn't appear to have the ID. I used the wizard, so the row source is:

SELECT qryAllRecipesAllData.Name FROM qryAllRecipesAllData ORDER BY qryAllRecipesAllData.Name

 

[philosofe]

Re: Updating data in "Find Record" combo box when current record on a form is changed

Ah ok. Try moving the CmdFindRecipe = Me.txtName to Form_Current. That should do the trick.

 

[Mel**]

Re: Updating data in "Find Record" combo box when current record on a form is changed

It works!

You're a legend. Thanks so much...