Limiting the filter on a drop down list using a macro

[Guest]

I would like the selection on one drop down list to limit the list of other
drop down lists on a form using a macro. Is this possible?

 

[Guest]

This is not a macro issue. I will post a previous post

FAQ: How can I filter one combobox based on another combobox selection?

Assume you have two tables:
tblStore
StoreID (PK)
StoreName

tblManager
ManagerID (PK)
StoreID (FK - tblStore)
ManagerName

You also have a form with two comboboxes:
cboStore
RecordSource: SELECT [tblStore].[StoreID], [tblStore].[StoreName] FROM
tblStore;
cboManager
RecordSource: SELECT [tblManager].[ManagerID], [tblManager].[StoreID],
[tblManager].[ManagerName] FROM tblManager;

You only want those managers visible that are in the store that has been
selected from cboStore.

To do this you will need to modify the cboManager RecordSource in the
AfterUpdate event of cboStore:

code:
Private Sub cboStore_AfterUpdate()
Dim sManagerSource As String

sManagerSource = "SELECT [tblManager].[ManagerID],
[tblManager].[StoreID], [tblManager].[ManagerName] " & _
"FROM tblManager " & _
"WHERE [StoreID] = " & Me.cboStore.Value
Me.cboManager.RowSource = sManagerSource
Me.cboManager.Requery
End Sub



You can apply this concept of having the value of one control affect the
value of another by keeping in mind the AfterUpdate event of the first
control is where you want to take the action on the second control.

__________________
JasonM

 

[Guest]

I am trying to do something similar.

I want a user to be able to choose a value from a table and then do a query
on the main table looking for records which have the chosen value somewhere
in one of several fields. To keep it simple, I will give an example using the
Parameter field.

I would like to have the user select a value for [Param] on a form, press
the GetRecords button and display only those records which contains the
keyword chosen from the Param list which in the Resp2Comm.Param field of the
master table. So,

if the user selects CPO, the query should look for records Like "*CPO*"

Here is my attemp to perform this query:
SQL = "SELECT Resp2Comm.Param, Resp2Comm.Comment, Resp2Comm.Response FROM
resp2comm WHERE ((Resp2Comm.Param = " & Chr(34) & "Like " & Chr(34) & "*" &
Me![Param] & "*" & Chr(34) & "));"

I get a Syntax error in string in query expression '((Resp2Comm.Param =
"*CPO*"));'. error message. I've tried various alternate coding using the "
and ' characters; but do not seem to be able to resolve this error.

Thanks for any and all help with this problem.

 

[Douglas J. Steele]

You can't use both = and Like

SQL = "SELECT Resp2Comm.Param, Resp2Comm.Comment, Resp2Comm.Response FROM
resp2comm WHERE ((Resp2Comm.Param Like " & Chr(34) & "*" &
Me![Param] & "*" & Chr(34) & "));"

or. more simply

SQL = "SELECT Param, Comment, Response " & _
"FROM resp2comm " & _
"WHERE Param Like " & Chr(34) & "*" & Me![Param] & "*" & Chr(34)