Complicated Custom AutoNumber Question

[Randy]

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

 

[Pavel Romashkin]

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

 

[Randy]

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
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,
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

 

[Tim Ferguson]

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

 

[Randy]

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"

 

[Tim Ferguson]

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