Re: Need a loop!

Tech-Archive recommends: Speed Up your PC by fixing your registry

Nice description and functionality. You may be able to simplify this by
binding your report to a record source and using a report like the samples
in the calendar report downloads at
http://www.invisibleinc.com/divFiles.cfm?divDivID=4.

There are some timeline type reports that work quite well.
--
Duane Hookom
MS Access MVP

"Bill (Unique as my name)" <brmcoyne@xxxxxxxxx> wrote in message
news:1150041032.828983.207920@xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx
I have a vehicle fleet, five of which are reservable. The vehicle
fleet table links to a scheduled use table in a one to many
relationship. The scheduled use table shows the dates when people
take, and return, vehicles.

I post a grid *** in the motor pool which shows vehicle reservation
dates.

The grid shows a span of twenty-eight days. Under each date are five
rectangles, with two layers of text boxes in each . The bottom layer
text is red and shows five boxes with the five default vehicles names.
Hence, for example, the code below fills in the the top box.

Dim tvso
For tvso = 1 To 28
[Forms]![Tovavrs].Controls("t" & CStr(tvso)) = " " &
[Forms]![Tovavrs]![Text335]
Next tvso

Similar code fills the other four boxes.

The top layer text is green. It shows the vehicle name if the date
above the five vehicle boxes on the grid *** falls within the range
of dates for which the vehicle is reserved. Hence, the code

[Forms]![Tovavrs]![r101] = " " & DLookup("vvmodel", "qvrs",
"[qvrs]![vvmodel] = [forms]![Tovavrs]![text335] AND
datevalue([forms]![Tovavrs]![text83]+0) >= datevalue ([qvrs]![udate])
AND datevalue([forms]![Tovavrs]![text83]+0) <=
datevalue([qvrs]![ureturned])")

for each of the five boxes under each of the twenty-eight dates. [qvrs]
is a query showing reservations beginning ten days from now().

I know this is all very stupid. But I was willing to do the grunt work
as long as we did not have more than five reservable cars. And even if
we go get more reservable cars, it won't be too much to add a sixth
line or more.

I tried select and crosstab queries, but failed. My supervisor wanted
the ***. I had to produce something. This works. It would be nice
if I could make it more concise.

Anyway, thanks for the helps. It saved me a lot of keyboard work. The
only thing troubling me now is the dlookup lines. I do not know how to
refer to a loop variable from the criteria. If I could get that, that
would be great.

Douglas J. Steele wrote:
I think you'd better step back a bit and explain what you're trying to
do.

As Steve pointed out, it's odd enough that you need to have 30 fields
you're
going to set to the same value. To subsequently need a bunch of DLookups
makes it sound as though you may be going about it the wrong way.

--
Doug Steele, Microsoft Access MVP
http://I.Am/DougSteele
(no private e-mails, please)


"Bill (Unique as my name)" <brmcoyne@xxxxxxxxx> wrote in message
news:1150026374.088453.42050@xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx
Thank goodness, only once in a blue moon. Doug's suggestion
([Forms]![Tovavrs].Controls("txt" & CStr(vso)) = ) worked perfectly.

But now I have a new request. I would like to insert the same kind of
variable reference into the criteria section of a dlookup statement.

Would it look something like

DLookup("vvmodel", "qvrs", "[qvrs]![vvmodel] =
[forms]![Tovavrs]![text335] AND datevalue([forms]![Tovavrs]![text83]+
(Cstr(vso))) >= datevalue ([qvrs]![udate]) AND
datevalue([forms]![Tovavrs]![text83]+ (Cstr(vso))) <=
datevalue([qvrs]![ureturned])")

Thanks again for the assistance! This place is so great!

Steve Schapel wrote:
. . .
Anyway, the whole thing is a bit unconventional, really - when was the
last time you wanted to set the value of 30 fields all to the same
thing, being the value of one other control :-) ?

--
Steve Schapel, Microsoft Access MVP




.

Quantcast