Access Calendar Style Report ...

[angelandgreg]

I have a great form that is used to schedule volunteers by clicking on New or editing an existing volunteer by clicking on the name on the date on the calendar form.

Once completed, I need to print it out as a report.

I have gotten some help by others examples (the form and all) but am stuck on a Compile Error and unable to figure out what the cause of this is. The field exists on the form (control) so why is it erroring out? How can I fix this so the Report will finally display the calendar with the scheduled information just as seen on my calendar form?

NOTE: the same form displays various information based on which information I select from an "options" form.

Hope that made sense? I don't know how to upload the zipped database so someone can see the database.

 

[dhookom]

It isn't clear at all what your issue is. You mention "Compile Error" but not much beyond that. Do you have a calendar report created? How is it related to a form?

Duane MS Access MVP
[green]Ask a great question, get a great answer.[/green] [red]Ask a vague question, get a vague answer.[/red]
[green]Find out how to get great answers faq219-2884.[/green]

 

[angelandgreg]

Ok, i got passed the Compile Error

(the Report wasn't quite updated / saved the txt and lst names that are on the Form).


Now my issue is I get a Run-time error 438 and can't figure out what the issue is. The code is on the Report.

'This snags the populated date box value for each day from the main form

---> Me.txt1 = [Forms]![frmCalendarLV]![txt1].Caption

 

[dhookom]

txt1" suggests a text box. If that is correct, your code will error since text boxes don't have a caption property.

If this isn't your issue, it would help to know where the code is running, what is the control source of txt1 in the report, why are you using code to set a value?

Duane MS Access MVP
[green]Ask a great question, get a great answer.[/green] [red]Ask a vague question, get a vague answer.[/red]
[green]Find out how to get great answers faq219-2884.[/green]

 

[angelandgreg]

the txt1-37 are for the date values from the form.

is there a way to upload the db?

 

[angelandgreg]

there's this also
I don't know how or where to define the .Caption??

For i = 1 To 6
If Controls("labl" & i).Value > 22 Then
Controls("labl" & i).ForeColor = intOtherMonthsColor
Controls("labl" & i).BackColor = intTextBackColor
Controls("lst" & i).ForeColor = intOtherMonthsColor
Else
Controls("labl" & i).ForeColor = intThisMonthsColor
Controls("labl" & i).BackColor = intTextBackColor
Controls("lst" & i).ForeColor = intListColor
End If
Next

For i = 7 To 28
Controls("labl" & i).ForeColor = intThisMonthsColor
Controls("labl" & i).BackColor = intTextBackColor
Controls("lst" & i).ForeColor = intListColor
Next

For i = 29 To 37
If Controls("labl" & i).Value < 15 Then
Controls("labl" & i).ForeColor = intOtherMonthsColor
Controls("labl" & i).BackColor = intTextBackColor
Controls("lst" & i).ForeColor = intOtherMonthsColor
Else
Controls("labl" & i).ForeColor = intThisMonthsColor
Controls("labl" & i).BackColor = intTextBackColor
Controls("lst" & i).ForeColor = intListColor
End If
Next

 

[angelandgreg]

btw - I changed all the "txt" to "labl".

 

[dhookom]

I don't really understand what you are attempting to do. Each reply from you seems to go off in another direction and reveals another mystery. For instance, you now are using "labl" which generally suggests a label control. Your code attempts to pull the "value" from the label control which isn't possible since labels don't have values.

I would have to think there might be an easier way to accomplish what you are attempting but with out knowing what you are attempting, I'm lost.

Duane MS Access MVP
[green]Ask a great question, get a great answer.[/green] [red]Ask a vague question, get a vague answer.[/red]
[green]Find out how to get great answers faq219-2884.[/green]

 

[angelandgreg]

dhookom,
sorry for the confusion ... it's the same issue. I guess I'm just not making sense. I tried attempting to restate the same issue.

Anyway, when you noted ""txt1" suggests a text box. If that is correct, your code will error since text boxes don't have a caption property."

I thought that might be my problem, so I changed all the "txt" to "labl"
So on the Report, the error occurs at

---> Me.labl1 = [Forms]![frmCalendarLV]![labl1].Caption

So I thought maybe it's the ".Caption".
It's not defined anywhere. Then I posted the code that seems to be similar to where the original DB (that I got the code from) shows defining ".Caption". So I looked for something similiar module and found something like it from the form. Which is the latest code. And want to know if that made sense and if so, how does the ".Caption" get defined?


Basically I have parts from form one sample and the report from another and having problems getting the Report.

The error being the Run-time error 438
at Me.Labl1 = [Forms]![frmCalendarLV]![Labl1].Caption


Hope that made more sense! I was trying to avoid too much information as I can go on and on but at the same time, lack of info is also a problem

 

[dhookom]

In all of your replies, I don't think you have told us if "Labl1" on the report (or the form) is a label or text box control. Do you understand the difference?

When I state "don't really understand what you are attempting to do" I mean on a bigger picture level. I would expect you to reply back with something like:
[blue]"I have a form with 37 text box that contain... I would like these values to be displayed in a report... The controls on my form are bound to fields in ... "[/blue]

It's like you are going on a trip. I am not as interested in where you ran off the road as I am in where you started from and where you are going.

Duane MS Access MVP
[green]Ask a great question, get a great answer.[/green] [red]Ask a vague question, get a vague answer.[/red]
[green]Find out how to get great answers faq219-2884.[/green]

 

[angelandgreg]

sorry!
It's obvious I don't know how to explain myself in this type of situation/enviornment so thanks for bearing with me ... hope you can help.

Yes, I have a form with the following controls

labl1-37 (these displays the dates for the month and year selected from two other controls, one is cboMonth and the other cboYear).

lst1-37 (these displays the Name of the person that is scheduled for a date).

The form (frmCalenderLV) does mutliple functions but all in all it uses the same controls as noted above labls and lsts).

