Updating Two Fields in a Table from a Form (1 Viewer)

[tsukaska]

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!

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

 

[pbaldy]

Along the lines of:

        strSQL = "INSERT INTO tblModel([Model][COLOR="Red"], Manufacturer[/COLOR]) " & _
                 "VALUES ('" & NewData & "'[COLOR="Red"], '" & Me.Manufacturer & "'[/COLOR]);"

 

[tsukaska]

THANK YOU SO MUCH!!!! I was hoping it was just a small addition to the current code I had! I had to correct my field title (it was labeled ManufacturerID, my bad...). I was worried for a moment because the field I was updating was really a linked one to many relationship, but it was flawless!

I completely forgot about the Me. designations. I guess that's what happens when I try to digest four different MS Access library books while developing a calibration database, trying to start up a business on the side while working full time at a different job, and having a family! Haha! Thank you thank you thank you!!

 

[pbaldy]

Happy to help, and welcome to the site by the way!