Printing different reports based on value in combo box

  • Thread starter Thread starter selgin
  • Start date Start date
S

selgin

I have a form "Individual Report" which uses two combo boxes, one to select
the company, and one to select the doctor. It works when I select company 1
but gives the error "Invalid use of Null" when i attempt to use company 2.

I would appreciate any help on this.

TIA
 
I forgot to attach the code in the previous message



Dim stDocName As String

Dim ContactMethod As Integer

Dim strCompany As Integer



ContactMethod = DLookup("[Preferred Contact Method]", "[Dr Table]",
[CHCS MSS ID] = Me.DRLOOKUP)

strCompany = DLookup("[Company]", "[Dr Table]", [CHCS MSS ID] =
Me.DRLOOKUP)



If strCompany = 1 Then

If ContactMethod = 1 Then

stDocName = "CHCS FAX INDIVIDUAL DR"

ElseIf (ContactMethod = 2 Or ContactMethod = 3) Then

stDocName = "CHCS PRINT INDIVIDUAL DR"

ElseIf ContactMethod = 4 Then

stDocName = "CHCS EMAIL INDIVIDUAL DR"

End If

End If



If strCompany = 2 Then

If ContactMethod = 1 Then

stDocName = "CHOICE FAX INDIVIDUAL DR"

ElseIf (ContactMethod = 2 Or ContactMethod = 3) Then

stDocName = "CHOICE PRINT INDIVIDUAL DR"

ElseIf ContactMethod = 4 Then

stDocName = "CHOICE EMAIL INDIVIDUAL DR"

End If

End If



DoCmd.RunMacro stDocName
 
selgin said:
I forgot to attach the code in the previous message



Dim stDocName As String

Dim ContactMethod As Integer

Dim strCompany As Integer



ContactMethod = DLookup("[Preferred Contact Method]", "[Dr
Table]", [CHCS MSS ID] = Me.DRLOOKUP)

strCompany = DLookup("[Company]", "[Dr Table]", [CHCS MSS ID] =
Me.DRLOOKUP)



If strCompany = 1 Then

If ContactMethod = 1 Then

stDocName = "CHCS FAX INDIVIDUAL DR"

ElseIf (ContactMethod = 2 Or ContactMethod = 3) Then

stDocName = "CHCS PRINT INDIVIDUAL DR"

ElseIf ContactMethod = 4 Then

stDocName = "CHCS EMAIL INDIVIDUAL DR"

End If

End If



If strCompany = 2 Then

If ContactMethod = 1 Then

stDocName = "CHOICE FAX INDIVIDUAL DR"

ElseIf (ContactMethod = 2 Or ContactMethod = 3) Then

stDocName = "CHOICE PRINT INDIVIDUAL DR"

ElseIf ContactMethod = 4 Then

stDocName = "CHOICE EMAIL INDIVIDUAL DR"

End If

End If



DoCmd.RunMacro stDocName

selgin said:
I have a form "Individual Report" which uses two combo boxes, one to
select the company, and one to select the doctor. It works when I
select company 1 but gives the error "Invalid use of Null" when i
attempt to use company 2.
Click to expand...
Click to expand...

I think there's something wrong in your lookups, whether that's the
cause of your immediate problem or not. These lines:
ContactMethod = DLookup("[Preferred Contact Method]", "[Dr
Table]", [CHCS MSS ID] = Me.DRLOOKUP)

strCompany = DLookup("[Company]", "[Dr Table]", [CHCS MSS ID] =
Me.DRLOOKUP)
Click to expand...

Are always either going to return data from the first record in the
table (where "first" is actually arbitrary), or no data at all. That's
because you don't have the criteria expression in quotes, so it will be
evaluated before passing it to the Dlookup function, and that evaluation
ig going to result in a value of either True or False (unless it raises
an error). Try these statements instead:

ContactMethod = _
DLookup("[Preferred Contact Method]", _
"[Dr Table]", _
"[CHCS MSS ID] = " & Me.DRLOOKUP)

strCompany = _
DLookup("[Company]", _
"[Dr Table]", _
"[CHCS MSS ID] = " & Me.DRLOOKUP)

Note: if [CHCS MSS ID] is a text field, you'll have to modify the above
to enclose the value from DRLOOKUP in quotes; e.g.,

"[CHCS MSS ID] = '" & Me.DRLOOKUP & "'")

As I said, I don't know whether this will cure your problem or not,
because I don't know exactly where the error is being raised, and I
don't know what's in those macros you're running. Try changing the code
as I suggested, then set a breakpoint and step through the code to see
if (a) the error goes away, or (b) the code itself is raising the error,
or (c) the error is being raised in the execution of the macro.
 
I have a form "Individual Report" which uses two combo boxes, one to select
the company, and one to select the doctor. It works when I select company 1
but gives the error "Invalid use of Null" when i attempt to use company 2.

I would appreciate any help on this.

TIA
Click to expand...
Well, we can't solve a problem that we cannot see.

Please post the relevant information - the SQL view of the Query, the
RowSource of the combo box, and the VBA code if any used to open the
form.

John W. Vinson[MVP]
 
Back
Top