How to validate data in Ms Access Recordset using VBA

Hankwembo Christopher,FCCA,FZICA,CIA,MAAT,B.A.Sc
Hankwembo Christopher,FCCA,FZICA,CIA,MAAT,B.A.Sc used Ask the Experts™
Dear All;
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("tblEfdReceipts") has data before proceeding to update , below is the full code:

Set rs = db.OpenRecordset("tblEfdReceipts")
    If lngStatus > 0 Then
    ElseIf lngStatus < 0 Then
        ' Handle error.
        On Error Resume Next
    End If
        ' Process data.
  Set JSONS = JsonConverter.ParseJson(strData)
    Z = 2
  For Each item In JSONS
           With rs
            rs![TPIN] = item("TPIN")
            rs![TaxpayerName] = item("TaxpayerName")
            rs![Address] = item("Address")
            rs![ESDTime] = item("ESDTime")
            rs![TerminalID] = item("TerminalID")
            rs![InvoiceCode] = item("InvoiceCode")
            rs![InvoiceNumber] = item("InvoiceCode")
            rs![FiscalCode] = item("FiscalCode")
            rs![TalkTime] = item("TalkTime")
            rs![Operator] = item("Operator")
            rs![Taxlabel] = item("TaxItems")("TaxLabel")
            rs![CategoryName] = item("TaxItems")("CategoryName")
            rs![Rate] = item("TaxItems")("Rate")
            rs![TaxAmount] = item("TaxItems")("TaxAmount")
            rs![VerificationUrl] = item("TaxItems")("VerificationUrl")
            rs![INVID] = Me.InvoiceID
        End With
        Z = Z + 1
      Set rs = Nothing
      Set db = Nothing
      Set JSONS = Nothing

Open in new window

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

Set rs = Me.Recordset.Clone
If Me.Recordset.RecordCount = 0 then 'checks for number of records
   msgbox "There is no records" 
End if

Open in new window

Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Chief Technology Officer
You already have code to do what you want to do, just combine it.
When you open the recordset rs, you can test to see if the .RecordCount = 0 or if it is >0.  If =0, then no records.  If >0, then there are records in the recordset.
ste5anSenior Developer

It's all about control flow, you need separate things. You should hand over the record set as parameter to your export function. Then you're code flow can look like this:

Option Compare Database
Option Explicit

Public Sub Export()

  On Local Error GoTo LocalError

  Dim Recordset As Dao.Recordset

  Dim ValidationMessages As String

  If Not RetrieveData(Recordset) Then
    MsgBox "No data.", vbWarning + vbOkOnly
    Exit Sub
  End If

  If Not ValidateData(Recordset, ValidationMessages) Then
    MsgBox "Validation failed: " & ValidationMessages, vbWarning + vbOkOnly
    Exit Sub
  End If

  If ExportData(Recordst) Then
    MsgBox "Exported.", vbInformation + vbOkOnly
    MsgBox "Not exported.", vbWarning + vbOkOnly
  End If

  Exit Sub

   ' Log error.
   MsgBox "Cannot export.", vbCritical + vbOkOnly

End Sub

Private Function ExportData(ByVal CRecordSet As DAO.Recordset) As Boolean

  On Local Error GoTo LocalError

  ExportData = False
  ' Your actual export.
  ExportData = True
  Exit Function

   ' Log error.
   MsgBox "Cannot export data.", vbCritical + vbOkOnly

End Function

Private Function RetrieveData(ByRef ORecordSet As DAO.Recordset) As Boolean

  On Local Error GoTo LocalError

  RetrieveData = False
  ' Read data and store it in ORecordSet.
  RetrieveData = (ORecordSet.RecordCount > 0)
  Exit Function

   ' Log error.
   MsgBox "Cannot retrieve data.", vbCritical + vbOkOnly

End Function

Private Function ValidateData(ByVal CRecordSet As DAO.Recordset, ByRef OValidationMessages As String) As Boolean

  On Local Error GoTo LocalError

  ValidateData = False
  ' Validate data, add error messages to OValidationMessages, log validate errors.
  ValidateData = (Len(Trim(OValidationMessages)) = 0)
  Exit Function

   ' Log error.
   OValidationMessages = "Cannot validate data."
   MsgBox "Cannot validate data.", vbCritical + vbOkOnly

End Function

Open in new window

DatabaseMX (Joe Anderson - Microsoft Access MVP)Database Architect / Application Developer
Top Expert 2007

The fastest way to check a LOCAL (not Linked) table for RecordCount is:

If CurrentDB.TableDefs("tblEfdReceipts").RecordCount = 0 Then 'whatever ....

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial