coding query prompts into form code of button

[Guest]

Hello all.

Currently I have a query that gives information based on region and the
region is the prompt in the query (select query). The button I have on my
form, uses an event procedure that opens that query, and obviously the user
gets the prompt, enters a region and there you go. I want to actually create
a seperate button for each region, but I dont want to create 8 queries to
match each button. Is there away in the VBA code of the buttons event
procedure to tell the prompt the information it needs? for instance the
central button would tell the query "Central" and there would be no prompt
for the user?

 

[Albert D. Kallal]

I going to suggest a slightly different approach to your problem.

First, get rid of all the prompts inside of those queries. They are hard to
write, hard to read, and if you need to use the query anywhere in report,
forms, code etc, then you get a annoying prompt.

So, remove the prompts,a nd have nice clean sql.

The next step is to make a nice form to prompt the user (and, that is
exactly what you are asking/proposing here).

It is important to note that all forms, and all reports have a feature
called the "where clause". This was designed to solve your problem. What
this feature does is allow you to pass there "where" part of your sql to any
form, or any report.

So, for your prompt form, you could have a combo box that lets the user
select what region.

And, the prompt box could even just be a text box if you do NOT have combo
list of all the regions (but, with good database design, you likely had a
combo box for entering the region anyway).

Here is what the button code on the form would look like if you had a text
box, (or combo box) called txtRegion.

Note that this text box is un-bound, and not attached to any field (and,
note that combo box can draw information and produce a "list" of regions,
but again not be attached dto any underlying table). So, these "prompt"
forms are not bound to a table.


dim strWhere as string


strWhere = "Region = '" & txtRegion & "'"

docmd.OpenReport "myReprot",,,strWhere

Or, for a form....

docmd.OpenReport "myform",,,strWhere

Here is some screen shots that uses the EXACT above idea:

http://www.members.shaw.ca/AlbertKallal/ridesrpt/ridesrpt.html

 

[Guest]

Actually im not using any sql whatsoever. I am using the simple [Enter
criteria] function in the query criteria itself. so when you start up the
query you will get this promp and it will give you all the information I have
designated. What I am wondering is since the form is based on that query, how
do I make buttons answer the criteria prompt instead of the user?