Full Version: Using If and subform to change forecolor
UtterAccess Forums > Microsoft® Access > Access Forms
stewalker2020
Hi All
I have a main form with a subform. The subform is a continuous form. On the main form I have several labels named Area 1, Area 2, Area 3 etc. On the subform, this logs which area has been covered and on what date it has been done. I want the labels on the main form to show red if the area does not exist, or green if the area does exist on the subform.
The above has been setup correctly and works.
The problem;
The above only works for the 1st entry on the continuous form. I suspect this is expected, but as I am no vba expert, I do not know how to get it to work for the rest of the entries in the subform...? or even if it is possible?
This is the code currently used. (frmPrevBookings is the subform)
If Me.frmPrevBookings.Form.fldArea = "Area 2" Then
Me.Label94.ForeColor = vbGreen
Else
Me.Label94.ForeColor = vbRed
End If
ANY HELP GREATLY APPRECIATED.
Many thanks
dashiellx2000
You code as is will only check the record in the subform that has the focus. Rather then testing the subform, you should test the subform's recordsource using a DCount to see if the area exists.
TH.
stewalker2020
Sorry, I dont understand what I am testing for?? DCount, would this just count the number of area's in the subform?
pologies for my lack of access knowledge..!
dashiellx2000
You test the subform's recordsource, not the subform itself (i.e. the subform's control source, the query/table it is bound too). Something like this:
!--c1-->
CODE
If Nz(DCount("*", "tblTableName", "[Area]='Area 2'"),0) <> 0 Then
    Me.Label94.ForeColor = vbGreen
Else
    Me.Label94.ForeColor = vbRed
End If

Do that for each area you are looking for and set your label color as needed.
HTH.
stewalker2020
This does not work correctly, probably because I did not mention the following...
The table that the data comes from holds this 'area' data for multiple employees. So employee 1 could have area 1, area 2 and area 5 against him for example. Employee 4 could have Area 1, 4 and 5 against him. So, on the table I have EmployeeID, fldArea, fldDate.
The vba would need to know which employeeID to search for, right?
Sorry about the confusion, I should of mentioned this earlier.
By the way, many thanks for your help up to now.
Steve
dashiellx2000
That is not a problem as your can add employeeid to your criteria:
If Nz(DCount("*", "tblTableName", "[Area]='Area 2' AND [EmployeeID]=" & Me.EmployeeID),0) <> 0 Then
The above assumes you have a control on your form called EmployeeID that you can use for the criteria.
HTH.
stewalker2020
SUPERB!
any thanks William, worked a treat.
Steve
dashiellx2000
Glad I was able to help.
This is a "lo-fi" version of UA. To view the full version with more information, formatting and images, please click here.