CODEWITHSUNDEEP
formsloopsms-accessrecord
Alex
Alex

Reputation: 113

MS-ACCESS How to loop all current form's query records to check if some field's value are equal to some textboxes value

I have a splitform linked to a query with some fields, this form has some textboxes but my focus is on 3 specific textboxes and query fields: type, date and id.

When i filter the datasheet view for some reasons, i want to check throught a buttonclick event if all the query records already filtered have the same field's values like the main form's textboxes values of my interesting.

For instance:

in the main form i have:

name: package

quantity: 10

type: normal

date: 01/01/2020

id:1

in the datasheet filtered by the 3 values of my interesting, i have 14 records that have the same values:

type: normal

date: 01/01/2020

id:1

How can i buttonclick's check if all the relative filtered query fields.value (type, date and id) of the 14 records are equal to the form's txtType.value(normal) and txtDate.value(01/01/2020) and txtid.value(1)?

thanks in advance.

Upvotes: 0

Views: 156

Answers (1)

Alex
Alex

Reputation: 113

found a solution..

i made a public function which compares all current query form's records with the relative txtboxes values thanks to the Form.RecordsetClone property:

Public Function CheckFilteredRecords(frmName As String) As Boolean

Dim rs As Recordset
Set rs = Forms(frmName).RecordsetClone

If Not (rs.EOF And rs.BOF) Then
    rs.MoveFirst 'Unnecessary in this case, but still a good habit
    Do Until rs.EOF = True
        
        If Forms(frmName).txtID.Value = rs!ID.Value _
        And Forms(frmName).txtType.Value = rs!Type.Value _
        And Forms(frmName).txtDate = rs!Date.Value Then
        CheckFilteredRecords = True
        Else
        CheckFilteredRecords = False
        End If
        

        'Move to the next record. Don't ever forget to do this.
        rs.MoveNext
    Loop
End If


rs.Close 'Close the recordset
Set rs = Nothing 'Clean up
End Function

You can call this function everywhere like this CheckFilteredRecords(Me.Name) and if all records match their relative txtbox.value, the function returns the True value.

Upvotes: 0

Related Questions