Parameter query with a list/combo box plz help

[mezmerize]

Firstly a description of the situation to give you an insight. have a table of motorcycles (Registration no.,makes,models,colour,no.owners, etc. and i would like to be able to run a query that allowed me to view only bikes of one make.

i would like to know if there is way of making the parameter prompt a list/combo box contain the values within the motorcycles table so only motorcycle makes within the table can be queried (searched).

ive tried out using a form with a unbound combo box (Makecombo) and using a button to run the macro to open the query. within the query using criteria "[Forms]![Motorcycles Make]![Combo25]" how ever no matter what make i select using the combo box and then click the button the query opens and appears blank.

any help would be greatly appriciated. =D

plz reply ASAP.

[BradHodge]

Firstly a description of the situation to give you an insight. have a table of motorcycles (Registration no.,makes,models,colour,no.owners, etc. and i would like to be able to run a query that allowed me to view only bikes of one make.

i would like to know if there is way of making the parameter prompt a list/combo box contain the values within the motorcycles table so only motorcycle makes within the table can be queried (searched).

ive tried out using a form with a unbound combo box (Makecombo) and using a button to run the macro to open the query. within the query using criteria "[Forms]![Motorcycles Make]![Combo25]" how ever no matter what make i select using the combo box and then click the button the query opens and appears blank.

any help would be greatly appriciated. =D

plz reply ASAP.

If you just want to query the data, you could build the query to look through the motorcycle table. In the "make" column, put [Which Make?] in the criteria line. This will prompt the user to type in the Make that they would like to search.

Hope that helps, and Welcome to theScripts!

Brad.

[mezmerize]

hi cheers thanks for the help.

your method stated could be used but i don't want the user to be able to search for makes that aren't within the motorcycles table and i also don't want to have to type in the make to be search manually as it can lead to validation and data entry errors is there a way to put a combo box/drop down list of the values in the motorcycles table (in the motorcycles table i already have a drop down value list of makes so they do not have to be entered manually.

[BradHodge]

hi cheers thanks for the help.

your method stated could be used but i don't want the user to be able to search for makes that aren't within the motorcycles table and i also don't want to have to type in the make to be search manually as it can lead to validation and data entry errors is there a way to put a combo box/drop down list of the values in the motorcycles table (in the motorcycles table i already have a drop down value list of makes so they do not have to be entered manually.

Sure... On your form build a combo box using the wizard. Have it look for values in your query (specifically the make of motorcycle).

You could then do things one of two ways (at least :). If you just wanted to open the query, you could put the combo box control name in the criteria line of your query (such as Forms![frmBikes]![cboMakes]). Then you would have the AfterUpdate event of the combo box say...

DoCmd.OpenQuery "qryBikes"

.
If however, you wanted all your data to appear on a form, you would use the combo box to filter your form. I usually use a small form with just the combo box on it to pose the question... "What do you want to look at?". In the After Update event of the combo box, you would have something like...

DoCmd.OpenForm,,,"[Make]='" & Me.cboMakes & "'"

Then, just have your small selection form close as the main form opens.

Hope this helps. Let me know if you get stuck.

Brad.

[mezmerize]

Sure... On your form build a combo box using the wizard. Have it look for values in your query (specifically the make of motorcycle).

You could then do things one of two ways (at least :). If you just wanted to open the query, you could put the combo box control name in the criteria line of your query (such as Forms![frmBikes]![cboMakes]). Then you would have the AfterUpdate event of the combo box say...

DoCmd.OpenQuery "qryBikes"

.
If however, you wanted all your data to appear on a form, you would use the combo box to filter your form. I usually use a small form with just the combo box on it to pose the question... "What do you want to look at?". In the After Update event of the combo box, you would have something like...

DoCmd.OpenForm,,,"[Make]='" & Me.cboMakes & "'"

Then, just have your small selection form close as the main form opens.

Hope this helps. Let me know if you get stuck.

Brad.

cheers ill try that now its been buggin me all day =D

May sound dim but where do i enter:

DoCmd.OpenQuery "qryBikes"

.
im new to this indepth access coding, gone are the days of simple flat fiel database and simple sort query....!

[BradHodge]

No worries at all. Put the form that contains your combo box in design view. Then double click your combo box to open it's properties. Go to the Event Tab. On the After Update line, choose [Event Procedure], and then click the ellipsis out beside the line. This will take you to the Visual Basic Editor and will give you Private Sub..._AfterUpdate( ) and End Sub. Between that, put your DoCmd.OpenQuery "YourQueryName".

Once you have that, close the visual basic editor and then reopen your form normally, and try your combo box.

There are other things that you can do to the code to handle errors, etc, but this will at least get you going.

Let me know how it goes.

Brad.

[mezmerize]

No worries at all. Put the form that contains your combo box in design view. Then double click your combo box to open it's properties. Go to the Event Tab. On the After Update line, choose [Event Procedure], and then click the ellipsis out beside the line. This will take you to the Visual Basic Editor and will give you Private Sub..._AfterUpdate( ) and End Sub. Between that, put your DoCmd.OpenQuery "YourQueryName".

Once you have that, close the visual basic editor and then reopen your form normally, and try your combo box.

There are other things that you can do to the code to handle errors, etc, but this will at least get you going.

Let me know how it goes.

Brad.

i've followed what you said however when ever i update the combo box the query appears blank even though in the table of motrocycles i have; for example 3 "Yamaha" make bikes in the table, any clue to why this is would be a help.

thanks in advance and cheers for all the help.

Benn

[BradHodge]

i've followed what you said however when ever i update the combo box the query appears blank even though in the table of motrocycles i have; for example 3 "Yamaha" make bikes in the table, any clue to why this is would be a help.

thanks in advance and cheers for all the help.

Benn

Hey Benn,

What specifically is the row source of your Combo Box? In case you're not sure how to tell... 1) Open your form in design view. 2) Double click on your combo box to get properties. 3) Click on the Data tab, and look at the row source. If you can, copy the entire line and send it back.


Brad.

[mezmerize]

Hey Benn,

What specifically is the row source of your Combo Box? In case you're not sure how to tell... 1) Open your form in design view. 2) Double click on your combo box to get properties. 3) Click on the Data tab, and look at the row source. If you can, copy the entire line and send it back.


Brad.

Row source:

SELECT [tbl_motorcycles (stock)].[Registration No], [tbl_motorcycles (stock)].[Make] FROM [tbl_motorcycles (stock)];

[BradHodge]

Row source:

SELECT [tbl_motorcycles (stock)].[Registration No], [tbl_motorcycles (stock)].[Make] FROM [tbl_motorcycles (stock)];

Benn,

Check to make sure that your combo box has 2 columns visible with the Bound Column set to column 2 (as this should be your "Make" column). Also, make sure that your query has this criteria in the Make column... Forms![YOURFORMNAME]![YOURCOMBOBOXNAME] . When you select from the combo box, your query should then open with the make of bike you chose.

If that doesn't work, let me know, I've got some other trouble shooting thoughts.

Brad.

[mezmerize]

Benn,

Check to make sure that your combo box has 2 columns visible with the Bound Column set to column 2 (as this should be your "Make" column). Also, make sure that your query has this criteria in the Make column... Forms![YOURFORMNAME]![YOURCOMBOBOXNAME] . When you select from the combo box, your query should then open with the make of bike you chose.

If that doesn't work, let me know, I've got some other trouble shooting thoughts.

Brad.

Got it! for sum reason i had set it to look in bound column 1!
Cheers for all the help!

You don't know how much easier you've made my life. this is all for my AS level course work and it just makes validation in the testing section so much more simple to complete, 4 years of the systems life cycle based coursework using Microsoft Access and you think i would have mastered it!

Thanks for everything!

Benn

[BradHodge]

Got it! for sum reason i had set it to look in bound column 1!
Cheers for all the help!

You don't know how much easier you've made my life. this is all for my AS level course work and it just makes validation in the testing section so much more simple to complete, 4 years of the systems life cycle based coursework using Microsoft Access and you think i would have mastered it!

Thanks for everything!

Benn

No problem.

When you set-up combo boxes like that, it will usually default to column 1 being the bound column (which can often be the primary key). You have to tell it to if you want it to look elsewhere.

Glad it's working for you.

Brad.