How do I update a list (whose source is another table) on a form

[SikhSioux]

Say I have a form 'breakfast' that contains a combo box control which allows you to choose a fruit from a list of fruits. The list of fruits is obtained from a table called 'fruits'. Is there a way that I can add a new fruit to the list so I can choose it from the list?

I have tried the following without success:

When the value of the control is a fruit which is not in the list (i.e when the 'Not In List' event occurrs) I have enabled a new form called 'frmFruits' to open, this form allows you to search for a fruit and add a new fruit to the 'fruits' table. On closing this form I have tried refreshing the 'breakfast' form (by running the 'refresh' command when the 'On Close' event occurrs) but I get message stating ' error 2237 the item selected is not in the list'

 

[Satal Keto]

I think this is what you're looking for

me.cboName.requery

I would put that in the Form_Activate sub.

I hope that this helps

Satal

 

[gemma-the-husky]

but also, in the notinlist event

when you open the new form to capture the new item, you need to wait for the popup to close before you requery the combo box - one way is to open the addon form in dialog mode

 

[SikhSioux]

Thanks for helping guys, Gemma following your advice I included the 'acdialog' constant in the 'Openform' command. I then tested the code and I found this successfully prevented the 2237 error message showing up, the code for the 'NotInList' event now reads:

Private Sub fruitId_NotInList (NewData As String, Response As Integer)
DoCmd.OpenForm "frmFruits",,,,, acDialog
End Sub

I then included the code specified by Satal in the 'frmBreakfast' forms 'Activate' event, the code in this event now reads:

Private Sub Form_Activate()
Me.fruitId.Requery
End Sub

When I then tested the code again I got the 'Not in list' error message (i.e error 2237) again.

Could the problem be because the Activate event is not the first event that fires when the 'frmBreakfast' form comes to the fore? It seems to me that the combo box's 'on got focus' event might be firing because the cursor seems to be in the combo box field

Also, the combo box's source is a query which has two columns - fruitId and fruitName, the bound coloumn is the fruit Id but it is hidden so the user only sees the fruitName when chossing from the list, could the problem be here?

Any suggestions?

 

[gemma-the-husky]

what you need is

Private Sub fruitId_NotInList (NewData As String, Response As Integer)
DoCmd.OpenForm "frmFruits",,,,, acDialog
fruitid.requery
End Sub

 

[boblarson]

what you need is

Private Sub fruitId_NotInList (NewData As String, Response As Integer)
DoCmd.OpenForm "frmFruits",,,,, acDialog
fruitid.requery
End Sub

And actually, if you just use

Response = acDataErrAdded

after the form opening in dialog mode instead of the requery it will make sure that the value is added.