Refresh subform with a click of a button? 3

[BotCow]

Okay I have a search form that executes code to send an SQL statement into a query with a click of a button. However, I want that same button to refresh a subform to display the results. How would I go about doing this. Right now, I have that subform displaying the query so for example, I'll search for "dog" and click search. It appears that nothing happens and I just exit the form and go back in, the search results are displayed. I want it so I can click on the button and the results are displayed instantaneously onto the subform. Thanks for your help.

 

[swaybright]

As the last line in the cmd button code, put

'button is on main form
Me.subformname.requery

'or button is on subform
Me.requery

'or button is on another subform
Me.parent.subformname.requery

Shane

 

[BotCow]

It can't seem to find the subform name in the code: Me.subformname.requery (in my case, Me.frmSearchResults.requery). It says method or data member not found.

 

[swaybright]

Check the name property of your subform and make sure it really is frmSearchResults. If that checks out, try

Me!frmSearchResults.requery

Shane

 

[BotCow]

Sadly it still doesn't work.

 

[Edski]

Have you tried Me.Refresh ?

 

[BotCow]

How do I get that to target the subform? (the button is on the main form)

 

[swaybright]

Me.subformname.refresh

 

[BotCow]

It doesn't seem to work still. Maybe I'm explaining my problem wrong. Okay I have a main form. It has a few text boxes that take input from a user in different categories and searches based upon what the user enters. The query is passed to a subform (in typical datasheet view) which is on the bottom of the main form. What I want this search form to refresh with the new search results in the subform below. Basically instead of popping up a new window after the button is clicked, an integrated window with search results will just refresh.

 

[BotCow]

Edit: Maybe I have the "subformname" wrong. It's just the title of the form right (caption)?

 

[Eupher]

Nope. It's the name of the subform control. There is no caption property for a subform control (although there is a caption for the label control that is attached to the subform control). If you open your main form in design view, then click on the subform, then click the "Properties" icon on the toolbar, the subform's property sheet will appear. The title of the property sheet will say "Subform/Subreport:" followed by the name of the subreport. The first property in the list is the name.

Ken S.

 

[BotCow]

Great it doesn't give me an error now. However, it still doesn't update with the current data. I used requery though because Refresh seemed to generate an error. The screen "blinks" at me like it is being refreshed but the subform doesn't update. (I used Me.frmSearchResults.Requery... Me.frmSearchResults.Refresh gives me a Method or Data member not found error, highlighting '.Refresh').

 

[Eupher]

Can you post the button's code? All of it, that is, not just the part where you're trying to refresh the subform. I'd like to see what the code is doing with the recordset.

Ken S.

 

[BotCow]

Public Sub cmdSearch_Click()

'Declarations'
Dim strSQL As String, strWhere As String, strOrder As String
Dim dbNm As Database
Dim qryDef As QueryDef

'Assignments'
Set dbNm = CurrentDb()
tblName = "tbl" & Me!cmbsource
strSQL = "SELECT [Page], [Date], Subject, Company, People, Diagram FROM " & tblName
strWhere = "WHERE"
strOrder = "ORDER BY " & tblName & ".Page"

'Error checking for two dates
If IsNull(Me.txtDate2) Or Me.txtDate2 = "" Then
MsgBox "Please enter an end date.", vbOKOnly, "Cannot perform search"
Else

'SQL Statement Construction'
If Not IsNull(Me.txtSubject) Then
strWhere = strWhere & " (" & tblName & ".Subject) Like '*" & Me.txtSubject & "*' AND"
End If

If Not IsNull(Me.txtPeople) Then
strWhere = strWhere & " (" & tblName & ".People) Like '*" & Me.txtPeople & "*' AND"
End If

If Not IsNull(Me.txtCompany) Then
strWhere = strWhere & " (" & tblName & ".Company) Like '*" & Me.txtCompany & "*' AND"
End If

If Not IsNull(Me.txtDate) Then
strWhere = strWhere & " (" & tblName & ".Date) Between #" & Me.txtDate & "# AND #" & Me.txtDate2 & "# AND"
End If

If Me.chkDiagram = True Then
strWhere = strWhere & " (" & tblName & ".Diagram) = TRUE AND"
Else
strWhere = strWhere & "(Diagram) = FALSE AND"
End If

'Remove the last AND from the SQL statment
strWhere = Mid(strWhere, 1, Len(strWhere) - 5)

'Pass the QueryDef to the query
Set qryDef = dbNm.QueryDefs("qrySearch&quot
qryDef.SQL = strSQL & " " & strWhere & " " & strOrder

End If

Me!frmSearchResults.Requery

End Sub

-------------

I might be thinking about doing a close and open form to just refresh it that way. I just can't get the open form function to work (hehe never actually used it). I'm quite young at Vb.

 

[BotCow]

Well I got the close/open form to work and I think that works okay. So if you don't feel like sorting through all that junk above, then don't worry about it. In fact, closing and opening the form to get the results has the effect of the form actually "refreshing". This might work better because the user can actually see the form do something instead of blinking.

 

[swaybright]

Maybe this will work...Instead of passing your sql to a query, set the recordsource of the subform to it. I think this syntax is right...

Me!frmSearchResults.form.RecordSource = strSQL & " " & strWhere & " " & strOrder
Me!frmSearchResults.Requery

Shane

 

[Eupher]

I'll futz with it a little this evening and see what I come up with...

Ken S.

 

[Eupher]

BotCow, swaybright is correct. I created a little test database using your field names, your code, everything. I ran into the very same obstacles. After doing as swaybright suggested and setting the subform's recordsource to the sql string, it worked perfectly. And you don't even need the requery statement.

strSQL = strSQL & " " & strWhere & " " & strOrder
Me!frmSearchResults.Form.RecordSource = strSQL

I suggest leaving the subform unbound, or if you want some data to be displayed when the user first opens the search form, set it to one of your tables. Unless you are using them elsewhere, it appears you don't need your query or querydef at all in this context.

Ken S.

 

[Eupher]

This has come up again in another thread so I fiddled with things a bit more and found you CAN simply change the SQL of the querydef. However, instead of requerying the subform, you need to re-assign the querydef as the subform's recordsource, i.e.:

Set qryDef = dbNm.QueryDefs("qrySearch")
   qryDef.SQL = strSQL & " " & strWhere & " " & strOrder

Me!frmSearchResults.Form.RecordSource = "qrySearch"

That should do it.

Ken S.

 

[BotCow]

Ahhh very nicely done and explained eupher. Thanks a lot.