Requery main form vs. subform problem

[millers92]

I have a main form (Main Archive Form) with a subform (frmarchivesubform). The subform's record source is a query called "Dynamic_Query". On the main form I have a combo box and two text boxes that specify search criteria and a command button that executes the search.

The command button has the following code in it:

Private Sub Command48_Click()
Dim db As DAO.Database
Dim QD As DAO.QueryDef
Dim SQL As String
Dim vWhere As Variant

Set db = CurrentDb()

On Error Resume Next
db.QueryDefs.Delete "Dynamic_Query"
On Error GoTo 0

vWhere = Null

If IsNull(Me.StartDispatchDate) And IsNull(Me.EndDispatchDate) Then
vWhere = vWhere & (" and [Drop Point(s)]='" + Me.Drop_Point_s_ + "'")
End If

If IsNull(Me.StartDispatchDate) = False And IsNull(Me.EndDispatchDate) = True Then
vWhere = vWhere & (" and [Drop Point(s)]='" + Me.Drop_Point_s_ + "'")
vWhere = vWhere & (" and [Dispatch Date]=#" & Me.StartDispatchDate & "#")
End If

If IsNull(Me.StartDispatchDate) = False And IsNull(Me.EndDispatchDate) = False Then
vWhere = vWhere & (" and [Drop Point(s)]='" + Me.Drop_Point_s_ + "'")
vWhere = vWhere & (" and [Dispatch Date] between #" & Me.StartDispatchDate & "# AND #" & Me.EndDispatchDate & "#")
End If

SQL = "SELECT * FROM [Forecast] " & (" WHERE " + Mid(vWhere, 5))

Set QD = db.CreateQueryDef("Dynamic_Query", SQL)

Me.frmArchivesubform.Form.Requery
End Sub


Everything displays correctly when the Main form first loads, but when I put new criteria in and click the command button, nothing happens. But, if I were to close down the Main form and get back in, then my new criteria would be there. This tells me that my Query is being updated properly.

Could someone give me some insight as to why my subform display is not being updated?

Thanks!

Steve

 

[casey]

Try the full reference name to the form...

Forms!frmMainFormName.frmArchivesubform.Form.Requery

instead of...
Me.frmArchivesubform.Form.Requery

 

[millers92]

I tried what you suggested and it still won't refresh the data.

Any other suggestions?

Steve

 

[casey]

Double check your form names. What do have them called on the form. For instance, when a subform is dropped on the mainform sometimes it defaults to a "Child" name even when the form's named something else. Make sure this is not the case.

frmMainFormName should be replaced by the actual name of your main form...

then if your subform's named frmArchivesubform, it should work

Also make sure that you've saved your changes. File>>Save

If that doesn't work, then set a breakpoint in the code preceding the Forms!frmMainFormName.frmArchivesubform.Form.Requery and step through the code to see that it's actually reaching that code.
HTH

 

[casey]

I'm noticing that your QueryDef may not be resetting your subform's recordsource either. I'm not completely sure of the procedure you've submitted, but couldn't you set the recordsource directly, therefore bypassing your query.

SQL = "SELECT * FROM [Forecast] " & (" WHERE " + Mid(vWhere, 5))

Forms!frmMainFormName.frmArchivesubform.Form.Recordsource=SQL
Forms!frmMainFormName.frmArchivesubform.Form.Requery

 

[millers92]

Thanks Casey! I did what you suggested and it worked like a charm!

Like you said, it looks like my QueryDef wasn't updating the subform.

I appreciate all of your help!!!!!

Steve