CODEWITHSUNDEEP
ms-accessvba
Michael
Michael

Reputation: 2657

MS Access Update reocrds based on value of another

I have the following table (tmpManifest - ID is the PK) - the data is populated from a barcode scanner so I cannot control how it is created:

enter image description here

However, I need to populate the Box column to look like the below - it needs to know when to change to correct Box:

enter image description here

What is the best way to achieve this via an Update query?

Upvotes: 1

Views: 30

Answers (1)

nagarajannd
nagarajannd

Reputation: 715

Try this solution - will loop through each record and update one by one.

Dim SQL As String, sUPC As String, sID As String
Dim rs As DAO.Recordset
Set rs = CurrentDb.OpenRecordset("SELECT [ID], [UPC], [Description] FROM [tmpManifest] ORDER BY [ID] ASC")

If Not (rs.EOF And rs.BOF) Then
    Do While Not rs.EOF
        sID = CStr(rs![ID])
        If IsNull(rs![Description]) Then
            SQL = "UPDATE [tmpManifest] SET [BOX] = '" & sUPC & "' WHERE [ID] = " & sID
            CurrentDb.Execute SQL
        Else
            sUPC = CStr(rs![UPC])
            SQL = "UPDATE [tmpManifest] SET [BOX] = '" & sUPC & "' WHERE [ID] = " & sID
            CurrentDb.Execute SQL

        End If
        rs.MoveNext
    Loop
End If

Upvotes: 2

Related Questions