Combo List Data Source

[Guest]

Hello:

On my form I have:

A List Box named lstLocations.
Row Source for List Box lstLocations is this query:

SELECT Incidents.JobLocRm, Incidents.JobLocCampus, Incidents.Selected
FROM Incidents
GROUP BY Incidents.JobLocRm, Incidents.JobLocCampus, Incidents.Selected
HAVING (((Incidents.JobLocRm)=[forms]![frmReportsLocations]![OptionLetter]));

In the footer of my form I placed an option group called CommonNameFilters
and I also created a text box named OptionLetter (for the selected criteria):

it has option buttons for user to click, eg.
A B C ...

If user clicks on A - lstLocation should fill with rooms starting with A,
when he/she click on B then it should fill with room starting with B, etc.

I have on click event on the lstlocation which places a proper string in the
OptionLetter text box.

On click event:
Private Sub CommonNameFilters_Click()
Select Case CommonNameFilters
Case 1
Me.OptionLetter = "like ""A*""" 'this is my text box to hold
the criteria
Me.lstLocations.Requery

Case 2
Me.OptionLetter = "B"
Me.lstLocations.Requery
End Select
End Sub

However, the query does not work and it does not select rooms with the
selected letter criteria in the option group.

It must be more effective way. I am not getting a box filled with the
values for the criteria selected by user in the option group.

Can you assist?

 

[Guest]

Amendment:

OOps! On click event is triggerred on CommonNamesFilters Option group and
not ls lstlocations box.

Thanks

 

[Guest]

Hi, Danka.

However, the query does not work and it does not select rooms with the
selected letter criteria in the option group.

Try the following query:

SELECT JobLocRm, JobLocCampus, Selected
FROM Incidents
GROUP BY JobLocRm, JobLocCampus, Selected
HAVING (JobLocRm LIKE Forms!frmReportsLocations!OptionLetter & "*")
ORDER BY JobLocRm;

Then paste the following code into your form's module:

Private Sub CommonNameFilters_AfterUpdate()

On Error GoTo ErrHandler

Select Case Me!CommonNameFilters.Value
Case 1
Me!OptionLetter.Value = "A"
Case 2
Me!OptionLetter.Value = "B"
End Select

Me!lstLocations.Requery
Me!lstLocations.Visible = True

Exit Sub

ErrHandler:

MsgBox "Error in CommonNameFilters_AfterUpdate( ) in" & vbCrLf & _
Me.Name & " form." & vbCrLf & vbCrLf & _
"Error #" & Err.Number & vbCrLf & vbCrLf & Err.Description
Err.Clear

End Sub


Private Sub Form_Open(Cancel As Integer)

On Error GoTo ErrHandler

Me!lstLocations.Visible = False

Exit Sub

ErrHandler:

MsgBox "Error in Form_Open( ) in" & vbCrLf & _
Me.Name & " form." & vbCrLf & vbCrLf & _
"Error #" & Err.Number & vbCrLf & vbCrLf & Err.Description
Err.Clear

End Sub

Save and compile the code. Close the form. Open the form in Form View and
the list box will not be visible. As soon as you select one of the letters
in the option group, the correct list of rooms will show in the list box.
I'd recommend not setting a default value for the option group, too, so that
no option is selected when you initially open the form.

HTH.
Gunny

See http://www.QBuilt.com for all your database needs.
See http://www.Access.QBuilt.com for Microsoft Access tips and tutorials.
http://www.Access.QBuilt.com/html/expert_contributors2.html for contact info.

Hello:

On my form I have:

A List Box named lstLocations.
Row Source for List Box lstLocations is this query:

SELECT Incidents.JobLocRm, Incidents.JobLocCampus, Incidents.Selected
FROM Incidents
GROUP BY Incidents.JobLocRm, Incidents.JobLocCampus, Incidents.Selected
HAVING (((Incidents.JobLocRm)=[forms]![frmReportsLocations]![OptionLetter]));

In the footer of my form I placed an option group called CommonNameFilters
and I also created a text box named OptionLetter (for the selected criteria):

it has option buttons for user to click, eg.
A B C ...

If user clicks on A - lstLocation should fill with rooms starting with A,
when he/she click on B then it should fill with room starting with B, etc.

I have on click event on the lstlocation which places a proper string in the
OptionLetter text box.

On click event:
Private Sub CommonNameFilters_Click()
Select Case CommonNameFilters
Case 1
Me.OptionLetter = "like ""A*""" 'this is my text box to hold
the criteria
Me.lstLocations.Requery

Case 2
Me.OptionLetter = "B"
Me.lstLocations.Requery
End Select
End Sub

However, the query does not work and it does not select rooms with the
selected letter criteria in the option group.

It must be more effective way. I am not getting a box filled with the
values for the criteria selected by user in the option group.

Can you assist?