Command button does not pick the correct report.

[randria]

Hello,

I have a cmd button in a form, 1 query, 2 different reports based on the
same query. Onclick of the button, a parameter is prompted and after
supplying a value the query runs. based the value that a field called
"Service" in the query, I would like access to select the right report. Below
is my codes but the problem is that it always picks the report named "Acc
voucher4". What is wrong with my codes ?

Private Sub Command63_Click()
On Error GoTo Err_Command63_Click

Dim stDocName As String
Dim Service As Long
Service = Service ' The field that shows the value of the service in
the query

If Service = 12 Then
stDocName = "Acc Voucher2"
DoCmd.OpenReport stDocName, acPreview
Else

stDocName = "Acc Voucher4"
DoCmd.OpenReport stDocName, acViewPreview

End If

Exit_Command63_Click:
Exit Sub

Err_Command63_Click:
MsgBox Err.Description
Resume Exit_Command63_Click

End Sub

 

[Marshall Barton]

I have a cmd button in a form, 1 query, 2 different reports based on the
same query. Onclick of the button, a parameter is prompted and after
supplying a value the query runs. based the value that a field called
"Service" in the query, I would like access to select the right report. Below
is my codes but the problem is that it always picks the report named "Acc
voucher4". What is wrong with my codes ?

Private Sub Command63_Click()
On Error GoTo Err_Command63_Click

Dim stDocName As String
Dim Service As Long
Service = Service ' The field that shows the value of the service in
the query

If Service = 12 Then
stDocName = "Acc Voucher2"
DoCmd.OpenReport stDocName, acPreview
Else

stDocName = "Acc Voucher4"
DoCmd.OpenReport stDocName, acViewPreview

If you are going to use the same name for a variable and a
control/field, then you must disambiguate the control/field:

Service = Me.Service

You could avoid this kind of confusion if you used different
names for your variables.

 

[tina]

Dim Service As Long
Service = Service ' The field that shows the value of the service in the
query

the above code declares a variable Service with Long data type. then it sets
the value of Service to itself. if i recall correctly, a Long variable has a
value of zero until its' value is set to something else. in any case, it
certainly doesn't have an initial value of 12. that being the case, the next
line of code

If Service = 12 Then

will always evaluate to False, so the Else statement will always run, and
report "Acc Voucher4" will always open.

you would need to set the value of Service before the If statement runs.
entering the Service query parameter filters the query, but that value
cannot be captured in the code the way you tried to do it. suggest the
following:

1. add a textbox or combobox control to the form, that the user can enter a
Service number into. i'll call it txtService.

2. remove the parameter from the query, and replace it with the following
criteria on the Service field, as

Forms!FormName!txtService

replace FormName with the correct name of your form.

3. replace the VBA code with the following, as

Private Sub Command63_Click()

On Error GoTo Err_Command63_Click

Dim stDocName As String

If Me!txtService = 12 Then
stDocName = "Acc Voucher2"
Else
stDocName = "Acc Voucher4"
End If

DoCmd.OpenReport stDocName, acViewPreview

Exit_Command63_Click:
Exit Sub

Err_Command63_Click:
MsgBox Err.Description
Resume Exit_Command63_Click

End Sub

hth

 

[randria]

Hi Marsh, thanks for your correction, I m very new to all these, I did try to
rename the field to avoid ambiguity... but I m still stuck. I will elaborate
more in my reply to Tina.
Many thanks.

 

[randria]

Hello Tina,
Thanks for your help, your recommendation also works very well because I
have a similar set up like that using a form, hence I marked this as helpful
so that other can benefit from it.
There is a slight nuance with my case, here I do not want to use a form
because the users aren not going to add new records. the query that the
reports depend on has a field called "VchN0" and this is the field that
prompts for a parameter value, after the user entered the VchN0, the query
will show that record that has that VchN0 with the field "Service" that would
show a number. so want wish to get is that if that number on "Service" is 12
or 9 then report should be Acc Voucher2, else Acc Voucher4. I created a text
box called "ServiceTxt" in both reports, my plan was to set these Txtbox
invisible and write the codes based on the value they get from the query,
however this does not seem to work. Is there way of getting this or do I have
to use a form ?
Many thanks.

 

[tina]

okay, i understand better what you're doing, but i'd still say use a form.
forms in Access aren't just for data entry; normally they are the user
interface between the working database and the user, for whatever tasks the
user has to perform within the database. if your users have direct access to
the objects in the database such as tables, queries, etc....the thought just
makes me cringe, really.

i'd say have a textbox or combobox on a form that allows the user to enter
the VchN0 value, and make sure the query criteria uses the control reference
from that open form. then i guess i'd say use the DLookup() function in VBA
to get the value of the Service field from the query - that's assuming that
the query either A) returns only one record, or B) every record in the
query's recordset has the same value in the Service field. assign the return
value of the DLookup() function to a variable in the code, and then use the
If...Then...Else code i already posted to test the value of the variable,
instead of testing the value of the form control.

hth

 

[randria]

Hi Tina, it works , many thanks for your help.

okay, i understand better what you're doing, but i'd still say use a form.
forms in Access aren't just for data entry; normally they are the user
interface between the working database and the user, for whatever tasks the
user has to perform within the database. if your users have direct access to
the objects in the database such as tables, queries, etc....the thought just
makes me cringe, really.

i'd say have a textbox or combobox on a form that allows the user to enter
the VchN0 value, and make sure the query criteria uses the control reference
from that open form. then i guess i'd say use the DLookup() function in VBA
to get the value of the Service field from the query - that's assuming that
the query either A) returns only one record, or B) every record in the
query's recordset has the same value in the Service field. assign the return
value of the DLookup() function to a variable in the code, and then use the
If...Then...Else code i already posted to test the value of the variable,
instead of testing the value of the form control.

hth

 

[tina]

you're welcome

Hi Tina, it works , many thanks for your help.