Solved

Pass a form value to query parameter

Posted on 2002-07-01
22
812 Views
Last Modified: 2012-08-14
Ongoing situation

Have a combo box on an unbound form. The form name is Parameter and the combo box name is Trainer. The combo box is linked to a lookup query. I want the value in the combo box to pass to another, parameterized, query.

In the query design page of the parameterized query I have set the following criteria: [Forms]![Parameter]![Trainer]

When I run the query, if the Parameter form is not open, I get prompted for the parameter value and the query returns the correct records. However if I use the Parameter form it does not return any records.

Thanks
0
Comment
Question by:ohgee
  • 13
  • 9
22 Comments
 
LVL 3

Expert Comment

by:YP
Comment Utility
Ohgee,

you can do it like this

Dim qd As DAO.QueryDef

Set qd = CurrentDb.QueryDef("YOURQUERY")
qd.Parameters("YOURPARAMETER") = [Forms]![Parameter]![Trainer]
qd.Execute

Good luck!

YP
0
 

Author Comment

by:ohgee
Comment Utility
Will check it out tonight. Thanks
0
 

Author Comment

by:ohgee
Comment Utility
YP
Where do I place this code?
0
 
LVL 3

Expert Comment

by:YP
Comment Utility
Ohgee,

it all depends what you want to do ...
the code I wrote above does an execute, and that's maybe not what you want.
If you want to show these records on the form, you should concatenate you sql statement
for example :
X.recordsource = "SELECT * FROM MYTABLE WHERE ID = " & [Forms]![Parameter]![Trainer]

or

you can change the sql statement of a query by (no longer a parameter query needed!)
set qd = currentdb.querydef("A_QUERY")
qd.SQL = "SELECT * FROM MYTABLE WHERE ID = " & [Forms]![Parameter]![Trainer]
qd.close
docmd.openquery "A_QUERY"

you can write this code on the afterupdate event of your combobox, or behind a button
to use DAO you will have to add a reference (Microsoft DAO X.X Object Library,if not yet done)
if ID is not numeric, don't forget to add (single) quotes

if you have any questions, just shoot, and try to give as much (interesting) information as possible, for example the Access version you are using, etc.

CU,
YP.
0
 

Author Comment

by:ohgee
Comment Utility
YP

I'm using Access 97 for this project, to record and report on in-house training, ie hours trained, trainer name, employee name, nature of training etc.

Have posted the parameter query as well as the parameter input dialogue box to http://members.rogers.com/ublizi/
The form name is Parameter and the combo box names are TeamMember and Trainer. Reports are based on the parameter query.

I thought it was possible to just make reference to the parameter form in the criteria section of the query and it would work. But the query returns no records if I use the form. However if I execute the query directly from the database window it brings up the parameter dialogue boxes and if I type values into these forms the query returns the right records. Would prefer to use the form though because the drop-down lists contain the right values - no typo's.

Thanks.
0
 

Author Comment

by:ohgee
Comment Utility
Have noticed that the date parameters are working on the form. It is just the drop down combo's that are not.
0
 
LVL 3

Expert Comment

by:YP
Comment Utility
Ohgee,

I think you have a problem with the value from your combobox. [Forms]![Parameter]![Trainer].Text may be different from [Forms]![Parameter]![Trainer].Value (is the case when you use 2 columns for your combo, and make the first one hidden). Put a breakpoint in the code where you do the assignment, and check the value of the parameter.

CU,
YP.
0
 

Author Comment

by:ohgee
Comment Utility
Thanks YP. I think you're right about the value but I'm getting lost on the coding. Do I have to code to solve this problem?
0
 

Author Comment

by:ohgee
Comment Utility
OK, have checked the value of [Forms]![Parameter]![Trainer]in the debug window and it returns 9. Same if I code [Forms]![Parameter]![Trainer].value

So it is returning the team member' primary id autonumber.
How do I get it to return text?

Thanks
0
 
LVL 3

Expert Comment

by:YP
Comment Utility
ohgee,

