[Astano]

Hi,

I am currently designing a database in Access 2007 and one of the forms I have has a combo box where the data for populating it is gotten from a table.

I have the limit to list property set to Yes and what I am trying to do is set the Not in List event to ask if the user wants to add the item to the list, if yes is selected it will open the data entry form for entering the item into the relevant table then, when the data has been entered, refresh the combo box so it shows the new addition.

My code (in VBA) for this is:

Private Sub Title_of_product_NotInList(NewData As String, Response As Integer)

Dim Msg As String

Msg = MsgBox("Add " & NewData & " to Products?", vbQuestion + vbYesNo)

If Msg = vbYes Then

		DoCmd.OpenForm "EnterNewProduct", , , , , acDialog

		Response = acErrDataAdded

End If



End Sub

According to my research this should acheive what I desire, but it is not, everything if fine until it comes to refresh the combo box to show the newly added item - it doesn't refresh.

Could anyone help? I can provide more info if needed.

Also, as an aside, would it be possible to set the relevant input on the pop up form to the new data being added so the user doesn't have to type it twice?

Thanks in advance!

This post has been edited by Astano: 25 February 2009 - 05:07 AM

[system.authorizing]

You don't need to open new form you can add data instead of asking user to add it in your combo box or not

Private Sub XYZ_NotInList(NewData As String, Response As Integer)
On error resume next 'Not Required though
Dim MyCtl as Control'Variable to use control
Dim SQL as String 'String variable for SQL 
If MsgBox("XYZis not in list" & vbCrLf & "Do you want to add it?", vbYesNo, "Do you want to add new XYZ") = vbYes Then
Response = acDataErrAdded
NewData =(NewData)
StrSql = "INSERT INTO XYZ(XYZ) SELECT '" & (NewData) & "'" 'Use your Table name and field names on XYZ(XYZ) parameters
DoCmd.SetWarnings False
DoCmd.RunSQL StrSql
myctl.Value = NewData
DoCmd.SetWarnings True
	Else
'IF USER CHOSE NO
Response = acDataErrContinue
myctl.Undo
	End If
End Sub

Hope this will work for you i can show you though if you want to show another form but i think you might want that caz you want to see the new value in combo box at that time you will need to use Combo.Requery or form.requery and you are done