Combo box criteria for report

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I'm having trouble trying to display a report the way I want. Hopefully you
all can help me.

I have 2 tables: Consultant Table and Skill Table.

Consultant Table has a field call Primary Skill and in the Skill Table, it
has the field containing the skill name and description.

I have a form with a combo box listing out the skills and a command button
to print out the report containing names of consultant that has the skill
selected in the combo box. how do i do that?
 
Michelle

Create a new query. Select your Consultant table, and Consultant Name.

Add the Skill field, uncheck it, and add a selection criterion that points
to the form - something like:

Forms!YourSelectionForm!cboYourSkillComboField

This should limit the query to Consultants with the Skill selected on the
form.

NOTE: the query works only when the form is open, and a skill selected.

Now, base your report on the query. If you wish, add a command button to
the form you use to select the Skill. The code behind the button opens the
report based on the query, based on the combo box.
 
I can't seem to get it to work properly. Sorry for all the troubles.

I have a table called "Australia & SE Asia" where all the details of
consultants are stored, including the Primary Skill and Secondary Skill (they
both come from the same skill list as user select them from a combo box on
the form "Data Entry - Australia & SE Asia").

I have a query titled "Skill - Australia & SE Asia" which have the fields
Primary Skills (sorted in ascending), Surname (sorted in ascending),
Firstname, Category and Secondary Skill. I used this query to display the
report titled "Skill - Australia & SE Asia".

I have this form "Select Skill - Australia & SE Asia" where it has a combo
box [SelectSkill] and a command button [PrintSkill] that has the following
code:

Option Compare Database

Private Sub PrintSkill_Click()
On Error GoTo Err_PrintSkill_Click

Dim stDocName As String

stDocName = "Skill - Australia & SE Asia"
DoCmd.OpenReport stDocName, acPreview, "", "[SkillList] = [Australia &
SE Asia]![Primary Skill]"

Exit_PrintSkill_Click:
Exit Sub

Err_PrintSkill_Click:
MsgBox Err.Description
Resume Exit_PrintSkill_Click

End Sub

The results of the report is what I wanted however, after I click on the
[PrintSkill] button, it pops up a input box requesting me to "enter parameter
value" of the SkillList. How do I make it to get the value from the combo
box? Hope you can help this damsel in distress. Thanks in advance.

Regards,
Michelle Lee
 
Michelle

If I'm reading correctly, you have a combo box named [Select Skill], but
your code seems to be opening the report with a field named [Primary Skill].

What am I missing?

Jeff Boyce
<Access MVP>
 
Hi Jeff,

The [Primary Skill] field is from the table "Australia & SE Asia". I have a
form "Data Entry - Australia & SE Asia" where there's also a combo box for
the user the input the skills into the [Primary Skill] field.

The "Select Skill - Australi & SE Asia" form is for the user to filter out
the consultants base on the [Primary Skill], which is why i put the [Select
Skill] Combo box. This combo box contains a list of all the skill type in
[Primary Skill] field.

For example, if the user click skill 20 from [Select Skill] Combo box, it
should prints out all the consultants that has skill 20 in their [Primary
Skill] field in the report.

I hope you understand what I'm saying and what I'm trying to do. Thanks very
much for your help.

Jeff Boyce said:
Michelle

If I'm reading correctly, you have a combo box named [Select Skill], but
your code seems to be opening the report with a field named [Primary Skill].

What am I missing?

Jeff Boyce
<Access MVP>

Michelle Lee said:
I can't seem to get it to work properly. Sorry for all the troubles.

I have a table called "Australia & SE Asia" where all the details of
consultants are stored, including the Primary Skill and Secondary Skill (they
both come from the same skill list as user select them from a combo box on
the form "Data Entry - Australia & SE Asia").

I have a query titled "Skill - Australia & SE Asia" which have the fields
Primary Skills (sorted in ascending), Surname (sorted in ascending),
Firstname, Category and Secondary Skill. I used this query to display the
report titled "Skill - Australia & SE Asia".

I have this form "Select Skill - Australia & SE Asia" where it has a combo
box [SelectSkill] and a command button [PrintSkill] that has the following
code:

