CODEWITHSUNDEEP
ms-accessvbams-access-2010
Christa Foster
Christa Foster

Reputation: 3

Me.Requery appears to be doing no action on form

I have searched and have found a lot of information on using requery on a subform, but I can't seem to find anything that indicates attempting to requery the active form with a new recordset.

I have a form based on a query. I am using an unbound text box to capture the address which needs to be searched then changing the sql statement in the query to locate the records then attempting to use me.requery to load the new results.

The code is updating the sql statement, but the form is not requerying with the new record results. My code is below.

I am fairly new to access and VBA, and appreciate any wisdom you may have. Also, is there ANYTHING that I could be doing in other code which would cause this to fail?

Private Sub Command51_Click()

Dim d As DAO.Database
Dim q As DAO.QueryDef
Dim Addy As String
Dim Search As String


Set d = CurrentDb()
Set q = d.QueryDefs("SQL_Search")

If IsNull(Me!Addy) Then
    MsgBox ("Please select a valid address from the list and try again.")
    GoTo CleanUp
Else: End If

Addy = Me!Addy

Search = "select * from dbo_ECNumberVerify Where (((dbo_ECNumberVerify.invalidrecord)=False) AND ((dbo_ECNumberVerify.updated)=False) AND ((dbo_ECNumberVerify.Locations) Like '*" & Addy & "*'));"

'Send SQL SP execute command.
q.SQL = Search

Me.Requery

CleanUp:

Set q = Nothing
Set db = Nothing

End Sub

Upvotes: 0

Views: 160

Answers (1)

Albert D. Kallal
Albert D. Kallal

Reputation: 49204

In your example you have a query, but the query is never set or attached to the forms record source in "any way". So the “query” acts independent from the form data source.

You can simply stuff the sql directly into the forms reocrdsouce like this:

Me.RecordSource = Search

(so you don’t need all of your existing code, nor do you need the queryDef.

And when you set the forms SQL directly as per above, then a requery is done automatic for you. So the code required will look like this:

Dim strSearch     As String

If IsNull(Me.Addy) Then
   MsgBox ("Please select a valid address fromthe list and try again.")
   Exit Sub
End If

strSearch = "select * from dbo_ECNumberVerify WHERE " & _
            "(invalidrecord = False) AND (updated = False) AND " _
            "(Locations Like '*" & Addy & "*')"
Me.RecordSource = strSearch

So you don't need much code, and you really don't need to use + declare the querydef at all.

Upvotes: 1

Related Questions