Create record and INSERT INTO with Constant field & Variable field data

Question

Within VBA, in Access 2003, I need to

1. Create a record in TableA with a new AutoNumber as the primary key (AID) and the current date
2. Create several records in TableC which associate AID from the new record in TableA with multiple foreign keys (BID) from TableB, as selected by QueryB.

So if QueryB returns records from TableB with BIDs 2,5,7,8, I would then like to create a record in TableA with AID = 1 and Date = 8/23/13

and then make TableC appear thusly:

AID   BID
---------    
  1    2
  1    5
  1    7
  1    8

How do I do this? Accomplishing the first thing went fine until I remembered I needed a second field. Now my code reads:

Dim SQLstr As String
Dim db As DAO.Database
Set db = CurrentDb

SQLstr = "INSERT INTO TableA (FieldA, Date) "_
         & "VALUES (list13.value, Date());"

db.Execute SQLstr

where List13 is a control in the open form (I can't change the name to something useful or it will no longer control the record displayed on the rest of the form). But once I added in FieldA and list13.value, I get an error message saying

Too few parameters. Expected: 1

I'm also not sure how to describe that I want to use the new record, (in this case, AID = 1) to be the constant that all the QueryB records are associated with. After a lot of searching, I think I want to use @@IDENTITY, but I'm not sure how.

EDIT: I've finally gotten the following code to return AID properly:

Dim rs As DAO.Recordset
Dim AIDvar As Integer

Set rs = db.OpenRecordset("select @@identity")
AIDvar = rs(0)

And I was browsing over here and the following code was suggested for using INSERT INTO with a mix of field data and constants:

strText = "foo"
strInsertSQL = _
"INSERT INTO tblA(fld1, fld2, fld3) " _
& "Select fldA, fldB, '" & strText & "' AS Expr1 " _
& "From tblB ;"

So what I'm left with are the following questions:

• What's wrong with using "list13.value" in my first code? EDIT: so, I fixed that with:

  strText = CStr(List13.Value)          
  SQLstr = "INSERT INTO InvoiceT (AcctID, InvDate) "_
  & "VALUES (" & strText & ", Date());"
  

But when I put it all together with the @@IDENTITY code, I get an overflow error. How do I get around that?

• What's the significance of "foo" in that code block I copied at the end?
• What's the significance of "AS Expr1" in the same code block?
• Will it work if I replace strText with AIDvar, from the @@IDENTITY code block?
• Why does "OpenRecordset" work if TableA is not actually open? Where is my thinking going wrong that this confuses me?

Answer 1

Maybe not the shortest way to achieve what you want, but it's probably the most flexible since you can easily modify what you do (like add new fields) without having to rethink your code too much:

Public Sub DoWork()
    Dim db As DAO.Database
    Dim rsA As DAO.Recordset
    Dim newAID As Long

    Set db = CurrentDb()

    ' First, add the new record in TableA ang get its ID '
    Set rsA = db.OpenRecordset("TableA", dbOpenTable, dbFailOnError)
    If Not (rsA Is Nothing) Then
        With rsA
            .AddNew
                !FieldA = list13.Value
                !ActionDate = Now
            .Update
            ' Safe way to get the ID '
            .Move 0, .LastModified
            newAID = !ID
            .Close
        End With
        Set rsA = Nothing
    End If

    Dim rsB As DAO.Recordset
    Dim rsC As DAO.Recordset

    ' Second, open QueryB as a read-only recordset '
    Set rsB = db.OpenRecordset("QueryB", dbOpenSnapshot)
    If Not (rsB Is Nothing) Then
        ' Only proceed if QueryB returned some data '
        If rsB.RecordCount > 0 Then
            ' Open TableC so we can insert the result of QueryB '
            Set rsC = db.OpenRecordset("TableC", dbOpenTable, dbFailOnError)
            With rsC
                Do While Not rsB.EOF
                    .AddNew
                        !AID = newAID
                        !BID = rsB!ID
                        ' whatever other fields from QueryB '
                        ' that you need to insert in TableC '
                    .Update
                Loop
                .Close
            End With
            Set rsC = Nothing
        End If
        rsB.Close
        Set rsB = Nothing
    End If

    Set db = Nothing

End Sub

You could easily replace all this code with straight SQL statements but I wanted to show how you would do something like this safely with pure recordsets.
The shorter alternative using manual SQL commands would be something like:

Public Sub DoWork()
    Dim sql As String
    Dim newAID As Variant

    ' First, add the new record in TableA ang get its ID '
    sql = sql & "INSERT INTO TableA (FieldA, ActionDate) "
    sql = sql & "VALUES (" & List13.Value & ", " & CDec(Now()) & ")"

    CurrentDb().Execute sql, dbFailOnError
    newAID = CurrentDb().Execute("SELECT @@IDENTITY")(0)

    ' Second, insert QueryB into TableC '
    sql = vbNullString
    sql = sql & "INSERT INTO TableC (AID, BID) "
    sql = sql & "SELECT " & newAID & ", QueryB.ID FROM QueryB"
    CurrentDb().Execute sql, dbFailOnError
End Sub

Some comments

• If you want to get the best out of this site, make sure you have a clear vision of what you need to achieve. The fact that you changed your mind and needed to add new fields makes it look like you really have no clear idea of where you want to go. Your naming convention for tables and fields is also inconsistent within the same question.
  Settle that first. At the moment it looks like you are stabbing in the dark, hoping to pin the answer to your question.
  No-one can help you if you don't have a clear vision of your problem.

• Do not use reserved words like Date for field names. This is bound to cause confusion and it will always come back to haunt you at one point or another, causing strange errors.

• Learn to use Recordsets. They are not the answer to all problems, but they are necessary if you need to insert or perform complex queries and updates.

• Avoid constructing SQL strings by hand. As much as possible (unless the query is really trivial), use parameterised queries. Never construct a query by hand that includes data that the user typed in a textbox: at one point or another it will contain characters that will break your query (like quotes '", line returns, special characters or even evil database commands).

• SELECT @@IDENTITY is useful but you must be careful when using it: it must be run directly after the insert and there are some cases where it doesn't return what you want.