Ubiquity-Designs
asked on
Access 2010 Invalid use of Null
I am having a bit of a logic problem I have an AfterUpdate event to populate a Text box if a record exists. When I run the code it works fine if the record does exist. However if the record does not exist I get an Invalid use if Null error. The Debugger highlights this line.
Here is the rest of the code.
workCompleted = rs2.Fields("workCompleted") 'Chooses the workCompleted FieldHere is the rest of the code.
Private Sub txtTicketNum_AfterUpdate()
Dim db As Database
Dim rs As DAO.Recordset
Dim rs2 As DAO.Recordset
Dim s As String
Dim s2 As String
Dim repairDayDate As String
Dim ticketNum As Integer
Dim fname As String
Dim lname As String
Dim phone As String
Dim pickedUp As String
Dim compType As String
Dim serialNum As String
Dim workCompleted As String
Set db = CurrentDb
s = "SELECT ticketNum, repairDayDate, computerType, serialNum, phoneNum, firstName, lastname From tblTickets WHERE ticketNum = " & [Forms]![frmWorkTicket]![txtTicketNum]
Set rs = db.OpenRecordset(s)
repairDayDate = rs.Fields("repairDayDate") 'Chooses the repairDayDate Field
compType = rs.Fields("computerType") 'Chooses the computerType Field
serialNum = rs.Fields("serialNum") 'Chooses the serialNum Field
phone = rs.Fields("phoneNum") 'Chooses the phoneNum Field
fname = rs.Fields("firstName") 'Chooses the firstName Field
lname = rs.Fields("lastName") 'Chooses the lastName Field
s2 = "SELECT workCompleted From tblTickets WHERE ticketNum = " & [Forms]![frmWorkTicket]![txtTicketNum]
Set rs2 = db.OpenRecordset(s2)
If rs2.RecordCount <> 0 Then
workCompleted = rs2.Fields("workCompleted") 'Chooses the workCompleted Field
[Forms]![frmWorkTicket]![txtWorkCompleted] = workCompleted
rs2.Close
Set rs2 = Nothing
End If
[Forms]![frmWorkTicket]![txtRepairDayDate] = repairDayDate
[Forms]![frmWorkTicket]![txtFirstName] = fname
[Forms]![frmWorkTicket]![txtLastName] = lname
[Forms]![frmWorkTicket]![txtPhoneNum] = phone
[Forms]![frmWorkTicket]![txtPickedUp] = pickedUp
[Forms]![frmWorkTicket]![cboComputerType] = compType
[Forms]![frmWorkTicket]![txtSerialNum] = serialNum
End SubASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
That did the trick. However I thought I had covered that situation with the If statement
If rs2.RecordCount <> 0 Then
Does that not mean if the record count is not 0 then finish the If statement?
If rs2.RecordCount <> 0 Then
Does that not mean if the record count is not 0 then finish the If statement?
The most reliable test is:
If rs2.RecordCount = 0 Then
' whatever
Else
' whatever else
End If
mx
If rs2.RecordCount = 0 Then
' whatever
Else
' whatever else
End If
mx
<< Does that not mean if the record count is not 0 then finish the If statement? >>
Your original rs2.RecordCount <> 0 should have done the trick as far as verifying that there were indeed records returned by the select statement.
The problem was not that there were no records but rather that you HAD records that happened to have null workCompleted.
That doesn's have anything to do with the recordcount, but rather properly handling nulls in fields in your records. As mx said, you can't set a string equal to null. His solution worked, because it changed workCompleted from a string (no nulls allowed) to a variant (accepts nulls).
IrogSinta's code should have worked too... the Nz function is another way of handling nulls.
Your original rs2.RecordCount <> 0 should have done the trick as far as verifying that there were indeed records returned by the select statement.
The problem was not that there were no records but rather that you HAD records that happened to have null workCompleted.
That doesn's have anything to do with the recordcount, but rather properly handling nulls in fields in your records. As mx said, you can't set a string equal to null. His solution worked, because it changed workCompleted from a string (no nulls allowed) to a variant (accepts nulls).
IrogSinta's code should have worked too... the Nz function is another way of handling nulls.
"IrogSinta's code should have worked too... the Nz function is another way of handling nulls."
However, I don't think that is the best way to handle this situation, because now you are setting workCompleted to an arbitrary value ... 0, when in fact it s/b Null ... if the Field value is Null.
btw ... my comment on RecordCount =0 was not specifically addressing why this issue occurred. Clearly that was because of the Dim statement, as I specified.
mx
However, I don't think that is the best way to handle this situation, because now you are setting workCompleted to an arbitrary value ... 0, when in fact it s/b Null ... if the Field value is Null.
btw ... my comment on RecordCount =0 was not specifically addressing why this issue occurred. Clearly that was because of the Dim statement, as I specified.
mx
Joe,
Understood, and understand that you understood that. :-)
I was posting for the author's benefit mainly.
I got the impression from the his last comment that the distinction between recordcount and null handling was not clear to him.
Understood, and understand that you understood that. :-)
I was posting for the author's benefit mainly.
I got the impression from the his last comment that the distinction between recordcount and null handling was not clear to him.
now you are setting workCompleted to an arbitrary value ... 0Actually it would have set it to a zero length string and not to 0. And a zero length string is fine in this case since the OP is just setting a textbox on his form to this value.
Now if the textbox was bound to a field then I could see this being a problem. However, if it that is the case, I would tend to think that the process itself needs to be looked at again and that perhaps a simple form requery would have sufficed.
@Ubiquity-Designs
Here's a revision of your code that avoids this problem in case you have any other fields that are also null.
Private Sub txtTicketNum_AfterUpdate()
Dim db As Database
Dim rs As DAO.Recordset
With [Forms]![frmWorkTicket]
s = "SELECT ticketNum, repairDayDate, computerType, serialNum, phoneNum, firstName, lastname From tblTickets WHERE ticketNum = " & ![txtTicketNum]
Set db = CurrentDb
Set rs = db.OpenRecordset(s)
![txtRepairDayDate] = rs!repairDayDate
![txtFirstName] = rs!firstName
![txtLastName] = rs!lastName
![txtPhoneNum] = rs!phoneNum
![txtPickedUp] = pickedUp
![cboComputerType] = rs!computerType
![txtSerialNum] = rs!serialNum
![txtWorkCompleted] = DLookup("[workCompleted]","tblTickets ","[ticketNum]=" & ![txtTicketNum])
rs.Close
Set rs = Nothing
End With
End Sub
" And a zero length string is fine in this case since the OP is just setting a textbox on his form to this value. "
Personally, I would never do that. I *never* use ZLS. I would set the text box to Null.
Imagine, if elsewhere ... say in a query ... you are referencing that text box ... testing for Null ... but, it's a ZLS. Criteria fails.
Visually, you cannot tell the difference, and it can lead to endless confusion.
Just sayin' ...
mx
Personally, I would never do that. I *never* use ZLS. I would set the text box to Null.
Imagine, if elsewhere ... say in a query ... you are referencing that text box ... testing for Null ... but, it's a ZLS. Criteria fails.
Visually, you cannot tell the difference, and it can lead to endless confusion.
Just sayin' ...
mx
I learned from here that it's best to test textboxes this way:
That way you're covered whether it has a ZLS or Null value.
Ron :)
If Nz(TextBoxName,"") = ""
or
If TextBoxName & "" = ""
or
If TextBoxName & "" = ""
That way you're covered whether it has a ZLS or Null value.
Ron :)
Right ... If ... it's a db I did not design :-)
mx
mx
Open in new window