Filtered Records as source for appending records to a table

[8l2255]

I have an inventory database with a form to create multiple items with the
same product information at once, it goes to a second form where the
individual serial numbers can be entered. The second form shows the filtered
recordset of items just created.

I want to be able to press a button on the second form and have a
transaction created in my transactiontbl using each of the itemids in the
filtered recordset.

The new transaction records should be as follows...

TransactionTbl
TransactionID -> Autonumber
Transdate -> Date ()
ItemID -> Number (foreign Key itemid from filtered recset)
UserID -> "15" (is always 15 on creation of a new item)
TransactionName -> "Initial Transaction"
Comments -> "" (can be left out - not filled out on new trans)
CurrentUser -> True (ticked checkbox)

The closest example had code similar to the following... my syntax in the
INSERT INTO or in VALUES appears to be very off!!

Private Sub Command35_Click()
Const NOCURRENTRECORD = 3021
Dim dbs As DAO.Database
Dim rst As Object
Dim lngItemID As Long
Dim strSQL As String
Set dbs = CurrentDb
Set rst = Me.Recordset.Clone
With rst
On Error Resume Next
.MoveFirst
Select Case Err.Number
Case 0
' no error
On Error GoTo 0
Do While Not .EOF
lngItemID = rst.Fields("ItemID")
strSQL = "INSERT INTO transactiontbl([ItemID], [TransDate],
[UserID], [TransactionName], [CurrentUser]) " & "VALUES( lngItemID ,
date(), 15 , 'Initial Transaction' , true )"
dbs.Execute strSQL
.MoveNext
Loop
Case NOCURRENTRECORD
MsgBox "No records match filter.", vbExclamation, "Warning"
Case Else
' unknown Error
MsgBox Err.Description, vbExclamation, "Error"
End Select
End With

End Sub


Any help welcome!

Thanks.

Lou

 

[8l2255]

Fixed my own problem (I think). Code is below.

Private Sub Command35_Click()
Const NOCURRENTRECORD = 3021
Dim dbs As DAO.Database
Dim rst As Object
Dim lngItemID As Long
Dim strSQL As String
Dim DQ As String

DQ = """"

Set dbs = CurrentDb
Set rst = Me.Recordset.Clone
With rst
On Error Resume Next
.MoveFirst
Select Case Err.Number
Case 0
' no error
On Error GoTo 0
Do While Not .EOF
lngItemID = rst.Fields("ItemID")
strSQL = "INSERT INTO transactiontbltest([ItemID], [TransDate],
[UserID], [TransactionName], [CurrentUser])" _
& "VALUES( " & DQ & lngItemID & DQ & " , " & DQ & Date & DQ & "
, 15 , 'Initial Transaction' , True);"
dbs.Execute strSQL


.MoveNext
Loop
Case NOCURRENTRECORD
MsgBox "No records match filter.", vbExclamation, "Warning"
Case Else
' unknown Error
MsgBox Err.Description, vbExclamation, "Error"
End Select
End With
DoCmd.close
End Sub