I am trying to get a Report to look like the Form, in the output format of looking like a calendar for the month selected on the frmCalenderLV with the associated data.

Did that make sense? Am I getting any better on trying to explain what I'm trying to help to accomplish?

I have a sample Report with the code but it's not working. I get the run-time error 438 and I can't figure out why.


Thank you for your patience!

 

[angelandgreg]

NOTE: the labls and the lsts are not bound.

these controls are unbound and the results are based on the form's Events.

OnLoad - this one bascially sets the margins
OnActivate
OnDeactivate


Then the Report also has all the same fields and the Event is OnActivate.
Private Sub Report_Activate()

'This checks the main form to see if a labl box is visible (ie; the 1st falls on a tuesday vs a Monday)
'If it is visible on the main form, then it will be on this report as well.....

If [Forms]![frmCalendarLV]![Labl1].Visible = True Then
Me.Labl1.Visible = True
Me.lst1.Visible = True
Else
Me.Labl1.Visible = False
Me.lst1.Visible = False
End If



this repeats down and then to
'This snags the populated date box value for each day from the main form
Me.Labl1 = [Forms]![frmCalendarLV]![Labl1].Caption <-- this is where I am getting my run-time error 438.

the above also repeats down and then to
'This snags the populated labl box value for each day from the main form
Me.lst1 = [Forms]![frmCalendarLV]![lst1].Caption

 

[dhookom]

I still don't see anything that states which controls are text boxes and which are labels. you have now added "lsts" to the mix which I would assume is a list box.

Bottom line, I assume your data is all stored in a table some place. I expect there is a better solution to what you are attempting to do.

Duane MS Access MVP
[green]Ask a great question, get a great answer.[/green] [red]Ask a vague question, get a vague answer.[/red]
[green]Find out how to get great answers faq219-2884.[/green]

 

[angelandgreg]

Yes, tblSchedule is where the data resides.

When you go to add the new schedule from the frmCalenderLV in the Enter New Schedule view, you double-click on the field (control) named "New" which opens another form that enters the data into the tblSchedule.

Then when you go to view the frmCalenderLV of all the scheduled list, it shows all the names of the people schedule for the dates on the calendar format form (frmCalenderLV).

The rptSchedule is what I need the calendar style of the data (as on the frmCalenderLV) to display so that I can print the calendar as I can't just print the form screen.

The labl1-37 is the calendar dates.
The lst1-37 is the scheduled information. In this case the scheduled person's names.

The tblSchedule contains the following fields:
1. ScheduleID - just a simple autonumber field.
2. ScheduleDate - mm/dd/yyyy
3. ScheduleName - text field with a combobox LookUp to a query.

 

[dhookom]

At least three of my responses have asked about your control types ie Text Box, List Box, or Label. You have yet to provide this simplest of information. It is very critical since they all have different properties.

You should be able to abandon your report in favor of the calendar reports at

http://www.access.hookom.net/Samples.htm.

These require little or no code and are quite flexible.

Duane MS Access MVP
[green]Ask a great question, get a great answer.[/green] [red]Ask a vague question, get a vague answer.[/red]
[green]Find out how to get great answers faq219-2884.[/green]

 

[angelandgreg]

Sorry, i thought i have in various formats :-(

I've seen the samples and can't figure out how to incorporate it with my db and the form. %-)

Hence I have adopted this other report I found but running into the error 438.

Let's try it this way ...

all the "labl" fields (controls) on the form are Text Boxes
all the "lst" fields (controls) on the form are ActiveX Controls. OLE Class is ListViewCtrl and Class is MSComctlLib.ListViewCtrl.2

Does this help??

 

[angelandgreg]

NOTE: the "labl" fields were originally named "txt" but I changed them to "labl" based on your earlier post as I thought that was my problem! LOL! )

 

[dhookom]

I would give up on the report and use the suggested samples I provided.

For clarification
[tt][green]
==== Property Available ====
Control Type Caption Value Row Source
-------------- -------- ------ ----------
Text Boxes X
Labels X
List Boxes X
[/green][/tt]

Duane MS Access MVP
[green]Ask a great question, get a great answer.[/green] [red]Ask a vague question, get a vague answer.[/red]
[green]Find out how to get great answers faq219-2884.[/green]

 

[angelandgreg]

done of your samples formats the calendar where the 1st of the month begins on the day of the week ...

October 1st falls on a Sunday
November 1st falls on a Wednesday


I have changed all the controls on the Report by copying the ActiveXControl fields from my form ... I have disabled the ".Caption" for all the text fields and disabled everything for the "lst" fields as the "lst" are not text fields.

Now I no longer get any error messages but am getting just "x" for all the "lst" fields (ActiveXControls) instead of the scheduled person's name as it does on the form.

And for the dates, I get 01 for the first few text boxes and then the 1st through the 9th of the month (in the proper day of the week boxes) and the 01 repeats again until the new month on the same calendar is available and starts with the 1st of the month for the following month and also only goes to the 9th day and the 01 fills the rest.


1. How do I get the "lst" ActiveXControl fields to pull the scheduled persons name instead of just the value "x"(qryScheduleName which pulls the info from tblSchedule)?
2. How do I get the rest of the days to fill instead of just the 1st through the 9th?

 

[dhookom]

done of your samples formats the calendar where the 1st of the month begins on the day of the week ...". It seems to me that you could easily make a control invisible if it doesn't meet some criteria.

Duane MS Access MVP
[green]Ask a great question, get a great answer.[/green] [red]Ask a vague question, get a vague answer.[/red]
[green]Find out how to get great answers faq219-2884.[/green]