Option Compare Database

Private Sub PrintSkill_Click()
On Error GoTo Err_PrintSkill_Click

Dim stDocName As String

stDocName = "Skill - Australia & SE Asia"
DoCmd.OpenReport stDocName, acPreview, "", "[SkillList] = [Australia &
SE Asia]![Primary Skill]"

Exit_PrintSkill_Click:
Exit Sub

Err_PrintSkill_Click:
MsgBox Err.Description
Resume Exit_PrintSkill_Click

End Sub

The results of the report is what I wanted however, after I click on the
[PrintSkill] button, it pops up a input box requesting me to "enter parameter
value" of the SkillList. How do I make it to get the value from the combo
box? Hope you can help this damsel in distress. Thanks in advance.

Regards,
Michelle Lee
Click to expand...
Click to expand...
 
Please re-read my response. Your OpenReport line of code has a "WHERE"
clause in it

DoCmd.OpenReport stDocName, acPreview, "", "[SkillList] = [Australia &
SE Asia]![Primary Skill]"

that says, essentially, find a value of [SkillList] in the report's query
that matches something called [Australia & SE Asia]![Primary Skill].

Re-check the use of this WHERE clause -- I believe it should be more like:

find a value of [Primary Skill] in the query's output that matches
Me![SkillList]

since Me![SkillList] refers to the combo box on the form that's open and
being used.


--
Good luck

Jeff Boyce
<Access MVP>
Michelle Lee said:
Hi Jeff,

The [Primary Skill] field is from the table "Australia & SE Asia". I have a
form "Data Entry - Australia & SE Asia" where there's also a combo box for
the user the input the skills into the [Primary Skill] field.

The "Select Skill - Australi & SE Asia" form is for the user to filter out
the consultants base on the [Primary Skill], which is why i put the [Select
Skill] Combo box. This combo box contains a list of all the skill type in
[Primary Skill] field.

For example, if the user click skill 20 from [Select Skill] Combo box, it
should prints out all the consultants that has skill 20 in their [Primary
Skill] field in the report.

I hope you understand what I'm saying and what I'm trying to do. Thanks very
much for your help.

Jeff Boyce said:
Michelle

If I'm reading correctly, you have a combo box named [Select Skill], but
your code seems to be opening the report with a field named [Primary Skill].

What am I missing?

Jeff Boyce
<Access MVP>

Michelle Lee said:
I can't seem to get it to work properly. Sorry for all the troubles.

I have a table called "Australia & SE Asia" where all the details of
consultants are stored, including the Primary Skill and Secondary
Click to expand...
Skill
(they
both come from the same skill list as user select them from a combo box on
the form "Data Entry - Australia & SE Asia").

I have a query titled "Skill - Australia & SE Asia" which have the fields
Primary Skills (sorted in ascending), Surname (sorted in ascending),
Firstname, Category and Secondary Skill. I used this query to display the
report titled "Skill - Australia & SE Asia".

I have this form "Select Skill - Australia & SE Asia" where it has a combo
box [SelectSkill] and a command button [PrintSkill] that has the following
code:

Option Compare Database

Private Sub PrintSkill_Click()
On Error GoTo Err_PrintSkill_Click

Dim stDocName As String

stDocName = "Skill - Australia & SE Asia"
DoCmd.OpenReport stDocName, acPreview, "", "[SkillList] = [Australia &
SE Asia]![Primary Skill]"

Exit_PrintSkill_Click:
Exit Sub

Err_PrintSkill_Click:
MsgBox Err.Description
Resume Exit_PrintSkill_Click

End Sub

The results of the report is what I wanted however, after I click on the
[PrintSkill] button, it pops up a input box requesting me to "enter parameter
value" of the SkillList. How do I make it to get the value from the combo
box? Hope you can help this damsel in distress. Thanks in advance.

Regards,
Michelle Lee
Click to expand...
Click to expand...
Click to expand...
 
Hi Jeff,

Sorry for my mistake. I now code it as:

DoCmd.OpenReport stDocName, acPreview, "", "[Primary Skill] = Me![SkillList]"