you can get the text by
[Forms]![Parameter]![Trainer].Text

Depending of you WHERE clause you should use the "text" or the "value" property.

If it's the "text" property you need, you should actually change the WHERE clause of your SQL statement, because it's highly recommended always to search on the primary key, your team member primary id autonumber, and not on a string (name, whatsoever). It has 2 advantages : primo, your query will be much quicker, secundo, you will never have the problem returning an other, or several, record(s) when you need only the one with your primary key.

Hope it's clear now, if not, just shoot!

YP
0
 

Author Comment

by:ohgee
Comment Utility
Thanks YP. I'm close but not there yet. Here is the sql underlying the TeamMember parameter in the parameter form. I've added a WHERE clause but do not know how to force it to return text.  

SELECT DISTINCTROW[QryParametersTeamMember].[TeamMemberLookupID], [QryParametersTeamMember].[TeamMember] FROM [QryParametersTeamMember] WHERE [QryParametersTeamMember].[TeamMember] ?????

I think I understand your point about searching on the primary key, but how ill users know what id number relates to what team member name?
0
How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

 
LVL 3

Expert Comment

by:YP
Comment Utility
oghee,

searching on the primary key :
that's the reason you should use a combo
put in your combo
row source : SELECT ID, NAME FROM YOURTABLE
column count : 2
column widths : 0;4
the users will only see the name, but you work with the ID

your sql statement should be :
if you use text (the TeamMember)
"SELECT DISTINCTROW
[QryParametersTeamMember].[TeamMemberLookupID], [QryParametersTeamMember].[TeamMember]
FROM [QryParametersTeamMember]
WHERE [QryParametersTeamMember].[TeamMember] ='" &
[Forms]![Parameter]![Trainer].Text & "'"

if you use value (the ID)
"SELECT DISTINCTROW
[QryParametersTeamMember].[TeamMemberLookupID], [QryParametersTeamMember].[TeamMember]
FROM [QryParametersTeamMember]
WHERE [QryParametersTeamMember].[TeamMemberLookupID] =" &
[Forms]![Parameter]![Trainer].Value

