Error 3346, trying to INSERT INTO, two consecutive

Question

I have those two tables is MS Access :

lkpSchemaPIT :

| UID |    lkpSchemaTitleEng     |  
|-----|--------------------------|  
|--1--|---------Title1-----------|  
|--2--|---------Title2-----------|  

...

lkpSchemaPITChronology :

| ID | UID | PUID | Sort | Level | DateStart | DateEnd |  
|----|-----|------|------|-------|-----------|---------|  
|--0-|--1--|--0---|---5--|--2----|---Now()---|--NULL---|  

...

The first table contains just nodes that i'm going to put in a treeview in access. I use the second table to construct the tree, but also keep track of all the parent that a node could've had through the years. You can see that the UID in the two tables are the same, but they have not a relationship between them, when I build the tree, I use a query with a join on it.

My problem is : When I want to add a new node in the lkpSchemaPIT table, I need to be able to add its "treeview" info as well (Parent, Sort, Level, etc.).
This is my code so far :

With CurrentDb
      .Execute _
        "INSERT INTO lkpSchemaPIT " & _
          "(lkpSchemaTitleEng) " & _
        "VALUES " & _
          "('" & Title & "')"
      .Execute _
        "INSERT INTO lkpSchemaPITChronology VALUES (" & .OpenRecordset("SELECT @@IDENTITY").Fields(0) & ", " & [ParentID] & ", " & [NewSort] & ", " & [Level] & ", " & Date & ", null)"

End With  

ParentID, NewSort, Level are 3 variables that have been determined before I call all this. The "Date" parameters is the VBA function that returns the current date.

I know that the first INSERT INTO is working because a new value is displayed in my table. But the second INSERT INTO isn't working and I was able to get the error :

Error 3346 - Number of query values and destination fields are not the same.

Is anyone ever had this kind of problem ?

Answer 1

Once again, here is an example where parameterized queries would be invaluable as you avoid quote enclosures (including # for date/times) and string concatenation that conflates data and SQL together, rendering hard to read and maintain code.

MS Access allows the PARAMETERS clause where you can define the parameter placeholder names and data types and then in VBA you can bind values to such parameters using QueryDefs. No quotes needed or string interpolation.

SQL (save both as stored queries)

PARAMETERS TitleParam Text(255);
INSERT INTO lkpSchemaPIT (lkpSchemaTitleEng)
VALUES (TitleParam);

PARAMETERS UIDParam Long, PUIDParam Long, SortParam Long, 
           LevelParam Long, DateStart Datetime;
INSERT INTO lkpSchemaPITChronology (UID, PUID, [Sort], [Level], DateStart)
VALUES (UIDParam, PUIDParam, SortParam, LevelParam, DateStartParam);

VBA

...
Dim qdef As QueryDef

With CurrentDb

   ' FIRST QUERY
   Set qdef = .QueryDefs("myFirstSavedAppendQuery")

   qdef!TitleParam = [Title]
   qdef.Execute dbFailOnError

   ' SECOND QUERY
   Set qdef = .QueryDefs("mySecondSavedAppendQuery")

   qdef!UIDParam = .OpenRecordset("SELECT @@IDENTITY").Fields(0)
   qdef!PUIDParam = [ParentID]
   qdef!SortParam = [NewSort]
   qdef!LevelParam = [Level]
   qdef!DateStart = Date()

   qdef.Execute dbFailOnError

End With

Set qdef = Nothing