Complicated Custom AutoNumber Question

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

Randy - 23 Jun 2004 18:36 GMT

I have a table [Analysisform] with a field of "CertificateNo". I need to
assign a custom autonumber that will advance one number, such as K1000,
K1001 etc. Not for each record, but for a summary of records. The summary
could include from 1 to 75 records. Each record must have the same
"CertiticateNo" When the summary of up to 75 records is completed, the same
"CertificateNo" used for previous records must end. The next record must
automaticaly advance to the next "CertificateNo" and continue with each new
record untill the Summary is printed. Any help is appreciated...Randy

Reply to this Message

Pavel Romashkin - 23 Jun 2004 22:41 GMT

This can be accomplished using a couple of IF statements and string
concatination. I am not providing a code example because I didn't
understand which part of the "autonumber" is the "CertificateNo" and how
is the program supposed to know if it is time to move on to the next
"CertificateNo". You are saying "from 1 to 75 records" so there must be
some criteria for this, or is it up to the user? Then, what is the
interaction of the user to use the next CertificateNo?

Pavel

> I have a table [Analysisform] with a field of "CertificateNo". I need to
> assign a custom autonumber that will advance one number, such as K1000,
[quoted text clipped - 4 lines]
> automaticaly advance to the next "CertificateNo" and continue with each new
> record untill the Summary is printed. Any help is appreciated...Randy

Reply to this Message

Randy - 24 Jun 2004 02:25 GMT

Currently the "CertificateNo" is not an autonumber. Users enter this number
manually. The only way the program would know when to assign a new
"CertificateNo" is when a command button is clicked to print the summary.
After the summary is printed, hopefully there is a way to create a new
"CertificateNo" on the next new record. I hope this is clearer. Thanks

> This can be accomplished using a couple of IF statements and string
> concatination. I am not providing a code example because I didn't
[quoted text clipped - 14 lines]
> > automaticaly advance to the next "CertificateNo" and continue with each new
> > record untill the Summary is printed. Any help is appreciated...Randy

Reply to this Message

Tim Ferguson - 24 Jun 2004 22:48 GMT

"Randy" <rcpritchard@comcast.net> wrote in news:AYidnQlGe48-sEfdRVn-
uA@comcast.com:

> The only way the program would know when to assign a new
> "CertificateNo" is when a command button is clicked to print the summary.
> After the summary is printed, hopefully there is a way to create a new
> "CertificateNo" on the next new record.

Presumably (hopefully?) you have another table for Certificates, which is
the "one" end of the one-to-many relationship with AnalysisForms. It would
be easy to make the button-press print the summary, and then insert a new
Certificate record, with an autonumber to identify it.

HTH

Tim F

Reply to this Message

Randy - 25 Jun 2004 01:17 GMT

Another table for "CertificateNo"? Are you saying I should have another
table for "CertificateNo" that the form "AnalysisForm" would select from?
Such as "tblCertificateNo" with two fields: [CertificateNo] and then a
autoumber such as [CertificateID"

> "Randy" <rcpritchard@comcast.net> wrote in news:AYidnQlGe48-sEfdRVn-
> uA@comcast.com:
[quoted text clipped - 12 lines]
>
> Tim F

Reply to this Message

Tim Ferguson - 26 Jun 2004 23:59 GMT

> > > The only way the program would know when to assign a new
> > > "CertificateNo" is when a command button is clicked to print the
[quoted text clipped - 9 lines]
>
> Another table for "CertificateNo"?

From your description (still quoted above), it sounds like you are tracking
some kind of certificates, and some number of AnalysisForms attached to
each certificate. That to me comprises two entities connected by a one-to-
many relationship. Of course, I have absolutely no idea what Certificates
or AnalysisForms are in your business context [1].

> Are you saying I should have
> another table for "CertificateNo"

Actually I name tables after the things they describe, so it would be
"Certificates"

> that the form "AnalysisForm" would select from?

GUI objects like forms come way after you have the data design (tables and
fields and relationships) nailed hard down.

> Such as "tblCertificateNo" with two fields:
> [CertificateNo] and then a autoumber such as [CertificateID"

I don't know what the CertificateNo would do differently from the
CertificateID; I also kind of assume that there are other things about
certificates you might want to record, such as IssuedDate, ValidTerm,
OfficerSignedOffBy, AnalysisComplete, or whatever (don't forget point [1]
above!).

Basically, when someone says, "we have so many of these that make up one of
them", then there are two things being counted.

Hope that helps

Tim F

Reply to this Message