Listbox refresh issue

[Ken Warthen]

I have an Access 2007 form that contains a combobox for selecting events, two
listboxes, one with the names of contacts registered for the event selected
in the combobox, the other with a list of all contacts. I have two command
buttons; one to add a selected contact to the list of registrants for the
event, the other to remove a contact from the list of registrants. Both of
the command buttons work, but after either adding a contact or removing a
contact the listbox of registrants will not refresh without me manually
selecting the event again from the combobox. I have tried using Refresh and
Requery, but can't seem to figure out how to get this to work. Any help will
be greatly appreciated.

Ken

 

[Klatuu]

Requery the list box in the Click event of the command button, after you
have added or removed the entry.

 

[ruralguy via AccessMonster.com]

Do you have any event code in the ComboBox?

I have an Access 2007 form that contains a combobox for selecting events, two
listboxes, one with the names of contacts registered for the event selected
in the combobox, the other with a list of all contacts. I have two command
buttons; one to add a selected contact to the list of registrants for the
event, the other to remove a contact from the list of registrants. Both of
the command buttons work, but after either adding a contact or removing a
contact the listbox of registrants will not refresh without me manually
selecting the event again from the combobox. I have tried using Refresh and
Requery, but can't seem to figure out how to get this to work. Any help will
be greatly appreciated.

Ken

 

[Ken Warthen]

I do. In the AfterUpdate event I have the following. — Ken

Private Sub cboEvents_AfterUpdate()
'Populate the listbox with the names of the particpants of the selected
event.
Dim intEventSelected As Integer
Dim strEventDescription As String
Dim intContactID As Integer
Dim strFirstName As String
Dim strLastName As String
Dim strFullName As String
Dim rst As DAO.Recordset

intEventSelected = Me.cboEvents.Column(0)
strEventDescription = Me.cboEvents.Column(2)
Me.txtEventDescription.Value = strEventDescription

'Clear any records from previous event selection
Me.lstRecordedParticpants.RowSource = ""

Set rst = CurrentDb.OpenRecordset("tblEventParticipants")
With rst
.MoveFirst
Do Until .EOF
If .Fields("EventID") = intEventSelected Then
intContactID = .Fields("ContactID").Value
strFullName = fncGetContactName(intContactID)
Me.lstRecordedParticpants.AddItem (strFullName)
End If
.MoveNext
Loop
End With
Set rst = Nothing

End Sub

 

[Ken Warthen]

I have tried that without success.

Requery the list box in the Click event of the command button, after you
have added or removed the entry.

 

[ruralguy via AccessMonster.com]

How about just giving the lstRecordedParticpants ListBox a RecordSource that
lists what you want rather than using the .AddItem method of the ListBox.
Then it would probably update on it's own or at least a Requery would work.
Right now it looks like only your ComboBox knows how to fill in the ListBox.

I do. In the AfterUpdate event I have the following. — Ken

Private Sub cboEvents_AfterUpdate()
'Populate the listbox with the names of the particpants of the selected
event.
Dim intEventSelected As Integer
Dim strEventDescription As String
Dim intContactID As Integer
Dim strFirstName As String
Dim strLastName As String
Dim strFullName As String
Dim rst As DAO.Recordset

intEventSelected = Me.cboEvents.Column(0)
strEventDescription = Me.cboEvents.Column(2)
Me.txtEventDescription.Value = strEventDescription

'Clear any records from previous event selection
Me.lstRecordedParticpants.RowSource = ""

Set rst = CurrentDb.OpenRecordset("tblEventParticipants")
With rst
.MoveFirst
Do Until .EOF
If .Fields("EventID") = intEventSelected Then
intContactID = .Fields("ContactID").Value
strFullName = fncGetContactName(intContactID)
Me.lstRecordedParticpants.AddItem (strFullName)
End If
.MoveNext
Loop
End With
Set rst = Nothing

End Sub

Do you have any event code in the ComboBox?

[quoted text clipped - 10 lines]