General discussion

Locked

Increment a Primary Key with a query

By anejako ·
Dear All:

Here's a bit of a stumper that should not be too hard but it is.

I have a field that I would like to append via a query to a table and have the values be "SHPF"(prefix)and 1 greater than the last value in the table. The field in thetable that needs to be "incremented" is called ShipmentInformationID. There would be at least one dummy record in the table so that values could be incremeneted from it, i.e. "SHPF1".

So, via an append query, I want to append a generated Primary Key value (SHPF2, SHPF3, SHPF4, and so on) with every record that I append to the table (thus autogenerating a Primary Key without using the lame AutoNumber feature)--I need these Primary Keys to be usable later in queries, and AutoNumbers just fall flat when you try to use them for that sort of thing.


I have tried this by assigning the value of the Primary Key field to a function that finds the maximum value of the ShippingInformationID, but all it does is put the same value on all rowsin the query!

Is there a better way to do this?

I need to use Access 97 (so pleez don't tell me to go buy SQL Server or something!) More than willing to try DAO or some other method, so any Access 97-capable solutions are welcome.

Thanks in advance.

This conversation is currently closed to new comments.

5 total posts (Page 1 of 1)  
| Thread display: Collapse - | Expand +

All Comments

Collapse -

Increment a Primary Key with a query

by RichTee In reply to Increment a Primary Key w ...

There are a few ways to accomplish what you want to due.
One very quick and simple way of accomplishing your goal is the following:


If an AutoID field is not in the table add one to the table that contains the ShipmentInformationID field.

Create an Update Query based on this table:
Field: ShipmentInformationID
Table: Name of the table
Update To: "SHPF" & [AutoID]+"87000"

When you RUN the query the values for the ShipmentInformatioID will be ?SHPF87001?, ?SHPF87002?, ?SHPF87003?, etc

For this to be an on-going part of your program:

Add the AutoID field to your input data form.

Add the following code to the ?On Exit? Event for the AutoID field:

Private Sub AutoID_Exit(Cancel As Integer)
ShipmentInformationID.SetFocus
ShipmentInformationID.Text = "SHPF " & [AutoID] + 87000
End Sub

You can control the start number by changing the ?87000? to whatever number you want to start with.
The return of the value for the ShipmentInformationID will continue to be AutoID plus the 87000 with the prefix SHPF.

To reduce input for your queries, in the criteria field you can use the following:
?SHPF? & [Enter ID Number]
You can also use the above with ?Between? and ?And?; >; etc.
If you used a space between SPHF in the UPDATE query and in the code (?SHPF ? & [AutoNum] +87000) to produce an ID ?SHPF 87001? then you will also have to use the space in the queries criteria (?SHPF ? & [Enter ID Number]

Hope this gets you on your way.

Rich

Collapse -

Increment a Primary Key with a query

by anejako In reply to Increment a Primary Key w ...

Thanks anyway--I guess Access is not very good at autogenerating primary keys. Oh well.

Collapse -

Increment a Primary Key with a query

by RichTee In reply to Increment a Primary Key w ...

There are a few ways to accomplish what you want to due.
One very quick and simple way of accomplishing your goal is the following:


If an AutoID field is not in the table add one to the table that contains the ShipmentInformationID field.

Create an Update Query based on this table:
Field: ShipmentInformationID
Table: Name of the table
Update To: "SHPF" & [AutoID]+"87000"

When you RUN the query the values for the ShipmentInformatioID will be ?SHPF87001?, ?SHPF87002?, ?SHPF87003?, etc

For this to be an on-going part of your program:

Add the AutoID field to your input data form.

Add the following code to the ?On Exit? Event for the AutoID field:

Private Sub AutoID_Exit(Cancel As Integer)
ShipmentInformationID.SetFocus
ShipmentInformationID.Text = "SHPF " & [AutoID] + 87000
End Sub

You can control the start number by changing the ?87000? to whatever number you want to start with.
The return of the value for the ShipmentInformationID will continue to be AutoID plus the 87000 with the prefix SHPF.

To reduce input for your queries, in the criteria field you can use the following:
?SHPF? & [Enter ID Number]
You can also use the above with ?Between? and ?And?; >; etc.
If you used a space between SPHF in the UPDATE query and in the code (?SHPF ? & [AutoNum] +87000) to produce an ID ?SHPF 87001? then you will also have to use the space in the queries criteria (?SHPF ? & [Enter ID Number]

Hope this gets you on your way.

Rich

Collapse -

Increment a Primary Key with a query

by anejako In reply to Increment a Primary Key w ...

Poster rated this answer

Collapse -

Increment a Primary Key with a query

by anejako In reply to Increment a Primary Key w ...

This question was closed by the author

Back to Software Forum
5 total posts (Page 1 of 1)  

Related Discussions

Related Forums