combo boxes

[PaulM]

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.

 

[Graham Mandeno]

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].

 

[PaulM]

Many thanks for the help Graham. Success first time!!!
--
Thank you for your help

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

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.