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™
on
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
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Chief Technology Officer
Commented:
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

Commented:
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

Open in new window

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

Commented:
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