for to next loop with dlookup

[Guest]

Is there a way to append a variable number (from a loop) to a [abcd] field?

Ex:

for x =1 tp 10
If DLookup("[x]", "table1", "[ID]=x") = False Then
[datax]=false
endif
next x

I would like all the "x" in this code to take the value of the loop.

I'm trying to make a camping ground reservation database. I've setup a table
where each column represents a lot and each row a date. Each lot field is
either true (free) or false (taken) for the corresponding date.
I intend to make a form with the basic layout of the camping ground with
each cells changing color according to the status for the asked dates.
So, if say someone wants to stay from the 6 to the 15, I have to loop
through those numbers and through all the lot numbers to find all the lots
available for this period.

The "real code" would look like something like this, where the looping
numbers are "dt" and "lot_no".
"lot" is the name of the cell in the form, actually named "lot1, lot2, lot3"
that will change color so I need to append the "lot_no" to "lot". Am I making
any sense here?
Of course, this particular code doesn't work.

For dt = [Date_in] To [Date_out]
For lot_no = 1 To 100
If DLookup("[dt]", "reservation", "[ID]=lot_no") = False Then
[lot lot_no].BackColor = 255
Else: [lot lot_no].BackColor = 65280
End If
Next lot_no
Next dt

thanks.
Pierre.

 

[Douglas J Steele]

For dt = [Date_in] To [Date_out]
For lot_no = 1 To 100
If DLookup("[" & dt & "]", "reservation", "[ID]=" & lot_no) = False
Then
Me.Controls("lot" & lot_no).BackColor = 255
Else
Me.Controls("lot" & lot_no).BackColor = 65280
End If
Next lot_no
Next dt

I have to question your database design, though, if you've got fields named
like that: they're called Repeating Groups, and they're a violation of
database normalization principles. You might want to check out some of the
resource Jeff Conrad has at
http://home.bendbroadband.com/conradsystems/accessjunkie/resources.html#DatabaseDesign101

 

[Guest]

Thanks.

I admit that the db design is not "up to standards" and these informations
and links you provided are very helpful indeed.
"problem" is that I'm more used to work with Excell, but that friend asked
me if it was
possible to do this in Access, because he already has an invoicing system in
Access, and
the possibility that he might link the two together was interesting. My big
mouth said yes...
The rest is history... I will try to mind my brain to think on another plane
to make that matrix.

Pierre.

For dt = [Date_in] To [Date_out]
For lot_no = 1 To 100
If DLookup("[" & dt & "]", "reservation", "[ID]=" & lot_no) = False
Then
Me.Controls("lot" & lot_no).BackColor = 255
Else
Me.Controls("lot" & lot_no).BackColor = 65280
End If
Next lot_no
Next dt

I have to question your database design, though, if you've got fields named
like that: they're called Repeating Groups, and they're a violation of
database normalization principles. You might want to check out some of the
resource Jeff Conrad has at
http://home.bendbroadband.com/conradsystems/accessjunkie/resources.html#DatabaseDesign101

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)

Is there a way to append a variable number (from a loop) to a [abcd] field?

Ex:

for x =1 tp 10
If DLookup("[x]", "table1", "[ID]=x") = False Then
[datax]=false
endif
next x

I would like all the "x" in this code to take the value of the loop.

I'm trying to make a camping ground reservation database. I've setup a table
where each column represents a lot and each row a date. Each lot field is
either true (free) or false (taken) for the corresponding date.
I intend to make a form with the basic layout of the camping ground with
each cells changing color according to the status for the asked dates.
So, if say someone wants to stay from the 6 to the 15, I have to loop
through those numbers and through all the lot numbers to find all the lots
available for this period.

The "real code" would look like something like this, where the looping
numbers are "dt" and "lot_no".
"lot" is the name of the cell in the form, actually named "lot1, lot2, lot3"
that will change color so I need to append the "lot_no" to "lot". Am I making
any sense here?
Of course, this particular code doesn't work.

For dt = [Date_in] To [Date_out]
For lot_no = 1 To 100
If DLookup("[dt]", "reservation", "[ID]=lot_no") = False Then
[lot lot_no].BackColor = 255
Else: [lot lot_no].BackColor = 65280
End If
Next lot_no
Next dt

thanks.
Pierre.