Form to Select records without VBA

arishy

Registered User.
Local time
Today, 12:37
Joined
Aug 12, 2013
Messages
37
The table has PK and city column can be one of several.
I need to create a filtered datasheet view (few columns only)
Since there are quite a few cities, I need to select one to display all the records in that city. My questions
Since it is a datasheet view I cannot use Combo Box. What to do then ?

If I use subform with a combo box, and bind the query in the subform with the selected combo text, it does not work.

here is the subform query

SELECT tblClient.ClientName, tblClient.HqCity FROM tblClient WHERE (((tblClient.HqCity)=[Forms]![frmSearchCity]![cboCity]));

I get nothing.....
 
Create a Main Form where you can have the ComboBox that SELECT all DISTINCT cityNames from the table. And AFTER UPDATE of the ComboBox you can Filter the SubForm (the datasheet view form).
 
Hold your horses....
You mean go to event (after update ) ??? If yes ...what do I do there. The query above is in the subform....
 
Thank you for your support.
You used VBA, and I do not understand it.
Private Sub Combo2_AfterUpdate()
Me.RESULT.Form.Filter = "[App_Status]=" & Chr(34) & Me.Combo2 & Chr(34)
Me.RESULT.Form.FilterOn = True
End Sub
my Combo Box name is cboCity
my subform name is sfrmSearchCity
my main form is frmSearchCity
can you please help
 
I tried
Private Sub cboCity_AfterUpdate()
Me.sfrmSearchCity.Form.Filter = "[App_Status]=" & Chr(34) & Me.cboCity & Chr(34)
Me.sfrmSearchCity.Form.FilterOn = True
End Sub

did not work !!

Also a side remark It is only ONE TABLE your example is using TWO Tables
 
I am also using only one Table.. It has two tables but the Subform and the combo box uses only one table. Make sure your SubForm's control name is sfrmSearchCity..
 
Arias,

You most likely don't have a field named "[App_Status]=" & Chr(34) & Me.cboCity & Chr(34).

The field App_Status is what is being set as the filter. Use your field name from your select statement for your combo box instead.
 
Thanks for attempting to explain the VBA provided. And yes I do not have that. What is confusing in his VBA is using RESULT and it is a LABEL for the subform.

I opted to avoid VBA altogether and use buildin macro to requery the subform based on the query I mentioned above.
I am really surprised that ms did not provide a wizard for such simple request:

By simple, I mean a combo box that can provide criteria to select multiple records from the SAME table. Excel do it almost automatically!!!
 

Users who are viewing this thread

Back
Top Bottom