Append many records at once

[karatelung]

i modified this code to suit my db (unsuccessfully) based on a db (MembershipV3A2K.mdb) that pat hartman posted in another thread. my intention is to be able to select multiple choices in a list box and append records with the click of a button.

here's the code:

Option Compare Database
Option Explicit

Private Sub cmdAddTraining_Click()
On Error GoTo Err_cmdAddTraining_Click

    Me.txtNotice = CreateTrainingRecords(Me.List0)
    Me.List0.Requery

Exit_cmdAddTraining_Click:
    Exit Sub

Err_cmdAddTraining_Click:
    MsgBox Err.Number & "-" & Err.Description
    Resume Exit_cmdAddTraining_Click
    
End Sub

Public Function CreateTrainingRecords(ctlRef As ListBox) As String
On Error GoTo Err_CreateTrainingRecords_Click

    Dim i As Variant
    Dim dbs As DAO.Database
    Dim rst As DAO.Recordset
    Dim qd As DAO.QueryDef
    
    Set dbs = CurrentDb
    Set qd = dbs.QueryDefs!qryAddTraining
    Set rst = qd.OpenRecordset

    For Each i In ctlRef.ItemsSelected
        rst.AddNew
        rst!IndFosterID = ctlRef.ItemData(i)
        rst!DateTraining = Me.txtAddDate
        rst!Title = Me.cmbAddTitle
        rst!Code = Me.cmbAddCode
        rst!Hrs = Me.txtAddHours
        rst.Update
    Next i
    Set rst = Nothing
    Set qd = Nothing
    CreateTrainingRecords = "Records Created"
    
Exit_CreateTrainingRecords_Click:
    Exit Function

Err_CreateTrainingRecords_Click:
    Select Case Err.Number
        Case 3022     'ignore duplicate keys
            Resume Next
        Case Else
            MsgBox Err.Number & "-" & Err.Description
            Resume Exit_CreateTrainingRecords_Click
    End Select
    
End Function

when i click the command button, i get "3219 - invalid operation." i think this is because i have a field in the tblTraining (qryAddTraining is based on tblTraining) called AutoNumber which is an AutoNumber data type.

How do i modify the code to involve the AutoNumber? i assume that i'll have to include AutoNumber in qryAddTraining. do i need to add that field on the form as well?

if i can get this feature to work, it will save someone hours of work every time these need to be entered.

i've attached pat hartman's original db to avoid confusion.

thank you.

richie

 

[Pat Hartman]

The table that this code appends records to has an autonumber primary key. You can include the autonumber in the query but DO NOT attempt to give it a value. It will be automatically populated.

 

[karatelung]

okay. it seems like that was the problem. thank you. i included AutoNumber in the query, but i got a different error:

"3622-You must use the dbSeeChanges option with OpenRecordset when accessing a SQL Server table that has an Identity."

i forgot to mention that the backend was a SQL Server 2000 DB. the Identity for Autonumber is set to "Yes", Identity Seed is "1", Identity Increment is "1"

i have no idea how to use dbSeeChanges. can someone shed some light on this?

thanks,

richie

 

[Pat Hartman]

Use dbSeeChanges as an argument of the OpenRecordset. Help will give you the syntax.

 

[vince1333]

Hi Guys,

I'm hoping someone can help me i borrowed this membership database for a uni project hope no one minds. That said, i managed to get a friend to modify it and add a image place holder on each record but now im facing some new problems which are:

1) When i update the image holder with a photo and then move to the next record it then becomes the replacement default image when i go to create another new entry. When really what i want it to do is revert to the original default image when i go to the next new record. (hope that makes sense)

2) When i update the image holder with a photo it instantly returns me to the first record in the database. I want it to stay on the current record after i update the image.

If i could solve these two problems i would be dancing in the street.

Thanks a bunch for any help you can provide.

Kind regards,
Vince

 

[georgedwilkinson]

Start a new thread.