在Ms Access VBA中，在更新记录集之前如何验证数据。

huangapple
go评论105阅读模式
英文:

How to validate data before update in the recordset in Ms access VBA

问题

在获取更多信息后，我认为有必要在将数据存储在Ms Access中的静态表之前进行数据验证。坦白地说，我对这个话题并不确定，这对我来说是新的，我只是试图看看它是否能够正常工作。

我想要检查在继续更新之前是否有数据，下面是完整的代码：

  1. Set rs = db.OpenRecordset("tblEfdReceipts")
  2. If lngStatus > 0 Then
  3. ElseIf lngStatus < 0 Then
  4.     ' 处理错误。
  5.     On Error Resume Next
  6. End If
  7. ' 处理数据。
  8. Set JSONS = JsonConverter.ParseJson(strData)
  9. Z = 2
  10. For Each item In JSONS
  11.     With rs
  12.         .AddNew
  13.         rs![TPIN] = item("TPIN")
  14.         rs![TaxpayerName] = item("TaxpayerName")
  15.         rs![Address] = item("Address")
  16.         rs![ESDTime] = item("ESDTime")
  17.         rs![TerminalID] = item("TerminalID")
  18.         rs![InvoiceCode] = item("InvoiceCode")
  19.         rs![InvoiceNumber] = item("InvoiceCode")
  20.         rs![FiscalCode] = item("FiscalCode")
  21.         rs![TalkTime] = item("TalkTime")
  22.         rs![Operator] = item("Operator")
  23.         rs![Taxlabel] = item("TaxItems")("TaxLabel")
  24.         rs![CategoryName] = item("TaxItems")("CategoryName")
  25.         rs![Rate] = item("TaxItems")("Rate")
  26.         rs![TaxAmount] = item("TaxItems")("TaxAmount")
  27.         rs![VerificationUrl] = item("TaxItems")("VerificationUrl")
  28.         rs![INVID] = Me.InvoiceID
  29.         rs.Update
  30.     End With
  31.     Z = Z + 1
  32. Next
  34. rs.Close
  35. Set rs = Nothing
  36. Set db = Nothing
  37. Set JSONS = Nothing
  39. 我正在尝试这段代码，但我不确定如何正确地执行它，接收到的数据在检查之前永远不会成为实际数据。
  41. **验证代码需要改进**
  44. ```vba
  45. rs = Me.Recordset.Clone
  46. If Me.Recordset.RecordCount = 0 Then '检查记录数
  47.     MsgBox "没有记录"
  48. End If

如果数据存在，然后进行处理。

英文:

After getting more information here, I think there is need to validate the data before storing in a stagnant table in Ms access. I’m not sure about this topic frankly its new to me , I’m just try to see whether it will work.

I want to check whether Set rs = db.OpenRecordset(&quot;tblEfdReceipts&quot;) has data before proceeding to update , below is the full code:

  1. Set rs = db.OpenRecordset(&quot;tblEfdReceipts&quot;)
  2.     If lngStatus &gt; 0 Then
  3.     ElseIf lngStatus &lt; 0 Then
  4.         &#39; Handle error.
  5.         On Error Resume Next
  6.     End If
  7.         &#39; Process data.
  8.   Set JSONS = JsonConverter.ParseJson(strData)
  9.     Z = 2
  10.   For Each item In JSONS
  11.            With rs
  12.          
  13.             .AddNew
  14.             rs![TPIN] = item(&quot;TPIN&quot;)
  15.             rs![TaxpayerName] = item(&quot;TaxpayerName&quot;)
  16.             rs![Address] = item(&quot;Address&quot;)
  17.             rs![ESDTime] = item(&quot;ESDTime&quot;)
  18.             rs![TerminalID] = item(&quot;TerminalID&quot;)
  19.             rs![InvoiceCode] = item(&quot;InvoiceCode&quot;)
  20.             rs![InvoiceNumber] = item(&quot;InvoiceCode&quot;)
  21.             rs![FiscalCode] = item(&quot;FiscalCode&quot;)
  22.             rs![TalkTime] = item(&quot;TalkTime&quot;)
  23.             rs![Operator] = item(&quot;Operator&quot;)
  24.             rs![Taxlabel] = item(&quot;TaxItems&quot;)(&quot;TaxLabel&quot;)
  25.             rs![CategoryName] = item(&quot;TaxItems&quot;)(&quot;CategoryName&quot;)
  26.             rs![Rate] = item(&quot;TaxItems&quot;)(&quot;Rate&quot;)
  27.             rs![TaxAmount] = item(&quot;TaxItems&quot;)(&quot;TaxAmount&quot;)
  28.             rs![VerificationUrl] = item(&quot;TaxItems&quot;)(&quot;VerificationUrl&quot;)
  29.             rs![INVID] = Me.InvoiceID
  30.             rs.Update
  31.         End With
  32.         Z = Z + 1
  33.     Next
  34.       
  35.       rs.Close
  36.       Set rs = Nothing
  37.       Set db = Nothing
  38.       Set JSONS = Nothing

I’m trying this code but I’m not sure of how do it correctly, the received data is never part of the live until checked.

Validation code require improvements

  1. rs = Me.Recordset.Clone
  2. If Me.Recordset.RecordCount = 0 then &#39;checks for number of records
  3.    msgbox &quot;There is no records&quot; 
  4. End if

If the data is present then process it.

答案1

得分: 3

使用 DCount：

  1. 如果 DCount("*", "tblEfdReceipts") = 0 Then
  2.     ' 表格没有记录。
  3.     Set rs = db.OpenRecordset("tblEfdReceipts")
  5.     ' <snip>
  6. Else
  7.     ' 表格有记录。跳过。
  8. End if
英文:

Use DCount:

  1. If DCount(&quot;*&quot;, &quot;tblEfdReceipts&quot;) = 0 Then
  2.     &#39; Table has no records.
  3.     Set rs = db.OpenRecordset(&quot;tblEfdReceipts&quot;)
  5.     &#39; &lt;snip&gt;
  6. Else
  7.     &#39; Table has records. Skip.
  8. End if

答案2

得分: 2

作为一般规则，您可以检查EOF（文件末尾）。

因此：

  1. Set rs = db.OpenRecordset("tblEfdReceipts")
  2. if rs.EOF = True then
  3.    ' 没有记录
  4. End If
英文:

As a general rule, you can check EOF (end of file).

Thus:

  1. Set rs = db.OpenRecordset(&quot;tblEfdReceipts&quot;)
  2. if rs.EOF = True then
  3.    &#39; no records
  4. End If

huangapple
  • 本文由 发表于 2020年1月3日 22:10:31
  • 转载请务必保留本文链接：https://go.coder-hub.com/59580009.html
  • json
  • ms-access
  • vba
匿名

发表评论

匿名网友

:?: :razz: :sad: :evil: :!: :smile: :oops: :grin: :eek: :shock: :???: :cool: :lol: :mad: :twisted: :roll: :wink: :idea: :arrow: :neutral: :cry: :mrgreen:

确定