Combo box criteria for report

[Michelle Lee]

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?

 

[Jeff Boyce]

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.

 

[Michelle Lee]

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

 

[Jeff Boyce]

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]

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.

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

 

[Jeff Boyce]

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>

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.

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

 

[Michelle Lee]

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]

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>

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.

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

 

[Michelle Lee]

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

 

[Jeff Boyce]

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>

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]

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>

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.

:

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