sequential number

H

Heidi

Form 1 has [Catch_Number] as it's primary key, linked to form 2 with
[Sample_Number] as it's primary key. When a new catch is entered and the
user starts entering samples for that catch, I want the field
[Sample_Identifier] to sequentially number from 1 to 10. There are always 10
samples per catch, so that number needs to start over at 1 when a new catch
is entered and the user begins entering samples for that new catch. This
seems like it would be basic, but I can't figure it out. Please help.
 
S

scubadiver

Use this as the text box default value.

Nz(DMax("Sample_Identifier", "tblname")) + 1
 
H

Heidi

For some reason, it is numbering but not sequentially... it assigns the
numbers like this: 2,2,3,3,4,4...

What did I do wrong?

scubadiver said:
Use this as the text box default value.

Nz(DMax("Sample_Identifier", "tblname")) + 1

Heidi said:
Form 1 has [Catch_Number] as it's primary key, linked to form 2 with
[Sample_Number] as it's primary key. When a new catch is entered and the
user starts entering samples for that catch, I want the field
[Sample_Identifier] to sequentially number from 1 to 10. There are always 10
samples per catch, so that number needs to start over at 1 when a new catch
is entered and the user begins entering samples for that new catch. This
seems like it would be basic, but I can't figure it out. Please help.
 
H

Heidi

And when I keep entering records, it never starts over at 1... it keeps
assigning them based on how many records are in the table, regardless of what
catch I am on... it needs to start over at 1 with each catch...

Heidi said:
For some reason, it is numbering but not sequentially... it assigns the
numbers like this: 2,2,3,3,4,4...

What did I do wrong?

scubadiver said:
Use this as the text box default value.

Nz(DMax("Sample_Identifier", "tblname")) + 1

Heidi said:
Form 1 has [Catch_Number] as it's primary key, linked to form 2 with
[Sample_Number] as it's primary key. When a new catch is entered and the
user starts entering samples for that catch, I want the field
[Sample_Identifier] to sequentially number from 1 to 10. There are always 10
samples per catch, so that number needs to start over at 1 when a new catch
is entered and the user begins entering samples for that new catch. This
seems like it would be basic, but I can't figure it out. Please help.
 
S

scubadiver

In the form's 'before insert' event try this


Me!fieldname = DMax("[fieldname]", "[tablename]") + 1
Me.Dirty = False


You will have to type '1' into the first record for it to work. I am not
sure how to re-start the numbering on a new batch number. As you have pointed
out it will only work on the highest number in the table

Heidi said:
For some reason, it is numbering but not sequentially... it assigns the
numbers like this: 2,2,3,3,4,4...

What did I do wrong?

scubadiver said:
Use this as the text box default value.

Nz(DMax("Sample_Identifier", "tblname")) + 1

Heidi said:
Form 1 has [Catch_Number] as it's primary key, linked to form 2 with
[Sample_Number] as it's primary key. When a new catch is entered and the
user starts entering samples for that catch, I want the field
[Sample_Identifier] to sequentially number from 1 to 10. There are always 10
samples per catch, so that number needs to start over at 1 when a new catch
is entered and the user begins entering samples for that new catch. This
seems like it would be basic, but I can't figure it out. Please help.
 
B

BruceM

It seems you have tables I will name tblCatch and tblSample (forms do not
have primary keys; rather, their underlying tables do), and that these table
are related one-to-many. If this is not how it is set up, it probably
should be. I am making up the field names in some cases.

tblCatch
Catch_Number (PK)
CatchDate
etc.

tblSample
Sample_Number (PK)
Catch_Number (foreign key, or FK)
Sample_Identifier (number field)
SampleDescription

There is a one-to-many relationship between the Catch_Number fields. Set
this up in Tools >> Relationships. I will assume you are clear on how to do
this, but ask if you are uncertain.

There needs to be a main form (frmCatch) based on tblCatch, and a subform
(fsubSample) based on tblSample. Set set the default view for fsubSample to
Continuous.

One way to do this is to create the forms separately. Open frmCatch in
design view, and use the toolbox to draw a subform control onto the form.
Set the subform control's Source Object to fsubSample. Click the Link Child
Fields line, then click the three dots to the right. Access will probably
suggest Catch_Number. Accept the suggestion.

You should now have a main form for Catch, and a subform for the ten Sample
records. Be sure this works correctly (although there is no incrementing
number yet).

Once this works, use a version of the formula scubadiver suggested in the
Default Value of the text box that is bound to the Sample_Identifier:

=Nz(DMax("Sample_Identifier","tblSample","[Catch_Number] = " &
Forms![frmCatch]![CatchNumber]),0) + 1

Note that this is all on one line.

Another way of doing this is to use the Current event for frmSample:

If Me.NewRecord Then
Me.CatchNumber = Nz(DMax("Sample_Identifier", _
"tblSample","[Catch_Number] = " & _
Me.Parent.Catch_Number),0) + 1
End If

Because of the relationship between the tables, each Sample record in
tblSample includes Catch_Number corresponding to Catch_Number in the related
tblCatch (the parent table). The code is telling Access to find the highest
number in tblSample in which Catch_Number is the same as Catch_Number in the
main record, and to add one to that number. If there are no records, start
with a 0, and add one to it.

Having said all of that, I wonder about the value of storing the numbers.
You can number the records in a report quite easily, or you can create a
query that will assign numbers to the records.

An important point to such a numbering system is that is there are to be
multiple users it will have to allow for the possiblity of two users
entering Sample information for the same Catch at the same time.

Heidi said:
For some reason, it is numbering but not sequentially... it assigns the
numbers like this: 2,2,3,3,4,4...

What did I do wrong?

scubadiver said:
Use this as the text box default value.

Nz(DMax("Sample_Identifier", "tblname")) + 1

Heidi said:
Form 1 has [Catch_Number] as it's primary key, linked to form 2 with
[Sample_Number] as it's primary key. When a new catch is entered and
the
user starts entering samples for that catch, I want the field
[Sample_Identifier] to sequentially number from 1 to 10. There are
always 10
samples per catch, so that number needs to start over at 1 when a new
catch
is entered and the user begins entering samples for that new catch.
This
seems like it would be basic, but I can't figure it out. Please help.
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top