Generating a number

[alicia]

I have two tables named "customer" and "encounter" which
are linked by a field named "CPI" (CPI is unique to the
customer). Currently I import data from another system
where all customers have a CPI. Now I need to track
customers that are not in the source system that I use
for importing, and their information will have to be
manually entered for each customer. I would like to have
a button on the form where the customer is entered and
a "CPI" in automatically generated with maybe a "G" in
front of it.

Seems to me that since I already have my primary keys set
up, queries/forms working, this would be the easiest
approach to get these customers in the database.

I assume I need some kind of code/macro/something when
this button is pressed though I am not a programmer by
any means and haven't a clue where to start! Any
suggestions are appreciated. Thanks in advance. Alicia

 

[Steve Schapel]

Alicia,

Assuming the CPI (apart from the proposed "G") is a number, you can use
the DMax() function. There are a number of ways and places this could
be done... in the Default Value property of CPI, in the code, as you
have suggested, of a command button, alternatively in the code of the
form's BeforeInsert event, etc. The details of how to reach this
decision will depend on the answers to a few questions... Is the "G" at
the beginning of the CPI for all records, or only those not in the
source system? If the latter, do those that are in the source system
have something else in front? If yes, would you consider putting this
prefix letter in a separate field, and then concatenating it into a
composite CPI whenever required for your purposes on form or report? Do
you want the number portion of the new CPI to be one more than the
overall highest number, or one more than the highest for just the
non-source customers? Is your Customers form a single view or
continuous view form?

 

[Guest]

I have copied your questions and have types my answer
with a leading "*****" following your question:

Is the "G" at the beginning of the CPI for all records,
or only those not in the source system?
*****Currently there is a "C000" in front of the CPI on
those from the source system. This is what lead me to
think that a "G000" may be a good scheme.

If the latter, do those that are in the source system
have something else in front?
*****"C000"

If yes, would you consider putting this prefix letter in
a separate field, and then concatenating it into a
composite CPI whenever required for your purposes on form
or report?
*****I suppose I could if I can get guidance on how to do
this.

Do you want the number portion of the new CPI to be one
more than the overall highest number, or one more than
the highest for just the non-source customers?
*****The CPI from the source system will continue to
increase as I import new customers, so I will need to
make it one higher than the non-source customers.

Is your Customers form a single view or continuous view
form?
*****Single View.

*****Thanks so much for your help!! Alicia

 

[Steve Schapel]

Alicia,

The general wisdom of the ages says that the number and the preceding
C/G are two pieces of information, and therefore should be in two
separate fields. In this case, this is what I would recommend. Make
the CPI a number field, and just keep the number part in there, and make
another field, lets say it is called CPIPrefix where you put the 'C' or
'G'. Initially you will need to rename your existing CPI field to
something else (e.g. CPI_old), add your new fields to the table, and
then run an Update Query to update CPIPrefix to Left([CPI_old],1) and
update CPI to Val(Mid([CPI_old],2)). Then you can make the table's
Primary Key a composite of both these fields. When you import data from
the source system, import it into a temporary table, and then use an
Append Query to add the records to the final table, using similar
expressions to the above to "split" the imported CPI into the two
components. Hope this doesn't seem too complicated... it is actually
simpler than it sounds But make sure you have a backup copy of your
data before you start any of this.

On your data entry form, the *only* new customers you enter will be
non-source, right? So, in design view of the form, you can set the
Default Value of the CPIPrefix control to "G" and the Default Value of
the CPI control to...
DMax("[CPI]","YourTableName","[CPIPrefix]='G'")+1

Then, whenever you need to see the CPI as it is currently arranged,
which would normally only be necessary for presentation purposes on a
report, or another form perhaps, you can use an expression like this in
the query that the form or report is based on...
FullCPI: [CPIPrefix] & Format([CPI],"000000")
.... or otherwise, done directly on the form or report in the control
source property of an unbound textbox...
=[CPIPrefix] & Format([CPI],"000000")

 

[alicia]

I'm actually proud of myself in that I completely
understand your directions. MAybe this means I'm turning
into a beginning programmer and won't be able to use
the "I'm not a programmer" excuse much longer?

