Update primary Key.

[ryan]

Hello,

I have a db with two tables that have a one-to-many relationship. I also
have a form with related sub-form for entering data into these tables. The
tables were imported from an existing excel table and due to the nature of
the data (one-to-many relationship for example) there was no way that I could
make the primary key an arbitrary number. Instead I concatenated multiple
fields to create a unique ID. I want to use the same system for future data
entry. Is there a way to have the Primary Key field "Entry_ID" update
automatically after data is entered in the other fields?

Example-

Plot-45
Count-02
Year-2009

and then have the Entry_ID update to - 45022009.

I have used an update query but that doesn't help me because I can't leave
the primary key field blank or null.

Thanks in advance.

 

[John Spencer]

Instead of building a primary key by concatenating the values, make the
primary key the three fields. Just open the table in design view,
Control-click on the three fields and then click on the Primary Key button.

If you insist on having them combined into one, then you will need some code
on the entry form to do this AND you will have to do all entry through a form.

In the before update event of the form you would need a line of code like the
following to set the value.

Me.EntryID = Me.txtPlot & Me.txtCount & Me.txtYear

John Spencer
Access MVP 2002-2005, 2007-2009
The Hilltop Institute
University of Maryland Baltimore County