Autonumber

[Guest]

Hi

I have to create a "Certificate" report which will have a unique numeric
reference for each "Certificate" generated.
I have created a select query that pulls the various pieces of data needed
to form the "Certificate" from the appropriate tables. The main points from
this are that once the record is selected and included within a "Cert" the
records are not used again, therefore I have used the "Is Not Null" and "Is
Null" criterias to help with this selection.
I need help with the number generation as I can cope with updating the
fields in the tables with the certificate number. I also need to point out
that there will be two versions of the "Certificate" report which will need
to use the same numbering system, for the integrity of the Certificates.

Thanks

Richard

 

[Allen Browne]

It sounds like you need to keep track of the number you issue for each
certificate. For starters, you need that consistency between the 2 reports,
and perhaps you need it later as well.

To do that you need to store the Certificate number somewhere. If there is a
main table that identifies the record that needs the certificate, you could
add a CertificateID number field to that table. It starts blank, and you
assign a number at the time when the certificate needs to be created.

Alternatively, if there are several tables that feed directly into the
certificate, you need to create a Certificate table, with a CertificateID
field (probably an AutoNumber.) You then create the certificate, and set the
CertificateID foreign key in the other tables with this value.

This will involve some code, so you will need to be familiar with VBA to
achieve this. Depending on the above, it could be a DMax() to get the
highest certificate used so far and add 1. Or it may involve executing an
Append query statement, or perhaps an OpenRecordset() so you can get the new
certificate number and execute an Update query on the related records in the
other tables.

 

[Guest]

Allen
I have tried an append query to a new table called "Certificate Details",
with an extra field that is set as 'autonumbering'. What is happening is that
each record is getting an new sequential number, but what I want is that each
record that is posted to "Certificate Details" with the append query receives
the same CertID. I will then be able to run an update query to post this
CertID number back to the original table(s).
Could you please advise code or format for the tables/Queries to achieve the
numbering

Many thanks

Richard

 

[Allen Browne]

Okay, so one Certificate has many child records in other tables. To do this
you need to add the record to the Certificate table first, get the new key
number, and then use an Update query to assign that new value to the foreign
keys in the other tables.

I can't write that code for you: it's not trivial. Use OpenRecordset() on
the Certificate table. Then AddNew and Update, getting the new ID. There's a
kind of example (assumes the recordset is already open) here:
http://allenbrowne.com/ser-57.html