jjmongol
01-25-03, 08:24
| 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 txtDate.SetFocus pdate = txtDate.Value cmbGroup.SetFocus group = cmbGroup.Value cmbSong.SetFocus song = cmbSong.Value strSQL = "" dbs.QueryDefs.Refresh For Each qdf In dbs.QueryDefs If qdf.name = "qryGroupMembers" Then dbs.QueryDefs.Delete qdf.name End If Next qdf 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. Any help is greatly appreciated!! |