Complex (for me) custom counter question....

[Fletcher]

Hi, I would like to set up a custom counter for processes in my
facility. I'll list my table structure first, then explain:

*ProcessXData*
DataID, where I want the custom counter, PK
Spec, number
ProcessDateTime, date/time, Now()
Operator, text
Comments, memo

*ProcessYData*
DataID, where I want the custom counter, PK
Spec, number
ProcessDateTime, date/time, Now()
Operator, text
Comments, memo

*ProcessZData*
DataID, where I want the custom counter, PK
Spec, number
ProcessDateTime, date/time, Now()
Operator, text
Comments, memo

*MeasurementsA*
MeasAID, text, PK (linked to DataID)
Top, number
Middle, Number
Bottom, Number

*MeasurementsB*
MeasBID, text, PK (linked to DataID)
MeasData, number
Limit, number

(for simplicity, I lave left out several tables that hold lists of data
that provide information such as operators and specs; I have also
generic-ized the data to make it easier for you, and not disclose any
information that could lead to problems)

So, here's the meat of what I'm having trouble doing. I have 3
different processes and each can have a different spec. For certain
specs on each process, a different measurement is done (MeasurementA or
MeasurementB). I have this worked out on a form where depending on the
spec number that you enter, a different subform for measurments will be
enabled. My dilema is that I can't use autonumber for each process as
an ID, so I would like to creat a custom counter that includes, say,
the abreviation of process name (4 or 5 letters) and a number. I
cannot think of how to do this, so if anyone would be helpful enough to
give me some input, I would appreciate it greatly.

Thanks ahead,
Fletcher...

 

[BruceM]

Is the abbreviation for the process the first five letters of the process
name, or is it derived otherwise? Also, do you want the numbers to start
from one for each process? That is, is the number First001, First002, etc.
and Second001, Second 002, etc., or is it First001, First002, Second003,
Second004, etc.?

 

[Jeff L]

You would need to write some code to make your ID. Access cannot
autogenerate what you are describing. If you are entering data via a
form, you would need something like

Me.DataID = Left(Me.ProcessName,5) & (Dcount("DataID","ProcessXData") +
1)

That is of course assuming you want your numbers to be sequential.

Hope that helps!

 

[Fletcher]

Is the abbreviation for the process the first five letters of the process

name, or is it derived otherwise?

The abbreviation is derived otherwise. I would like to specify it in
the forms code and and have it commented so that later personnel will
know where to change it.

Also, do you want the numbers to start from one for each process?

Yes, I do want them to start from one and be sequential.

Thanks,

Fletcher...

 

[Fletcher]

Me.DataID = Left(Me.ProcessName,5) & (Dcount("DataID","ProcessXData") +

1)

For this, would the DataID field have to be visible on the form? Or
could it be hidden? Or does it have to be present at all?

I'll experiment with this.

Thanks,
Fletcher...

 

[BruceM]

You're must be specific. How is the abbreviation derived? Knowing that you
want to specify it in the form's code etc. tells me nothing. If later
personnel change it, do you expect the number to start over from 1 with the
new abbreviation, or what? Jeff wrote a suggestion along the lines of what
I would have suggested, except I would have used DMax rather than DCount.

 

[Jeff L]

You would need to have DataID as part of your Record Source, but it
would not have to appear on the form.

 

[Fletcher]

Bruce,
The abbreviation is derived from the Process name. Usually some
combination of letter or words that can identify the process. For
example, we have a Spin Rinse Dryer...we abbreviate that with SRD, or
Boron Diffusion is Bdiff, or Gold Arsenic is Gars...it just depends on
what the process is.

As for starting over at one, it doesn't really matter as long as the
entries in *MeasurementsA* or *MeasurementsB* are related to a given
entry in the *ProcessXData* or *ProcessYData* or *ProcessZData*. We
really only care that a measurment can be attached to a pass through
the machine. Chances are that the engineers won't even see this field.

What is the difference between Dmax and DCount?

