Conditional formating but using limits from a table

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I need to be able to change many limits at once. I could use conditional
formating but that would mean changing each on every time a change is
required. Is there a way to produce the same effect as conditional formating
but using limits based on a table? The next stage would be to stop data
being entered if it's outside the limits I set, would this be easier?
 
If the limits are the same for all records in a form, you could place a pair
of text boxes on the form, and set their ControlSource to a DLookup()
expression that reads the limiting values. Then set the conditional
formatting to an Expression such as:
([MyField] < [MinLimit) OR ([MyField] > [MaxLimit])

If the limits change for each record in a continuous form/datasheet, you can
probably put the limits into some kind of lookup table that relates to the
table the form is based on. You can then create a query containing both
tables, and use this as the RecordSource for your form. In this way, the
limiting values are available in each record, so you can use the same
expression in your conditional formatting as discussed above.
 
Thank you I'll give it a go!

Allen Browne said:
If the limits are the same for all records in a form, you could place a pair
of text boxes on the form, and set their ControlSource to a DLookup()
expression that reads the limiting values. Then set the conditional
formatting to an Expression such as:
([MyField] < [MinLimit) OR ([MyField] > [MaxLimit])

If the limits change for each record in a continuous form/datasheet, you can
probably put the limits into some kind of lookup table that relates to the
table the form is based on. You can then create a query containing both
tables, and use this as the RecordSource for your form. In this way, the
limiting values are available in each record, so you can use the same
expression in your conditional formatting as discussed above.

--
Allen Browne - Microsoft MVP. Perth, Western Australia.

Reply to group, rather than allenbrowne at mvps dot org.

semimaker said:
I need to be able to change many limits at once. I could use conditional
formating but that would mean changing each on every time a change is
required. Is there a way to produce the same effect as conditional
formating
but using limits based on a table? The next stage would be to stop data
being entered if it's outside the limits I set, would this be easier?
Click to expand...
Click to expand...
 
Here's something I did in a survey database -----
The survey was comprised of Yes/No questions and questions where the answer
was selected from a drop-down list. I created a question type table.
QuestionTypeID is 1 for questions where the answer was selected from a
drop-down list and QuestionTypeID is 2 for Yes/No questions. QuestionTypeID
is a foreign key in the questions table. QuestionID is the PK. I also had an
answers table and QuestionID is a foreign key in that table. I built a query
from all these tables to serve as the form's recordsource. For entering the
survey answers, the form contained a textbox programmed to work like an
option button and a combobox. QuestionTypeID was added as a hidden field. I
then used conditional formatting based on QuestionTypeID to enable/disable
the answer textbox and combobox. In the end, all the questions appear in the
end; the Yes/No questions have the textbox enabled and the combobox disabled
and vise versa for the other type of question. Disabling these controls
keeps the user from enter either Yes or No for a question that requires the
user to select from a list and entering a selection from a list for a Yes/No
question.

You could use your limits the same way I used QuestionTypeID.
 
Back
Top