Form Refresh problem

[DB88]

Hello,



I have a form bound to a query and a VBA code that changes that query's definition (.QueryDefs). The code includes a form refresh (Me.Refresh) but the records don't update. If I close and then re-open the form then the records update. But this is a user interface so I don't want to do that.

Am I doing something wroing with .Refresh or will that just not work in this case?

[Bob Fitz]

Try Requery instead of Refresh

[DB88]

That worked!!! Thank you so much, that was the last piece of a Search Interface I've been building for 4 days! It works Happy Dance!!!

[Bob Fitz]

That worked!!! Thank you so much, that was the last piece of a Search Interface I've been building for 4 days! It works Happy Dance!!!

You're welcome. Glad to help

[DB88]

whoops spoke to soon ( pause happy dance).

[Bob Fitz]

whoops spoke to soon ( pause happy dance).

Sorry, I don't understand. Do you still have a problem.

[DB88]

Yes, the form doesn't update. If I close the form and then re-open it then the correct records show. Me. Refresh and Me.Requiry do not change the displayed records

[Bob Fitz]

Me.Requery does work. Please post your code.

[DB88]

If lngLen1 > 0 Then
CurrentDb.QueryDefs("SubSearch1").SQL = strQ1
Else: CurrentDb.QueryDefs("SubSearch1").SQL = "SELECT DISTINCT Variant FROM Variant"
End If
If lngLen2 > 0 Then
CurrentDb.QueryDefs("SubSearch2").SQL = strQ2
Else: CurrentDb.QueryDefs("SubSearch2").SQL = "SELECT DISTINCT Variant FROM Variant"
End If
If lngLen3 > 0 Then
CurrentDb.QueryDefs("SubSearch3").SQL = strQ3
Else: CurrentDb.QueryDefs("SubSearch3").SQL = "SELECT DISTINCT Variant FROM Variant"
End If
If lngLen4 > 0 Then
CurrentDb.QueryDefs("SubSearch4").SQL = strQ4
Else: CurrentDb.QueryDefs("SubSearch4").SQL = "SELECT DISTINCT Variant FROM Variant"
End If
CurrentDb.QueryDefs("CombineSearch").SQL = "SELECT SubSearch1.Variant, SubSearch2.Variant, SubSearch3.Variant, SubSearch4.Variant FROM SubSearch4 INNER JOIN (SubSearch3 INNER JOIN (SubSearch2 INNER JOIN SubSearch1 ON SubSearch2.Variant = SubSearch1.Variant) ON SubSearch3.Variant = SubSearch1.Variant) ON SubSearch4.Variant = SubSearch1.Variant"
Me.Requery

This is just the portion of the code that sets the Queries and then does a refresh. I figured you didn't need to rest where I set up the strings.