Writing variables to report and do loop problem

[Guest]

Hi, more problems (late, I want to go home). Please help.

I am using the following code to try to create a report, and am having three
problems with the following code (long, sorry):


Dim stDocName As String
Dim plan_date As Date
Dim project As String
Dim end_date As Date
Dim time_left As Integer
Dim first_del As Date
Dim user_test_end As Date
Dim user_test_beg As Date
Dim end_devel As Date
Dim beg_devel As Date
Dim X As Integer
Dim Y As Integer

plan_date = Now()
project = txtPROJECTID
end_date = dtmDUEDATE
time_left = dblETC
first_del = end_date + 2
user_test_end = end_date - 4
user_test_beg = end_date - 7
end_devel = end_date - 8

X = 1
Y = time_left

Do While Y > 0
' If Weekday(end_devel - X, 2) > 5 Then
' work = 0
' Else
work = 2
Y = Y - work
X = X + 1
Loop
begin_dev = end_devel - X


stDocName = "rptPROJ_WORKLOAD"
DoCmd.OpenReport stDocName, acPreview
Reports(stDocName).Text2 = project
Reports(stDocName).Text4 = end_date
Reports(stDocName).Text6 = time_left
Reports(stDocName).Text8 = first_del
Reports(stDocName).Text10 = user_test_end
Reports(stDocName).Text12 = user_test_beg
Reports(stDocName).Text13 = end_devel
Reports(stDocName).Text14 = begin_dev

Exit_Command27_Click:
Exit Sub

Err_Command27_Click:
MsgBox Err.Description
Resume Exit_Command27_Click

End Sub

Question 1 - when I try to uncomment the lines in the Do...Loop to include
the if then else, I get an error message that I have a loop without a do
statement.

Question 2 - When I run this, I get a blank report, but when I go toggle
between design and preview mode, the data shows up where I would expect. I
think the assign variable statements are in the wrong place in relation to
the docmd.openreport.

Question 3 - most tricky. When I run this, then toggle back and forth
between design and preview mode, I get data, but only the first record of
from the query/form this is being produced from, what do I do to make the
report show every record from the source form (which is a continuous form -
is that a hint?).

Thanks for your help.

 

[John Nurick]

1) No End If

2) Normally, the RecordSource of the report is a table or query, and
controls on the report are bound to fields in the RecordSource (by
setting each control's ControlSource property to the name of a field),
or to other expressions.

3) Forms don't contain records, tables do. If you want the report to
display the the same records as the form, you have to give it the same
RecordSource.

 

[Guest]

Thanks John,

I started to head down the correct path for the third one. So if I base the
report off the query the form is based on, then run some of this code on the
report on open? for instance, would that get me closer?

Sean

 

[John Nurick]

I suppose so, though I really don't know what you're trying to do. I suggest
you first get the report working properly without this code (i.e. displaying
all the records in the query), and then start on the fancy stuff.

Are these controls (Text2, Text4, Text6...) located on the report header or
footer, so their values only have to be worked out when the report opens? If
so, consider calculating the values in advance and putting them in textboxes
on your form (hidden if need be). Then (as far as I can remember, I don't
often use reports) you can refer to them with syntax like this in the
textboxes on the report:
=Forms![FormName]![TextboxName]

If on the other hand Text2, Text4... are on the report's Detail section, you
have to calculate their values for every single record. Far the best way of
doing that is usually to include calculated fields in the query on which the
report is based; ditto for values for page or group headers and footers.
Otherwise you have to use code in the various sections' or headers' Format
events, which can get complicated.

 

[Guest]

Based on your previous message, I redid the report to read from the query and
do a series of calculations in the report, i.e., one date is two days from
another, so the control is enddate + 2. However, the calculation for the
date is more complicated and I wrote the following loop to calculate it.
However, I don't know how to get it into the field that I name begindev. I
thought about putting this in the reportopen event, but was told I cannot
assign a value to that object:

Dim enddevel As Date
Dim beg_dev As Date
Dim X As Integer
Dim Y As Integer


X = 1
Y = time_left

Do While Y > 0
If Weekday(end_devel - X, 2) > 5 Then
work = 0
Else
work = 2
End If
Y = Y - work
X = X + 1
Loop
BEGIN_DEV = enddevel - X

'begindev is the name of the unbound control on the report.
Me.begindev = begin_dev

Any ideas?

 

[John Nurick]

Does every record have its own begindev date, or does the date relate to
the whole report or to groups of records, or what?

If the date has to be calculated for every record and you want to use an
unbound textbox, I _think_ you need to calculate the value in the Format
event of the report's Detail section. But as I've said I don't know much
about reports and you'll probably do better to ask in the Reports
newsgroup.

Alternatively if you put the function that calculates the begindev value
into an ordinary code module (as opposed to the module associated with a
report or form) you can call it from a calculated field in your query.

If the begindev date applies to the whole report, the begindef textbox
is presumably in the report or page header or footer, and as far as I
know you can calculate the value using code in the form and put it in a
textbox on that form, from where the report can get it as i suggested in
my previous message.

 

[Guest]

John, thanks, I will try either the on format, or a separate code module. I
appreciate you bearing with me as I try to figure out my question.

Biggles

 

[Guest]

John, just to follow up, it works great. Thank you.

 

[John Nurick]

John, just to follow up, it works great. Thank you.

Thanks for the feedback. Please give Algy my best wishes (I'll never
forget that night in Tangier).