Clicking a button programmatically

[peterFisp]

I have a form with search critieria listboxes from which I assemble a revised Recordsource for a subform, that then displays the resultset. All works nicely. If the criteria result in an empty result set, I clear those fields.

What I then appear to do successfully, is to amend the Recordsource of me! that is, of the main form, in order to again display the full set of records. I definitely do so successfully in two other subs.

Forms!HyperSearch_Main_Form.RecordSource = "SELECT Q_Super_Search_This.* FROM Q_Super_Search_This;"

But all my efforts in trying to have the main form show that full recordset fail. I have read elsewhere that updating the Recordsource property of a form causes a requery regardless. This does not appear to happen. Executing a simple "Me.Requery" also has no effect. Either way, no errors are returned (not any longer!).

However, when I click my 'Search' button again with the criteria all nulled out, I do get the full recordset back.

So I wondered if I can get code to click that button for me. And I found input on a PerformClick. However, any way of getting this into my code failed for me. I am unsure if it is even valid for VB.

Would there be a way to get that button clicked within my sub? Or would there be some other way of getting back my full recordset in the main form?

Your input would be greatly appreciated.

[June7]

If revised SQL for subform does not actually change the field set, just applies filter criteria, could instead set Filter and FilterOn properties.

But I am confused. You say code revised RecordSource of a subform then you say issue is with RecordSource of main form.

[peterFisp]

Well, there you go; seems I was confused well before you (and for some time). I am of course updating the wrong Recordsource. So I shall regroup on that input. So thank you for posing that question.

However, might you have input on the other question of a PerformClick method? Does it exist for Access VB?

[June7]

Yes, code can call event procedure. If you want one form to call another form procedure, that procedure cannot have Private qualifier which Access uses by default.

So, Form1 calls Form2 procedure:

Forms!Form2.MyButtonName_Click

That assumes Form2 is not used as subform. If it is, referencing gets a little more complicated.

[peterFisp]

Thanks for the input. Sadly, it appears that I cannot call the sub_button_click from itself.

So I am back to trying to update the recordsource of the subform such that it should return a full recordset once more.

Hoping you are really interested, I attach two images. The main form offers search fields. A successful search displays the set in the subform. A nil result displays an advice telling me there are no records. The form and subform are then clear of all data. If I press the Search button once more, the full recordset is once again displayed.

What I am trying to do is to immediately display the full recordset when the search produces a nil result.

What works nicely is the search when there are rows to show. It includes this succeeding statement:

Me![HyperSearch_SubForm].Form.RecordSource = MyRecordSource

So far so good.

When I try to change the Recordsource of the subform again - when there is a nil result - I get a message as per the one image; it says Microsoft cannot find it. This is the code:

Forms![HyperSearch_SubForm].Form.RecordSource = "SELECT * FROM Q_Super_Search_This "   ''   also tried without the .Form  and also with Me!

I just cannot see why this is so. Can you?

[June7]

Again, if selection of fields does not change, why change RecordSource? I use code that applies criteria to form Filter property. Process described by Allen Browne http://allenbrowne.com/ser-62.html

Only time I've ever coded to change a form's RecordSource is when form first opens and it is only to view records, no editing, then user closes form. RecordSource is not reloaded. So, never experienced this issue.

How is the variable MyRecordSource set?

If you want to provide db for analysis, follow instructions at bottom of my post.

[peterFisp]

Well, I do change the recordsource successfully in two spots; and in this case I just could not get rid of an error when the recordset was nil.

However, I now found the neat solution:

Private Sub Form_Current()
 
 
'' first I am hiding or showing some buttons; then 
 
To prevent clicking the ‘Show Details’ Button - and avoid going first or last when no sense
    If Me.Recordset.RecordCount = 0 Then
        Me.Button_OpensMainForm.Visible = False
        Me.cmdLast.Enabled = False
        Me.cmdNext.Enabled = False
   
    Else:
        Me.Button_OpensMainForm.Visible = True

So I hide it when it makes no sense using it.

And since you asked, this is the code that assembles the SQL statement:

Private Sub Search_Execute_Button_Click()
    On Error GoTo Err_Search_Execute_Button_Click

'       Search_Execute_Button generates and then executes the Query
'      It creates a WHERE clause using search criteria entered by user and
'      set RecordSource property of HyperSearch_SubForm.
 
    ArgCount = 0                 'Initialize argument count
    MyCriteria = ""              'Initialize SELECT statement

         MySQL = "SELECT * FROM Q_Super_Search_This WHERE "

'              Use values entered in search form header to build WHERE clause.

    AddToWhereTerm [Search_Field_1], "[Search_Field_1]", MyCriteria, ArgCount
    AddToWhere [Search_Field_2], "[Search_Field_2]", MyCriteria, ArgCount
    AddToWhereTerm [Search_Field_3], "[Search_Field_3]", MyCriteria, ArgCount
    AddToWhere [Search_Field_4], "[Search_Field_4]", MyCriteria, ArgCount
    AddToWhere [Search_Field_5], "[Search_Field_5]", MyCriteria, ArgCount
    
'       -->>>                   If no criterion specifed, return all records
'                               otherwise, assemble an SQL statement.
'                       confirmed this 26/09/2019 - when suppressed, the SQL stmt is missing the where clause; so produces an error

    If MyCriteria = "" Then
            MyCriteria = "True"
            MyRecordSource = MySQL & MyCriteria      ' very important for Null search 26/09/2019
       Else
           
            MyRecordSource = MySQL & MyCriteria & "ORDER BY Q_Super_Search_This.Search_Field_1"

    End If

    Me![HyperSearch_SubForm].Form.RecordSource = MyRecordSource
       
'           If no records match criteria, display message.
    
    If Me![HyperSearch_SubForm].Form.Recordset.RecordCount = 0 Then
    
        MsgBox "No entries match the criteria you entered. " & vbCrLf & "Review your criteria, then click the Find button again. ", 48, "No Entries Found"
    End If
    
        Me!Button_Clear.SetFocus

One of the two crucial subs is:

Private Sub AddToWhere(FieldValue As Variant, FieldName As String, MyCriteria As String, ArgCount As Integer)

    If FieldValue <> "" Then
        '  Add "and" for second and subsequent criterion.
        If ArgCount > 0 Then
            MyCriteria = MyCriteria & " and "
        End If

        '  Append criterion to empty or existing criteria
        '  Enclose FieldValue and asterisk in quotation marks

        MyCriteria = (MyCriteria & FieldName & " Like " & Chr(39) & FieldValue & Chr(42) & Chr(39))
        
        '  Increase argument count.
        ArgCount = ArgCount + 1
    End If
End Sub

The AddToWhereTerm additionally appends an asterisk for a more flexible search.