Simple Textbox color change question 1

[dante1963]

I have two tables in my Access 2K db: tblData, and tblDateRange.

TblData's colums are made up of fname and lname columns, and then a series of dates.

My query pulls all of the rows from the tblData where any of the date columns fall between the two dates listed in the tblDateRange table. (Sdate and Edate).

My report lists all the rows from the query.

Here is my problem: How do I programatically (using VB) change the backcolor of the text boxes on the report for those dates that fall within the range?

I've managed to do it using the conditional format menu, but I would prefer to do it using VB.

I have tried various combinations of using Dlookup that compare to what is in the conditional format screen, but to no avail. I am assuming that whatever the code is, it should be in the ON Format event of the report, right?

I have tried something like this, but no luck:

*************
Private Sub Detail_Format(Cancel As Integer, FormatCount As Integer)

Dim dteSdate As Date
Dim dteEdate As Date

dteSdate = DLookup("[sdate]", "tblDateRange&quot
dteEdate = DLookup("[edate]", "tblDateRange&quot

If [1_year] >= dteSdate And [1_year] <= dteEdate Then
[1_year].BackColor = vbBlue
End If

End Sub
***********
([1_year] is the name of one of the textboxes.)

I have also tried something along the lines of &quot;if [1_year] between dlookup(&quot;[sdate]&quot;,&quot;tbldata&quot;,&quot;tbldaterange&quot And dlookup(&quot;[sdate]&quot;,&quot;tbldata&quot;,&quot;tbldaterange&quot Then
[1_year].backcolor = vblue
end if&quot;

That I know that isn't close to the right syntax.

What is the proper way to do this?

Thanks!

 

[Trendsetter]

The way I see this (Ihave not tried this) is that the code you have shown is not related to the report in any way and therefore can not have any effect.
The report will have a Date field which is the entry you wish to change so:

If Me.EntryDate >= dteSDate And <= dteEDate Then
Me.EntryDate.BackColor = vbBlue
Else
Me.EntryDate.BackColor = vbWhite
End if

dteSDate and dteEDate would be the variable parameters that are entered into the query... somehow?

Frank J Hill
FHS Services Ltd.
frank@fhsservices.co.uk

 

[dante1963]

Thanks for replying!

The control to be changed, the textbox on the report (or one of them, anyway) is 1_year.

What I was trying (and failing ) to do was to compare the value of 1_year on the report to the date range specified by the sdate and edate fields in the table tbldaterange.

The dtesdtate and dteedate are just temporary variables to hold the value those two fields. I guess I don't need them.

I only went that way when the failed to find the proper syntax that this was supposed to represent&quot;

*************
if [1_year] between dlookup(&quot;[sdate]&quot;,&quot;tbldaterange) And dlookup(&quot;[sdate]&quot;,&quot;tbldaterange&quot Then
[1_year].backcolor = vblue
end if
****************

I'm still very new at this, so please pardon my ignorance.

Dan

 

[Trendsetter]

I've just tested this code snippet in a report and it works fine.
The code was entered in the Format Detail section:

If Me.txtInvDate > #4/22/1999# And Me.txtInvDate < #10/25/2001# Then
Me.txtInvDate.BackColor = vbRed
Me.InvoiceNum.BackColor = vbRed
Else
Me.txtInvDate.BackColor = vbBlue
Me.InvoiceNum.BackColor = vbBlue
End If

Frank J Hill
FHS Services Ltd.
frank@fhsservices.co.uk

 

[dante1963]

Thanks!

Using your code, I was able to see what I was doing wrong.

I've made the changes and it works fine. If you have time, could you take one more look my code? In the following code, I know that all those If statements aren't necessary and that I should just be able to do it in a loop of some sort--but I'm a little fuzzy on how to loop through controls.

What is the preferred way to do the following?

**************
Private Sub Detail_Format(Cancel As Integer, FormatCount As Integer)

Dim dteSdate As Date
Dim dteEdate As Date

dteSdate = DLookup(&quot;[sdate]&quot;, &quot;tblDateRange&quot
dteEdate = DLookup(&quot;[edate]&quot;, &quot;tblDateRange&quot

If Me.txt30_days >= dteSdate And Me.txt30_days <= dteEdate Then
Me.txt30_days.BackColor = vbYellow
Else
Me.txt30_days.BackColor = vbWhite
End If

If Me.txt90_Days >= dteSdate And Me.txt90_Days <= dteEdate Then
Me.txt90_Days.BackColor = vbYellow
Else
Me.txt90_Days.BackColor = vbWhite
End If

If Me.txt6_months >= dteSdate And Me.txt6_months <= dteEdate Then
Me.txt6_months.BackColor = vbYellow
Else
Me.txt6_months.BackColor = vbWhite
End If

If Me.txt1_year >= dteSdate And Me.txt1_year <= dteEdate Then
Me.txt1_year.BackColor = vbYellow
Else
Me.txt1_year.BackColor = vbWhite
End If

If Me.txt5_years >= dteSdate And Me.txt5_years <= dteEdate Then
Me.txt5_years.BackColor = vbYellow
Else
Me.txt5_years.BackColor = vbWhite
End If

If Me.txt7_years >= dteSdate And Me.txt7_years <= dteEdate Then
Me.txt7_years.BackColor = vbYellow
Else
Me.txt7_years.BackColor = vbWhite
End If

If Me.txt10_years >= dteSdate And Me.txt10_years <= dteEdate Then
Me.txt10_years.BackColor = vbYellow
Else
Me.txt10_years.BackColor = vbWhite
End If

If Me.txt20_years >= dteSdate And Me.txt20_years <= dteEdate Then
Me.txt20_years.BackColor = vbYellow
Else
Me.txt20_years.BackColor = vbWhite
End If

End Sub

**************************

Thanks again!

 

[Trendsetter]

Hi Dan,
To be honest I would not change the code if it works!
I'm not sure that looping could work properly as you need to detect only certain controls on the form not all and the qualifying of the controls could be messy.
You can't really use Case statements because you have Else statements for each iteration and using elseif wouldn't work for the same reason.
So &quot;If it ain't broke, don't fix it!&quot;

Frank J Hill
FHS Services Ltd.
frank@fhsservices.co.uk

 

[dante1963]

Thanks, again for the help, Frank!