Conditional Formatting in Form Using Query

[Sarahz]

Hello all,

I'm having an issue with conditional formatting. I originally set up my form in continuous view to disable certain payroll fields if the department equaled 400. Obviously this is a static solution, and while it worked great, now I need to change it so that those fields are disabled if the department equals 400, 290, etc - by looking up the department's perm status (checkbox) in a different table (tblDepts).

Here's the conditional formatting I have so far in the fields to be disabled:

Condition 1
Expression is: [dept]=(SELECT tblDepts.code FROM tblDepts WHERE tblDepts.perm = True}

However, I can't get the code to work if a query is used. In fact, it only works if I specifically state [dept] = 400, which is not a good solution since the departments will be changing.

Any ideas how to get it working? Thanks in advance!!

 

[RichD]

Sarahz,

It should work in the forms On Current Event, as follows:

I assume that the dept is dispalyed in a text box on the form (eg txtDept)

Dim DeptStatus as Variant

DeptStatus = dlookup("Perm", tblDepts", "Dept = '" & Me!TxtDept & "'&quot

If DeptStatus = True then
[Field1].enabled = true
[Field2].enabled = true
else
[Field1].enabled = false
[Field2].enabled = false
end if


HTH
Rich Lead Developer

http://www.blsoftware.co.uk

 

[Sarahz]

Hi RichD,

Thanks so much for your help! The only problem is that, since my form is in continuous view, the function disables every total_hrs field, for example, on the form regardless of the other records' department status instead of just disabling the current record's. And As soon as I tab out of the triggered record, all of the fields are enabled again.

Do you know how to get the function to disable only the fields for each record with the appropriate department status? I did have to make a small tweak to your code to get it working in my db, so maybe that's the reason it's applying to all fields? It's implemented as below:

Dim DeptStatus As Variant

DeptStatus = DLookup("perm", "tblDepts", "dept = " & Me!dept)

If DeptStatus = True Then
Me!total_hrs.Enabled = False
Else
Me!total_hrs.Enabled = True
End If

Dept is actually a combo box, so I'd like to activate the function when the department changes. That's how I have it set up now, in dept's OnChange event.

Thanks again! I really appreciate your help!

 

[RichD]

Sarahz,

I don't think you can enable / disable individual records. However here is another option.

I assume the dept combo box is in each record, in which case you can put perm as a column in it (set column width to 0cm if you don't want it displayed.

Then in the total_hrs On-Got-Focus event put

If Dept.column(1) = True Then 'Note first column is 0
Me!total_hrs.Locked = False
Msgbox "Sorry you don't have permission to change this field"
Else
Me!total_hrs.Locked= True
End If

Alternatively you can use the Dlookup as before, but referring to the combo column will be quicker.

I have also used the locked property not the enabled, because although all Total_hrs fields will be locked, you won't realise it because it does not change the appearance of the field, as does disabling. That is also the reason why I have added the Msgbox.

Good luck.

Rich
Lead Developer

http://www.blsoftware.co.uk