SQL Select Statement Form button On Click

[tms05]

I have a button on the form that I would like to run a simple sql select statement and I can't seem to get mine to work. I need to run one query with different criterias so I thought I would use SQL opposed to have 10 different variances of the same query. The query I have as the base query is qryReps. Each button on my form is going to run qryReps with different location codes. FOr Example button "REP ONE" I need to run qryREP where loccode = "1". Button two needs to run qryREP where loccode="4". And so forth. I would runsql but it is telling me it needs to be an action query and not a simple select query.

Thanks!!!

 

[Zathras]

You would probably get better results in one of the Access forums. I suggest you re-post your question there.

I would suggest in passing that 10 pushbuttons on a form is probably not the best user interface design. I recommend you consider using one pushbutton and one combo box, with the combo box containing the 10 possible rep codes for which you want to run a query. (You could even make the loading of the combo box dynamic from the database so when a new rep is added to the database it will appear in the combo box without you needing do anything to the code. -- The alternative would have you adding a pushbutton to the form every time a new rep is hired.)

 

[TonyJollans]

Hi tms05,

Firstly, I agree with Zathras (on both counts). Even if (you think) there will never be more than ten Reps a single combo is much neater than a lot of buttons.

Secondly, what are you trying to do with your query? If it is the source for a textbox, I would look at using DLookup. If the query is in a subform you should just need to set the Link Master field to a control with the value you want.

Enjoy,
Tony

 

[tms05]

Sorry, but I have never created a combo box on a form before. How would i go about setting that up??

 

[JCG4LOM]

You may also want to consider using a textbox on your form versus a combobox. It seems like you know already what rep number you want to run the query for. In that case a user could populate the text box with the number (1 for example). Once the user clicks the run button you can capture the textbox value as part of your SQL statement.

Good luck,
Jason