Question Validating a field based on the selection of another field. Record validation

[adewale4favour]

Hey Guys,
Kindly throw in your contributions please. I will appreciate being assisted.


I have a table with about 7 fields. Two of these fields are important, PROCESS & CONFIRMATION fields. I need to force users to enter RECEIVED in the CONFIRMATION field, before they can change the PROCESS field from "Open" to any other thing.


This means, the PROCESS FIELD will not accept any other options, if the CONFIRMATION field is left blank, or with any other remarks apart from RECEIVED.


Many thanks in anticipation.


Regards
Michael

 

[vba_php]

you probably can't do this at the table with a validation rule or mask, so you'll have to do it through a form. you probably can't get it done using the interface resources and manipulating the form object that way either. do you want help with code to do it?

 

[adewale4favour]

If this cannot be done using Validation rules, it's ok if I get a code to get it working. But does that not mean I will have to change some of the already built in structures in forms?


Regards
Michael

 

[Galaxiom]

I suspect you are not using the right table structure. It sounds like you are working across the table when you should probably be adding records for the steps as they proceed.

This allows you to have any number of different types of steps for different jobs. Think of each step as a transaction against the job. You record it with other fields like ActionDateTime, ActionAuthority etc.

Your system prevents an Open record being added until a Received record is committed on the JobID.

Record your ActionName against ActionID as an integer in another separate table. Use the ActionID in your main table. Much easier to verify a sequence of numbers.

Another table with JobActions records the sequence of Actions that apply to a particular JobTypeID. The individual Jobs are assigned a JobTypeID when they commence.

 

[Galaxiom]

With the structure I described the query to report what state each job is at is very simple.

SELECT JobID, Max(ActionID) AS LastAction FROM thetable GROUP BY JobID

Working across the fields requires you to determine which is the last non Null field for a JobId. Databases are not designed to work that way.

The form will have a main form with the Job information and a subform with the Actions. I can show you how to set up the ActionIDs to appear automatically in the subform based on the JobTypeID and only become records when you add the rest of the Action information.

Don't fret that you will need to redo your form. It is vital to get the table structure right. This effort now will save you masses of time in the project because the "across" structure will need complex workarounds all over the place.

 

[Deleted member 147267]

I don't get the same impression about the structure because there's not enough information but I could be wrong about that. If Process and Confirmation fields are 2 attributes of whatever the table is about (and the rest of the fields are associated this way) then the table is quite likely OK. However, if Received and Open are different values for the SAME attribute (e.g. it's a status thing) then I agree that the design is wrong. Perhaps we need to know more about the table(s).

IMHO, to continue as is would require that validation is done in code behind the form. Maybe that's true regardless of whether or not the table design is correct.

 

[vba_php]

I suspect you are not using the right table structure..

GALAX,

You and every other professional here constantly say this type of thing as a response to a guy like this asking this type of question. what you guys do not realize is that a lot of these question askers are working for people that are business oriented and don't know a darn thing about "table structure" or anything else that is software related, nor do they care. so it's very possible that the guy asking the question in this thread is stuck with what he's got. I see this constantly, so the proper thing to do is give him a solution NOW and then hope he takes the advice and restructures his software they way we tell him it should be structured. Don't be a "typical" engineer my friend. In the economy the way it is now, that doesn't work in a lot of cases. Everyone that is trying to do business and differentiate themselves from everyone else are constantly getting involved in "workaround". that's called differentiation for the purpose of competition.

If this cannot be done using Validation rules, it's ok if I get a code to get it working. But does that not mean I will have to change some of the already built in structures in forms?

Michael,

you probably won't have to change much in your forms. as I told you before, this is a form issue and the coding behind it is not difficult. this is simple lookup operations. if you want a code sample, say so and I or someone else can give you something u can understand.

 

[Pat Hartman]

@vba_php
We do have a certain responsibility to instruct people in how to design tables and forms that won't impede future development. If the OP cannot change the structure, then he can't but hopefully, he will understand how to do it better in the future.

You may elect to encourage poor design but the rest of us won't. Certain design patterns are "best practice" for a reason and if we don't share "best practice" suggestions, how will any one get any better? It's not like we're being paid to do this.

 

[vba_php]

how will any one get any better? It's not like we're being paid to do this.

at this point in time Pat, it's really a crapshoot in terms of who wins. you guys the trained engineers, or someone like me, the creative workaround guy. neither way works better than the other, although "proper" is always encouraged I'll grant ya that. and if I'm not mistaken, I DO try to tell people about how to be "proper" if I get a feeling they have any chance at all of changing things to be that way and convincing the boss. but in some of these threads, it's blatantly obvious that the people asking the questions have been put in situations that you would probably not end up in because you're so good at getting your point across, and I assume you're also a good planner as well.

 

[Galaxiom]

at this point in time Pat, it's really a crapshoot in terms of who wins. you guys the trained engineers, or someone like me, the creative workaround guy. neither way works better than the other,

Absolute nonsense. A properly constructed database will literally outperform a "spreadsheet in a table" by orders of magnitude.

You choose your terms to paint a picture of stuffy professionals versus a "shoot from the hip" fixer. There is nothing "creative" at all about kludging a poor structure into working using masses of RBAR (Row By Agonising Row) code where much of business logic is embedded in the code.

That kind of approach results in a dead end database. It is not extensibile because adding features requires table, form and report reconstruction and rewriting code. The developer has to get involved when business rules change. Good design supports extension by simply adding records.

It is not scalable because it is miserably inefficient, grinding down to a snail's pace when heavily loaded. It is not maintainable because of the complexity involved in the workarounds. Much of the code is specific to the database so is not portable for use in other projects.

When the time comes to build the application properly, all those kludges represent an incredible waste of effort and resources because everything already done is thrown away. Tragically it often comes at a time of rapid expansion when the business can least afford to start again.

I DO try to tell people about how to be "proper" if I get a feeling they have any chance at all of changing things to be that way and convincing the boss.

I've not seen any evidence that you actually know how to properly normalize data. Your ridiculous comment, "neither way works better" reinforces that impression.

Moreover, you derided me, and professionals like me who point out how the job should have been done, for being "a typical engineer".

By all means go ahead and offer your kludges but don't have the temerity to suggest that offering sound design advice is inappropriate. It is up to the person asking for help to decide whether they want a quick and dirty fix continuing to develop a dead end or have a goal to build a tool with a future.

 

[pbaldy]

neither way works better than the other

You can't be serious. If I could have said it all any better than Galaxiom I would have, but I can't.

 

[vba_php]

You can't be serious. If I could have said it all any better than Galaxiom I would have, but I can't.

u guys are probably right. Ur way does work better and should. And it probably wont be that far in the future when job creators and entrepreneurs can finally slow down again and listen to how things should be properly done. I look forward to that day. I may even end up in a computer science degree program at a university. Who knows...

 

[vba_php]

Hey you guys ive got a great article on how being "proper" COULD lead to disaster for you IF you tell the wrong person your secrets. Im sure you smart dudes already know this but maybe you dont. Im on my phone now but when i get home ill start a thread in the watercooler bout the subject and we can talk bout it there. Lets end this discussion here cuz we dont want to do the same thing to this poor question asker that we did to kissbambi! More to follow....

 

[MajP]

Dude, we are standing by on pins and needles awaiting your vast wisdom.

 

[Uncle Gizmo]

GALAX,

You and every other professional here constantly say this type of thing as a response to a guy like this asking this type of question...

Yes that is true I agree with you.

GALAX,

What you guys do not realize is that a lot of these question askers are working for people that are business oriented and don't know a darn thing about "table structure"..

This is true up to a point, but there's one glaring and obvious omission. Many years ago we were not professionals we were beginners just like these people we are trying to help.

We also constructed Access databases along the lines of "Excel" and many of us did not have the luxury of a forum like this, they weren't very common when MS Access first came on the scene.

Not only did we learn about this problem the hard way, it caused us massive damage and heartache, so it's ingrained in us to avoid this type of mistake.

Now there's one question I have for you Adam, how on Earth did you get into database construction without falling into this trap? Because I'm sure if you had fallen into this Excel type trap then you would be just the same as us and warn everyone of it.

Another thing Adam, why don't you pick up the baton for these people and develop some class modules and code for sorting out the very problems they experience? It should be relatively easy to write code that can scan through Fields horizontally instead of vertically and you would be doing these poor unfortunate newcomers to MS Access a great favour, well until they had to do anything serious with the malformed data!

Sent from Newbury UK

 

[vba_php]

Dude, we are standing by on pins and needles awaiting your vast wisdom.

that's totally unwarranted Maj, but if I deserve it fine. To see what you desire, my "vast wisdom", read what you requested (and by all means, take it LIGHTLY!):

https://www.access-programmers.co.uk/forums/showthread.php?t=308509

Now there's one question I have for you Adam, how on Earth did you get into database construction without falling into this trap? Because I'm sure if you had fallen into this Excel type trap then you would be just the same as us and warn everyone of it.

perhaps it was because my father put me onto MS ACCESS when I was 12 years old when it first came out and I totally skipped over excel and the ease of it's use. I have no idea. I just started in Access and manipulated it to death to find out everything I could with it and I never once touched the Excel program.

Another thing Adam, why don't you pick up the baton for these people and develop some class modules and code for sorting out the very problems they experience? It should be relatively easy to write code that can scan through Fields horizontally instead of vertically and you would be doing these poor unfortunate newcomers to MS Access a great favour, well until they had to do anything serious with the malformed data!

I would love to do that Tony, and I might just post some good stuff up here at some point but I'm a little too busy right now to do so. I'm in the process of creating my wordpress site for my consultancy and the amount of content that is required to make it effective is taking a lot of my time at the moment. But it shouldn't be too long and it will be live and I will share it here with all of you so you can give me a push/pull conversation about what you might hate or like about it. Programmers will surely love the resources I will offer, but the primary purpose will be geared towards business people and their understand of the way we operate.

 

[Gasman]

I have worked at two places in the past (one a reasonably large company, one where the system was sold to customers) where the system that was created did the job it was meant to do admirably.

However behind the forms, it was a mass of spaghetti programming, with kludge after kludge to keep it going. Not a nice task to take on.

One can get away with that if the bosses and rest of the team know less than the person in charge.?

 

[vba_php]

However behind the forms, it was a mass of spaghetti programming, with kludge after kludge to keep it going. Not a nice task to take on.

from what i've seen, that can happen for a number of reasons, and my suspicion is that the primary reason it happens is because every manager wants to get their way and look good and thus each of them give seperate requirements to their people.

that might not be true, but i've seen it take place first hand in businesses that are software related and non-software related. for instance, when I started in this business I was working at Pearson:

https://www.pearson.com/us/

and they're primary scoring software was a massive java platform in the form of applets. here in iowa city they had 1 dude maintaining the code and he was so well protected and unbothered all day long that he wasn't even seen for most of the day by anyone else. he was constantly on call because whenever the software went down or needed a patch, he was called to add even more spaghetti to the already unintelligble code structure of the application.