Multiple True/False columns, forcing behaviour

[RoscreaMou]

Hi,


I'm adding a fivefields to a table, all of which should have a true false value, the first four can be true or false at the same time but the fifth can only be true if the first four are false and conversely the fifth has to be false if any of the first four columns are true, how can I force this behaviour?

In Sql server I'd guess I'd just use a stored proc but there isn't a direct equivalent in access and I don't think you can trigger VBA from an record update event, or can you?

Thanks
Trev

[pbaldy]

Well, 2010 has data macros, which are a little like triggers. Personally I'd probably use the before update event of the form to test the logic:

http://www.baldyweb.com/BeforeUpdate.htm

which is in effect "triggering VBA from an record update event".

[RoscreaMou]

Thanks Paul, that works a treat from a form but the user wants to be able to check the boxes in the table (set to true or false) and from there I want to enforce the data quality on the other fields, guess I can't really do that can I? I can superimpse a form on them I guess........darn users

[pbaldy]

I never let users in tables (or queries); you have virtually no control there. If you want to do it, I suppose a data macro is your only choice, though I'm not sure how it would be structured. I know they exist, but I haven't used them other than rudimentary testing.

[RoscreaMou]

Yeah, I have a preference for keeping them at bay...were possible, having said that I understand their belief it's better to have access to the underlying values.....think I'll force them to use the form, the worst they can do is say no and not want the changeds

[pbaldy]

With a form you are giving them access to the underlying values...under your control.

You've provided me with a reminder to be grateful that I work for people that don't bother me about "how"; just that the application performs the required functions. If they demanded access to the raw data, I'd get a signed release that I was no longer responsible for data integrity.

Welcome to the site by the way!

[RoscreaMou]

Thanks Paul, delighted to be on the site, I work in Risk Mgt but run a lot of my calcs out of Access (normally triggered by email events kicking off vba code in outlook) with some other things run in Excel at the end, however in this case I'm helping a friend who's been asked by a client of his to change their existing database, I'll sneak the form in under the guise of 'improving' the database integrity and security.......

[pbaldy]

Sounds like you'll be a good resource here. I often automate Outlook from Access, but sometimes I suspect it would be better done from within Outlook completely. For instance I just built a little db that imports data from the text of an email with a particular subject. I fire off that db from an Outlook rule, and it loops the inbox looking for that subject, then processes the email and closes. I suspect it would be cleaner within Outlook, but I'm more experienced/comfortable working from Access. Next time I'll ask you!

[RoscreaMou]

The great thing about working withing outlook is you never trigger the outlook security 'features' when you want to send mails etc!

Actually I've shifted most of the things over the last few years to be controlled from outlook, still code everything in Excel or Access (clearly you can do everything from within outlook but I like being able to see the tables etc in front of my face, I'm a bit old fashioned like that) but just trigger the event from outlook, it's a surprisingly effecient way of doing things...having said that, even with VBA, people who can do anything with outlook are a small sub set, the uber-nerds , it's not got the most transparent framework.....at my last employer who used internal forums for user help I was the one answering most of the questions (for a firm of 80k+) on outlook & outlook vba....well until the outlook server team noticed and felt they should get involved....think I hurt their professional pride, after all I am only a user

Sue Mosher (who's an outlook MVP) has a very good book on it, actually at the time I was looking for one I'm fairly sure she had the only power on outlook VBA! lol