Help ! Please - 'Object Required' - NEARLY THERE !!!

[PhilipEwen]

Hi, following on from my last post, I have partly solved the problem.....now i get 'invalid use of Null'
basically if a TreatmentID exists then it opens the form. If it doesn't exist then i want it to show an error message.

It works if a TreatmentID does exist, but comes up with 'invalid use of Null' if it doesn't. Where am i going wrong ??

Private Sub TreatmentDate_DblClick(Cancel As Integer)


On Error GoTo Err_TreatmentDate_DblClick

Dim stDocName As String
Dim stLinkCriteria As String
Dim stID As String
stID = Me![TreatmentID]

If stID = "" Then
Dim strTitle As String
Dim strMessage As String
Dim lResponse As Long
strTitle = "No Treatment History"
strMessage = "There is no history for this Customer, please Add a History " & stID
lResponse = MsgBox(strMessage, vbInformation + vbOKOnly, strTitle)
Cancel = True
'Me.Undo
Exit Sub
Else
stDocName = "Customer_Treatment_Detailed"
stLinkCriteria = "[TreatmentID]=" & Me![TreatmentID]
DoCmd.OpenForm stDocName, , , stLinkCriteria
End If

Exit_TreatmentDate_DblClick_:
Exit Sub

Err_TreatmentDate_DblClick:
MsgBox Err.Description
Resume Exit_TreatmentDate_DblClick_


End Sub

THANKS !!

 

[D-Fresh]

You didn't mention where the error was coming up but I'm assuming it's coming up on the msgbox. Why are you concatenating stID to the end of your message? If you are seeing that message, then stID must be null.. Access doesn't like outputting a null value through a messagebox. If you take that "& stID" off the end, you should be fine. Hope that helps.

Doug

 

[PhilipEwen]

No, the error is coming straight from Access not from the MsgBox.
Putting the variable at the end of the message to view it for error checking.
I have removed it but still get the same problem.
I think it may be the error trapping in 'On Error GoTo Err_TreatmentDate_DblClick' which jumps it straight out of the command process. Could this be the case - if so, can i include 'If...Then' statements in the Error processing at the bottom ???

 

[PhilipEwen]

Got it !!!
It was jumping to the error message before executing 'if...then' statements, this not processing them and supplying the wrong error message...


Thanks for all who helped though...

FYI the code is....

Private Sub TreatmentDate_DblClick(Cancel As Integer)

On Error GoTo Err_TreatmentDate_DblClick

Dim stDocName As String
Dim stLinkCriteria As String
Dim stID As String
stID = Me![TreatmentID]
'*****REMOVED FROM HERE AND ADDED IN ERROR REPORTING AT BOTTOM**************
'If stID = "" Then
'If IsNull(Me![TreatmentID]) Then
'Dim strTitle As String
'Dim strMessage As String
'Dim lResponse As Long
'strTitle = "No Treatment History"
'strMessage = "There is no history for this Customer, please Add a History "
'lResponse = MsgBox(strMessage, vbInformation + vbOKOnly, strTitle)
'Cancel = True
'''Me.Undo
'Exit Sub
'Else

'*************************************
stDocName = "Customer_Treatment_Detailed"
stLinkCriteria = "[TreatmentID]=" & Me![TreatmentID]
DoCmd.OpenForm stDocName, , , stLinkCriteria
'End If

Exit_TreatmentDate_DblClick_:
Exit Sub

Err_TreatmentDate_DblClick:
'****MOVED TO HERE INSTEAD************
If IsNull(Me![TreatmentID]) Then
Dim strTitle As String
Dim strMessage As String
Dim lResponse As Long
strTitle = "No Treatment History"
strMessage = "There is no history for this Customer, please Add a History "
lResponse = MsgBox(strMessage, vbInformation + vbOKOnly, strTitle)
Cancel = True
Else

MsgBox Err.Description
Resume Exit_TreatmentDate_DblClick_
End If
End Sub

 

[D-Fresh]

alright, I think I got it now... Replace the line "If stID = "" then" with "If isnull(stID) then". A null value is different than an empty string. When you use the empty string, the program thinks there is a value, which is why you're getting your invalid use of null. Hope that helps now...

Doug