If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
Invalid Use of Null
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 |
#2
|
|||
|
|||
Invalid Use of Null
hi Michael,
Michael wrote: 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 -- |
#3
|
|||
|
|||
Invalid Use of Null
!!! 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 -- Steve S -------------------------------- "Veni, Vidi, Velcro" (I came; I saw; I stuck around.) "Michael" wrote: 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 |
#4
|
|||
|
|||
Invalid Use of Null
Thanks for the help
|
Thread Tools | |
Display Modes | |
|
|