Sub Form Refresh

[Abes]

I have this problem which I am unable to resolve, even from reading items in
this newsgroup. I am sure it is something I have missed but I cannot find it.

I have a main form (frmScheduleTippers) which contains a subform called
(sfrmScheduleTips). A combo box (cboRound) on the main form provides a value
which is passed to a query (qryCompetition2), along with other values that
are constant (such as Names and Competition), via VBA code. (The values for
firstname,LastName and Competition are passed by another form.)

My problem is that I cannot 'force' a refresh/requery of the subform data
(to reflect the data passed to the query) no matter what I try, unless I
close and reopen the entire form.

I have checked and the values of the SQL string (and therefore
qryCompetition2) are correct, as I change the combo box.

The code i am using is as follows:
Private Sub cboRound_AfterUpdate()
'To set the variables for the Query
Dim db As DAO.Database
Dim qdf As DAO.QueryDef
Dim strSQL As String
Dim strCompetition As String
Dim txtRoundNo As String
Set db = CurrentDb
Set qdf = db.QueryDefs("qryCompetition2")

'Get data for the query, from the Competition form

strSQL = "SELECT tblTips.* " & _
"FROM tblTips " & _
"WHERE tblTips.LastName='" & Me.txtTipperLastName.Value & "'" & _
"AND tblTips.FirstName='" & Me.txtTipperFirstName.Value & "'" & _
"AND tblTips.Competition='" & Me.txtipperCompetition.Value & "'" & _
"AND tblTips.RoundNo='" & Me.cboRound.Value & "'" & _
"ORDER BY tblTips.GameNo;"

qdf.SQL = strSQL

' This code to refresh the subform,dependant on the Round
' selected on main form

Me.sfrmScheduleTips.Visible = True
' Me.sfrmScheduleTips.SetFocus
Me.sfrmScheduleTips.Requery
' DoCmd.Requery "sfrmScheduleTips"

' Forms!frmScheduleTippers.Form!sfrmScheduleTips!txtRoundNo.SetFocus
' Forms!frmScheduleTippers.Form!sfrmScheduleTips!txtRoundNo.Requery
' txtRoundNo = Me.sfrmScheduleTips!txtRoundNo
' Forms!frmScheduleTippers!sfrmScheduleTips!txtRoundNo.SetFocus
' Forms!frmScheduleTippers!sfrmScheduleTips.Requery
DoCmd.RunCommand acCmdRefreshPage

Debug.Print strSQL
' Debug.Print txtRoundNo

Set qdf = Nothing
Set db = Nothing


End Sub

The commented actions are ones that I have attempted but to no result.

Any assistance to point out my error is greatly appreciated

 

[John Vinson]

'Get data for the query, from the Competition form

strSQL = "SELECT tblTips.* " & _
"FROM tblTips " & _
"WHERE tblTips.LastName='" & Me.txtTipperLastName.Value & "'" & _
"AND tblTips.FirstName='" & Me.txtTipperFirstName.Value & "'" & _
"AND tblTips.Competition='" & Me.txtipperCompetition.Value & "'" & _
"AND tblTips.RoundNo='" & Me.cboRound.Value & "'" & _
"ORDER BY tblTips.GameNo;"

qdf.SQL = strSQL

Rather than changing the SQL of the (volatile, not used anyplace other
than your code) qdf object, set the Subform's Recordsource to strSQL.

John W. Vinson[MVP]

 

[Abes]

John,

VMT for your prompt response. However I am confused as to what you are
suggesting.
The problem subform currently has 'qyrCompetition2' as its recordsource
(which in turn gets it's data frm strSQL). The debug.print statement (and if
I view the query 'Competition2') its data is correct) so my problem appears
to be refreshing the subform.
If I close the main form and open it the subform records are the ones it
recieved (remembered) when it opened. ie if I selected 2 in the drop down box
closed the main form, opened it again, selected 5 in the drop down combo,
then the subform would show the records associated with '2', and so on.

Are you suggesting that the subform recordsource should be changed to =
strSQL? If so how do I go about referencing it to that?


--
Cheers

Abes

 

[John Vinson]

Are you suggesting that the subform recordsource should be changed to =
strSQL?

Yes. But see and alternative below.

If so how do I go about referencing it to that?

Me!subformname.Form.Recordsource = strSQL

Alternatively, you can set the Subform's Master Link Field property to
the name of the combo box on the mainform (if the combo box's name is
the same as the name of a table field, I think you'll need to change
it to remove the ambiguity). This approach needs no code at all!

John W. Vinson[MVP]

 

[Abes]

John,

Thanks for that, solved my problem.

Appreciate your help

--
Cheers

Abes