refreshing data on a sub form

[jebushatescats]

Hello all I hope I can explain by problem well enough for you all to understand what is going on.
I have a form with a sub form, normally this form upens up with the sub form getting its data from a query.
There are times when I need to open the form and then teh sub form using a different set of crieria for the sub form.
When I execute the code below(for the first time) the sub form will open with data that is not properly filtered. IF I close the form and imdediatly execute the same code the sub form will contain the correct data. Can anyone explain what I am doing wrong regarding the requery command?

sSQlName = "SELECT * FROM qryResponse WHERE
qryResponse.Person = " & "'" &
sUserName & "'" & ";" ' w9734

'build criteria link and open elements form
stDocName = "frmElement"
stLinkCriteria = "[EFG_ElementType]=" & "'" &
sElementType & "'" & " and " &
"[DocId]in(" & sDocId & ")" & ""
DoCmd.OpenForm stDocName, , , stLinkCriteria

' refresh responses sub form on elements form
Set dbs = CurrentDb
Set qdf = dbs.CreateQueryDef("", sSQlName)
Set rs1 = qdf.OpenRecordset(dbOpenDynaset)
Form_frmElement.sfResponseSubForm.Form.RecordSource = sSQlName
Form_frmElement.sfResponseSubForm.Requery


thanks!!

 

[emik]

I don't know if this will work, but on all my Access forms with subforms, whenever I update a value in a table I refresh the parent form itself:

Form_yourFormName.Refresh

If that doesn't work you can try to requery the subform, I forget the exact reference but it's something like

Form_yourFormName.YourSubForm.Refresh (or .requery)

Hope this helps

 

[jebushatescats]

Hey all, I wanted to answer my own question, partly so I can refernece it in the future but also couse this has been a real pain in my@##$#.
What has worked for me is the following
open up the form passsing in the criteria to be used
close form
then re-open form
do a requery on sub form

DoCmd.OpenForm stDocName, , ,stLinkCriteria
DoCmd.Close
DoCmd.OpenForm stDocName, , , stLinkCriteria
Set dbs = CurrentDb
Set qdf = dbs.CreateQueryDef("", sSQlName)
Set rs1 = qdf.OpenRecordset(dbOpenDynaset)
Form_frmElement.sfResponseSubForm.Form.RecordSource = sSQlName
Form_frmElement.sfResponseSubForm.Requery


just doing the requery is not enough, for what ever wierd and flukie reason the form needs to be closed and reopened.
Not the cleanest of fixes but it works

 

[Zion7]

no offense jebu..., but A LOT OF REDUNDANCY, going on there.
Why a querydef??? your SQL statement, is your SQL statement,
why a query def?
Very haphazard to open & reopen form.
Where do yiou get sSQLName from?
What's rs1 for?

If you showed all the code, we might be able to see, why
a double open is required???