Adding "All" to a combobox

[Guest]

Hello,

I am hoping there is someone out there who will be able to help me with this
problem I'm having.

I have a form with a combobox and when a selection is made from the combo
box, all the records are displayed in a subform, specific to that selection.

e.g. when the location (Bronx, NY) is selected in the combobox (in the form
header section), all the subway stations in Bronx, NY shows up in a subform
(in the detail section of the form).

I need help adding an "ALL" selection to my combobox which, when selected
will display all the the subway stations (with there locations Bronx, NY
Queens, NY etc.).

Can anyone help me.

Thanks,
Sharda

 

[Ken Snell [MVP]]

See this article at The ACCESS Web:
http://www.mvps.org/access/forms/frm0043.htm

 

[Guest]

Thanks, but I don't think thats helping me.

The location field is the only field in the combobox, which is from a table
where it is the primary key.

Thanks.

 

[Ken Snell [MVP]]

Assuming that you're using the subform control's LinkChildFields and
LinkMasterFields to tie the subform to the combo box, then you are correct
that this article won't give you that result.

However, you can use the article to get the "ALL" to show in the combo box.
Then, you'll need to change the setup of your form.

Delete the Link properties from the subform control.

Put code on the AfterUpdate event of the combo box that will create the
actual recordsource to be used by the subform, and will set the subform to
that recordsource. Something like this (note: SubformName in this generic
code is the name of the subform control -- that is, the control on the main
form that holds the subform):

Private Sub ComboboxName_AfterUpdate()
Dim strSQL As String
strSQL = ""
If Len(Me.ComboboxName.Value & "") > 0 Then
strSQL = "SELECT * FROM Tablename"
If Me.ComboboxName.Value <> "ALL" Then
strSQL = strSQL & " WHERE LocationField = '" & _
Me.ComboboxName.Value & "'"
End If
strSQL = strSQL & " ORDER BY LocationField;"
End If
Me.SubformName.Form.RecordSource = strSQL
End Sub

--

Ken Snell
<MS ACCESS MVP>