Opening a report using a combo box

[randria]

I have 2 reports, "vrpt1" and "vrpt2". I have a combo "vchTxt" that has a
dropdown list of Voucher numbers. After the user selects a value from the
list, I want access to check the value of column(4) and if that value is 12
or 9 or 5 then vrpt1 should open, else vrpt2. Can someone help me to check
what is wrong with my code because, it opens only vrpt2 even if column(4) is
12 or 9 or 5 ?

Private Sub VchTxt_AfterUpdate()
On Error GoTo Err_VchTxt_AfterUpdate

Dim stDocName As String
Dim stLinkCriteria As String
Dim i As Long


stLinkCriteria = "[VoucherN0]=" & Me![VchTxt]
i = Nz(Me.VchTxt.Column(4), 0) 'If I remove nz() then it gives error
"Invalid use of null"

If i = 12 Or i = 9 Or i = 5 Then
stDocName = "vrpt1"
DoCmd.OpenReport stDocName, acViewPreview, , stLinkCriteria
Else
stDocName = "vrpt3"
DoCmd.OpenReport stDocName, acViewPreview, , stLinkCriteria
End If

Exit_VchTxt_AfterUpdate:
Exit Sub

Err_VchTxt_AfterUpdate:
MsgBox Err.Description
Resume Exit_VchTxt_AfterUpdate
End Sub

 

[fredg]

I have 2 reports, "vrpt1" and "vrpt2". I have a combo "vchTxt" that has a
dropdown list of Voucher numbers. After the user selects a value from the
list, I want access to check the value of column(4) and if that value is 12
or 9 or 5 then vrpt1 should open, else vrpt2. Can someone help me to check
what is wrong with my code because, it opens only vrpt2 even if column(4) is
12 or 9 or 5 ?

Private Sub VchTxt_AfterUpdate()
On Error GoTo Err_VchTxt_AfterUpdate

Dim stDocName As String
Dim stLinkCriteria As String
Dim i As Long

stLinkCriteria = "[VoucherN0]=" & Me![VchTxt]
i = Nz(Me.VchTxt.Column(4), 0) 'If I remove nz() then it gives error
"Invalid use of null"

If i = 12 Or i = 9 Or i = 5 Then
stDocName = "vrpt1"
DoCmd.OpenReport stDocName, acViewPreview, , stLinkCriteria
Else
stDocName = "vrpt3"
DoCmd.OpenReport stDocName, acViewPreview, , stLinkCriteria
End If

Exit_VchTxt_AfterUpdate:
Exit Sub

Err_VchTxt_AfterUpdate:
MsgBox Err.Description
Resume Exit_VchTxt_AfterUpdate
End Sub

1) How many columns does your combo box actually have? 4?
Column(4) is actually the combo's 5th column (Combo boxes are Zero
based).
Perhaps changing the column number to Column(3) will work for you.

or....

2) Where does the value in the column come from?
Perhaps the values are Text datatype values, not Number datatype
values.
Why do you need to check for Nulls (the Nz() function)?

Try the simplified the code this way:

Private Sub VchTxt_AfterUpdate()
On Error GoTo Err_VchTxt_AfterUpdate

Dim stLinkCriteria As String

stLinkCriteria = "[VoucherN0]=" & Me![VchTxt] ' * see my note below

If Me![vchTxt].Column(4) = "12" Or Me![vchTxt].Column(4) ="9" Or
Me![vchTxt].Column(4) = "5" Then

DoCmd.OpenReport "vrpt1", acViewPreview, , stLinkCriteria
Else
DoCmd.OpenReport "vrpt3" , acViewPreview, , stLinkCriteria
End If

Exit_VchTxt_AfterUpdate:
Exit Sub

Err_VchTxt_AfterUpdate:
MsgBox Err.Description
Resume Exit_VchTxt_AfterUpdate
End Sub

* The above assumes the value in Me![VchTxt] is a Number datatype
value.
If it is a Text datatype value, then use:

stLinkCriteria = "[VoucherN0]='" & Me![VchTxt] & "'"

Change Column(4) to the correct column number.

If the 12, 5, and 9 values are indeed Long Integer values and not
Text, then use

If Me![vchTxt].Column(4) = 12 Or Me![vchTxt].Column(4) =9 Or
Me![vchTxt].Column(4) = 5 Then

 

[Ken Sheridan]

The Column property is zero-based, so Column(4) is the fifth column of the
control's RowSource. is that what you want? If it’s the fourth column then
you need to use Column(3).

Ken Sheridan
Stafford, England

 

[Ken Sheridan]

The Column property is zero-based, so Column(4) is the fifth column of the
control's RowSource. is that what you want? If it’s the fourth column then
you need to use Column(3).

Ken Sheridan
Stafford, England