T
Tony Williams
I have a combo box control on a form, the form is based on mt table
tblmaintabs and the combobox is called "cmbmaincompany" and the input is
stored in a field "txtcompany" in my table. I have Limit to list set to Yes
and the following code in the On NotinList property of the combo box:
Private Sub cmbmaincompany_NotInList(NewData As String, Response As Integer)
Dim strMsg As String
strMsg = "'" & NewData & "' is not on the list of companies." & vbCrLf
& " Do you want to add them to the current List?" & vbCrLf & " Click Yes to
Add or No to re-type it."
If MsgBox(strMsg, vbQuestion + vbYesNo, "Add new name?") = vbNo Then
Response = acDataErrContinue
Else
DoCmd.OpenForm "frmCompany", , acFormAdd
DoCmd.Close acForm, "frmCompnay", acSaveYes
DoCmd.Requery "cmbmaincompany"
If Err Then
MsgBox "An error occurred. Please try again."
Response = acDataErrContinue
Else
Response = acDataErrAdded
End If
End If
Set db = Nothing
Set rs = Nothing
End Sub
What I want to happen is if the value the user has typed in the box is not
in the list I want the form frmcompany to open, the user adds certain
details including the company name and when they close the form frmcompany
the new information is added to the list and populates the control on my
open form.
The above code gives me an error 2019 saying that it can't find the field
cmbmaincompany. I've tried substituing txtcompany but that doesn't work
either.
If I omit the line DoCmd.Requery "cmbmaincompany" then I get my message and
when I press Yes my frmCompany will open but I also get the standard Access
NotinList message. If I then add a new company record and close my
frmcompany the new company is still not accepted until I go through the
process again.
I'm relatively new at VBA so can someone help me here please.
Thank you
Tony
tblmaintabs and the combobox is called "cmbmaincompany" and the input is
stored in a field "txtcompany" in my table. I have Limit to list set to Yes
and the following code in the On NotinList property of the combo box:
Private Sub cmbmaincompany_NotInList(NewData As String, Response As Integer)
Dim strMsg As String
strMsg = "'" & NewData & "' is not on the list of companies." & vbCrLf
& " Do you want to add them to the current List?" & vbCrLf & " Click Yes to
Add or No to re-type it."
If MsgBox(strMsg, vbQuestion + vbYesNo, "Add new name?") = vbNo Then
Response = acDataErrContinue
Else
DoCmd.OpenForm "frmCompany", , acFormAdd
DoCmd.Close acForm, "frmCompnay", acSaveYes
DoCmd.Requery "cmbmaincompany"
If Err Then
MsgBox "An error occurred. Please try again."
Response = acDataErrContinue
Else
Response = acDataErrAdded
End If
End If
Set db = Nothing
Set rs = Nothing
End Sub
What I want to happen is if the value the user has typed in the box is not
in the list I want the form frmcompany to open, the user adds certain
details including the company name and when they close the form frmcompany
the new information is added to the list and populates the control on my
open form.
The above code gives me an error 2019 saying that it can't find the field
cmbmaincompany. I've tried substituing txtcompany but that doesn't work
either.
If I omit the line DoCmd.Requery "cmbmaincompany" then I get my message and
when I press Yes my frmCompany will open but I also get the standard Access
NotinList message. If I then add a new company record and close my
frmcompany the new company is still not accepted until I go through the
process again.
I'm relatively new at VBA so can someone help me here please.
Thank you
Tony