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

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

"8l2255" wrote:

> 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