programatically update a chart on a form

[jonefer]

I tried updating a querydef in VBA, but I'm not having any success getting
the chart to update on the form:

If I manually change the query, close the form and then open it up again, it
changes. So it must be possible to do it programatically.

I've included my code.

The name of my chart object on the form is 'chCOLO'
The chart just needs 2 parameters to change the query:
Clinic, and PCP

I'm using the following two routines:

Private Sub cmdSearch_Click()
Call UpdateChartDefinitions(Me.cmbClinic.Value, Me.lblSelectedPCPs.Caption)
Me.chCOLO.Requery

End Sub

Private Sub UpdateChartDefinitions(strClinic As String, strPCP As String)
Dim strQry As String
Dim qdfCOLO As DAO.QueryDef
Dim rs As DAO.Recordset
Dim db As DAO.Database


strQry = "SELECT [PCP LOC], [PCP NAME], COLO, YYYYMM " _
& "FROM qAllMonths " _
& "WHERE [PCP LOC]= '" & strClinic & "' AND [PCP NAME] " & strPCP & " AND
COLO >0;"



Set db = CurrentDb
Set qdfCOLO = db.QueryDefs("qAllCOLO")
qdfCOLO.SQL = strQry

db.QueryDefs.Refresh


Set qdfCOLO = Nothing
Set db = Nothing

End Sub

 

[Marshall Barton]

I tried updating a querydef in VBA, but I'm not having any success getting
the chart to update on the form:

If I manually change the query, close the form and then open it up again, it
changes. So it must be possible to do it programatically.

I've included my code.

The name of my chart object on the form is 'chCOLO'
The chart just needs 2 parameters to change the query:
Clinic, and PCP

I'm using the following two routines:

Private Sub cmdSearch_Click()
Call UpdateChartDefinitions(Me.cmbClinic.Value, Me.lblSelectedPCPs.Caption)
Me.chCOLO.Requery
End Sub

Private Sub UpdateChartDefinitions(strClinic As String, strPCP As String)
Dim strQry As String
Dim qdfCOLO As DAO.QueryDef
Dim rs As DAO.Recordset
Dim db As DAO.Database

strQry = "SELECT [PCP LOC], [PCP NAME], COLO, YYYYMM " _
& "FROM qAllMonths " _
& "WHERE [PCP LOC]= '" & strClinic & "' AND [PCP NAME] " & strPCP & " AND
COLO >0;"

Set db = CurrentDb
Set qdfCOLO = db.QueryDefs("qAllCOLO")
qdfCOLO.SQL = strQry

db.QueryDefs.Refresh

[]

You need an = (or some comparison) and if [PCP NAME] is a
text field, you also need more quotes:

.... & "' AND [PCP NAME] = '" & strPCP & "' AND ...

 

[jonefer]

oh yeah, strPCP produces in the variable either '=pcp A' (for one entry) or
'IN ('pcp C', 'pcp D', 'pcp H') for multiple entries.

The problem that I am experiencing is that I can get the update to happen
only if I completely close the form and reopen it.

Shouldn't that be accomplished by me.requery and me.refresh? along with the
requery for each of the charts?

I tried updating a querydef in VBA, but I'm not having any success getting
the chart to update on the form:

If I manually change the query, close the form and then open it up again, it
changes. So it must be possible to do it programatically.

I've included my code.

The name of my chart object on the form is 'chCOLO'
The chart just needs 2 parameters to change the query:
Clinic, and PCP

I'm using the following two routines:

Private Sub cmdSearch_Click()
Call UpdateChartDefinitions(Me.cmbClinic.Value, Me.lblSelectedPCPs.Caption)
Me.chCOLO.Requery
End Sub

Private Sub UpdateChartDefinitions(strClinic As String, strPCP As String)
Dim strQry As String
Dim qdfCOLO As DAO.QueryDef
Dim rs As DAO.Recordset
Dim db As DAO.Database

strQry = "SELECT [PCP LOC], [PCP NAME], COLO, YYYYMM " _
& "FROM qAllMonths " _
& "WHERE [PCP LOC]= '" & strClinic & "' AND [PCP NAME] " & strPCP & " AND
COLO >0;"

Set db = CurrentDb
Set qdfCOLO = db.QueryDefs("qAllCOLO")
qdfCOLO.SQL = strQry

db.QueryDefs.Refresh

[]

You need an = (or some comparison) and if [PCP NAME] is a
text field, you also need more quotes:

.... & "' AND [PCP NAME] = '" & strPCP & "' AND ...

 

[Marshall Barton]

Ahhh. that's different. In that case, I would expect
requerying the chart to be sufficient.

I don't think closing and reopening the form accomplishes
much more than requerying the form, but, from what you've
posted, I don't see why that would be needed.
--
Marsh
MVP [MS Access]

oh yeah, strPCP produces in the variable either '=pcp A' (for one entry) or
'IN ('pcp C', 'pcp D', 'pcp H') for multiple entries.

The problem that I am experiencing is that I can get the update to happen
only if I completely close the form and reopen it.

Shouldn't that be accomplished by me.requery and me.refresh? along with the
requery for each of the charts?

I tried updating a querydef in VBA, but I'm not having any success getting
the chart to update on the form:

If I manually change the query, close the form and then open it up again, it
changes. So it must be possible to do it programatically.

I've included my code.

The name of my chart object on the form is 'chCOLO'
The chart just needs 2 parameters to change the query:
Clinic, and PCP

I'm using the following two routines:

Private Sub cmdSearch_Click()
Call UpdateChartDefinitions(Me.cmbClinic.Value, Me.lblSelectedPCPs.Caption)
Me.chCOLO.Requery
End Sub

Private Sub UpdateChartDefinitions(strClinic As String, strPCP As String)
Dim strQry As String
Dim qdfCOLO As DAO.QueryDef
Dim rs As DAO.Recordset
Dim db As DAO.Database

strQry = "SELECT [PCP LOC], [PCP NAME], COLO, YYYYMM " _
& "FROM qAllMonths " _
& "WHERE [PCP LOC]= '" & strClinic & "' AND [PCP NAME] " & strPCP & " AND
COLO >0;"

Set db = CurrentDb
Set qdfCOLO = db.QueryDefs("qAllCOLO")
qdfCOLO.SQL = strQry

db.QueryDefs.Refresh

[]