Mind the difference between Numeric conditions and Text conditions : string conditions need the quotes ('), numeric do not.

Good luck
YP





0
 

Author Comment

by:ohgee
Comment Utility
OK YP, I can see the logic. You are assigning the text value of the combo box to the query with the WHERE clause.
I have done this but get a syntax error in the WHERE clause. Have copied it to Word and blown it up to 200% to see the exact syntax - have copied it as you descibed. Also tried putting spaces in but no luck.

As an aside I generally do use combo boxes the way you say, ie use the primary key (hidden) and set the column widths. But I find when I do this I can see the names fine in the drop down box, but when I choose a record it reflects only one field. For example say the fields are NameID, FirstName, LastName. There are 3 columns, and column widths are 0";1";1"
If I now select a record with the drop down box it inserts only the first name, not the last. This happens even if the column widths have been set 0 1 1. Why does this happen YP?
0
 

Author Comment

by:ohgee
Comment Utility
YP
If I do use the ID solution (sounds like I should) where does the sql statement go - ?? in the row souce property of the combo box. A little ways above you said "put in your combo row source : SELECT ID, NAME FROM YOURTABLE".

Dont understand. What is SELECT ID and what is NAME FROM YOURTABLE.
Getting real confused now. Dont know enough to get myself out of the dwang.  

Thanks
0
 
LVL 3

Expert Comment

by:YP
Comment Utility
ohgee,

let's get back to the basics.
What are you trying to do? Explain me precisely, so I can answer how you can solve such a problem. What I would like to hear for example is, I want to show a form to the user and so on and so on. Tell me what you want to show in which way (form, report, query, ) after which event (what has to user have to do the see that result)

Paste your SQL statement that returns the error and so I can see what goes wrong.

To answer you question about the SELECT ID etc.
with SELECT ID, NAME FROM YOURTABLE
I mean the same as what you do
SELECT NameID, FirstName, LastName FROM Persons
ID --> NameID
NAME --> FirstName
YOURTABLE --> Persons
The reason why you only see the FirstName is because the combobox can only show 1 field
If you want to see First and LastName together, you should do it this way
SELECT NameID, FirstName & " " & LastName FROM Persons
with column widths 0;2

CU,
YP

0
 

Author Comment

by:ohgee
Comment Utility
Thanks for staying with this YP

There is a report, based on a parameterized query. There is also a form which is supposed to collect the parameter values and pass them to the report query. The form name is parameter. One of the parameters is a date range. The other two parameters are Trainer and TeamMember. Because there are many TeamMember and Trainer values it would be preferable if the user has a drop-down list to choose the Trainer and TeamMember parameters.

So I've created two queries one of which will populate the Trainer combo box control on the parameter form (QryParametersTrainer). The other query (QryParametersTeamMember) will populate the TeamMember combo box control on the parameter form.

The Trainer query is based on the Trainer table and the TeamMember query is based on the TeamMember table. The queries each have two fields. - for the Trainer query one field is TrainerID and the other is a named field, ie a concatenation of TainerFirstName and TrainerLastName. Same for TeamMember.

So I created the form to collect the parameters by simply using the combo box wizard on an unbound form. There are two combo boxes, one for TeamMember and the other for Trainer.  The combo box row source for the TeamMember data is

SELECT DISTINCTROW[QryParametersTeamMember].[TeamMemberLookupID], [QryParametersTeamMember].[TeamMember] FROM [QryParametersTeamMember];

Column Widths is 0;1 and the combo boxes appear to work fine. I select a name from the list and it goes into the combo box. All fine so far.

Now the report. It is based on the paramterized query.  This query also has named fields for Trainer and TeamMember,  In the criteria row (design window) of the named Trainer field and the named TeamMember field I placed the parameter references to the Parameter form. For the TeamMember parameter, the reference reads
[Forms]![Parameter]![TeamMember].
So what should happen is that the query goes to the Parameter form, reads the value (a  team members name) if there is one and the query should return the records for that team member.

The TeamMember combo box sql which appears to successfully insert a team member name is

DISTINCTROW[QryParametersTeamMember].[TeamMemberLookupID], [QryParametersTeamMember].[TeamMember] FROM [QryParametersTeamMember];


When I add your suggestion ie

SELECT DISTINCTROW
[QryParametersTeamMember].[TeamMemberLookupID], [QryParametersTeamMember].[TeamMember]
FROM [QryParametersTeamMember]
WHERE [QryParametersTeamMember].[TeamMemberLookupID] =" &
[Forms]![Parameter]![Trainer].Value

I get an error, ie “Syntax error in string in query expression. QryParametersTeamMember].[TeamMemberLookupID] =" & [Forms]![Parameter]![Trainer].Value”.

Thank YP
0
 
LVL 3

Accepted Solution

by:
YP earned 100 total points
Comment Utility
Ohgee,

if I understand your problem well,
you have a form that you use to open a report.

The comboboxes on your form are fine, but I wouldn't use a parameterquery in this case.
What I would do is add a button on that form to open the report on its click event.
The recordsource of you report would be like
"SELECT * FROM YOURTABLE", is showing all the records of your table, but because you don't want to see all the records, you add a wherecondition on your openreport command.
You build the WHERE clause depending on the info given by the user on your form. (you could/should write a routine for making your WHERE clause dynamic, but that is another thing)

All this together would bring you to this ...

Private Sub btnReport_Click()
Dim strWhere As String

strWhere = ""
If Not IsNull(Me!Trainer) Then
    strWhere = "TrainerID = " & Me!Trainer.Value
End If

If Not IsNull(Me!TeamMember) Then
    If Len(strWhere) <> 0 Then
    strWhere = strWhere & " AND TeamMemberID = " & Me!TeamMember.Value
    Else
        strWhere = "TeamMemberID = " & Me!TeamMember.Value
    End If
End If

