Problem with requery a subform

[zixingw via AccessMonster.com]

I inserted a form (treate it as a subform) into a main form, an also added a
combobox to the main form as a parameter select control for the record source
of that subform. The basic codes in a click cmdButton are as follows:
......
Dim db as DAO.Database
Dim qryDef as DAO.QueryDef
Dim strSQL as String
strSQL ="Select .. from .. Where MyId='" & combBox.Value & "'"
Set db=currentDB
Set qryDef=db.QueryDefs("MyQuery")
qryDef.SQL= strSQL

Me.frmSubFormName.Form.Requery
Me.Requery
..................

When I select a value from combBox and click the cmdButton, The data in the
subform does not updated, until I close the main form and open the main form
again.

I did a lot of ways that posts on Website, such as using Docmd.Reqeury "Me.
subform.Form" etc, to resolve this issue, but all are failed.

Any one can help me.

Thanks,

Zixing

 

[NevilleT]

Better to use a query for the subform that has a parameter of the combBox
value and then requery the subform. In the code you have created you need to
set the recordsource of the subform to the query you created.

 

[zixingw via AccessMonster.com]

The query named "MyQuery" is the query (a real query object) for the subform.
When I close the main form and re-open it, it works, but it does not work
when I just click the button to execute this procedure.

Thanks,

Zixing

 

[NevilleT]

Hi Zixing

What I was suggesting is a query for the subform that has a WHERE clause
referring to the main form. You do not have to worry about setting a
querydef. Just run a subform requery. Don't requery the main form or you
may loose your combo box selection.

Query: WHERE tblMyTable.Criteria = Forms!frmMainForm.cmbSelection

CmdButton. Me.frm.SubFormName.Requery

Neville Turbit
www.projectperfect.com.au