Thank you so much for the detailed advice. I will work
with it this afternoon and see how it goes. I'm sure
something will come up where I'll have add'l questions.
Thanks again and I'll be back soon.

PS...IS there a way to view a questions I have posted on
this newsgroup wothout having to go through each posting
to find mine from potentially many days back? Not sure I
know how to use this newgroup correctly?
Thanks again, alicia

-----Original Message-----
Alicia,

The general wisdom of the ages says that the number and the preceding
C/G are two pieces of information, and therefore should be in two
separate fields. In this case, this is what I would recommend. Make
the CPI a number field, and just keep the number part in there, and make
another field, lets say it is called CPIPrefix where you put the 'C' or
'G'. Initially you will need to rename your existing CPI field to
something else (e.g. CPI_old), add your new fields to the table, and
then run an Update Query to update CPIPrefix to Left ([CPI_old],1) and
update CPI to Val(Mid([CPI_old],2)). Then you can make the table's
Primary Key a composite of both these fields. When you import data from
the source system, import it into a temporary table, and then use an
Append Query to add the records to the final table, using similar
expressions to the above to "split" the imported CPI into the two
components. Hope this doesn't seem too complicated... it is actually
simpler than it sounds But make sure you have a backup copy of your
data before you start any of this.

On your data entry form, the *only* new customers you enter will be
non-source, right? So, in design view of the form, you can set the
Default Value of the CPIPrefix control to "G" and the Default Value of
the CPI control to...
DMax("[CPI]","YourTableName","[CPIPrefix]='G'")+1

Then, whenever you need to see the CPI as it is currently arranged,
which would normally only be necessary for presentation purposes on a
report, or another form perhaps, you can use an expression like this in
the query that the form or report is based on...
FullCPI: [CPIPrefix] & Format([CPI],"000000")
.... or otherwise, done directly on the form or report in the control
source property of an unbound textbox...
=[CPIPrefix] & Format([CPI],"000000")

--
Steve Schapel, Microsoft Access MVP

I have copied your questions and have types my answer
with a leading "*****" following your question:

Is the "G" at the beginning of the CPI for all records,
or only those not in the source system?
*****Currently there is a "C000" in front of the CPI on
those from the source system. This is what lead me to
think that a "G000" may be a good scheme.

If the latter, do those that are in the source system
have something else in front?
*****"C000"

If yes, would you consider putting this prefix letter in
a separate field, and then concatenating it into a
composite CPI whenever required for your purposes on form
or report?
*****I suppose I could if I can get guidance on how to do
this.

Do you want the number portion of the new CPI to be one
more than the overall highest number, or one more than
the highest for just the non-source customers?
*****The CPI from the source system will continue to
increase as I import new customers, so I will need to
make it one higher than the non-source customers.

Is your Customers form a single view or continuous view
form?
*****Single View.

*****Thanks so much for your help!! Alicia



number, you can use


places this could
the

code, as you


the code of the


reach this


questions... Is the "G" at
those

not in the
the

source system


consider putting this


concatenating it into a


form or report? Do


more than the
for

just the


view or

.

 

[Bas Cost Budde]

I'm actually proud of myself in that I completely
understand your directions. MAybe this means I'm turning
into a beginning programmer and won't be able to use
the "I'm not a programmer" excuse much longer?

Definately--and you won't be 'beginning' for long. Welcome to the group.

PS...IS there a way to view a questions I have posted on
this newsgroup wothout having to go through each posting
to find mine from potentially many days back? Not sure I
know how to use this newgroup correctly?
Thanks again, alicia

That depends on your news reader. I know the setting for that in Outlook
Express: it is in the View menu, under 'replies to my posts'. What
reader do you use?

 

[Steve Schapel]

Alicia,

I am not really familiar with the web interface for newsgroups, so I
can't comment. However, if you want more control and functionality with
your newsgroup usage, you would be better to get going with a newsreader
programme. Outlook Express has a newsreader built-in, and many people
use this. Forté Agent (of which there is a free version) is another
popular choice. Other free newsreaders I have seen recommended recently
are Mozilla Thunderbird, and 40tude Dialog.