addnew results in "invalid use of null" message

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I'm using addnew to append records to a table. It steps across the fields
and works ok until it gets to a text field that is blank, then it gives the
message "invalid use of null" and quits. I guess it is tying to to put Null
into the blank text field and bombs out. what's going on?
 
What line of code generates this error?

Perhaps you are trying to assign a zero-length string (ZLS) to a field that
has its Allow Zero Length property set to No (as it should).

Or perhaps you are trying to assign a null to a variable? Only the Variant
type can accept a null.

For further suggestions, see:
Common errors with Null
at:
http://allenbrowne.com/casu-12.html
 
Field is set to Allow Zero Length: yes. Code follows:

RsTemp.MoveFirst
Do Until RsTemp.EOF
RsFinal.AddNew
For Each fld In RsFinal.Fields
MsgBox (RsTemp(fld.Name)) ‘ at the first text field with
no data, get e error message
RsFinal(fld.Name) = RsTemp(fld.Name)
Next fld
RsFinal.Update
RsTemp.MoveNext
Loop
RsFinal.Close
RsTemp.Close
 
The problem is occuring at the line:

RsFinal(fld.Name) = RsTemp(fld.Name)

The record has the following field types: numbers, text, and dates.

I tried

RsFinal(fld.Name) = nz(RsTemp(fld.Name),"")

It works fine for numbers and text. But I get a "conversion error" when it
gets to a blank Date field.
 
I would have expected it to fail on numeric fields as well.

You'll have to check the field type, then, before assigning the value:

If rsFinal.Fields(fld.Name).Type = dbDate Then
RsFinal(fld.Name) = Nz(RsTemp(fld.Name), 0)
Else

RsFinal(fld.Name) = Nz(RsTemp(fld.Name), "")
End If

although, more correctly, it should probably be:

Select Case rsFinal.Fields(fld.Name).Type
Case dbText, dbMemo
RsFinal(fld.Name) = Nz(RsTemp(fld.Name), "")
Case Else
RsFinal(fld.Name) = Nz(RsTemp(fld.Name), 0)
End Select
 
How many records do you have in the RsTemp table/query? If you have more
than 1 record, on which record does the code crap out?
 
DC Holley: There may be hundreds of records that I will be appending.

DJ Steele et al: the following works (not elegant - but it works). Also,
setting the date to zero gives me 12/30/1899. How can I make the date blank?

Do Until RsTemp.EOF
RsFinal.AddNew
For Each fld In RsFinal.Fields
If fld.Type = dbText Or fld.Type = dbMemo Then
RsFinal(fld.Name) = Nz(RsTemp(fld.Name), "")
ElseIf fld.Type = dbDate Then
RsFinal(fld.Name) = Nz(RsTemp(fld.Name), 0) ' when blank,
sets date to 12/30/1899
Else
RsFinal(fld.Name) = Nz(RsTemp(fld.Name))
End If
Next fld
RsFinal.Update
RsTemp.MoveNext
Loop
 
Insert an If...then to check if the code is on the field for the Date
within the If...then test for 0 and override it to Null as in

If fld.Name = "date" then
If rsTemp(fld.name) = 0 then
rsFinal(fld.Name) = Null
else
rsFinal(fld.name) = rsTemp(fld.Name)
end if
end if
 
Back
Top