Results 1 to 2 of 2
  1. #1
    Join Date
    Jan 2003

    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 = "qryGroupMembers" Then
    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!!

  2. #2
    Join Date
    Jan 2003
    Dordrecht, The Netherlands

    Re: Append records to MS Access table using VB

    In general, if you want to update a field 'Field1', e.g. for the whole recordset, you have to loop through the recordset like this:

    Do Until rst.EOF
    rst.Fields("Field1")= 'SomeValue'

    Hope this helps

    Ad Dieleman

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts