Field Data Validation

Hi,

Can someone advise me on how to validate against a list that contains child data from another list field.

I have a field named ‘Client’ which validates against a predefined list. I also have a field named ‘Owner’ The owner is a child record of its parent (Client) and therefore should validate from the chosen Client record. I can have only one Client but many Owners. I would like the validation only to show Owners that belong to their Client.

I’m sure that placing this in a list is not the correct procedure as the user could potentially choose an Owner that does not belong to the correct parent (Client). Can you suggest a better way to force data integrity

Thanks

Hello Richard,
I would suggest using database lists on your case. Something like this:
www.chronoscan.org/doc/filling_a_help_li...TE4&st=MA==&sct=NjY1

This way all you need is to use the correct SQL Query based on the Client selection.

Let us know if that helps!

Hi,

I'm not sure that is what i want TBH. What i would like is for the Owner list to display data that relates to the client.

I have an access db that has two fields clients and owners. When the user or the system enters the client i want only the owners that belong to that client to be displayed in the drop down and or validated against when populated.

I'm sure this can be achieved with a SELECT script statement, but i don't know how

I did some testing. On the list that should be dynamic use this:
SELECT Owner FROM [Client] WHERE Owner = '%docfield.Owner%'

You can use many variables on the select statement. Check them out at the Variables Wizard.
www.chronoscan.org/doc/variables_wizard_...Tg1&st=Mg==&sct=MA==

Hi Gsimao

I get the idea thanks, but syntacticaly i'm obviosuly getting it wrong.

I have created a Access Db called TenensChrono.accdb I have two tables;

Client & Owner

We can forget Client as it only has one field, therefore i am using the table Owner

Owner has two user defined fields, those being fClient and fOwner, fClient is unique whilst fOwner is the child of fClient.

Just for arguments sake i have an fClient named 'SCMAC' and it has four child records,

SCMAC
LRS
MONSTER
NESTLE

Therefore i have tried to write;

SELECT * FROM [Owner] WHERE fClient='SCMAC'

Just for testing prior to stating fClient='%docfield.Client%'

Both scenarios are incorrect, can you please point me in the right direction.

Hello Richard, I hope you had a great end of the year celebration. I wish you a happy 2018!

Try this:
SELECT [fClient] FROM [Owner] WHERE fClient = 'SCMAC'

Let me know how it goes.