But it still pops up the message box, asking for me to "Enter Parameter
Value". If I put in the correct value, such as code "10", it will print out
the correct report that I wanted.

Probably I forgot to mention to you that the combo box have two columns.
First column lists out the codes such as , 1;2;3; and so on and the second
column displays the description of that particular code. I only need to
extract the code value as it = to [Primary Skill]

Jeff Boyce said:
Please re-read my response. Your OpenReport line of code has a "WHERE"
clause in it

DoCmd.OpenReport stDocName, acPreview, "", "[SkillList] = [Australia &
SE Asia]![Primary Skill]"

that says, essentially, find a value of [SkillList] in the report's query
that matches something called [Australia & SE Asia]![Primary Skill].

Re-check the use of this WHERE clause -- I believe it should be more like:

find a value of [Primary Skill] in the query's output that matches
Me![SkillList]

since Me![SkillList] refers to the combo box on the form that's open and
being used.


--
Good luck

Jeff Boyce
<Access MVP>
Michelle Lee said:
Hi Jeff,

The [Primary Skill] field is from the table "Australia & SE Asia". I have a
form "Data Entry - Australia & SE Asia" where there's also a combo box for
the user the input the skills into the [Primary Skill] field.

The "Select Skill - Australi & SE Asia" form is for the user to filter out
the consultants base on the [Primary Skill], which is why i put the [Select
Skill] Combo box. This combo box contains a list of all the skill type in
[Primary Skill] field.

For example, if the user click skill 20 from [Select Skill] Combo box, it
should prints out all the consultants that has skill 20 in their [Primary
Skill] field in the report.

I hope you understand what I'm saying and what I'm trying to do. Thanks very
much for your help.

Jeff Boyce said:
Michelle

If I'm reading correctly, you have a combo box named [Select Skill], but
your code seems to be opening the report with a field named [Primary Skill].

What am I missing?

Jeff Boyce
<Access MVP>

I can't seem to get it to work properly. Sorry for all the troubles.

I have a table called "Australia & SE Asia" where all the details of
consultants are stored, including the Primary Skill and Secondary Skill
(they
both come from the same skill list as user select them from a combo box on
the form "Data Entry - Australia & SE Asia").

I have a query titled "Skill - Australia & SE Asia" which have the fields
Primary Skills (sorted in ascending), Surname (sorted in ascending),
Firstname, Category and Secondary Skill. I used this query to display the
report titled "Skill - Australia & SE Asia".

I have this form "Select Skill - Australia & SE Asia" where it has a combo
box [SelectSkill] and a command button [PrintSkill] that has the following
code:

Option Compare Database

Private Sub PrintSkill_Click()
On Error GoTo Err_PrintSkill_Click

Dim stDocName As String

stDocName = "Skill - Australia & SE Asia"
DoCmd.OpenReport stDocName, acPreview, "", "[SkillList] = [Australia &
SE Asia]![Primary Skill]"

Exit_PrintSkill_Click:
Exit Sub

Err_PrintSkill_Click:
MsgBox Err.Description
Resume Exit_PrintSkill_Click

End Sub

The results of the report is what I wanted however, after I click on the
[PrintSkill] button, it pops up a input box requesting me to "enter
parameter
value" of the SkillList. How do I make it to get the value from the combo
box? Hope you can help this damsel in distress. Thanks in advance.

Regards,
Michelle Lee
Click to expand...
Click to expand...
Click to expand...
 
i got it to work. thanks for your help. below is the code i use:

Private Sub PrintSkill_Click()
On Error GoTo Err_PrintSkill_Click

selectSkill = True
strReptCriteria = ComboSkill

If IsNull(Me.ComboSkill) Then
MsgBox "You must select a skill!", vbExclamation, "Error"
ComboSkill.SetFocus
selectSkill = False
End If

If selectSkill Then

Dim stDocName As String

stDocName = "Skill - Malaysia & SE Asia"
DoCmd.OpenReport stDocName, acPreview, , "[Primary Skill] = '" &
strReptCriteria & "'"

End If

Exit_PrintSkill_Click:
Exit Sub

