Multiple Selection Listbox to Table (1 Viewer)

[BrotherBook]

Hi-

I have a form called "Record Call" when a user can input the calls that they have made or are scheduled. This form is based on the table "CallRecords". I have created a new table called "CallAttendees" and added a listbox to my form for users to select who attended/participated in the call.

My code loops through the selections and writes these selections to the "CallAttendees" table. In this table i have an auto #, CallRecord, and Attendee fields. My code is supposed to write the "ID" field from the "Call Record" table to the "CallAttendees" table so that i can create the relationship between the two tables. However, this field is coming up blank since my form isn't generating the auto # until the record is closed.

The tables are stored in a SQL server. I also tried saving the record prior to running my code, but that didn't help.

Any help to get this relationship to work would be helpful.

Mike

 

[pbaldy]

How are you saving the record? That should make the ID available to the code.

 

[BrotherBook]

After the user has selected the "Attendees" there is a button to add the attendees. The first line in this code i added DoCmd.Save before it loops through the selections and writes them down, but i still get a blank. Below is the code in it's entirety.

Private Sub AddAttendees_Click()
'Save the record to populate the "ID" field
DoCmd.Save

'add Attendess to CallAttendees table
Dim MyDB As dao.Database
Dim varItem As Variant
Dim lst As ListBox

Set lst = Me.Attendees

Dim rst As dao.Recordset

If lst.ItemsSelected.Count = 0 Then
    MsgBox "No Attendee Selected", vbExclamation, _
        "No attendees selected"
            Exit Sub
    
End If

Set MyDB = CurrentDb
Set rst = MyDB.OpenRecordset("CallAttendees", dbOpenDynaset, dbSeeChanges)

With rst
    For Each varItem In lst.ItemsSelected
        .AddNew
            ![CallRecord] = Me.ID
            ![Attendee] = lst.ItemData(varItem)
        .Update
    Next varItem
End With

rst.Close
Set rst = Nothing

DoCmd.OpenTable "CallAttendees", acViewNormal, acReadOnly
DoCmd.Maximize

Exit_cmdAddAttendees_Click:
    Exit Sub

Err_cmdAddAttendees_Click:
    MsgBox Err.Description, vbExclamation, "Error in cmdAddAttendees_Click"
    Resume Exit_cmdAddAttendees_Click
End Sub

 

[pbaldy]

DoCmd.Save saves the object (form) not the data. Try

If Me.Dirty Then Me.Dirty = False

 

[BrotherBook]

That worked. Thanks!

 

[pbaldy]

No problemo!