How to validate data in Ms Access Recordset using VBA

Hankwembo Christopher,FCCA,FZICA,CIA,MAAT,B.A.Sc used Ask the Experts™

on 2020-01-03

Microsoft Access

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
         
            .AddNew
            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
            rs.Update
        End With
        Z = Z + 1
    Next
      
      rs.Close
      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

Comment

Watch Question

Do more with

EXPERT OFFICE^® is a registered trademark of EXPERTS EXCHANGE^®

View Solution Only

Mark EdwardsChief Technology Officer

Verified Expert

Commented: 2020-01-03

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

Verified Expert

Commented: 2020-01-03

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
  Else
    MsgBox "Not exported.", vbWarning + vbOkOnly
  End If

  Exit Sub

 LocalError:
   ' 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

 LocalError:
   ' 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

 LocalError:
   ' 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

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

End Function