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

    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
    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!!

  2. #2
    Join Date
    Jan 2003
    Location
    Dordrecht, The Netherlands
    Posts
    95

    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.Edit
    rst.Fields("Field1")= 'SomeValue'
    rst.Update
    rst.MoveNext
    Loop

    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
  •