Want to win a PS4? Go Premium and enter to win our High-Tech Treats giveaway. Enter to Win

x
?

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

Posted on 2017-10-12
10
Low Priority
?
38 Views
Last Modified: 2017-10-16
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.
0
Comment
Question by:Ummar Hathaf
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 3
  • 3
  • 2
  • +1
10 Comments
 
LVL 52

Expert Comment

by:Gustav Brock
ID: 42329540
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
 

Author Comment

by:Ummar Hathaf
ID: 42329741
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
 
LVL 52

Expert Comment

by:Gustav Brock
ID: 42329790
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
Get your Disaster Recovery as a Service basics

Disaster Recovery as a Service is one go-to solution that revolutionizes DR planning. Implementing DRaaS could be an efficient process, easily accessible to non-DR experts. Learn about monitoring, testing, executing failovers and failbacks to ensure a "healthy" DR environment.

 

Author Comment

by:Ummar Hathaf
ID: 42329832
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
 
LVL 48

Expert Comment

by:Dale Fye
ID: 42329851
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
 

Author Comment

by:Ummar Hathaf
ID: 42329900
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
 
LVL 48

Expert Comment

by:Dale Fye
ID: 42329930
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
 
LVL 39

Expert Comment

by:PatHartman
ID: 42330166
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
 
LVL 48

Expert Comment

by:Dale Fye
ID: 42330892
Always taking the easy way out, Pat!   LOL
0
 
LVL 39

Expert Comment

by:PatHartman
It's basic laziness :)
0

Featured Post

Tech or Treat!

Submit an article about your scariest tech experience—and the solution—and you’ll be automatically entered to win one of 4 fantastic tech gadgets.

Join & Write a Comment

Access custom database properties are useful for storing miscellaneous bits of information in a format that persists through database closing and reopening.  This article shows how to create and use them.
In Part II of this series, I will discuss how to identify all open instances of Excel and enumerate the workbooks, spreadsheets, and named ranges within each of those instances.
In Microsoft Access, learn the trick to repeating sub-report headings at the top of each page. The problem with sub-reports and headings: Add a dummy group to the sub report using the expression =1: Set the “Repeat Section” property of the dummy…
Visualize your data even better in Access queries. Given a date and a value, this lesson shows how to compare that value with the previous value, calculate the difference, and display a circle if the value is the same, an up triangle if it increased…

609 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question