combo boxes

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I am fairly new to Access. I have a table containing 250+ records of names
and addresses. I need to create combo boxes in the header on a form enabling
the user to first select surname. Where there is an instance of duplicate
surnames, I need another combo box allowing selection of firstname so
selecting the correct record. All relevant data associated with the record
will then populate the form.
 
Hi Paul

Create the surname combo first:

Name: cboSelectSurname
ControlSource: Unbound (leave blank)
RowSource: Select distinct [surname field] from [your table]
order by [surname field]
LimitToList: Yes

Leave the other properties set to their default values.

Now create the first name combo:

Name: cboSelectFirstName
ControlSource: Unbound (leave blank)
RowSource: (leave blank)
LimitToList: Yes

Now, add the following event procedure attached to the AfterUpdate event for
cboSelectSurname:

(To add an event procedure, go to the AfterUpdate line in the combo's
property sheet and type a left square bracket "[". "[Event Procedure]"
should appear. Then click the build button [...])

Private Sub cboSelectSurname_AfterUpdate()
With cboSelectFirstName
.RowSource = "Select distinct [firstname field] from [your table] " _
& "where [surname field]=""" & cboSurname _
& """ order by [firstname field]"
.Value = Null
.SetFocus
.DropDown
End With
End Sub

Now add the following AfterUpdate event procedure for cboSelectFirstName:

Private Sub cboSelectFirstName_AfterUpdate()
Me.Filter = "[surname field]=""" & cboSelectSurname _
& """ and [firstname field]=""" & cboSelectFirstName & """"
Me.FilterOn = True
End Sub

Barring typos on my part, this should do the trick. Obviously you will need
to fill in your own names in place of [firstname field], [surname field] and
[your table].
 
Many thanks for the help Graham. Success first time!!!
--
Thank you for your help


Graham Mandeno said:
Hi Paul

Create the surname combo first:

Name: cboSelectSurname
ControlSource: Unbound (leave blank)
RowSource: Select distinct [surname field] from [your table]
order by [surname field]
LimitToList: Yes

Leave the other properties set to their default values.

Now create the first name combo:

Name: cboSelectFirstName
ControlSource: Unbound (leave blank)
RowSource: (leave blank)
LimitToList: Yes

Now, add the following event procedure attached to the AfterUpdate event for
cboSelectSurname:

(To add an event procedure, go to the AfterUpdate line in the combo's
property sheet and type a left square bracket "[". "[Event Procedure]"
should appear. Then click the build button [...])

Private Sub cboSelectSurname_AfterUpdate()
With cboSelectFirstName
.RowSource = "Select distinct [firstname field] from [your table] " _
& "where [surname field]=""" & cboSurname _
& """ order by [firstname field]"
.Value = Null
.SetFocus
.DropDown
End With
End Sub

Now add the following AfterUpdate event procedure for cboSelectFirstName:

Private Sub cboSelectFirstName_AfterUpdate()
Me.Filter = "[surname field]=""" & cboSelectSurname _
& """ and [firstname field]=""" & cboSelectFirstName & """"
Me.FilterOn = True
End Sub

Barring typos on my part, this should do the trick. Obviously you will need
to fill in your own names in place of [firstname field], [surname field] and
[your table].
--
Good Luck!

Graham Mandeno [Access MVP]
Auckland, New Zealand

PaulM said:
I am fairly new to Access. I have a table containing 250+ records of names
and addresses. I need to create combo boxes in the header on a form
enabling
the user to first select surname. Where there is an instance of duplicate
surnames, I need another combo box allowing selection of firstname so
selecting the correct record. All relevant data associated with the
record
will then populate the form.
Click to expand...
Click to expand...
 
Back
Top