Err_PrintSkill_Click:
MsgBox Err.Description
Resume Exit_PrintSkill_Click

End Sub
 
Michelle

This sounds suspiciously like you've used a Lookup Field data type in your
table definition. If true, you'll want to reconsider that!

--
Good luck

Jeff Boyce
<Access MVP>

Michelle Lee said:
Hi Jeff,

Sorry for my mistake. I now code it as:

DoCmd.OpenReport stDocName, acPreview, "", "[Primary Skill] = Me![SkillList]"

But it still pops up the message box, asking for me to "Enter Parameter
Value". If I put in the correct value, such as code "10", it will print out
the correct report that I wanted.

Probably I forgot to mention to you that the combo box have two columns.
First column lists out the codes such as , 1;2;3; and so on and the second
column displays the description of that particular code. I only need to
extract the code value as it = to [Primary Skill]

Jeff Boyce said:
Please re-read my response. Your OpenReport line of code has a "WHERE"
clause in it

DoCmd.OpenReport stDocName, acPreview, "", "[SkillList] = [Australia &
SE Asia]![Primary Skill]"

that says, essentially, find a value of [SkillList] in the report's query
that matches something called [Australia & SE Asia]![Primary Skill].

Re-check the use of this WHERE clause -- I believe it should be more like:

find a value of [Primary Skill] in the query's output that matches
Me![SkillList]

since Me![SkillList] refers to the combo box on the form that's open and
being used.


--
Good luck

Jeff Boyce
<Access MVP>
Michelle Lee said:
Hi Jeff,

The [Primary Skill] field is from the table "Australia & SE Asia". I
Click to expand...
have
a
form "Data Entry - Australia & SE Asia" where there's also a combo box for
the user the input the skills into the [Primary Skill] field.

The "Select Skill - Australi & SE Asia" form is for the user to filter out
the consultants base on the [Primary Skill], which is why i put the [Select
Skill] Combo box. This combo box contains a list of all the skill type in
[Primary Skill] field.

For example, if the user click skill 20 from [Select Skill] Combo box, it
should prints out all the consultants that has skill 20 in their [Primary
Skill] field in the report.

I hope you understand what I'm saying and what I'm trying to do.
Click to expand...
Thanks
very
much for your help.

:

Michelle

If I'm reading correctly, you have a combo box named [Select Skill], but
your code seems to be opening the report with a field named [Primary Skill].

What am I missing?

Jeff Boyce
<Access MVP>

I can't seem to get it to work properly. Sorry for all the troubles.

I have a table called "Australia & SE Asia" where all the details of
consultants are stored, including the Primary Skill and Secondary Skill
(they
both come from the same skill list as user select them from a
Click to expand...
combo
box on
the form "Data Entry - Australia & SE Asia").

I have a query titled "Skill - Australia & SE Asia" which have the fields
Primary Skills (sorted in ascending), Surname (sorted in ascending),
Firstname, Category and Secondary Skill. I used this query to
Click to expand...
display
the
report titled "Skill - Australia & SE Asia".

I have this form "Select Skill - Australia & SE Asia" where it has
Click to expand...
a
combo
box [SelectSkill] and a command button [PrintSkill] that has the following
code:

Option Compare Database

Private Sub PrintSkill_Click()
On Error GoTo Err_PrintSkill_Click

Dim stDocName As String

stDocName = "Skill - Australia & SE Asia"
DoCmd.OpenReport stDocName, acPreview, "", "[SkillList] = [Australia &
SE Asia]![Primary Skill]"

Exit_PrintSkill_Click:
Exit Sub

Err_PrintSkill_Click:
MsgBox Err.Description
Resume Exit_PrintSkill_Click

End Sub

The results of the report is what I wanted however, after I click
Click to expand...
on
the
[PrintSkill] button, it pops up a input box requesting me to "enter
parameter
value" of the SkillList. How do I make it to get the value from
Click to expand...
the
combo
box? Hope you can help this damsel in distress. Thanks in advance.

Regards,
Michelle Lee
Click to expand...
Click to expand...
Click to expand...
 
Back
Top