Pass a memory variable from form to query

[birdjfk]

I'm setting up a form whereby the user will click on a letter(A-Z) on a form. The form will then pass the memory variable (mletter) to a query which will display all the matches for last name beginning with mletter.
How do I pass mletter? I would appreciate any help. Thanks.

 

[scriverb]

You can do this two ways:

First, you can directly reference the forms control for the particular letter:

WHERE Left([tblYourTable]![FieldName],1) = FORMS![frmYourFormName]![LetterControlName]

Secondly, you can create a global variable and a function to call up the global variable value:
DATABASE MODULE:
Global vLtrSelect as String
Function LtrSelect()
LtrSelect = vLtrSelect
End Function

Now in the forms On click of the control for the letter load the letter value to the global variable:
vLtrSelect = Me![LtrControl]

Now the query can reference the selected letter by calling the function:
WHERE Left([tblYourTable]![FieldName],1) = LtrSelect()

Just update the above examples with your table, field, and control names. Let me know if you need more assistance with this process.

Bob Scriver

Nobody believes the official spokesman... but everybody trusts an unidentified source.
Author, Bagdad Bob???

 

[ReluctantDataGuy]

It depends... as it usually does.

If each "letter" on the form is a button, then you can place a text box on the form. Make each button set the value of the text box to the appropriate letter plus the asterisk symbol (ie: A* or B*, etc), then open the query.

Kind of like this:

Private Sub cmdA_Click()
Me.Text6 = "A*"
DoCmd.OpenQuery "Query2"
End Sub

In the query's criteria line, add the criteria. Like this with appropriate modifications for your setup:

Like Forms![Form2].[Text6]

Notice there's no variable (mletter)required here.

There are lots of other ways to do this. If you have a form based on either the table or a query, you can open the form and specify the criteria in the Open command rather than in the query.

For example the code in the command button [cmdA] could be:

DoCmd.OpenForm "MyResultForm",,,"[Description] Like 'A*'"

cmdB would be the same except at the end:

DoCmd.OpenForm "MyResultForm",,,"[Description] Like 'B*'"

and so on...

The nice thing about opening a form instead of a query is that you have a lot more control over look, feel, and actions the user can take.

Hope that helps.

 

[birdjfk]

First of all, I want to thank both of you for replying to my post. After a lot of "fooling around" I was able to get the results I needed by setting up a command button on a form and adding: DoCmd.OpenForm "MyResultForm",,,"[Description] Like 'A*'"
on the On Click
Once again...thank you both, I probably would still be spinning my wheels if not for your direction.

Julie