Requery subform

[pascal]

Hello,

I have a form (called MyParentForm) with a subform (called MySubForm). On MyParentForm I have a text field called "txtState" and a command button called "cmdFind". The subform is based on a query I call here "MyQuery". When I fill in a state in the text field and click on the command button I want my subform to be filled with the records containing that state.

Now, here is the code that I've put in the click-event of the command button:

Private Sub cmdFind_Click()

Dim db As DAO.Database
Dim qdf As DAO.QueryDef
Dim strSQL As String
Set db = CurrentDb
Set qdf = db.QueryDefs("MyQuery")

strSQL = "SELECT [MyTable].* FROM [MyTable] WHERE [MyTable].State = '" & txtState & "';"

qdf.SQL = strSQL

End Sub

So far the code. Now, what do I have to put after the code qdf.SQL = strSQL to let the command button look up all the records containing the State choosen in the text field on my parent form?

Thanks in advance.

 

[WayneRyan]

pascal,

Me.MySubForm.Requery

Wayne

 

[pascal]

Hello again,

Thanks Wayne for your reply. I've tried it out and the code does update the query but it's not shown in my subform. I have to close the form first and then reopen it to see the new result.

Anyone who can help me further with this one?

Thanks

 

[WayneRyan]

Hi pascal,

I forgot. When a form opens, it assigns the recordsource immediately.
You have to reassign the recordsource, then requery. This way it
recognizes the NEW recordsource.

qdf.sql = strSQL
qdf.Close
Me.MySubform.RecordSource = "MyQuery"
Me.MySubform.Requery

Wayne

 

[pascal]

Wayne,

Thanks once again. This time it works fine although I've left 2 lines of code you've written it still works fine.

This is the final code now:

Private Sub cmdFind_Click()

Dim db As DAO.Database
Dim qdf As DAO.QueryDef
Dim strSQL As String
Set db = CurrentDb
Set qdf = db.QueryDefs("MyQuery")

strSQL = "SELECT [MyTable].* FROM [MyTable] WHERE [MyTable].State = '" & txtState & "';"

qdf.SQL = strSQL

Me.MySubform.Form.RecordSource = "MyQuery"

End Sub

Now, why did you insert the two following lines in it:

qdf.Close
Me.MySubform.Requery

It works fine without them, or is it better to use them anyway? Why using them?

Thanks

 

[WayneRyan]

pascal,

They were just in "some old thing" that I had lying around. To be honest,
I rarely work with QueryDefs.

I usually have a fixed query. In this case, the criteria for the State field
would be Forms![MyParentForm]![txtState]. Then when the text box
txtState changed, I only Me.MySubForm.Requery.

Just easier.

Glad to see it is working though ... see ya
Wayne