CODEWITHSUNDEEP
ms-accessvbawildcardms-access-2013
Travis Patterson
Travis Patterson

Reputation: 93

Wildcard in Access VBA

This is kind of a follow up question to this post: Access VBA recordset string comparison not working with wildcard but I don't have the rep to answer/comment on it to ask it in house. What I'm curious about is this line of code specifically:

If ![ACOD] = "*_X" Then '"$ICP_X" works
        Debug.Print ![ACOD] 'For testing
        '.Delete
End If

I want to know if this can be modified so that on a button click, it looks at all fields in a form with the field name of *_New (with the hope to catch all fields where the name ends in _New) and if they are not Null then confirm that the user wanted to make a the change indicated in the field. I was thinking of something along the lines like this:

If Not isNull(*_New.value) Then
   If Msgbox ("Do you want to make these changes?",vbOKCancel, "Confirm Changes") = 1 Then
      '### Do something with the record ###
   End If
End If

EDIT

As of posting the above information, I did not have the Microsoft VBScript Regular Expressions Reference installed, currently I have version 5.5 (it was the latest version). With that installed (referenced?) and seeing the information from this site MS Access with VBA Regex, I'm wondering if it's better to do something like this:

Dim re As RegExp
Set re = New RegExp
re.IgnoreCase = True
re.Global = True
re.Pattern = "*_New"
If ##Not sure on syntax to match all fields## Then
   Msgbox(##Same stuff as above MsgBox##)
End If

EDIT 2

Here's a sample case for my form I'm working on. Each of the fields to the right have names that end in _New. What I want to do is on the button click, to check and see what fields on the right have been filled in and ask the user if they want to confirm the changes to the record.

Sample case for testing form

Upvotes: 1

Views: 969

Answers (1)

Krish
Krish

Reputation: 5917

Not sure what you are trying to achieve but there is a way to access the control collection in a form. Here is a public function where you can loop through all controls and check its name.

Public Function FN_CONFIRM_CHANGES(iSender As Form)
    Dim mCtl As control
    For Each mCtl In iSender
        If VBA.Right(mCtl.name, 4) = "_New" Then
            Debug.Print mCtl.name & " is a match and its a " & VBA.TypeName(mCtl)
        End If
    Next mCtl
End Function

Call this function like

FN_CONFIRM_CHANGES Me 'Where me is referencing the form you are in.

You can modify the above code to return a boolean value to stop further execution if user decided not to save your changes or whatever logic you are trying to implement.

Upvotes: 1

Related Questions