DoCmd.OpenReport "TheReportName", acViewPreview, , strWhere

End Sub

You get the idea?
If not, just ask ...

Good luck!
CU,
YP
0
 

Author Comment

by:ohgee
Comment Utility
YP,  thanks for all your help. Have got the parameter query figured out. The combo box was returning a number (ID) and I had the report-query parameters in the text fields.
Need to get the db done quickly so will go with the param query now.

However do really appreciate your assistance and the code solution looks really interesting. Something I can't figure though. In the statement
strWhere = "TrainerID = " & Me!Trainer.Value
why do you assign both TrainerID and Trainer.value to StrWhere? Wouldn't TrainerID be enough if the combo box is bound to the TrainerID field?

Also, thanks for

The reason why you only see the FirstName is because the combobox can only show 1 field
If you want to see First and LastName together, you should do it this way
SELECT NameID, FirstName & " " & LastName FROM Persons
with column widths 0;2

Combo boxes work fine now.

0
 
LVL 3

Expert Comment

by:YP
Comment Utility
Ohgee,

the trick with strWhere goes like this.
strWhere holds a string,
Me!Trainer.Value holds a value, for example 9
if you write
strWhere = "TrainerID = " & Me!Trainer.Value
strWhere will hold "TrainerID = 9"
what is exactly the restriction you want !

You get the picture ?

CU,
YP
0
 

Author Comment

by:ohgee
Comment Utility
Got it, thanks. "TrainerID = 9" is a string with an assigned value. Does it not need single quotes?

One last thing before we sign off on this one. Is it possible to run part of the restriction from a parameter query and part of it from code. If I want to add more resrtictions, can I write code for these additional restrictions whilst leaving the param queries in place for the existing restrictions?
0
 
LVL 3

Expert Comment

by:YP
Comment Utility
About part1
Numbers don't need single quotes, Text does.
So if TrainerID is a number field, or autonumber
you should use
strWhere = "TrainerID = " & Me!Trainer.Value
without the quotes
if you would put a restriction on Text field, you have to use single quotes (assume Me!TrainerName exists and holds "Smith")
for example
strWhere = "TrainerName = '" & Me!TrainerName & "'"
will return "TrainerName = 'Smith'"

About part2
I never did it myself, because I wouldn't use a parameter query in this case, and I certainly would not advice somebody to use 2 different methods in 1 routine (try to be consistent, all the way) But you could test it ;-)
If you have a fixed WHERE clause, for example "KindOfPeople = 'Crazy'", you can make a query first, for example "SELECT * FROM¨Persons WHERE KindOfPeople = 'Crazy'", save this in under your queries as, for example, qryCrazyPeople, and use this query qryCrazyPeople as the recordsource of your report.
This way you can add an extra restriction without a problem, because the recordsource will become
SELECT * FROM qryCrazyPeople WHERE TrainerID = 9

CU,
YP

0
 

Author Comment

by:ohgee
Comment Utility
Very good communication at a level I can follow. Lots of useful info.
Thanks!!
0

Featured Post

6 Surprising Benefits of Threat Intelligence

All sorts of threat intelligence is available on the web. Intelligence you can learn from, and use to anticipate and prepare for future attacks.

Join & Write a Comment

Regardless of which version on MS Access you are using, one of the harder data-entry forms to create is one where most data from previous entries needs to be appended to new records, especially when there are numerous fields and records involved.  W…
Introduction The Visual Basic for Applications (VBA) language is at the heart of every application that you write. It is your key to taking Access beyond the world of wizards into a world where anything is possible. This article introduces you to…
Basics of query design. Shows you how to construct a simple query by adding tables, perform joins, defining output columns, perform sorting, and apply criteria.
In Microsoft Access, learn how to “cascade” or have the displayed data of one combo control depend upon what’s entered in another. Base the dependent combo on a query for its row source: Add a reference to the first combo on the form as criteria i…

743 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

Need Help in Real-Time?

Connect with top rated Experts

16 Experts available now in Live!

Get 1:1 Help Now