Validate data entered against another table

[russdwright]

I am working on a form that will show progress on a project involving several applications. I have the tables setup and a form created.



The core application information is stored on one table, while the progresses will be stored on three separate tables.

I have the Asset ID for the application setup as the PK for the AppInfo table, and I want to make sure the numbers entered in the other tables (the ones following the progress) match. The eventual goal is that when I enter the AssetID, the AppInfo loads in one piece of the form and the progress tables show the information in tabs below the App Info.

What is the best way to achieve this form of validation?

[June7]

Possibly use of form/subform arrangement for data entry/edit will meet your requirements. It is conventional approach.

[russdwright]

But what do I need to use to handle the validation aspect to insure that the Asset ID exists?

[orange]

Do you have your tables and relationships identified in your relationships window? You could post a jpg of this to help readers with your set up.
You could also give us a 5-6 line overview of your business and database so we can relate your tables to your business.

For simple validation you can use
If Dcount("[Asset ID]","yourtableName","[Asset ID] =" & yourFormControl) >0 sort of construct

If the count is >0 then it exists, if 0 then it does not exist.

I recommend you do not use embedded spaces in your field/object names.

[russdwright]

Here is the picture of my current relationship setup.

I work for a financial institution and we are in the process of centralizing the granting of access within various applications to a specific group (the group I'm working for).

Here is the breakdown of the tables shown:

AppInfo - Core application information. This has the Asset ID, Application Name, CIID (proprietary ID Code that is at least 20 characters long), and Contact Information for the application owner

Discovery - This is for tracking the Discovery portion of the process. We have sent out questionnaires via email to the Application Owners and this will be used to track when the information has been sent/received, when the follow-up meetings have been scheduled, and the results of that meeting.

DevHostOptions, RequestProcess, ScopeYear, and Defer Reason - These are setup for options for specific fields within the Discovery Table.

[orange]

OK. Where but where does this fit?

The core application information is stored on one table, while the progresses will be stored on three separate tables.

What exactly is Progress(es)?
I think you may be confusing a form and a table with Discovery??? A form is just a "window" on to a Table or combination of tables.

Sounds like
Each Application has a unique ID and an Owner(s) (1 owner per App?)
Questionnaire(s) 1 or more per Application (1 time or repeated?)

To clarify things for yourself and for readers, you might want to model the process(es) involved.
-identify Application
-identify Owner
-What question(s) are on the questionnaire(s)? These will probably break out to tables/attributes.
-What sort of responses do you expect? Mock up a questionnaire(s) and model the results/responses.

You mention meeting(s). Is it 1 or many? Where/How do you record the results?

[russdwright]

There are 3 stages for progress: Discovery, Transition, and Turnover. Each one will have their own meetings, the results of which will be covered in their own table (not yet created, as we are waiting to get more information so that those tables can be accurately created).

There is only one questionnaire sent for each application and there is one owner per application, though that owner may own multiple applications.

[orange]

Perhaps you review some of the links in this post to help with design. It may help get a complete picture.
Good luck.