Jeff,
I tried your code in Form_Current. I'm having some problems with it
though. Maybe you can help. I put it in very similarly to what you
had (I changed the generic names back to the real names), but I don't
have a Me.ProcessName, so I changed that section to what I wanted it to
be (BDiff, one of the abbr.'s exampled above). The problem is that
first it didn't want to start counting, so I put the first entry in the
table with BDiff1. Then it started counting, but it left off the BDiff
and started putting only numbers in. Any idea how to fix this?

Thanks to you both.

Fletcher...

 

[BruceM]

Responses inline.

Bruce,
The abbreviation is derived from the Process name. Usually some
combination of letter or words that can identify the process. For
example, we have a Spin Rinse Dryer...we abbreviate that with SRD, or
Boron Diffusion is Bdiff, or Gold Arsenic is Gars...it just depends on
what the process is.

Then you will need a table of abbreviations, or some other way of assigning
an abbreviation to a process.

As for starting over at one, it doesn't really matter as long as the
entries in *MeasurementsA* or *MeasurementsB* are related to a given
entry in the *ProcessXData* or *ProcessYData* or *ProcessZData*. We
really only care that a measurment can be attached to a pass through
the machine. Chances are that the engineers won't even see this field.

You are going to be in for a complex bit of coding if you want the same
counter to span several tables, if it is even possible at all. You would be
better off to use a single table, and to designate the record as being for
Process X, Y, or Z (by selecting an option button, or selecting the process
from a list, or chicking a box, or whatever). Either that or you will need
to use a separate counter for each table.

It would help if you provide an example of the numbering sequence you
expect. If the first record is for SRD, the second for BDiff, the third for
SRD, fourth SRD, and fifth BDiff, do you expect to see: SRD1, BDiff1, SRD2,
SRD3, BDiff2, or do you expect: SRD1, BDiff2, SRD3, SRD4, BDiff5? If the
former, I think you will need to store the abbreviation as part of the
number. If the latter, you could just use DMax. Make the table's ID field
Data Type (in table design view) Number (Long Integer). Make a form based
on the table. In the form's Current event:

If Me.NewRecord Then
Me.YourID = Nz(DMax("YourID", "tblYourTable") + 1, 1)
End If

You can concatenate this number with the department abbreviation in an
unbound text box or in a query.
Note that Nz is necessary only for the first record. It may not be
necessary at all if the default value for the field is 0, but I'm not sure.

If you need the number to be, say, five digits each time:
Me.YourID = Format(Nz(DMax("YourID", "tblYourTable") + 1, 1),"0000")

What is the difference between Dmax and DCount?

DMax is the highest value. DCount is the total number of records. If you
have five records (the last one being numbered 5) and delete the fourth one,
the record count will go down to 4. Adding one to that will make it 5,
which you already have. DMax is the highest value. If you have five
records and delete the fourth one, the highest-numbered record will still be
5. Adding one to that will produce a unique number.

 

[Fletcher]

I can almost get this to work, but I think I need to explainmyself a
bit better apparently. I'll try harder this time.

Then you will need a table of abbreviations, or some other way of assigning
an abbreviation to a process.

We would prefer that we don't put this table in, but rather just have
the code for the counter assign a constant abbreviation. That way, it
can't be changed accidentally. Even though it doesn't matter that it's
constant and we simply desire a correlation between a given measurement
of either type and the pass through a machine (i.e. *ProcessXData*), we
would prefer that this abbreviation stays constant. If I have to use a
table, I can do that...but I don't exactly want to go through and track
down every single abbreviation that we use.

You are going to be in for a complex bit of coding if you want the same
counter to span several tables, if it is even possible at all. You would be
better off to use a single table, and to designate the record as being for
Process X, Y, or Z (by selecting an option button, or selecting the process
from a list, or chicking a box, or whatever). Either that or you will need
to use a separate counter for each table.

Your last sentence was right on the money. That's exactly what I would
like to do.

It would help if you provide an example of the numbering sequence you
expect.

For example, I expect the counter for *ProcessXData* to go soemthing
like this:
PrcX1
PrcX2
PrcX3
And so on.
And for *ProcessYData* :
PrcY1
PrcY2
PrcY4
and the same for *ProcessZData*.

With the relationship, forms and subforms being used a given
measurement for any process will acquire the same entry for it's ID
field as it's corresponding Process pass.

Make the table's ID field Data Type (in table design view) Number (Long Integer).
Make a form based on the table.

The point of this post was to find a way to get away from the numeric
ID (because we can't have every entry in 3 different process tables
with numeric ID's).

You can concatenate this number with the department abbreviation in an
unbound text box or in a query.

Unless this can be stored in the Process?Data.DataID field, it won't
help.

We really need an ID field with a unique identifying field, and the
only thing I can personally think of is the process abbr and a number.

I have no idea how to do this though. I imagine that it would have to
look at the previous entries and take off the abbreviation, then find
the maximum number, add one to it, and then put the abbreviation back
onto it. The only thing that I have trouble with is the coding.

And thanks for taking the time to respond.

Fletcher....

 

[Fletcher]

Well I've figured out one way to do it. I was having trouble with type
mismatching trying to do addition in a text field, so I'll tell you how
I did it.

I added a column to my table called DumbNumber. And a unbound textbox
on my form with a default as the abbreviation that I want. Then in
Form_Current I put a line like this:

Me.DataID = Left(Me.ProcessAbbr, 5) & (DMax("Number", "ProcessXData") +
1)

This might have been one of the ways you described Bruce, but I don't
think I understood at first. If you can think of another way, I would
give it a shot as well.

Thanks for all your help,

Fletcher...

 

[John Vinson]

We would prefer that we don't put this table in, but rather just have
the code for the counter assign a constant abbreviation. That way, it
can't be changed accidentally. Even though it doesn't matter that it's
constant and we simply desire a correlation between a given measurement
of either type and the pass through a machine (i.e. *ProcessXData*), we
would prefer that this abbreviation stays constant. If I have to use a
table, I can do that...but I don't exactly want to go through and track
down every single abbreviation that we use.

Having the abbreviations in a (properly secured) table is BETTER for
this purpose than having them buried in code. Authorized people can
edit them much more simply than by digging through multiple instances
of cryptic code.

If you have them in your code now, can you *reliably guarantee* that
you have the abbreviations consistant in all your forms, reports,
etc.? How can you be sure that you didn't make a typo in one of them?

John W. Vinson[MVP]

 

[Fletcher]

I would add an abbreviations table if it were that complex and I were
using the abbreviations in more places than just one instance on a
form. If it comes down to it and I begin to use it more than that, I
will probably do it. But for now, each abbreviation is only being used
once. And as of yet, only one abbreviation has been used. I'm having
problems with how the form functions for this process.

And since I have an MVP's ear, I'll ask you to view my other topic in
the forms area and give some advice on that issue. And if you have any
questions, or need more details, feel free to ask.

Thanks,

Fletcher...

 

[Fletcher]

The other thread is "Form locks up when subform gets focus..."

 

[david epsom dot com dot au]

Are you aware that you can have a primary key on multiple
columns? One way to do this is to put the 'prefix' into
a text column, and the 'number' into a number column.

Not that there is anything wrong with having a composite
tag field (we do that too), but if you want the numbers
for anything, its better to have them in a numeric field.

To maintain our custom counters across multiple tables,
we have a custom counter table. We just increment the
number in the counter table. We maintain multiple counters
across multiple tables: each counter is a separate record
in the counter table.

We also have a manual process that scans the multiple
tables to correct any synchronisation errors between the
counter table and the data tables. But actually we only
get errors like that if we create them in test.

(david)

 

[BruceM]

Responses inline.

Well I've figured out one way to do it. I was having trouble with type
mismatching trying to do addition in a text field, so I'll tell you how
I did it.

I added a column to my table called DumbNumber. And a unbound textbox
on my form with a default as the abbreviation that I want. Then in
Form_Current I put a line like this:

Me.DataID = Left(Me.ProcessAbbr, 5) & (DMax("Number", "ProcessXData") +
1)

I don't see the point of the extra DumbNumber field.

This might have been one of the ways you described Bruce, but I don't
think I understood at first. If you can think of another way, I would
give it a shot as well.

Thanks for all your help,

Fletcher...

So where does SRD and BDiff come into the picture? I thought they were to
be part of the ID number. Are you saying that every ID number (counter) in
ProcessXData starts with "PrcX", and is followed by an incremented number?
If so, you absolutely do not need to store that text over and over.

There is no problem with numeric IDs from what you have stated so far.
However, if you need to perform math on text you could try looking up the
Val function.

What process abbreviation? Your example above shows the same prefix for
every ID number in a given table. Remember, we cannot see you database, and
are not familiar with your line of business. "Process abbreviation" can
only mean what you have defined it to mean, and so far you have not defined
it consistently.

The problem is very solvable, but I **need to know** about the prefixes.
See below, where I asked very specifically about that.

As Jeff pointed out later in this thread, you could use a separate table
just for incrementing. I had not considered that option, but from what I
can figure out that is not what you want to do.

How to proceed depends upon your answer to the question in the preceding
paragraph, and/or the question about whether the prefix is the same for
every ID number in a given table.

 

[BruceM]

Correction: It was David who made the observation about the custom counter
table.

 

[Fletcher]

So where does SRD and BDiff come into the picture? I thought they were to

be part of the ID number. Are you saying that every ID number (counter) in
ProcessXData starts with "PrcX", and is followed by an incremented number?
If so, you absolutely do not need to store that text over and over.

Yes, that is exactly what I am saying. For a process table, the
abbreviation for the ID should be the abbreviation of that process.
How would I avoid storing the text over and over?

I'll re-describe the reason that I would like to do this. For example
I have 3 process tables and two measurement tables. Each process can
have either measurement preformed on it. So my problem is that if each
process table has a number field as PK, then the numbers will repeat in
the measurement tables and will not be correctly related back to the
process table. To fix that, I would like to have a PK that can relate
a measurement to it's process entry effectively and distinguish between
the processes. The PK that I have chosen too use is a combination of
process abbreviation and increasing number. As explained above, the
process abbreviation will be the same throughout a process table. If
you can explain how to do this without storing the text, that would be
awesome. I am always looking to learn new things about access.

As Jeff pointed out later in this thread, you could use a separate table
just for incrementing. I had not considered that option, but from what I
can figure out that is not what you want to do.

Correct...

 

[BruceM]

A PK is unique. That's all. It can be text or number. It could probably
be other things, but these are the main ones. The point is that it is
unique. Looking back at your original description of your tables, I see
that you have PKs in the Data tables related to PKs in the Measurement
tables. That means you have a one-to-one relationship: each process can
have only one associated measurement record, and each measurement can be
associated with only one process. If that is what you intend, the PK will
be the same for the related record in each table. I'm not very familiar
with one-to-one relationships, not having found use for one so far, but I
believe you can use code to set the default value of MeasurementID to the
same number as ProcID
Remember, the numerical PK can be stored, and something else can be
displayed. For instance, in an unbound text box:

= "ProcXData" & Format(ProcXID,"000")

Again, you can create ProcXID in an event.

If Me.NewRecord Then
Me.ProcXID = DMax("ProcXID","ProcessXDataTable") +1
End If

You can use the form's Current event in a single-user environment, or you
can use Before Update, or the After Update event of a text box, or maybe the
form's Change event -- it depends on your needs, and on whether you need to
see the number when you start the record.

Remember, if two fields are related then releated records will contain the
same value in those fields. That's the point. Records are related when
then contain the same value in designated fields.

 

[Fletcher]

Thanks Bruce, I understand how a PK works and how the tables link.
What I am looking for is something that distinguishes the PK in one
process table from the PK in another process table. Since the three
process tables are linked to the same measurement tables, they can't
all have an autonumber PK. For example, if the three process tables
had the a value in the PK of 7...how would an entry in MeasurementA
linked to the number 7 distinguish between processX and processY? To
fix this, I wanted to create a unique primary key with a combination of
a cutsom counter and a process abbreviation. I have figured out how to
do this, but you have told me that there is a better way. If you can
explain how to better to this, I would appreciate it greatly. I am
always in search of new (and especially better, more effecient) ways of
doing things. A life of learning for me.


Thanks,
Fletcher...