How to autofill query parameter using on click event of a command button?

Dear All,

I have 17 more command boxes in a single form showing different route names. When user is clicking a particular command button, the query parameter should be auto filled with some fixed data. I am in need to auto fill the Route_ID query parameter for every command button once it has been clicked or opened. There are 17 different routes and when user chooses the particular route by clicking the command button, the query's parameter behind that will be auto fill with that Route_ID.

I need the VBA code for that. Thank you.
Ummar HathafData Analyst EngineerAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Gustav BrockCIOCommented:
This expert suggested creating a Gigs project.
For that many controls, instead of writing a bunch of duplicate code, you would use WithEvents.  Study an example here:

Create Windows Phone Colour Palette and Selector using WithEvents

Or you could open a project in Gigs.

/gustav
0
Ummar HathafData Analyst EngineerAuthor Commented:
Dear Mr. Gustav,

Thanks for your reply. Actually, I need to know the VBA code for to pass the parameter value to a query by clicking a particular command button in a form. I have a form with 17 more command buttons and when the user chooses the particular command button, it opens an other form for data entry in where I have a combo box which asks for parameter value. I want this parameter value to be filled as "1" or "2" or "17" depending upon which command button that the user has choosed.

Kindly help me in this regard. Thank you.
0
Gustav BrockCIOCommented:
Could be:

Dim qdf As DAO.QueryDef
Dim Button As Control

Dim Value As Variant

Set qdf = CurrentDb.QueryDefs("YourQuery")
Set Button = Me!YourButton

Select Case Button.Name
    Case "SomeButton"
        Value = SomeValue
    Case "SomeOtherButton"
        Value = SomeOtherValue
    Case "AnotherButton"
        Value = YetAnotherValue
End Select

qdf.Parameters(0).Value = Value
' Open or run query.

Open in new window

/gustav
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
10 Tips to Protect Your Business from Ransomware

Did you know that ransomware is the most widespread, destructive malware in the world today? It accounts for 39% of all security breaches, with ransomware gangsters projected to make $11.5B in profits from online extortion by 2019.

Ummar HathafData Analyst EngineerAuthor Commented:
Dear Mr. Gustav,

Many Thanks for your reply again. I don't know where to use those VBA codes in my database. I have uploaded my database in the Google Drive and shared the link below.

Need Resolution

In the above attached database, I have two forms named "Department Forms - Report" and "Customer_Transaction_Form".  On the "Department Forms - Report", there are seventeen Route names mentioned on seventeen command buttons. Once the user click the first command button, the "Customer_Transaction_Form" will open in where there are two combo boxes, one for Route_ID and an other for "Customer_Name". The Route_ID parameter value has to be filled automatically as "1", if the user choose the first command button. How can I make this. Can you do this for me? I will be so thankful to you in this regard.
0
Dale FyeCommented:
on my iPad, so cannot access your database, but on question first.

1.  Do you want to run the query after you have populated that query parameter?

I would create a single function in the code behind your form, and use code similar to what Gustav provided:

Private Function RunRouteQuery(RouteNum as integer)

    Dim qdf As DAO.QueryDef

    Set qdf = CurrentDb.QueryDefs("YourQuery")
    qdf.Parameters(0).Value = Value
    qdf.Execute dbfailonerror
    set qdf = nothing 

End Function

Open in new window

Then, in the Click event of each of the buttons, enter code that looks like:button clickjust remember to change the number inside the function call to coincide with the number on the button.
0
Ummar HathafData Analyst EngineerAuthor Commented:
Dear Mr. Dale Fye,

Thanks for your valuable reply. Actually, I have two different forms in my database. One form is having all the seventeen (17) route names which has been mentioned on the command button and an other form is for the customer's sales transaction data entry. Once the user has chosen the particular route, the sales transaction data entry form will be open in where there are two combo boxes for Route_ID & Customer_ID. The Route_ID combo box query will be asking the parameter value once the user has chosen the particular command button and I want it to be auto filled by some number according to which command button has been chosen. If you could have a look at my database, you will come to know the problem which I am facing. Thank you.
0
Dale FyeCommented:
Sorry, I don't have access to the database right now.

In that case, rather than filling in the parameter of a query, which would be useless in this case, I would create a tempvar.  TempVars are actually a collection of variables which can be used anywhere in your application and which do not lose their values when an unhandled error is encountered (like private, public, and global variables).

To set the value of the TempVar, you simply need something like:

Tempvars!RouteID = 1

I have a function I use:
Public Function fnTempvars(VariableName as string, Optional VariableValue as Variant = Null) as Variant

    if NOT IsNull(VariableValue) then Tempvars("VariableName") = VariableValue

    fnTempvars = Tempvars!VariableName

End Function

Open in new window

With this function I can set the value of a Tempvar:
fntempvar "RouteID", 1

Open in new window

or retrieve it:
lngRouteID = fnTempvars("RouteID")

Open in new window

and can even use it within a query:
SELECT * FROM yourTable WHERE [RouteID] = fnTempvars("RouteID")

Open in new window

An advantage of this is that I can even set the values of tempvars in the immediate window and run a query or report which requires the value.  Or, in the case of your second form, you could set the value of the textbox or combo box to that value:
me.txtRouteID = fntempvars("RouteID")

Open in new window

which could also be written:
me.txtRouteID = tempvars!RouteID

Open in new window

or
me.txtRouteID = tempvars("RouteID")

Open in new window

HTH
Dale
0
PatHartmanCommented:
And then there's the option of normalizing the data.  Instead of having 17 command buttons each with individual code which will require form/code changes if you need to add an 18th, why not use a combo or listbox and ONE command button?  

Create a table that holds two values.  What you want to display (route name) and what you want to save (routeID).  If both are the same, then the table only needs a single column.  Use this table as the RowSource for a combo or listbox.  The control you use depends on how you want this to look visually and either will work if you can select only a single value at one time.  However, if you want the ability to select multiple values, you would need to use a listbox.  That way, your single button needs one line of code to open the form and pass in the value of the route as the openarg.  Then the form that you are opening can reference the openarg when it needs to get the route value.

Docmd.OpenForm "yourformname", , , , , acWindowNormal, Me.cboRouteID

Then in the form you opened, reference the openargs property

Me.RouteID = Me.OpenArgs

I can't tell exactly where you will need to put this code since I don't understand your explanation.
0
Dale FyeCommented:
Always taking the easy way out, Pat!   LOL
0
PatHartmanCommented:
It's basic laziness :)
0
Gustav BrockCIOCommented:
Solutions provided.
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Access

From novice to tech pro — start learning today.