Run Time Error 94 Invalid Use of Null

[Guest]

When trying to sync data I encounter the Run-Time Error I placed brackets
around the issue that is highlighted in yellow below in the program,should I
change the =rs.Fields(0) to reflect =rs.Fields(1)?


Do While Not rs.EOF
Dim strWMS As String
{strWMS = rs.Fields(0)}
updateJob strWMS, Cnxn, db
rs.MoveNext
Me.mainSyncProgressBar = i / rcCountInt * 100
Me.mainSyncProgressBar.Refresh
Dim istr As String
istr = i
Me.statusText.value = istr + " of " + rcCountStr + " jobs updated "
Me.SetFocus
Me.Refresh
Me.Repaint
i = i + 1
Loop
wrkDefault.CommitTrans
rs.Close

 

[John Spencer]

Hard to say, but is the first field Null at times? If so, you cannot assign
that to a string variable.


--
John Spencer
Access MVP 2002-2005, 2007
Center for Health Program Development and Management
University of Maryland Baltimore County
..

 

[Guest]

Yes, the first field is Null, then what steps should I take to resolve this
issue.

 

[John Spencer]

It kind of depends on what you want to do if the field is null.

The simplest solution would be to test the value of the field and skip to
the next record in the recordset,. Since I don't know what updateJob is
doing I don't know if that is a good solution.

IF IsNull(rs.Fields(0)) = False THEN
strWMS = ...
updateJob ...
End If
rs.MoveNext

If you need to process the record anyway, then you can use
strWMS = NZ(rs.Fields(0),"")
to force a zero-length string into strWMS. That can be a problem is
upDateJob does not know what to do with a zero length string or (guessing
here) the field in the table cannot accept zero-length strings.


--
John Spencer
Access MVP 2002-2005, 2007
Center for Health Program Development and Management
University of Maryland Baltimore County
..

 

[Keith Wilby]

Yes, the first field is Null, then what steps should I take to resolve
this
issue.

Use the NZ function to assign a value to it, eg assign a "0":

strWMS = Nz(rs.Fields(0),0)

Keith.
www.keithwilby.com

 

[Guest]

Good Morning John,
I took your advice on useing strWMS = NZ(rs.Fields(0),"") and it worked.

Thank you.

 

[Guest]

Good Morning Keith,

I took your advice and used Use the NZ function to assign a value to it, eg
assign a "0":

strWMS = Nz(rs.Fields(0),0)

and the program is working fine now.

Thank you

 

[Keith Wilby]

Good Morning Keith,

I took your advice and used Use the NZ function to assign a value to it,
eg
assign a "0":
and the program is working fine now.

Thank you

You're welcome, glad I could help.

Keith.