invalid use of null

[pbuethe]

I am getting "invalid use of null" error on the following code:

Public Sub DupTapeData()
Dim prevRecID As String
Dim thisRecID As String

Dim SpecCode As String
Dim CatOfServ As String
Dim BillType As String
Dim RateCode As String

Dim dbs As Database
Dim rst As DAO.Recordset

Set dbs = CurrentDb
Set rst = dbs.OpenRecordset("tblAdjMasterSort", dbOpenTable)
With rst
.MoveFirst
.Edit
End With

thisRecID = rst![txtIDNbr]


Do Until rst.EOF
rst.MoveNext
prevRecID = thisRecID
thisRecID = rst![txtIDNbr]
SpecCode = rst!txtSpecCode
CatOfServ = rst!txtCatOfServ
BillType = rst!txtBillType
RateCode = rst!txtRateCode
If thisRecID = prevRecID Then
With rst
.MovePrevious
!txtSpecCode = SpecCode
!txtCatOfServ = CatOfServ
!txtBillType = BillType
!txtRateCode = RateCode
.Update
End With
End If

Loop

With rst
.Update
.Close
End With
End Sub

The "invalid use of null" comes on the line:
SpecCode = rst!txtSpecCode

I am trying to copy 4 fields from the second of two records, where the two records have the same txtIDNbr, to the first record. The table, tblAdjMasterSort, is sorted by txtIDNbr and txtAdjTapeNbr; the records with the earlier txtAdjTapeNbr are blank in the 4 fields, txtSpecCode, txtBillType, txtCatOfServ, and txtRateCode.

What am I doing wrong? Any help is appreciated.

 

[Fizzio]

If you want to be able to handle null variables, Dim them as variant, not strings
ie Dim SpecCode as Variant.

Just a couple of things though - If you have to duplicate data your data structure may not be efficient.
Also, your field nomeclature is more often used for form controls, not table Fields.

 

[pbuethe]

If you want to be able to handle null variables, Dim them as variant, not strings

Fizzio, I tried changing the variables to Variant, but got the error:

Update or Cancel Update without AddNew or Edit

on the line:
!txtSpecCode = SpecCode

Just a couple of things though - If you have to duplicate data your data structure may not be efficient.

I realize that but it is very complicated to explain why I am duplicating the data. Two records will have mostly the same data, but some fields have corrections to the other record. Also the 4 fields I want to update now, were added to the table belatedly. (These are not the same fields as have corrections.) When I tried to update them with a query, it deleted the txtIDNbrs from the earlier records. I copied and pasted to restore them. So I was just looking for the easiest way to update these 4 fields. If I could do it with a query instead of code that would be good.

Also, your field nomeclature is more often used for form controls, not table Fields.

What should the nomenclature be then for table fields?

Thanks for your help.

 

[Fizzio]

You need to move the .edit and .update parts of the code closer to the loop ie

Set dbs = CurrentDb
Set rst = dbs.OpenRecordset("tblAdjMasterSort", dbOpenTable)
rst.MoveFirst

thisRecID = rst![txtIDNbr]

Do Until rst.EOF
rst.MoveNext
prevRecID = thisRecID
thisRecID = rst![txtIDNbr]
SpecCode = rst!txtSpecCode
CatOfServ = rst!txtCatOfServ
BillType = rst!txtBillType
RateCode = rst!txtRateCode
If thisRecID = prevRecID Then
With rst
.MovePrevious
.edit
!txtSpecCode = SpecCode
!txtCatOfServ = CatOfServ
!txtBillType = BillType
!txtRateCode = RateCode
.Update
End With
End If

Loop

rst.Close

Nomenclature for tables is usually just the name without the qualifier ie SpecCode, BillType

example
Table / Form / Code
BillType / txtBillType / strBillType

 

[pbuethe]

I followed your suggestions, but now when I click the button to run the subroutine the database locks up.

 

[Fizzio]

The code is in a never ending loop between move next and move previous

alter this section to as follows

!txtRateCode = RateCode
.Update
.movenext
End With
End If

 

[pbuethe]

Now I get "no current record" on

thisRecID = rst![txtIDNbr] in the loop.

 

[pbuethe]

I finally got the query to do what I wanted, so I did not need the code after all. Thanks anyway Fizzio.

 

[Fizzio]

Glad you sorted it 'the easy way'