Drop down box for query

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I have a query that has two discriminators. The first one is easy, it just
asks if the report is open or not, but the second one asks for the contract
number. I have multiple contracts and they are detailed in name, example:
N00012-04-X-0011. That's obviously too much to type and if someone misses a
dash or a zero the query won't come up with anything. So I was wondering if
there was a way to make the query question in the begining to have a drop
down box that has a list of all the contracts so the user can just highlight
the one they want? Is this possible and is it difficult?
 
I have a query that has two discriminators. The first one is easy, it just
asks if the report is open or not, but the second one asks for the contract
number. I have multiple contracts and they are detailed in name, example:
N00012-04-X-0011. That's obviously too much to type and if someone misses a
dash or a zero the query won't come up with anything. So I was wondering if
there was a way to make the query question in the begining to have a drop
down box that has a list of all the contracts so the user can just highlight
the one they want? Is this possible and is it difficult?
Click to expand...

Sure. Create a little unbound Form named frmCrit, with a Combo Box
cboContractID, selecting all the valid contracts.

Use a criterion

=[Forms]![frmCrit]![cboContractID]

in your Query. Put another suitable control on frmCrit for the other
criterion (a checkbox maybe?).

It's convenient to base a Form and/or a Report on the query, and put a
button on frmCrit to open that form or report.

John W. Vinson[MVP]
 
Alright, I've got my frmCrit established. Couple things I'm not clear about,
the cboContractID, what does the cbo mean. Also, when I plug that expression
in, it's supposed to go under my contractid filed on my orginal query right?
Thanks for the help.


John Vinson said:
I have a query that has two discriminators. The first one is easy, it just
asks if the report is open or not, but the second one asks for the contract
number. I have multiple contracts and they are detailed in name, example:
N00012-04-X-0011. That's obviously too much to type and if someone misses a
dash or a zero the query won't come up with anything. So I was wondering if
there was a way to make the query question in the begining to have a drop
down box that has a list of all the contracts so the user can just highlight
the one they want? Is this possible and is it difficult?
Click to expand...

Sure. Create a little unbound Form named frmCrit, with a Combo Box
cboContractID, selecting all the valid contracts.

Use a criterion

=[Forms]![frmCrit]![cboContractID]

in your Query. Put another suitable control on frmCrit for the other
criterion (a checkbox maybe?).

It's convenient to base a Form and/or a Report on the query, and put a
button on frmCrit to open that form or report.

John W. Vinson[MVP]
Click to expand...
 
Alright, I've got my frmCrit established. Couple things I'm not clear about,
the cboContractID, what does the cbo mean.
Click to expand...

cboContractID is the Name property of the combo box. If you name the
combo box ContractID, use ContractID; if you name the combo box Fred,
use Fred.
Also, when I plug that expression
in, it's supposed to go under my contractid filed on my orginal query right?
Click to expand...

I presume so, but since I have no idea what the fieldnames are in your
query I can't be certain. Put it under whatever field you want to
search.

John W. Vinson[MVP]
 
Thanks a bunch. It works.

John Vinson said:
cboContractID is the Name property of the combo box. If you name the
combo box ContractID, use ContractID; if you name the combo box Fred,
use Fred.


I presume so, but since I have no idea what the fieldnames are in your
query I can't be certain. Put it under whatever field you want to
search.

John W. Vinson[MVP]
Click to expand...
 
I must have clicked something I shouldn't have because now it doesn't work.
I put the expression =[Forms]![frmCrit]![cboContractNumber] into the criteria
section for ContractNumber in my query but it doesn't recognize it. All I
get is the original dialog box I was using to begin with asking me what
contract. I assume that the expression is supposed to call the form to open
so you can pick the contract you want, but for some reason I can't get it to
work. Any further assistance would be greatly appreciated.
 
I must have clicked something I shouldn't have because now it doesn't work.
I put the expression =[Forms]![frmCrit]![cboContractNumber] into the criteria
section for ContractNumber in my query but it doesn't recognize it. All I
get is the original dialog box I was using to begin with asking me what
contract. I assume that the expression is supposed to call the form to open
so you can pick the contract you want, but for some reason I can't get it to
work. Any further assistance would be greatly appreciated.
Click to expand...

The Query doesn't open the form for you: you need to open frmCrit
first. That's why it's convenient to put a command button on frmCrit
to launch the Report or Form that's based on the query (you shouldn't
ordinarily need to open the query datasheet itself).

John W. Vinson[MVP]
 
Okay, I see. So what you're saying is the contract list form frmCrit would
have to be opened first, the user picks a contract and then clicks the
command button to open query and the contract data is filled in
automatically. And since opening the datasheet for the query is a bad idea,
i can just have the command button open the form that represents my query?
Click to expand...

Exactly. The user doesn't need to see the query datasheet; they need
to see the *information* returned by the query, preferably in a nicely
arranged, formatted, readable Form. So base your Form on the query;
have the user select the contract using the combo box; and then -
either using a command button or the AfterUpdate event of the combo -
open the form.

John W. Vinson[MVP]
 
Back
Top