Same Field in Two Tables

[Sanjo]

-Access 2010
-I have created two Tables, A & B
-Data is always entered into Table A first
-both tables have Field X
-when a value Y is entered into Field X of Table B, can VBA code be written that can check Table A to ensure value Y is present in that table's Field X?
I am obviously a newby to Access and VBA. Of course, if code can be written, your creation of it wouldn't be rejected.
thanks in advance for any help
Joe

 

[gemma-the-husky]

it is unusual to need the same value in two different tables. what are the tables, and why do you think you need to do this?

 

[Sanjo]

-Table A has a field that shows the "Niche" number that has been sold in a columbarium (a resting place for urns holding human ashes). In other words, who has purchased which niche.

-Table B also has a field that will show the niche number that an urn has been placed in.

So, the question arises that when we key the niche number into Table B that an urn is to be placed in, how will the system confirm that that niche number is really "owned". Perhaps we are placing the urn in a niche that hasn't even been purchase.

I guess this raises the question as to whether the table showing ownership (A) and the one showing which niche the urn is inurned (b) should be seperate or one and the same..

pls advise.

Joe

 

[Pat Hartman]

This is what referential integrity is intended to control.

tblA
NicheID
OwnerFirstName
OwnerLastName
etc...

tblB
DeceasedID
NicheID
etc...

You will then define RI between NicheID in both tables. Do not specify cascade update or cascade delete. You also need to make NicheID a required field in both tables. If a Niche can only hold a single urn, you don't need tbl2 at all. Just put the data regarding who is in the urn directly into the Niche record. If niches vary in size and so can accomodate more than one urn, add a count to the Niche record.
On your form, you would choose the NicheID for tbl2 from a combo that lists only Niches that have been purchased but are empty. i.e. OwnerLastName Not Null and dCount("*", "tbl2", "NicheID = " & Forms!YourForm!cboNicheID) <= UrnCount