Adding Records to Access Table (Inconsistent Results)-CodePudding

I am working on a VBA project in Excel as a record management tool. In one case, a record(s) need to be added to 2 tables within the same Access DB with varying field info. I am able to successfully add to the first table (Tours_Items) which can add multiple records by looping through the available rows. However, I cannot get the following records to add to their respective table (Tours_Tours). When the code is executed I get the following error: "Run-time error'438': Object doesn't support this property or method".

The VBA for executing on Tours_Items is just about identical to Tours_Tours outside of the table names and the looping feature that is required for Tours_Items so I am just utterly bamboozled as to why it will not execute correctly when the more complicated one will. The subs run one after the other; Tours_Items and then Tours_Tours. I have even tried just executing Tours_Tours and I still get the same error.

The fields correctly match the Access DB fields (I have triple checked), and even if they did not, VBA would not throw the object error. The Access DB fields are appropriately configured to accept the types of data that are being added to the record.

Code:

Sub DBAddTours_Items()

Dim DBFile, Tbl               As String
Dim SQL                       As String
Dim j, NxtRowItems, NxtItemRw As Integer
Dim con, rs                   As Object
Dim wsToursTtl                As Worksheet

Set wsToursTtl = Worksheets("Tour Subtotal")

    'Fill Items to DB
        'Connect to the Database
            Set con = CreateObject("ADODB.connection")
        'Set File and tables
            DBFile = ThisWorkbook.path & "\" & "DailyInfo.accdb"
            con.Open "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" & DBFile
        'Define
            NxtRowItems = WorksheetFunction.CountA(wsToursTtl.Range("B:B"))
            NxtItemRw = WorksheetFunction.CountA(Sheets("Tours_Items").Range("A:A"))   1
        'Create the ADODB recordset object.
            Set rs = CreateObject("ADODB.Recordset")
        'Define Table and SQL
            Tbl = "Tours_Items"
            SQL = "SELECT * FROM " & Tbl
            'Set the necessary recordset properties.
                With rs
                    .CursorType = 1   'adOpenKeyset on early binding
                    .LockType = 3     'adLockOptimistic on early binding
                'Open the recordset.
                    .Open SQL, con
                'Loop through and add all items
                     For j = 2 To NxtRowItems
                        .AddNew
                        .Fields("ItemID") = (j - 3)   NxtItemRw
                        .Fields("TourID") = wsToursTtl.Cells(j, 1)
                        .Fields("ItemNum") = wsToursTtl.Cells(j, 2)
                        .Fields("Admission") = wsToursTtl.Cells(j, 3)
                        .Fields("Ticket") = wsToursTtl.Cells(j, 4)
                        .Fields("Premium/BAT") = wsToursTtl.Cells(j, 5)
                        .Fields("Tour") = wsToursTtl.Cells(j, 6)
                        .Fields("Price") = wsToursTtl.Cells(j, 7)
                        .Update
                    Next j
                'Close Recordset
                    .Close
                End With
        'Close the connection.
            con.Close
    'Release Variables
        Set rs = Nothing
        Set con = Nothing
        SQL = ""
        Tbl = ""

End Sub

Sub DBAddTours_Tours()

Dim DBFile, Tbl               As String
Dim SQL                       As String
Dim con, rs                   As Object
Dim wsToursTtl                As Worksheet

Set wsToursTtl = Worksheets("Tour Subtotal")

    'Fill Tour info Tours_Tours
        'Connect to the Database
            Set con = CreateObject("ADODB.connection")
        'Set File and tables
            DBFile = ThisWorkbook.path & "\" & "DailyInfo.accdb"
            con.Open "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" & DBFile
        'Create the ADODB recordset object.
            Set rs = CreateObject("ADODB.Recordset")
        'Define Table and SQL
            Tbl = "Tours_Tours"
            SQL = "SELECT * FROM " & Tbl
            'Set the necessary recordset properties.
                With rs
                    .CursorType = 1   'adOpenKeyset on early binding
                    .LockType = 3     'adLockOptimistic on early binding
                'Open the recordset.
                    .Open SQL, con
                'Add new record to table
                    .AddNew
                    .Field("TourID") = wsToursTtl.Range("TourID_Dest").Value
                    .Field("TourDate") = wsToursTtl.Range("TourDate_Dest").Value
                    .Field("TourTime") = wsToursTtl.Range("TourTime_Dest").Value
                    .Field("Premium/Bat/Disc") = Me.PremiumBAT_Lbl.Caption
                    .Field("TourType") = wsToursTtl.Range("TourType_Dest").Value
                    .Field("GuestName") = wsToursTtl.Range("GuestName_Dest").Value
                    .Field("GuestAddress") = wsToursTtl.Range("GuestAddress_Dest").Value
                    .Field("GuestPhone") = wsToursTtl.Range("GuestPhone_Dest").Value
                    .Field("GuestEmail") = wsToursTtl.Range("GuestEmail_Dest").Value
                    .Field("GuestMember") = wsToursTtl.Range("GuestMem_Dest").Value
                    .Field("TourComments") = wsToursTtl.Range("Comments_Dest").Value
                    .Field("SaleDate") = wsToursTtl.Range("SaleDate_Dest").Value
                    .Field("TransactionDetails") = wsToursTtl.Range("Trxn_Dest").Value
                    .Field("SoldBy") = wsToursTtl.Range("SoldBy_Dest").Value
                    .Field("PaymentType") = wsToursTtl.Range("PayType_Dest").Value
                    .Field("BookedNum") = 2
                    .Field("LastModified") = Now
                    .Field("ModifiedBy") = Application.UserName
                    .Update
                'Close Recordset
                    .Close
                End With
                
        'Close the connection.
            con.Close
    'Release Variables
        Set rs = Nothing
        Set con = Nothing
        SQL = ""
        Tbl = ""

End Sub

Any help is much appreciated!

CodePudding user response：

The correct collection to access a field is .Fields plural.

So DBAddTours_Items is correct where in your other procedure, DBAddTours_Tours you called upon .Field singular. Which doesn't exist.

Documentation found here: https://docs.microsoft.com/en-us/sql/ado/guide/data/the-fields-collection?view=sql-server-ver15