Form not sourcing new data: cbo Limit to list, add, open form (to fill in new data)

[mattvogt]

I'm trying to make my form more user friendly, and when a value isn't in the db I want to a form to open and so the right data can be entered. I have the form
I have the "Limit to list" property set, the vb yes/no box appears, the data is added, and my form opens (the macro on the last line). The issue I have is that my newly entered data isn't what appears in the form, and so when I go to save rest of the new data, I have redundant primary keys as the "Arbor Number" was already added.

Ideally, I would like to keep this code and just have the form open with the new arbor number already pulled up and ready for customer information to be entered into the other fields of the form.

[code]Private Sub cboArborNumber_NotInList(NewData As String, Response As Integer)

Dim strSQL As String
Dim i As Integer
Dim Msg As String

'Exit this sub if the combo box is cleared
If NewData = "" Then Exit Sub

Msg = "'" & NewData & "' is not currently in the list." & vbCr & vbCr
Msg = Msg & "Do you want to add it?"

i = MsgBox(Msg, vbQuestion + vbYesNo, "Unknown Arbor Number...")
If i = vbYes Then
strSQL = "Insert Into tblCustomers ([ArborNumber]) " & _
"values ('" & NewData & "');"
CurrentDb.Execute strSQL, dbFailOnError
Response = acDataErrAdded


Else
Response = acDataErrContinue
End If
DoCmd.RunMacro ("mcr")[Code]

Thanks,

[John_G]

Hi -

After adding the new data to tblCustomers, you have to requery the combo box to see the new entry.

cboArborNumber.Requery should work.

John

[mattvogt]

Thank you for your reply, however after the add I am opening a data entry form to enter the rest of the customer information. I want the ArborNumber to auto-populate into the field in the new form. What code would I use to do such a task?

Thanks,

[June7]

Options:

1. set the customer form DefaultValue property of ArborNumber textbox to reference the ArborNumber field on the first form

2. VBA code in form Current event to populate the ArborNumber field
Code can reference the ArborNumber field on first form
or pass the ArborNumber in OpenArgs argument of OpenForm then reference OpenArgs to extract the value

[mattvogt]

Incredibly helpful, thanks! I set the default value to the ArborNumber and it is perfect.