Advanced Conditional Formatting 1

[freeman74]

I need to be able to use the values in TABLE "tblLimits" to format fields of a FORM "frmInsCon" that is based on TABLE "tblInsCon". I will explain the best I can.

tblLimits - Contains minimum values necissary for compliance. Each record is a different set of limits and has a number in the key field to identify the limit set.

tblInsCon - Contains the main information of the database. It houses insurance records that need to have a minimum amount of coverage to be in compliance.

frmInsCon - An entry form to enter data in to tblInsCon. When data is entered into any of the amount fields I want it to check the tblLimits to make sure the amount meets the minimum requirements for compliance. If it does not I want the amount to turn red and go bold.

I can get this to work if I "HardCode" the amounts in the vba code but I need to be able to use different sets of limits for different types of insurance claims. My problem is that I don't know how to referece the data in tblLimits from frmInsCon in VBA

Any help would be much appricated
Thanks

 

[Tranman]

freeman,
Here's a little snippet that will do that lookup for you in code. If you're using Access 2K or XP, it will work with no other actions. If using Access 97, you will need to open your code window and go into Tools|References, and add a reference to "Microsoft ActiveX Data Objects nn Library".

Public Function LookupLimit(KeyVal as Double)

Dim rstLimit As New ADODB.Recordset
Dim con As New ADODB.Connection

con.Open "Driver={Microsoft Access Driver (*.mdb)}; Dbq=" & CurrentDb.Name & ";" '****

rstLimit.Open &quot;Select * from tblLimits where <key field name> = &quot; & Keyval & &quot;;&quot;, con, , , adCmdText

If val(forms!<formname>.<textboxname>.text) < rstLimit.Fields(&quot;<low limit fieldname>&quot then
<textboxname>.setfocus
<code to make text red>
End If

rstLimit.Close
con.Close

Set rstLimit = Nothing
Set con = Nothing

End Function

Of course, you'll have to make the If statement check the values you want to check, but the format will be like this. You can put a call to this function in the LostFocus Event of the textbox(es), and pass the key value from there, or alternatively, instead of passing the key to the function, you could just get it from the form. Whichever is easier. You could also just take the code out of this function and put it in the lostfocus event.

There are about a zillion ways to do this. This sample is one of those.

Tranman

 

[freeman74]

Thank you very much, it worked perfectly