Next number based on current record

[John H.]

I maintain and update a database where I record Alphanumeric tags. Different
sets of tags are used at any given time. When I enter a new record, I
currently use a macro that will copy all the information from the current
record, create a new record, and paste that data into it, so I have minimal
data entry to do. Only two fields need to change on each subsequent record,
one of which is Tag_Number. My question: I want to have the tag_number
field increase by one in the pasted record. How can I do this?

Thank you very much,
John

 

[BruceM]

This will provide a method of incrementing a numeric value by 1.
http://www.rogersaccesslibrary.com/download3.asp?SampleName=AutonumberProblem.mdb
If you need to add letters into the mix you will need to provide more
information, such as the origin of the letters. Assuming you mean to
increment by 1, do you intend to increment by 1 over the highest number so
far, or by 1 over the current number no matter what? Out of curiosity, why
store the same information in every record? Why not just put the
information into labels, or place the values into a separate table, or
something like that? Of course, I don't know the details of what you are
trying to do.

 

[John H.]

The database is a record of tagged fish. I record when a tag goes into a
fish (data table), and then again when it is reported to us by an angler
(recapture table). The two tables are joined using the tag number for time
at large, growth, distance traveled, etc. We have different sets of tags, we
use depending on the situation. They are all individually numbered, have
different prefixes, such as MS#####, MR#####, and are not used in sequence,
because several different teams will be on the water at different times.

In short, I want to increment by 1 over current number, no matter what.
Thank you for asking!

 

[BruceM]

Is the sequence: MS00001, MS00002, MR00001, MS00003
or is it: MS00001, MS00002, MR00003, MS00004?
In either case, if you are at the record with MS00001 and you want to create
a new record with the MS prefix, you want that tag number to be MS00002,
even though there is already a tag with that number? What happens to the
record that used to have MS00002?
I understand that the tags are not used in sequence, but you want them to be
numbered in sequence, right. Presumably somebody takes the list of numbers
and inscribes them (or performs some procedure that has that effect) one at
a time onto tagsthat are distributed to the various teams.
What is the source of the prefix?

 

[John H.]

There are no duplicates... Tags are preprinted from the manufacturer. The
purpose of the prefix is multi-faceted. One, it allows for duplicate numbers
to be used in different studies, or in other cases, tags are of different
sizes depending on the type of fish tagged. In some cases I'll have 50-100
records to enter. As I said before, each new record already has all the
information on it, save for the tag number needs to be increased by one.
There are still times when I have to enter a tag number anew, but that's no
big deal. I'm just trying to make it a bit more automated. Thanks again!

 

[BruceM]

This can be done, but the sentences with question marks need answers or I
won't know what you want. So you are not creating tag numbers, but rather
you want the preprinted tag number to be part of a new record? Does that
mean you will be doing data entry only, just one new record after another,
and you will be working from a list that is in the correct order? I'm just
trying to understand the situation.

 

[John H.]

Bruce,
Thanks for replying! it's MS00001, MS00002, MR00001,MR00002, MS00003,
MS00004, MS00005
I want to enter the tag numbers as they are given to me. Sorry, I should
have specified the data entry part.
The process begins each time with my getting the sheet, with all the info
about that set of data. I open the form to create a new blank record, and
enter the location and conditions information (Header-type stuff), as well as
the data about the first fish on the sheet. There are 25 lines per page.
From here on out, only two fields will have to be changed in each new record
until the dataset is closed, or I change the header information and
essentially begin a new dataset. In effect, each record is the template for
the next one. I currently use a macro that creates a new record based on the
current one, then puts the focus on the length control. I overtype the data
and then have to manually place the cursor at the end of the tag number
field, press backspace to erase the last digit, and type in the next digit.
Click on New Record and repeat. I'd like to modify it so it will replace
the current number in sequence in the tag number field, and *then* put the
focus on the length control - each time a new record is created. Would
reduce manual input by over 50%.
My apologies for the lengthy post,
John

 

[BMore via AccessMonster.com]

John H., if only 2 fields of a record change each time, it seems your
database is not properly normalized. The table should only contain these 2
changing fields and a foreingkey to the table containing the data that is not
changing.
After normalizing your table, you do not have the copy/paste problem at all.

Ben

 

[BruceM]

I agree with the other response that duplicating most of the fields suggests
a structure that is not well normalized. I don't know the details of the
situation, but it seems that you would want something like a Locations
table, a Survey table (assuming there are several surveys per location over
time, or something like that), and a FishData table.
Assigning the number is the same general procedure in any case. For
instance, assuming you have duplicated the entire record, you could have a
command button on the form with something like the following (untested air
code):
Me.TagNumber = Left(Me.TagNumber,2) &
Format(Val(Right(Me.TagNumber,5))+1,"00000")
Me.Length = ""
Me.Length.SetFocus

TagNumber and Length are the fields suggested by the names. This assumes
that you are not changing the prefix. I assume that if the prefix changes
you could just manually type the new prefix and number, and that subsequent
records would have the same prefix as the one you just changed. You may be
able to do:
If Me.NewRecord
{code here}
End If

and place it into the form's Current event, which should cause it to run
when you create a new record, but I'm not exactly sure how that would work
when you duplicate a record to create a new one.

Although it may seem like more work at the moment, using several tables as
suggested would almost surely help in the long run.

tblLocation
LocationID (primary key, or PK)
LocationName
Town, etc.

tblSurvey
SurveyID (PK)
LocationID (foreign key, or FK)
StartDate
EndDate
etc.

tblFishData
FishDataID (PK)
SurveyID (FK)
TagNumber
Length

Each PK is in a one-to-many relationship with its namesake FK in another
table (tblLocation and tblSurvey are related through LocationID, for
instance). This is set up in the Relationships window. There would be a
main form based on tblLocation, a subform based on tblSurvey, and a
continuous subform within the Survey subform for FishData. Something like
that.