Smart questions
Smart answers
Smart people
Join Tek-Tips Forums
INTELLIGENT WORK FORUMS
FOR COMPUTER PROFESSIONALS

Member Login




Remember Me
Forgot Password?
Join Us!

Come Join Us!

Are you a
Computer / IT professional?
Join Tek-Tips now!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!

Join Tek-Tips
*Tek-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.
Jobs from Indeed

Link To This Forum!

Partner Button
Add Stickiness To Your Site By Linking To This Professionally Managed Technical Forum.
Just copy and paste the
code below into your site.

angelandgreg (TechnicalUser) (OP)
20 Sep 06 17:40
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 (Programmer)
21 Sep 06 0:48
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
Ask a great question, get a great answer. Ask a vague question, get a vague answer.
Find out how to get great answers FAQ219-2884.

angelandgreg (TechnicalUser) (OP)
21 Sep 06 18:14
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 (Programmer)
21 Sep 06 21:37
"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
Ask a great question, get a great answer. Ask a vague question, get a vague answer.
Find out how to get great answers FAQ219-2884.

angelandgreg (TechnicalUser) (OP)
22 Sep 06 1:12
the txt1-37 are for the date values from the form.

is there a way to upload the db?
angelandgreg (TechnicalUser) (OP)
22 Sep 06 1:47
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 (TechnicalUser) (OP)
22 Sep 06 1:48
btw - I changed all the "txt" to "labl".
dhookom (Programmer)
22 Sep 06 8:49
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
Ask a great question, get a great answer. Ask a vague question, get a vague answer.
Find out how to get great answers FAQ219-2884.

angelandgreg (TechnicalUser) (OP)
22 Sep 06 11:57
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 smile
dhookom (Programmer)
22 Sep 06 12:38
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:
"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 ... "

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
Ask a great question, get a great answer. Ask a vague question, get a vague answer.
Find out how to get great answers FAQ219-2884.

angelandgreg (TechnicalUser) (OP)
22 Sep 06 13:33
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 (TechnicalUser) (OP)
22 Sep 06 13:51
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 (Programmer)
22 Sep 06 14:53
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
Ask a great question, get a great answer. Ask a vague question, get a vague answer.
Find out how to get great answers FAQ219-2884.

angelandgreg (TechnicalUser) (OP)
22 Sep 06 16:53
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 (Programmer)
22 Sep 06 22:04
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
Ask a great question, get a great answer. Ask a vague question, get a vague answer.
Find out how to get great answers FAQ219-2884.

angelandgreg (TechnicalUser) (OP)
22 Sep 06 22:19
Sorry, i thought i have in various formats sad

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

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 (TechnicalUser) (OP)
22 Sep 06 22:30
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! clown
dhookom (Programmer)
22 Sep 06 22:43
I would give up on the report and use the suggested samples I provided.

For clarification

                 ==== Property Available ====
Control Type     Caption   Value   Row Source
--------------   --------  ------  ----------
Text Boxes                   X
Labels               X
List Boxes                             X

Duane MS Access MVP
Ask a great question, get a great answer. Ask a vague question, get a vague answer.
Find out how to get great answers FAQ219-2884.

angelandgreg (TechnicalUser) (OP)
23 Sep 06 13:45
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 (Programmer)
24 Sep 06 0:03
"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
Ask a great question, get a great answer. Ask a vague question, get a vague answer.
Find out how to get great answers FAQ219-2884.

angelandgreg (TechnicalUser) (OP)
24 Sep 06 17:14
Duane,
thanks for trying to help.
I finally got what I needed although I don't know why I didn't think of it before. The simplest solution was right there in front of me (although not the exact one I was trying to do but all-in-all, the end result is the same).

I have this problem of making things more difficult then it really needs to be. dazed

Anyway, after looking at this thing from all this different angles, I realized that I could get what I needed but simplye creating a simpler form for just the results then doing the report from that. Avoided all the ActiveX Controls (which is what I was having issues with).thumbsup2

Reply To This Thread

Posting in the Tek-Tips forums is a member-only feature.

Click Here to join Tek-Tips and talk with other members!

Close Box

Join Tek-Tips® Today!

Join your peers on the Internet's largest technical computer professional community.
It's easy to join and it's free.

Here's Why Members Love Tek-Tips Forums:

Register now while it's still free!

Already a member? Close this window and log in.

Join Us             Close