Create and Run a query in code

[Guest]

Hi

How can I create and run a query in vba, I need to have a form with a textbox and a button so that the user, will enter a value in the text box and press the button and the query would be based on the vaule entered in the textbox. Ex., I have a table named people and a field in the table named gender, the user would type in male or female and press the button and would see all people that are either male or female

thanks

 

[Dale Fye]

Jenny,

1. First, lets address the issue of your gender text box. Why are you
doing this rather than using a combo box? Much more user friendly to use
the combo.

2. Regarding your query. What do you plan on doing with the query. Is it
the recordsource for your form? If so, you might want to consider just
setting the filter property?

Something like:

Private Sub cbo_Gender_AfterUpdate()

me.Filter = "[Gender] = " & chr$(34) & me.cbo_Gender & chr$(34)
me.FilterOn = True

end sub

This assumes that you have a field in your table called [Gender] and that is
a text field. The reason I usee chr$(34) is that it is the representation of
the quote ("), and you need that to set off text variables.

HTH

Dale

Hi

How can I create and run a query in vba, I need to have a form with a

textbox and a button so that the user, will enter a value in the text box
and press the button and the query would be based on the vaule entered in
the textbox. Ex., I have a table named people and a field in the table
named gender, the user would type in male or female and press the button and
would see all people that are either male or female.

 

[MacDermott]

If you don't have other reasons for using a form with a textbox and button,
you can put an item like this on the Criteria line of your query (under the
Gender field):
[Which Gender?]
When you run this query, an input box will pop up with the title "Which
Gender?"
The query will be filtered for whatever you type into the box.

If you do have other reasons for requiring the form - textbox - button
approach, instead of the question in brackets, you can put something like
this on the criteria line:
=Forms!MyForm!txtGender
(substituting, of course, the name of your form and textbox).

Now when the query is run, it will be filtered on whatever value is in that
textbox.
(Note that the form must be open when the query is run.)

Sorry -
no vba in any of the above solutions.

- Turtle

Hi

How can I create and run a query in vba, I need to have a form with a

textbox and a button so that the user, will enter a value in the text box
and press the button and the query would be based on the vaule entered in
the textbox. Ex., I have a table named people and a field in the table
named gender, the user would type in male or female and press the button and
would see all people that are either male or female.

 

[Guest]

Thanks MacDermott, the second one is what I was looking for, one more quick question,
How about if I wanted to store the user inputted value in a module for later use, suppose I have a module named mycodes and a public field named mygender, can I reference this field in the query the same way

I tried using =modules!mycodes!mygender and this did not work even though I tested mygender for a value with a message box? Any help would be really appreciated

Thanks

 

[MacDermott]

AFAIK, you can't reference variables directly in your queries.
However, you can write a wrapper function in a standard module:
Public Function fGender() as String
fGender=mygender
End Function
You can use =fGender() in the criteria line of your query.
(at last! a little vba! <g>)

HTH
- Turtle

Thanks MacDermott, the second one is what I was looking for, one more quick question,
How about if I wanted to store the user inputted value in a module for

later use, suppose I have a module named mycodes and a public field named
mygender, can I reference this field in the query the same way?

I tried using =modules!mycodes!mygender and this did not work even though

I tested mygender for a value with a message box? Any help would be really
appreciated.