append a record using vba where there is an auto number field

[marky_dp]

Hi, basically what i want to do is append a record to a table using vba. I would like to take two values from my form (productID, supplierID) and insert them into a table (supplierProductsTBL) under the same headings. I've constructed an sql statement but am getting the following error:

runtime error '3346'

number of query values and destination fields are not the same'

I think this happens because the table also contains an autonumber field (supplierProductID) and i'm not referencing this in my sql statement (below):

mySQL = "INSERT INTO SupplierProductsTBL ( SupplierID, ProductID ) VALUES ('" & SupplierID & "'), ('" & ProductID & "')"

i wuld just get rid of the autonumber field but i need this.

Any help on this would be appreciated, cheers mark.

 

[reclusivemonkey]

Mark,

What version of Access are you using. I have a very similar setup; I have an audit trail table for a couple of tables. I insert a record with various fields to a table. The table has an AutoNumber field, but I don't include this in the SQL and the AutoNumber is still created. The code, just so you can see my SQL follows the same principal is;

Private Sub Form_BeforeUpdate(Cancel As Integer)
    ' Create an audit trail to track changes to cost centres
    Dim frmObjCC As Object
    Dim CCAField As String
    Dim CCAOldValue As String
    Dim CCANewValue As String
    Dim CCADateStamp As Date
    Dim CCAUserName As String
        
    For Each frmObjCC In Forms!frmCostCentres
        If Left(frmObjCC.Name, 10) = "CostCentre" Then
                If frmObjCC.Value <> frmObjCC.OldValue Then
                        CCAField = frmObjCC.Name
                        CCAOldValue = frmObjCC.OldValue
                        CCANewValue = frmObjCC.Value
                        CCADateStamp = Now()
                        CCAUserName = Environ("Username")
                        strSQL = "INSERT INTO tblCostCentresAudit (CCentAuditCostCentre, CCentAuditField, "
                        strSQL = strSQL + "CCentAuditOldValue, CCentAuditNewValue, CCentAuditDateStamp, "
                        strSQL = strSQL + "CCentAuditUserName) VALUES ("
                        strSQL = strSQL + "'" & CostCentreID.Value & "', "
                        strSQL = strSQL + "'" & CCAField & "', "
                        strSQL = strSQL + "'" & CCAOldValue & "', "
                        strSQL = strSQL + "'" & CCANewValue & "', "
                        strSQL = strSQL + "#" & CCADateStamp & "#, "
                        strSQL = strSQL + "'" & CCAUserName & "')"
                        DoCmd.RunSQL (strSQL)
                    Else
                End If
            Else
        End If
    Next frmObjCC

End Sub

 

[marky_dp]

Cheers for the guidance,

i changed my sql statement slightly (below) and things seem to be working now.

mySQL = "INSERT INTO SupplierProductsTBL ( SupplierID , ProductID ) VALUES ('" & SupplierIDCombo & "', '" & ProductID & "')"

Now what i need to figure out is how to append data from a form into two tables using the same statement.

 

[reclusivemonkey]

Now what i need to figure out is how to append data from a form into two tables using the same statement.

Wouldn't this be as simple as repeating the statement with the second table referenced instead of the first??

 

[marky_dp]

i dont think it will, as the data to be posted into the second table is different from the first table.

The sql statement i just got to work is posting two values from my form, (supplierID, productID) into table 1 (supplierProductsTBL). This table also includes one other field, which is the auto-number field (supplierProductID), which now automatically generates. So Far Everything OK.

The second table (stockRecievedTBL) also needs data from the form posting into it at the same time.
This table includes fields;

StockRecievedID (AutoNumber)
SupplierProductID (Number - This is linked to supplierProductsTBL, supplierProductID)
AmountRecieved(Number)
DateCheckedIn(Date)
TimeCheckedIn(Time)
EmployeeCheckedInID(Number).

Posting the amount recieved, date, time and employee will be no problem but posting the SupplierProductID (the linked field) so that it is the same as the SupplierProductID in the first table (SupplierProductsTBL) is what i cant get my head round. Any suggestions ?

 

[reclusivemonkey]

Is the SupplierProductID on your form showing the correct number? If so, then just use this. Have you tried it and found a problem? I am still suffering from a cold at the moment so my brain isn't working too well.

 

[marky_dp]

supplierProductID isnt shown on the form, this is the auto-number field that is created in the first table (supplierProductsID), when a supplierID and productID is added. This auto-number then needs to be added in the supplierProductID field in the stockRecievedTBL in order to link the two tables.