Evaluate for Null Record and Write Record in VBA

[harrisa23]

Good Afternoon!

I am needing some advice on how to accomplish something in Access 2002. I have a table that has 2 fields. The first field is a part number, the second is the equipment number that the part is used on. When the data was exported from our inventory program, if there was more than one piece of equipment that a part was used on, it left the part number field empty and filled the equipment field only.
For example:

Part # Equip #
1004 R374
M721
1005 M213
1006 K572
K580
L421
etc....

I need to come up with some code that will evaluate the part number field and if it is null, enter the part number of the previous field. I thought it would include something like: read record, if not null, put in temp, write record, write equip number, if null, write record, write equip number. But I dont know how to write VBA code or use the Modules in Access.

Any help is much appreciated!

Thank you for your time,

A. Harris

 

[MajP]

something like this
(Put in your field and table names)

Public Sub addPartNo()
  Dim rs As DAO.Recordset
  Dim oldPartNo As Variant
  Set rs = CurrentDb.OpenRecordset("tblData", dbOpenDynaset)
  Do While Not rs.EOF
    If Trim(rs.Fields("partNo") & " ") = "" Then
      rs.Edit
        rs.Fields("partNo") = oldPartNo
      rs.Update
     End If
     oldPartNo = rs.Fields("partNo")
     rs.MoveNext
  Loop
End Sub

 

[MajP]

Make a copy in case this does not do what you want. No warranties implied.

 

[harrisa23]

Thanks! I'll give it a try.

A. Harris