Requerying a subform after changing it's source query 1

[TheFoxy]

Hey,

I'm making a small DB for storing book details. I have one table, books, and I've made a simple form to lookup books and edit their records. The idea is to enter criteria in the row of text boxes at the top, and have the subform bring up any records that match.

The form is unbound, with boxes to enter details of the book(s) to be found. The user presses a button, and the subform is populated with matching records. This is done by VBA code behind the button, which generates an SQL statement based on what's entered in the boxes. The code then replaces the subfom's source query with that statement, and calls Me.subform.requery to re-run the query.

Dim db As DAO.database
Dim querymod As DAO.QueryDef

Dim strSQL As String
strSQL = "SELECT * FROM book WHERE "

Set db = CurrentDb
Set querymod = db.QueryDefs("qry_searchform_1")

If Me.author <> "" Then strSQL = (strSQL & "book.author_id=" & Me.author & " AND ")

If Me.title <> "" Then strSQL = (strSQL & "book.title=" & Me.title & " AND ")

If Me.type <> "" Then strSQL = (strSQL & "book.type_id=" & Me.type & " AND ")

If Me.cat <> "" Then strSQL = (strSQL & "book.cat_id=" & Me.cat & " AND ")

If Me.isbn <> "" Then strSQL = (strSQL & "book.isbn=" & Me.isbn)

'//remove trailing 'AND '
If (Right(strSQL, 4) = "AND ") Then
    strSQL = StrReverse(strSQL)
    strSQL = Replace(strSQL, StrReverse("AND "), "    ", , 1, vbBinaryCompare)
    strSQL = LTrim(strSQL)
    strSQL = StrReverse(strSQL)
End If

strSQL = (strSQL & ";")
    
querymod.SQL = strSQL

Me.subform.Requery
'// have also tried subform.requery

Set querymod = Nothing
Set db = Nothing

Unfortunately, the subform refuses to change. I've tried DoCmd.Requery("subform"), but that doesn't work either. I did read somewhere that the requery action reloads the query, while the requery method doesn't, but I've only found the one.

The queries themselves are generated fine, and when the form is closed/reopened, the last query to be generated is used to populate the subform as would be expected. It's just that requerying doesn't work!

 

[Dor100]

I sometimes have a similar problem so what I do is simply add docmd.close and docmd.openform "form name." It's a bit of two-step dance number, but so far it's been the only way to deal with such issues. Nobody's ever expressed any unhappiness about it and it's quick enough.

 

[rubbernilly]

I have a similar situation where I apply the SQL from a particular query (selected in a combobox) to the single subform object on a form.

Once I've set the SQL, I do a

me.subform.sourceobject = ""
me.subform.sourceobject= "Query.MyQuery"

I think that causes less bloat to the database than actually closing and opening the form.

Hope this helps.

 

[MajP]

Assume your subform control is named "subFrmControl". To reference the form on a subform control you use the following:

Me.controls("subfrmControl").form

So in your case, I think
Me.Subform.requery
does not work because you are trying to requery a control, and not its form. So, To reference a control (txtBxOne) on a subform:
Me.controls("subFrmControl").form.controls("txtBxOne")