Using a combobox to receive parameters for a query

[samash1]

Hi,

I have a form (called 'QueryForm') with a combobox (called 'SerialNo') which displays the serial nos of different pieces hardware coming from a table called 'hardware'.

I am trying to create a query which will use the data from the combobox to run and display all the information on a particular piece of hardware using its serial no to identify it.

I have made the same query using a textbox instead of a combobox and it works fine but I don't know why it won't work with a combobox..

Any help would be greatly appreciated
Thanks,
Sam

 

[stopher]

Hi,

I have a form (called 'QueryForm') with a combobox (called 'SerialNo') which displays the serial nos of different pieces hardware coming from a table called 'hardware'.

I am trying to create a query which will use the data from the combobox to run and display all the information on a particular piece of hardware using its serial no to identify it.

I have made the same query using a textbox instead of a combobox and it works fine but I don't know why it won't work with a combobox..

Any help would be greatly appreciated
Thanks,
Sam

When you say it won't work, what do you mean exactly? Do you get an error? Or is the query not returning the right results?

How many columns are you using in your combo?

Chris

 

[samash1]

It doesn't display any results, just returns a blank table
Im only using 1 column

Sam

 

[stopher]

Could you post the query here.
Chris

 

[samash1]

Obviously this is not my real db but this query is identical to the one in my real db and only uses the one table and one form that I have included
Thanks

 

[stopher]

Your combo is currently set to 2 columns albeit the first column is 0cm and therefore invisible. So...

- Change the source for your combo to that only the Serial No field is returned.
- Set the column width to 2.54cm only (currently says 0cm;2.54cm)

hth
Chris

 

[samash1]

Yep that fixed it, thanks so much
Sam

 

[stopher]

Great.

Just for explanation, what was happening was that although the 2nd column is displayed, it's the first column that is held as the "value" i.e. HardwareID. So your query would have been using the HardwareID value as the criteria rather than the serial number.

Chris

 

[samash1]

I now have another problem.. Im trying to add other comboboxs to the form using the same method and I want to be able to select a combination of options and I want to be able to leave some of the boxes blank. It works when I only choose 1 using the 'or' function in query design but if i change it to 'and' then I will have to choose a value for all the boxes, do you know how to make a combination of the 2?

 

[samash1]

Doesnt matter, I managed to sort it out using some sql code.
Thanks
Sam

 

[Rabbie]

It is always helpful if you can post your solution. It may help the next person with his problem

 

[samash1]

I used the following code which removes the problem of an empty box.

WHERE (((Hardware.Type)=[Forms]![QueryForm]![Type] Or IsNull([Forms]![QueryForm]![Type])) AND ((Hardware.Site)=[Forms]![QueryForm]![Site] Or (IsNull([Forms]![QueryForm]![Site]))));

You can then add on additional AND statements using the same construct to add more comboboxes