Using a list or combo box to set the value for a report- one more

  • Thread starter Thread starter jegrau
  • Start date Start date
J

jegrau

Thank you for your response Yes this is exactly what I am trying to
accomplish. Could anyone possibly give me an example of the code to use?
--
John E. Grau
IT Specialist
Bureau of Land Managment
Miles City Field Office
Miles City MT 59301
(e-mail address removed)


Jeff Boyce said:
It may be only a matter of terminology ...

A report in Access is a design for a printed output. As such, it doesn't
have (and can't make sense of) a list of values. That said, folks often
want to run a report for a particular value.

The common approach to handling this is to use a form with a combobox that
lists possible values. After the user selects a particular value, s/he
clicks the command button you have on the form, and the code behind the
command button opens the report for the value selected on the form.

Is this what you're trying to accomplish?

Regards

Jeff Boyce
Microsoft Office/Access MVP
Click to expand...

--
John E. Grau
IT Specialist
Bureau of Land Managment
Miles City Field Office
Miles City MT 59301
(e-mail address removed)
 
OK I get the following error: Syntax error in string in query expression
'skillset='Air'

Code: DoCmd.OpenReport "skillsetlookup", acViewReport, , "skillset='" &
cbxSkillset '"
--
John E. Grau
IT Specialist
Bureau of Land Managment
Miles City Field Office
Miles City MT 59301
(e-mail address removed)


June7 via AccessMonster.com said:
Do you need the code to open the report? This example uses the WhereCondition
argument of the OpenReport method to open the report filtered to the desired
skill record. The report's RecordSource must have query of the Skills table.
(I am using aliases, use your actual names)
DoCmd.OpenReport "SkillSetReport", , , "SkillName='" & cbxSkillName &
"'"
You can place this code in AfterUpdate event of the combobox or in Click
event of a button.
Thank you for your response Yes this is exactly what I am trying to
accomplish. Could anyone possibly give me an example of the code to use?
It may be only a matter of terminology ...
Click to expand...
[quoted text clipped - 27 lines]
There are so many that people can't remember the exact skill set name to
type.
Click to expand...
Click to expand...
Click to expand...
 
You are missing an & and a quote mark at the end of the expression.

The following should all be on one line.

DoCmd.OpenReport "skillsetlookup", acViewReport, , "skillset='" &
cbxSkillset & "'"


'====================================================
John Spencer
Access MVP 2002-2005, 2007-2009
The Hilltop Institute
University of Maryland Baltimore County
'====================================================
 
Ok the code works but now the report does not take the value it still just
brings up the imput box.

Enter Parameter Value
Skillset

In my query i have skillset1 through skillset8 fields with a field
expr1:[skillset]
Criteria as [Skillset1] Or [Skillset2] Or [Skillset3] and so on.

the report works fine if you manualy input the skillset in the enter
parameter box.
--
John E. Grau
IT Specialist
Bureau of Land Managment
Miles City Field Office
Miles City MT 59301
(e-mail address removed)
 
You might be able to use this string (get rid of Skillset parameter in
the query)

"'" & cbxSkillset & " in (Skillset1,Skillset2,Skillset3,..., Skillset8)"

If that doesn't work then you might have to use a more complex expression.

"skillsetlookup", acViewReport, ,
"skillset1='" & cbxSkillset &
"' or Skillset2='" & cbxSkillset &
"' or Skillset3='" & cbxSkillset &
"' or Skillset4='"& cbxSkillset &
"' or Skillset5='" & cbxSkillset &
"' or Skillset6='" & cbxSkillset &
"' or Skillset7='" & cbxSkillset &
"' or Skillset8='" & cbxSkillset & "'"

'====================================================
John Spencer
Access MVP 2002-2005, 2007-2009
The Hilltop Institute
University of Maryland Baltimore County
'====================================================
 
Thank you so much that did the trick
--
John E. Grau
IT Specialist
Bureau of Land Managment
Miles City Field Office
Miles City MT 59301
(e-mail address removed)


John Spencer said:
You might be able to use this string (get rid of Skillset parameter in
the query)

"'" & cbxSkillset & " in (Skillset1,Skillset2,Skillset3,..., Skillset8)"

If that doesn't work then you might have to use a more complex expression.

"skillsetlookup", acViewReport, ,
"skillset1='" & cbxSkillset &
"' or Skillset2='" & cbxSkillset &
"' or Skillset3='" & cbxSkillset &
"' or Skillset4='"& cbxSkillset &
"' or Skillset5='" & cbxSkillset &
"' or Skillset6='" & cbxSkillset &
"' or Skillset7='" & cbxSkillset &
"' or Skillset8='" & cbxSkillset & "'"

'====================================================
John Spencer
Access MVP 2002-2005, 2007-2009
The Hilltop Institute
University of Maryland Baltimore County
'====================================================

Ok the code works but now the report does not take the value it still just
brings up the imput box.

Enter Parameter Value
Skillset

In my query i have skillset1 through skillset8 fields with a field
expr1:[skillset]
Criteria as [Skillset1] Or [Skillset2] Or [Skillset3] and so on.

the report works fine if you manualy input the skillset in the enter
parameter box.
Click to expand...
Click to expand...
 
Back
Top