vba - Access 2016 - VBA - Filling an Empty Field/Column with Data Based on the Value Within an Adjacent (same row) Cell

Question

Here is my table (within my form) in MS Access 2016.

I wish to have code in VBA which:

1.) Scans all of the "Tag_Value" field

2.) While scanning, IF the values I indicate are found, THEN insert a "text string" (such as "Watchdog Timer") into the "ErrorDescription" field on the same row that the corresponding value is found on

Seems simple, right? I hope it is... It's cake using Excel, but I need the table used by my form to be linked with a MySQL server (for constant updates), not Excel.

I have managed to get it to write to the very first row using IF statements (_OnLoad and _Click [of the filter button]), but the party stops there.

Any help would be greatly appreciated. Thanks in advance for your time!

Got it!

See code below:

Dim d As Database
Dim r As Recordset
Dim Tag_Value As Field, ErrorDescription As Field

Set d = CurrentDb()
Set r = d.OpenRecordset("alarmlogwithdescs")
Set Tag_Value = r.Fields("Tag_Value")
Set ErrorDescription = r.Fields("ErrorDescription")
r.LockEdits = True
r.MoveFirst

    While Not r.EOF
        If [Tag_Value] = 7194 & IsNull([ErrorDescription]) Then
        r.Edit
        ErrorDescription = "Watchdog Timer"
        r.Update
        End If

        If [Tag_Value] = 3483 & IsNull([ErrorDescription]) Then
        r.Edit
        ErrorDescription = "Controller Fault1"
        r.Update
        End If

        If [Tag_Value] = 6816 & IsNull([ErrorDescription]) Then
        r.Edit
        ErrorDescription = "Controller Fault2"
        r.Update
        End If

        If [Tag_Value] = 3105 & IsNull([ErrorDescription]) Then
        r.Edit
        ErrorDescription = "Controller Fault3"
        r.Update
        End If

        If [Tag_Value] = 6438 & IsNull([ErrorDescription]) Then
        r.Edit
        ErrorDescription = "HMI Fault2"
        r.Update
        End If

        If [Tag_Value] = 2727 & IsNull([ErrorDescription]) Then
        r.Edit
        ErrorDescription = "HMI Fault2"
        r.Update
        End If

        If [Tag_Value] = 6060 & IsNull([ErrorDescription]) Then
        r.Edit
        ErrorDescription = "HMI Fault3"
        r.Update
        End If

        If [Tag_Value] = 2349 & IsNull([ErrorDescription]) Then
        r.Edit
        ErrorDescription = "Motor Overheating"
        r.Update
        End If

        If [Tag_Value] = 5682 & IsNull([ErrorDescription]) Then
        r.Edit
        ErrorDescription = "Motor Failed to Start"
        r.Update
        End If

        If [Tag_Value] = 1971 & IsNull([ErrorDescription]) Then
        r.Edit
        ErrorDescription = "Motor Failed to Stop"
        r.Update
        End If

    r.MoveNext

    Wend

    r.Close

标签： vbams-access

Answer 1

Dim d As Database
Dim r As Recordset
Dim Tag_Value As Field, ErrorDescription As Field

Set d = CurrentDb()
Set r = d.OpenRecordset("alarmlogwithdescs")
Set Tag_Value = r.Fields("Tag_Value")
Set ErrorDescription = r.Fields("ErrorDescription")
r.LockEdits = True
r.MoveFirst

    While Not r.EOF
        If [Tag_Value] = 7194 & IsNull([ErrorDescription]) Then
        r.Edit
        ErrorDescription = "Watchdog Timer"
        r.Update
        End If

        If [Tag_Value] = 3483 & IsNull([ErrorDescription]) Then
        r.Edit
        ErrorDescription = "Controller Fault1"
        r.Update
        End If

        If [Tag_Value] = 6816 & IsNull([ErrorDescription]) Then
        r.Edit
        ErrorDescription = "Controller Fault2"
        r.Update
        End If

        If [Tag_Value] = 3105 & IsNull([ErrorDescription]) Then
        r.Edit
        ErrorDescription = "Controller Fault3"
        r.Update
        End If

        If [Tag_Value] = 6438 & IsNull([ErrorDescription]) Then
        r.Edit
        ErrorDescription = "HMI Fault2"
        r.Update
        End If

        If [Tag_Value] = 2727 & IsNull([ErrorDescription]) Then
        r.Edit
        ErrorDescription = "HMI Fault2"
        r.Update
        End If

        If [Tag_Value] = 6060 & IsNull([ErrorDescription]) Then
        r.Edit
        ErrorDescription = "HMI Fault3"
        r.Update
        End If

        If [Tag_Value] = 2349 & IsNull([ErrorDescription]) Then
        r.Edit
        ErrorDescription = "Motor Overheating"
        r.Update
        End If

        If [Tag_Value] = 5682 & IsNull([ErrorDescription]) Then
        r.Edit
        ErrorDescription = "Motor Failed to Start"
        r.Update
        End If

        If [Tag_Value] = 1971 & IsNull([ErrorDescription]) Then
        r.Edit
        ErrorDescription = "Motor Failed to Stop"
        r.Update
        End If

    r.MoveNext

    Wend

    r.Close