Need a loop!

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 sheet 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 sheet 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 sheet. 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)" wrote in message
ups.com...
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

Since a DLookup can only go against a single table (or query), you do not
qualify field names. That means that [qvrs]![wmodel], [qvrs]![udate] and
[qvrs]![ureturned] are incorrect.

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

I'm assuming you're using datevalue([forms]![Tovavrs]![text83]+0) to handle
cases where text83 contains Null.

I suppose that should work.

Sounds to me as though Duane Hookom's Calendar Reports demo at
http://www.invisibleinc.com/divFiles.cfm?divDivID=4 might be an alternative.

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


"Bill (Unique as my name)" wrote in message
oups.com...
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 sheet 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 sheet 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 sheet. 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)" wrote in message
ups.com...
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

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)" wrote in message
oups.com...
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 sheet 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 sheet 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 sheet. 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)" wrote in message
ups.com...
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

I'm assuming you're using datevalue([forms]![Tovavrs]![text83]+0) to handle
cases where text83 contains Null.

Didn't think of that. Mainly I needed a character for spacing. I had
so many lines that I placed them in a spreadsheet and dissected them
for quick edits

Since a DLookup can only go against a single table (or query), you do not
qualify field names. That means that [qvrs]![wmodel], [qvrs]![udate] and
[qvrs]![ureturned] are incorrect.

Nonetheless, the statements work. I'll edit them for neatness after I
come back from a quick break.

Anyway, I shaved off a few seconds every time I open the form or change
a date on it.
Thanks to you, my code is infinitely more concise, and now I'm actually
not embarrassed to show it to somebody. I included some of it below.
I thought Duane Hookom might get a kick to see I used his suggestion.
The syntax was killing me.

I very much appreciate the interest you showed, the time you gave, and
the gentle guiding you offered.

Dim vsq As Integer
For vsq = 101 To 128
[Forms]![tovavrs].Controls("r" & CStr(vsq)) = " " & DLookup("vvmodel",
"qvrs", "[qvrs]![vvmodel] = [forms]![Tovavrs]![text335] AND
datevalue([forms]![Tovavrs]![text83] + " & vsq - 101 & " ) =
datevalue ([qvrs]![udate]) AND datevalue([forms]![Tovavrs]![text83] +
" & vsq - 101 & " ) = datevalue([qvrs]![ureturned])")
Next vsq

For vsq = 129 To 156
[Forms]![tovavrs].Controls("r" & CStr(vsq)) = " " & DLookup("vvmodel",
"qvrs", "[qvrs]![vvmodel] = [forms]![Tovavrs]![text337] AND
datevalue([forms]![Tovavrs]![text83] + " & vsq - 129 & " ) =
datevalue ([qvrs]![udate]) AND datevalue([forms]![Tovavrs]![text83] +
" & vsq - 129 & " ) = datevalue([qvrs]![ureturned])")
Next vsq

For vsq = 157 To 184
[Forms]![tovavrs].Controls("r" & CStr(vsq)) = " " & DLookup("vvmodel",
"qvrs", "[qvrs]![vvmodel] = [forms]![Tovavrs]![text338] AND
datevalue([forms]![Tovavrs]![text83] + " & vsq - 157 & " ) =
datevalue ([qvrs]![udate]) AND datevalue([forms]![Tovavrs]![text83] +
" & vsq - 157 & " ) = datevalue([qvrs]![ureturned])")
Next vsq

For vsq = 185 To 212
[Forms]![tovavrs].Controls("r" & CStr(vsq)) = " " & DLookup("vvmodel",
"qvrs", "[qvrs]![vvmodel] = [forms]![Tovavrs]![text339] AND
datevalue([forms]![Tovavrs]![text83] + " & vsq - 185 & " ) =
datevalue ([qvrs]![udate]) AND datevalue([forms]![Tovavrs]![text83] +
" & vsq - 185 & " ) = datevalue([qvrs]![ureturned])")
Next vsq

For vsq = 213 To 240
[Forms]![tovavrs].Controls("r" & CStr(vsq)) = " " & DLookup("vvmodel",
"qvrs", "[qvrs]![vvmodel] = [forms]![Tovavrs]![text340] AND
datevalue([forms]![Tovavrs]![text83] + " & vsq - 213 & " ) =
datevalue ([qvrs]![udate]) AND datevalue([forms]![Tovavrs]![text83] +
" & vsq - 213 & " ) = datevalue([qvrs]![ureturned])")
Next vsq

Douglas J. Steele wrote:
Since a DLookup can only go against a single table (or query), you do not
qualify field names. That means that [qvrs]![wmodel], [qvrs]![udate] and
[qvrs]![ureturned] are incorrect.

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

I'm assuming you're using datevalue([forms]![Tovavrs]![text83]+0) to handle
cases where text83 contains Null.

I suppose that should work.

Sounds to me as though Duane Hookom's Calendar Reports demo at
http://www.invisibleinc.com/divFiles.cfm?divDivID=4 might be an alternative.

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

What's the matter, Duane? The plug I gave wasn't good enough? g

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


"Duane Hookom" DuaneAtNoSpanHookomDotNet wrote in message
...
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

My reply was one of those where I had the email open while my grandson asked
the see the video of him fishing in my backyard pond. Since Jack gets what
he wants, my "Send" was delayed and you were replying about the time the
video caught him swearing and saying "there's no fishes in there" ;-).

--
Duane Hookom
MS Access MVP

"Douglas J. Steele" wrote in message
...
What's the matter, Duane? The plug I gave wasn't good enough? g

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


"Duane Hookom" DuaneAtNoSpanHookomDotNet wrote in message
...
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