One field cannot be more than another field

[Rob_Jones101]

Hi there.

I have two number fields and one field cannot be more than the other.

What is the basic code to check that one is not more than the other.

Thanks

 

[MajP]

Do this in the forms before update event.
This event fires before updating the record
Do your check and if false, then
Cancel = true ' will cancel the update and return the user to the record
Msgbox "some message here telling them to fix it"

 

[theDBguy]

Hi. You might also be able to use the Validation Rule for something like this. Just a thought...

 

[The_Doc_Man]

Microsoft Access tips: Validation Rules

How and when to use Validation Rules on your fields and tables, in Microsoft Access databases.

allenbrowne.com

In this article by Allen Browne, look for the "Table Validation" rules. It shows you briefly how you would approach this.

Note that it is EASIER if you are doing data entry through a form to have the form's VBA code do the validation so that you never enter bad data in the first place. Direct data entry to the table is always problematical.

 

[theDBguy]

Microsoft Access tips: Validation Rules

How and when to use Validation Rules on your fields and tables, in Microsoft Access databases.

allenbrowne.com

In this article by Allen Browne, look for the "Table Validation" rules. It shows you briefly how you would approach this.

Note that it is EASIER if you are doing data entry through a form to have the form's VBA code do the validation so that you never enter bad data in the first place. Direct data entry to the table is always problematical.

Hi. Just for completeness sake, Forms have a Validation Rule property as well that you can use. So, you don't necessarily have to do it at the table level. However, doing it at the table level can save you time because the next time you create a form for the same table, the form will inherit the Validation Rule from the table. But for existing forms, you will have to repeat/enter the same Validation Rule you put in the table. Cheers!

 

[zeroaccess]

How about if the sum of a field in the subform records should not be greater than a field on the main form?

So sum of field1 in child subform can not be greater than fieldA in parent record on main form

I think a query would first have to be created to tally that up. Correct?

 

[theDBguy]

How about if the sum of a field in the subform records should not be greater than a field on the main form?

So sum of field1 in child subform can not be greater than fieldA in parent record on main form

I think a query would first have to be created to tally that up. Correct?

Hi. In your case, it would have to be done at the form level. No, a query doesn't necessarily have to be created for it. If you want to do it at the table level, you could try using data macros. In other words, this situation will require the use of some code to validate the data input.

 

[zeroaccess]

Hi. In your case, it would have to be done at the form level. No, a query doesn't necessarily have to be created for it. If you want to do it at the table level, you could try using data macros. In other words, this situation will require the use of some code to validate the data input.

And my fear is that it would run before update of every subform record for every user during entry, increasing the amount of database lookups, etc...I'll have to think this through.

 

[theDBguy]

And my fear is that it would run before update of every subform record for every user during entry, increasing the amount of database lookups, etc...I'll have to think this through.

Hi. And if you're really trying to figure this out for your own project, may I suggest starting your own thread to avoid adding any confusion to this one. Good luck!

 

[The_Doc_Man]

Yep, theDBguy is spot-on. You can do field-level or record-level validation, but the farther you attempt to reach for comparison, the harder it will get.

Look at it as though it were a matter of scope/visibility.

Field-level validation rules can only look at the field itself and constants or conditions. Things like >0, <> "", <100

Record-level validation rules can look at other fields in the same record. Things like [StartDate]<=[EndDate]

To go beyond that, you need some way of looking outside of the record of current interest, and the next-closest place you can do that is if you have a data entry form, put some code in an appropriate event. Otherwise, it will be strictly after-the-fact validation through queries and that might take you into a place you don't want to go, since you would have to decide what to do if a query found discrepancies.

 

[Pat Hartman]

And my fear is that it would run before update of every subform record for every user during entry, increasing the amount of database lookups, etc...I'll have to think this through.

FYI, The BeforeUpdate event of a form runs ONLY if the form is dirty and is always the last event to run before the record gets saved. If you dirty a record by modifying it (or deleting or adding), the BeforeUpdate event runs. If you don't update a record, the event does not run. Don't look at events as something you automate from afar and very few programmers would ever automate a form to update a recordset. They would use an update query (most efficient) or run a DAO update loop using VBA. Form events are just that. They are triggered by some action the user takes - ONE record at a time.