Word VBA runtime error 94 Invalid Use of Null

[Jules]

Hi, I receive a runtime 94 error on below line:

drow.Cells(i).Range.Text = myActiveRecord.Fields(i - 1) 'runtime
error 94 - Invalid Use of Null

The database is opened and the first row is created and data inserted, the
second row is created but aborts at about the 4th field.
Does it make a difference that there are 500 records in this database being
loaded (1,000 is the norm) into a Word table from Access? I'm using Word
2007 to run this code. Any ideas please?.


Sub DB1_test()

Dim myDataBase As Database
Dim myActiveRecord As Recordset
Dim i As Long
Dim dtable As table, drow As Row
'Open a database
Set myDataBase = OpenDatabase("Y:\PRECS\Writing to Text.mdb")
'Access the first record from a particular table
Set myActiveRecord = myDataBase.OpenRecordset("tblContacts",
dbOpenForwardOnly)
'Add a table to the document with one row and as many fields as there are in
the database table
Set dtable = ActiveDocument.Tables.Add(Range:=Selection.Range, NumRows:=1,
numcolumns:=myActiveRecord.Fields.Count)
Set drow = dtable.Rows(1)
'Loop through all the records in the table until the end-of-file marker is
reached
Do While Not myActiveRecord.EOF
'Populate the cells in the Word table with the data from the current
record
For i = 1 To myActiveRecord.Fields.Count
drow.Cells(i).Range.Text = myActiveRecord.Fields(i - 1) 'runtime
error 94 - Invalid Use of Null
Next i
'Add a new row to the Word table and access the next record
Set drow = dtable.Rows.Add
myActiveRecord.MoveNext
Loop
'The last row will be empty, so delete it
drow.Delete
'Then close the database
myActiveRecord.Close
myDataBase.Close
End Sub

 

[Norman Yuan]

It looks like the RecordSet's field (myActiveRecord.Fields(i-1))'s value is
NULL. That is, the corresponding column in database is allowed to be null
value. In this case, when you retrieve the record and intend to use the
value from that field as String/Text value, you have to make sure to force
it to be a String value if null is possible. the simple way to do it is:

drow.Cells(i)Range.Text=myActiveRecord.Fields(i-1) & ""

 

[Jules]

You are a whiz - thank you so much SOLVED.

 

[Jules]

Although, it output the 506 records perfectly - it didn't delete the blank
last row? Any ideas why it couldn't?

 

[Norman Yuan]

You should only create the table in Word only if the RecordSet returned at
least one record and add rows during the loop only if there is record. So,
here is the quick code that modifies your code logic a bit:

Dim myDataBase As Database
Dim myActiveRecord As Recordset
Dim i As Long
Dim dtable As table, drow As Row

'Open a database
Set myDataBase = OpenDatabase("Y:\PRECS\Writing to Text.mdb")

'Access the first record from a particular table
Set myActiveRecord = myDataBase.OpenRecordset("tblContacts",
dbOpenForwardOnly)

'Add a table to the document with one row and as many fields as there are in
the database table

If Not myActiveRecord.EOF Then

'==Only add table when there is record returned from database
Set dtable = ActiveDocument.Tables.Add(Range:=Selection.Range,
NumRows:=1, numcolumns:=myActiveRecord.Fields.Count)
Set drow = dtable.Rows(1)

Do

'Populate the cells in the Word table with the data from the current
record
For i = 1 To myActiveRecord.Fields.Count
drow.Cells(i).Range.Text = myActiveRecord.Fields(i - 1)
Next i

'==Move to next record
myActiveRecord.MoveNext

==Only add new row to table when needed
If Not myActiveRecord.EOF Then Set drow = dtable.Rows.Add

Loop While Not myActiveRecord.EOF

End If

'Then close the database
myActiveRecord.Close
myDataBase.Close

End Sub

 

[Jules]

Thanks for the lesson - much better logic as you say.