dropdown – Use VBA to add multiple fields to one record while also adding a category to each field


I could not find the answer to this question elsewhere. I am a new VBA user.

I have a very simple form for entering data on plant specimens. There are only 3 fields that have drop down boxes. Two fields, “Species” and “Code” reference information from another table called “MasterVegList” to facilitate field data entry.

In order to allow the end user to add new species into to the form and the “MasterVegList” table, I wrote the following VBA code:

Private Sub Code_NotInList(NewData As String, Response As Integer)
Dim strTmp As String

    'Get confirmation that this is not just a spelling error.
    strTmp = "Add '" & NewData & "' as a new category?"
    If MsgBox(strTmp, vbYesNo + vbDefaultButton2 + vbQuestion, "Not in list") = vbYes Then

        'Append the NewData as a record in the Categories table.
        strTmp = "INSERT INTO MasterVegList ( Code ) " & _
            "SELECT """ & NewData & """ AS Code;"
        DBEngine(0)(0).Execute strTmp, dbFailOnError

        'Notify Access about the new record, so it requeries the combo.
        Response = acDataErrAdded
    End If
End Sub

Private Sub Species_NotInList(NewData As String, Response As Integer)
Dim strTmp As String

    'Get confirmation that this is not just a spelling error.
    strTmp = "Add '" & NewData & "' as a new category?"
    If MsgBox(strTmp, vbYesNo + vbDefaultButton2 + vbQuestion, "Not in list") = vbYes Then

        'Append the NewData as a record in the Categories table.
        strTmp = "INSERT INTO MasterVegList ( Species ) " & _
            "SELECT """ & NewData & """ AS Species;"
        DBEngine(0)(0).Execute strTmp, dbFailOnError

        'Notify Access about the new record, so it requeries the combo.
        Response = acDataErrAdded
    End If
End Sub

The problem is that when a new species is entered, a new record is created for both species and code in the MasterVegList table. I need both species and code to be entered into the same record. Please note that I am referring to the MasterVegList table, not the table meant to be populated using the form.



لینک منبع

پاسخ دهید

نشانی ایمیل شما منتشر نخواهد شد. بخش‌های موردنیاز علامت‌گذاری شده‌اند *