Report VBA problem

[Matt Winberry]

Hi all,
I'm having trouble with the following setup. I've been tasked with creating
an Access DB with a report which will use graphic symbols for different
status conditions, (ie. green up arrow to indicate positive progress, red
down arrow to indicate negative progress, yellow double headed horizontal
arrow to indicate no change) and I have no leeway on changing the the
requirement for the symbols. My idea was to create a table with Yes/No values
for each status (schedule_up, schedule_down, schedule_same) linked to option
buttons on a form. The report would look at the status of the field and,
depending on whether it were set to True or False, set the visibility of the
graphic to True or False. I've been try to use the following code block to
get started for testing purposes, and can't get the report to turn my test
graphic off based on test data. The graphic is visible for all records, not
just the records with the True Schedule_Up value. Can someone tell me what I
might be doing wrong? Thanks!

Matt

Private Sub VBAExample()
Dim db As Database
Dim rst As Recordset

Set db = CurrentDb()
Set rst = db.OpenRecordset("Input")
rst.MoveFirst

While Not rst.EOF
If rst!Schedule_Up = True Then
SchedUp.visibility = True
Else
SchedUp.visibility = False
End If

rst.MoveNext
Wend

rst.Close

End Sub

 

[Evi]

Hi matt, put a simpler version of the code in the report itself in On Format
Event of the section which contains Schedup



If me.Schedule_Up = True Then
SchedUp.visible = True
Else
SchedUp.visibile = False
End if


ie in Report Design view
Click on the grey bar just above the section that contains your control
Click on the Properties button
Click on the Events tab
Choose Event Procedure next to On Format
Click just right of that to open a code page
Just above End Sub, type the above code.
(note visible - not visibility)

Evi

 

[Matt Winberry]

Thanks Evi! I'm getting an error now, which is so much more positive than
nothing at all. The error is '2465', evidently it can't find my field for
Schedule_Up. I've noticed that my project window only shows me the Report
itself, and doesn't allow me to look for controls in the Table. I had been
trying to define the table in the If statement previously, but no matter what
arguments I used (Tables.Input.Schedule_Up, Tables!Input!Schedule_Up) it
would keep telling me "Object not found".

Thank you, for giving me the solution to move this forward.

 

[Evi]

I see that you are a VBA whizz and are trying to live VBA in Access.

Open the report in Design View. You won't need the Project Window very much
in Access.

Schedule_Up needs to be in your report. If you don't want to see it, use the
Properties button on the Format tab when you are clicked on it and set
Visible to no.

Click the Field List button on the toolbar and slide the field it onto the
report. If it isn't there, then click beyond the report grid and in
Properties, Data, click next to Data Source to open up the Table or Query on
which the report is based.

Add ScheduleUp to the query grid

Evi

Thanks Evi! I'm getting an error now, which is so much more positive than
nothing at all. The error is '2465', evidently it can't find my field for
Schedule_Up. I've noticed that my project window only shows me the Report
itself, and doesn't allow me to look for controls in the Table. I had been
trying to define the table in the If statement previously, but no matter what
arguments I used (Tables.Input.Schedule_Up, Tables!Input!Schedule_Up) it
would keep telling me "Object not found".

Thank you, for giving me the solution to move this forward.

Hi matt, put a simpler version of the code in the report itself in On Format
Event of the section which contains Schedup



If me.Schedule_Up = True Then
SchedUp.visible = True
Else
SchedUp.visibile = False
End if


ie in Report Design view
Click on the grey bar just above the section that contains your control
Click on the Properties button
Click on the Events tab
Choose Event Procedure next to On Format
Click just right of that to open a code page
Just above End Sub, type the above code.
(note visible - not visibility)

Evi

 

[Matt Winberry]

Beautiful! Thank you!

I see that you are a VBA whizz and are trying to live VBA in Access.

Open the report in Design View. You won't need the Project Window very much
in Access.

Schedule_Up needs to be in your report. If you don't want to see it, use the
Properties button on the Format tab when you are clicked on it and set
Visible to no.

Click the Field List button on the toolbar and slide the field it onto the
report. If it isn't there, then click beyond the report grid and in
Properties, Data, click next to Data Source to open up the Table or Query on
which the report is based.

Add ScheduleUp to the query grid

Evi









Hi matt, put a simpler version of the code in the report itself in On Format
Event of the section which contains Schedup



If me.Schedule_Up = True Then
SchedUp.visible = True
Else
SchedUp.visibile = False
End if


ie in Report Design view
Click on the grey bar just above the section that contains your control
Click on the Properties button
Click on the Events tab
Choose Event Procedure next to On Format
Click just right of that to open a code page
Just above End Sub, type the above code.
(note visible - not visibility)

Evi