Hi,
I need some help with some VBA code in Access 2013. I have a form where I have a Manufacturer combo box (coming from tblManufacturer) and a Model combo box (coming from tblModel). I have set up the Manufacturer combo box to add a new manufacturer, pending a yes/no response from a popup message box if the entered item isn't in the list. I want to do the same thing for the Model combo box, but I have two fields to update, not just the one. One for the model, and one for the corresponding Manufacturer. Below is the code used for the Manufacturer with updated names for the Model tables.
One of the problems I can see is that I want to take the manufacturer value from the current dirty record... I'm having a heck of a time with this one. Please help! TY in advance!
I need some help with some VBA code in Access 2013. I have a form where I have a Manufacturer combo box (coming from tblManufacturer) and a Model combo box (coming from tblModel). I have set up the Manufacturer combo box to add a new manufacturer, pending a yes/no response from a popup message box if the entered item isn't in the list. I want to do the same thing for the Model combo box, but I have two fields to update, not just the one. One for the model, and one for the corresponding Manufacturer. Below is the code used for the Manufacturer with updated names for the Model tables.
One of the problems I can see is that I want to take the manufacturer value from the current dirty record... I'm having a heck of a time with this one. Please help! TY in advance!
Code:
On Error GoTo Model_NotInList_Err
Dim intAnswer As Integer
Dim strSQL As String
intAnswer = MsgBox("The Model you entered, " & NewData & ", " _
& "is not currently listed." & vbCrLf & _
"Would you like to add it to the list now?" _
, vbQuestion + vbYesNo, "Model List")
If intAnswer = vbYes Then
strSQL = "INSERT INTO tblModel([Model]) " & _
"VALUES ('" & NewData & "');"
DoCmd.SetWarnings False
DoCmd.RunSQL strSQL
DoCmd.SetWarnings True
MsgBox "The new Model has been added to the list." _
, vbInformation, "Model List"
Response = acDataErrAdded
Else
MsgBox "Please choose a Model from the list." _
, vbInformation, "Model List"
Response = acDataErrContinue
End If
Manufacturer_NotInList_Exit:
Exit Sub
Model_NotInList_Err:
MsgBox Err.Description, vbCritical, "Error"
Resume Manufacturer_NotInList_Exit
End Sub