Unanswered: Append records to MS Access table using VB
I'm working on an Access 2000 DB to track musical performances. The relevant tables and fields are:
Performance - PerfID (AutoNumber), PerfDate, SongID, GroupID, MemberID
GroupMember - GM_ID (AutoNumber), GroupID (Primary Key of Groups Table), MemberID (Primary Key of Members Table)
I have a form to enter the PerfDate, SongID and GroupID since they are the same for each performance and I'd like to append a record to the Performance table for each member of the group.
THe farthest I've gotten is:
Private Sub cmdUpdate_Click()
On Error GoTo Err_cmdUpdate_Click
Dim dbs As Database, qdf As QueryDef, strSQL As String, rst As Recordset
Dim pdate As Date group As Integer, song As Integer
Set dbs = CurrentDb
pdate = txtDate.Value
group = cmbGroup.Value
song = cmbSong.Value
strSQL = ""
For Each qdf In dbs.QueryDefs
If qdf.name = "qryGroupMembers" Then
strSQL = "SELECT GroupID,MemberID FROM GroupMember WHERE GroupID = " + group
Set qdf = dbs.CreateQueryDef("qryGroupMembers", strSQL)
Set rst = dbs.OpenRecordset("qryGroupMembers")
For Each Record In rst
. . .
Well, that's it. I can't seemd to find any help on the VB Append Method and at this point I'm not even sure if that's right solution.
I'm also open to creating a new table to temporarily store the data from my form and the data from the recordset and then using an append query to get it all into the Performance table. Unfortunately, I can't find the proper syntax for the the Create TableDef command.