Hi everybody I just have a quick question. I am making a database and I want to be able to have two or three combo boxes where I could select criteria and then be able to run a macro and these criteria would be used in the query that the macro runs. This means that there would not be any pop up boxes that ask you the question it would just automatically know what the criteria was. Is there any way of doing this? Thanks for the help.
Tek-Tips is the largest IT community on the Internet today!
Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!
-
Congratulations derfloh on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!
using forms to help run a query 1
- Thread starter phil009
- Start date
-
1
- #2
in your Query instead of setting the criteria as [Please enter your Name Here], you would use something like the following (assuming your Form is called frmUserInput)
Forms![frmUserInput]![txtName]
When you run your macro, the Query would pickup the Data from the Form (the form off course has to be open for this to work).
Hope this Helps,
Gavin,
Forms![frmUserInput]![txtName]
When you run your macro, the Query would pickup the Data from the Form (the form off course has to be open for this to work).
Hope this Helps,
Gavin,
Best, but probably most difficult, method of doing this is is called "querying by form"
The trouble with above mentioned method is, if you have 6 criteria, there are 64! combinations of this criteria that a user might wish to query on and it is impractical to predefine 64 queries, especially if you change the fields that you need to display and have to go through all 64 adding this field. or even worse adding an extra criteria.
lets assume you have 3 criteria
the basic idea behind "Querying by Form" is simple, you create a form with your 3 controls, one for each criteria and you have a chk box for each criteria, the chk box defines whether or not the users wishes to include the field in there search
then have a btn that looks for all the criteria where the users has checked the box, and then compiles a SQL query and outputs the data based on this SQL string.
If you give me your email i will send you an example, which was built in vb.net as a custom query builder for a database i built. The users do not have the ability to design there own queries etc so we needed an easy solution for them to get information out of the database.
its in vb.net but once you see what it does and howit does it, you should be able to knock something up in your database.
The trouble with above mentioned method is, if you have 6 criteria, there are 64! combinations of this criteria that a user might wish to query on and it is impractical to predefine 64 queries, especially if you change the fields that you need to display and have to go through all 64 adding this field. or even worse adding an extra criteria.
lets assume you have 3 criteria
the basic idea behind "Querying by Form" is simple, you create a form with your 3 controls, one for each criteria and you have a chk box for each criteria, the chk box defines whether or not the users wishes to include the field in there search
then have a btn that looks for all the criteria where the users has checked the box, and then compiles a SQL query and outputs the data based on this SQL string.
If you give me your email i will send you an example, which was built in vb.net as a custom query builder for a database i built. The users do not have the ability to design there own queries etc so we needed an easy solution for them to get information out of the database.
its in vb.net but once you see what it does and howit does it, you should be able to knock something up in your database.
- Thread starter
- #4
- Status
Similar threads
- Locked
- Question