CODEWITHSUNDEEP
ms-accessvba
Paolo Bernasconi
Paolo Bernasconi

Reputation: 2030

How to Requery a subform inside a form?

I'm having a problem in which I can't requery a subform inside of a form in Access.

The form's name is frmSearch The subform's name is SearchResults

I've tried

Private Sub Command38_Click()

Me!SearchResults.Form.Requery (or)
Me.SearchResults.Form.Requery

End Sub

My form & subform look like this:

enter image description here

To be clear, I'm using the "Search" button to create a string which contains the textbox and combobox values. This string creates a SQL query called qryTrialQuery. Then my subform makes a query of the qryTrialQuery and produces the results in the table bellow.

I would like to be able to press the search button and then the results appear below it immediately after. The problem is, is that the results don't appear unless I close and reopen the form.

Thanks for all your help in advance.

Update

The following is the code I used to create a query from the textbox and combobox values.

LineOne = "SELECT tblPoolPersonnel.LName, tblPoolPersonnel.FName, tblPoolPersonnel.[Tel Natel], tblPoolPersonnel.[Tel Home], tblPoolPersonnel.Email" & vbCrLf
LineTwo = "FROM (tblPoolPersonnel INNER JOIN tblDayAvailable ON tblPoolPersonnel.Code_Personal = tblDayAvailable.Code_Personal) INNER JOIN tblServiceYES ON tblPoolPersonnel.Code_Personal = tblServiceYES.Code_Personal" & vbCrLf
LineThree = "WHERE (((tblServiceYES.Service)=" & comboService & ") AND ((tblDayAvailable.Availability)=True) AND ((tblDayAvailable.Date)=" & txtDate & ") AND ((tblDayAvailable.CodeHoraire1)=" & comboCodeHoraire & "));"

Set qdf = CurrentDb.QueryDefs("myQuery")
Application.RefreshDatabaseWindow
strSQL = LineOne & LineTwo & LineThree
qdf.SQL = strSQL
qdf.Close
Set qdf = Nothing
Set dbs = Nothing

Upvotes: 1

Views: 27625

Answers (2)

Renaud Bompuis
Renaud Bompuis

Reputation: 16806

Assuming you are reconstructing the SQL of your query based on the criteria the user selected, you should just be able to do something like this:

Private Sub Command38_Click()
    Dim qryTrialQuery as String
    ...
    ' code to construct the SQL SELECT statement for the query, '
    ' based on the criteria the user entered                    '
    ...
    SubForm.Form.RecordSource = qryTrialQuery
End Sub

Setting the Subform's RecordSource will refresh the data.

Upvotes: 4

Jay
Jay

Reputation: 1216

You can try this:

Dim frm as Form
Set frm = frmSearch
frmSearch!SearchResults.Form.Requery

Upvotes: 2

Related Questions