I am setting up a database and the client wants and autonumber generated as the main search key and [u]have it updated in other tables upon generation. </u> I researched that setting up referential integrity will not work. Are there any other suggestions besides possibly creating an append query in a macro to run when an autonumber is generated????

What do you mean by "have it updated in other tables upon generation"?

Clive Astley

Appended to other tables. I will have several tables.

Say Item# is autonumber in the Items table. I need for it to show in the Customer and Contact table also.

I think you are referring to referential integrity. Please be completly clear on your explanation.



Sal

I can't get any clearer. I said in my first post that referential integraty will not update other tables with an autonumber that is generated in the orginal table.

I looking to see if anyone has any other ideas besides using queries to append the autonumber in the original table such as the Items Table to the Customer and Contact table.

If you have it as an autonumber in table1, then in related table2 it cannot be an autonumber field. Specify it as a number field and it will update accoridingly when you run your update query (or whatever process you are using).

Regards,

Beth M

Howdy!
You may give this a thought...I takes care of what I needed it to do for my RFQ database.
Create your two (or more) tables. Have table1 have the autonumber field. Have table2 have the exact same name for a field, but leave it as a number. Create a one-to-many relationship on your autonumber field that has all records from table1 and only those records in table2 that have the same value. Then create a query with all the items except the like field from table2 and only the autonumber field from table1. Then create your form for table2 off the query instead of the table. It will have the records there for you to fill out, but it won't write them to table2 until you have added something to one of the fields from table2.
I hope this is what you are looking for. It's not often I get to supply the help....usually it is all of you answering my questions!:D

Regards,
Laura

The only thing standing between you and your goal is doubt. Quit doubting yourself and you'll be able to accomplish anything!

It is not that I like to pick on people. With this in mind I must ask again. Please be more clear. Beth is correct with what she mentions. Now, are you trying to set-up a one to one relationship? It sounds to me that this is the case. I have had a client ask me this exact question.


Is that what you are trying to do?







Sal