passing multiple criteria to an append query from a text box

[WalkieTalkie]

I have a list box populated by a series of dates. The user (multi)selects the dates they want to book, and then click a command button "Make Booking". I want the command button to run an append query, which will append the selected dates (and other info related to the booking) into a table - tblTransaction.

The way I have gone about this may seem rather convoluted, due to my inexperience, but its the only way I can think of within my capability: I have made a hidden text box on the form, and when the user clicks on the 'Make Booking' command button, the IDs of the selected dates are put into the hidden text box. Then, the append query which runs, has the hidden text box text as its date criteria.

Here is the code behind the command button:

 Private Sub cmdMakeBooking_Click()

Dim varItem As Variant
Dim txtTemp As String

For Each varItem In Me.lstDate.ItemsSelected
txtTemp = txtTemp & Me.lstDate.ItemData(varItem) & " Or "

Next
txtTemp = Left(txtTemp, Len(txtTemp) - 4)

Me.HiddenControl = txtTemp

DoCmd.OpenQuery "qryMakeBookings"

End Sub

And here is the append query "qryMakeBookings":

INSERT INTO tblTransaction ( TransDate, KidAccountID, AttendStatus, Amount )
SELECT tblDate.Date, tblKidAccount.KidAccountID, "1" AS AttendStatus, "0.00" AS Amount
FROM tblDate, tblKidAccount
WHERE (((tblKidAccount.KidAccountID)=[Forms]![frmKids05]![KidAccountID]) AND ((tblDate.ID)=[Forms]![frmKids05]![HiddenControl]));

I can make it work as long as there is only one date, but when the user selects more than one date, the query returns no records.

What can I do to sort this out?

 

[dhookom]

Your query criteria will not resolve like that. You need to update the query SQL property or just build the entire query in code and execute it.
I would try something like this assuming the Amount & KidAccountID are numeric and AttendStatus is text:

Private Sub cmdMakeBooking_Click()

Dim varItem As Variant
Dim txtTemp As String
Dim strSQL as String

For Each varItem In Me.lstDate.ItemsSelected
txtTemp = txtTemp & Me.lstDate.ItemData(varItem) & ","

Next
txtTemp = Left(txtTemp, Len(txtTemp) - 1)

strSQL = "INSERT INTO tblTransaction ( TransDate, KidAccountID, AttendStatus, Amount ) " & _
"SELECT tblDate.Date, " & Me![KidAccountID] & ", '1' , 0.00 " & _
"FROM tblDate " & _
" WHERE ID IN (" & txtTemp & ")"
Debug.Print strSQL  'for troubleshooting
CurrentDb.Execute strSQL, dbFailOnError

End Sub

Duane
Hook'D on Access
MS Access MVP