Massive Combo Box - Microsoft Access Forum


	Back to Access Forum Comments List

Upload Images @Reply Bookmark Link Email Next Unseen

Massive Combo Box

William Farrar

5 years ago

I currently have over 300 members listed in a combo box in a form. As you start typing the last name, the combo box starts filtering the potential matches making it easier to locate the member that you want. Currently every event has 90 to 120 attendees.

I would like to use a VBA subroutine; but Im not sure which of the events in the properties of the combo box to select and what to write in a VBA subroutine (2 members in each record: Attendee and Guest) that will search previous records. If the member(s) exist; a MsgBox opens asking if you would like to edit that record or erase the current selection (duplicate) in the combo box and make a new selection.

	Adam Schwanz @Reply 5 years ago
If you're wanting to pick a name in the combobox, then check if it exists and then prompt that message box, you will want to use the before/after update event, depending on how you're going to handle "erase the current selection"

Adam Schwanz @Reply

5 years ago

It's hard to tell you what to put for the code without knowing exactly what you have so I just put notes, but I would do something like this.

Dim X As Integer
Dim Y As Integer

If DCOUNT("MemberName","TableName","MemberID=" & MemberID) > 0 Then
'Exists
X = Msgbox("Customer Exists, Do you want to edit it?",vbyesno)
  If X = vbyes then
  Y = 'DLOOKUP statement to find the record
  'go to the record and edit it
  Else
  'Undo the record
  Me.Undo
  End If
Else
'doesn't already exist
'do whatever you're doing here
End If

	William Farrar OP @Reply 5 years ago
Thank you very much... I wasn't sure when the selection in the combo box was written to the record, therefore didn't know which to choose between Before / After update. I wasn't sure if after making a selection if the ID would have been written into the current record since you're leaving a search box form. All other data isn't written until you change/leave the main record.

	Adam Schwanz @Reply 5 years ago
Yea I would use before update, if it sees the duplicate it's easier to get out of it from there, otherwise you might need to "erase the current selection" with a SQL statement that deletes the record.

This thread is now CLOSED. If you wish to comment, start a NEW discussion in Access Forum.

Next Unseen

New Feature: Comment Live View