Access 2016 - VBA -根据相邻(同一行)单元格内的值填充空字段/列

Question

​

​

这是MS Access 2016中我的桌子(以我的形式)。

我希望在VBA中有这样的代码：

1.)扫描所有"Tag_Value“字段

2.)扫描时，如果找到我所指示的值，则将“文本字符串”(如“看门狗计时器”)插入到相应值所在行的"ErrorDescription“字段中。

看起来很简单对吧？我希望是..。它是使用Excel的蛋糕，但我需要我的表单使用的表被链接到MySQL服务器(用于不断更新)，而不是Excel。

我已经设法使用IF语句(筛选器按钮的_OnLoad和_Click )将其写入到第一行，但参与者就此结束了。

任何帮助都将不胜感激。提前谢谢你的时间！

明白了!

见下面的代码：

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

Answer 1

数据不应该硬编码。

因此，创建一个表来保存这些警报代码描述：

[Tag_Value]  ErrorDescription 
       3188  "Watchdog Timer"
       6522  "Controller Fault1"

etc. 

然后在[Tag_Value]上加入一个查询来检索错误描述。

Answer 2

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