Auto field entry

I have a MAIN database and a Detail database. They are linked by ID
field. I have a case number in both databases and I want to be able to
enter the case number in the MAIN database and when I create a new
record I want it to automatically show up in the Detail database case
number field.

I have gone in and linked the two case number fields in the
relationship boxes, but it still won't update in the Detail DB.

What do I need to do?

re: Auto field entry

Umm... whoa. this sounds like FileMaker-speak. They're not
*databases*, they're *tables*. Okay, that out of the way, on with the
questions... do you have a relationship set up between the two tables?
(Open relationships window, drag and drop primary key of parent table
onto PK of child table.)

Then reopen your form and see what happens... it should work now.

re: Auto field entry

The Main Table already has an ID field that is set up with a hidden
autonumber that is linked to an ID field in the Detail table. The Main
ID field is the primary key.

In the Detail table I have a CODE field that is autonumber that is the
primary field.

I need for there to be a one to many relationship with the case number.
The Case Number can not be a primary field is the message I get when I
try to make it the primary number. Is there another work around?

re: Auto field entry

Okay, so you should have a Long Integer type field in the Detail table
that you use to link to the Main Table. Once you have that, you can
join the two tables. This may not work if you have data in the two
tables and you violate referential integrity.

I know it's coming, so let me explain that. (Ready for a bit of set
math?) Basically, you cannot create a relationship between two tables
if there are values in the *child* table (Detail table, in your case)
that are NOT in the primary key of the main/parent table.

You can find these records that would cause this by using the Find
Unmatched query wizard. Or use something like this:

SELECT Detail.[Case Number]
FROM Detail LEFT JOIN MainTable ON Detail.[CaseNumber]=MainTable.ID
WHERE MainTable.ID IS NULL;

Then you should get the list of all key values that are causing your
problem. Then you can change the filter to NOT NULL instead of NULL
and append those to a new table and enforce integrity there...