Invalid Use of Null

[Michael]

I have the following sub working except for the situation where the
field is empty.
In the case that either the path or filename fields of the record set
are empty I simply want to skip over the record.
I keep getting an error in the line " Path = rst!Path.Value" if there
is no path in the dataset. The error says "invalid use or NULL" I have
tried trapping the error and also an if statement that checks the rst!
Path.Value for NULL but nothing seems to work.

Am I missing something


'This Sub updates data based on path and file name found in fields
Sub DbReadExcelAndUpdate()
Dim dbs As DAO.Database
Dim rst As DAO.Recordset
Dim strSQL As String

Dim Path As String
Dim Filename As String
Dim varArray As Variant

Debug.Print "start " & Now()

Set dbs = CurrentDb
strSQL = "SELECT * FROM tmpExcelDat"
Set rst = dbs.OpenRecordset(strSQL, dbOpenDynaset)


Do Until rst.EOF
Debug.Print rst!DealName.Value

Path = rst!Path.Value

Filename = rst!Filename.Value

varArray = GetExcelPFDat(Path, Filename)

rst.Edit

rst!Units.Value = varArray(0)
rst!taxcredit.Value = varArray(1)

rst.Update
rst.MoveNext

Loop


rst.Close
dbs.Close

Set rst = Nothing
Set dbs = Nothing
Debug.Print "End " & Now()

End Sub

 

[Stefan Hoffmann]

hi Michael,

I keep getting an error in the line " Path = rst!Path.Value" if there
is no path in the dataset. The error says "invalid use or NULL" I have
tried trapping the error and also an if statement that checks the rst!
Path.Value for NULL but nothing seems to work.

You cannot assign NULL to a string or any other "concrete" data type.

Do Until rst.EOF
Debug.Print rst!DealName.Value

If Not IsNull(rst![Path]) And Not IsNull(rst![Filename]) Then

Path = rst!Path.Value
Filename = rst!Filename.Value
varArray = GetExcelPFDat(Path, Filename)
rst.Edit
rst!Units.Value = varArray(0)
rst!taxcredit.Value = varArray(1)
rst.Update End If
rst.MoveNext
Loop

btw, you don't need the .Value for a recordset field as it is the
default member. It's imho also easier to read when using the bang notation.


mfG
--> stefan <--

 

[Steve Sanford]

!!! AIR CODE - but it should run !!!!

Option Compare Database
Option Explicit
'--------------------------------------------------
Sub DbReadExcelAndUpdate()
Dim dbs As DAO.Database
Dim rst As DAO.Recordset
Dim strSQL As String

Dim Path As String
Dim Filename As String
Dim varArray As Variant

Debug.Print "start " & Now()

Set dbs = CurrentDb
strSQL = "SELECT * FROM tmpExcelDat"
Set rst = dbs.OpenRecordset(strSQL, dbOpenDynaset)

'check for records
If rst.BOF And rst.EOF Then
'no records
MsgBox "No records found!"
Else
'records found
rst.MoveFirst

Do Until rst.EOF
Debug.Print rst!DealName

Path = Nz(rst!Path, "")
Filename = Nz(rst!Filename, "")

If Len(Trim(Path)) > 0 Or Len(Trim(Filename)) > 0 Then
varArray = GetExcelPFDat(Path, Filename)

rst.Edit

rst!Units = varArray(0)
rst!taxcredit = varArray(1)

rst.Update

End If

rst.MoveNext

Loop

End If

rst.Close
' dbs.Close '<< you didn't open it, so don't close it

Set rst = Nothing
Set dbs = Nothing
Debug.Print "End " & Now()

End Sub
'--------------------------------------------------



HTH

 

[Michael]

Thanks for the help