sequential numbering that starts over

H

Heidi

I have a form [CATCH] with a subform [SAMPLE]. Each time I enter a new
catch, I need the sample number to start over at 1 and progress to 10 (we
always enter 10 samples for each catch). So, my SAMPLE table will look like
this:

AutoNum CatchID SampleNumber
1 1 1
2 1 2
3 1 3...
4 2 1
5 2 2
6 2 3...
 
H

Heidi

At the 10th sample, the user will either exit the program or enter another
catch. If they begin to enter another catch, there will be 10 samples to
enter along with that catch.

Klatuu said:
What you don't say is what you want to happen when you hit 10 samples.
--
Dave Hargis, Microsoft Access MVP


Heidi said:
I have a form [CATCH] with a subform [SAMPLE]. Each time I enter a new
catch, I need the sample number to start over at 1 and progress to 10 (we
always enter 10 samples for each catch). So, my SAMPLE table will look like
this:

AutoNum CatchID SampleNumber
1 1 1
2 1 2
3 1 3...
4 2 1
5 2 2
6 2 3...
Click to expand...
Click to expand...
 
K

Klatuu

How do you prevent them from putting in Sample 11 is the question.
--
Dave Hargis, Microsoft Access MVP


Heidi said:
At the 10th sample, the user will either exit the program or enter another
catch. If they begin to enter another catch, there will be 10 samples to
enter along with that catch.

Klatuu said:
What you don't say is what you want to happen when you hit 10 samples.
--
Dave Hargis, Microsoft Access MVP


Heidi said:
I have a form [CATCH] with a subform [SAMPLE]. Each time I enter a new
catch, I need the sample number to start over at 1 and progress to 10 (we
always enter 10 samples for each catch). So, my SAMPLE table will look like
this:

AutoNum CatchID SampleNumber
1 1 1
2 1 2
3 1 3...
4 2 1
5 2 2
6 2 3...
Click to expand...
Click to expand...
Click to expand...
 
H

Heidi

it could be a restriction on the table that prevents them from entering >10.
And I wanted this to automatically populate so there is not a chance of them
entering, lets say, "2" twice.

Klatuu said:
How do you prevent them from putting in Sample 11 is the question.
--
Dave Hargis, Microsoft Access MVP


Heidi said:
At the 10th sample, the user will either exit the program or enter another
catch. If they begin to enter another catch, there will be 10 samples to
enter along with that catch.

Klatuu said:
What you don't say is what you want to happen when you hit 10 samples.
--
Dave Hargis, Microsoft Access MVP


:

I have a form [CATCH] with a subform [SAMPLE]. Each time I enter a new
catch, I need the sample number to start over at 1 and progress to 10 (we
always enter 10 samples for each catch). So, my SAMPLE table will look like
this:

AutoNum CatchID SampleNumber
1 1 1
2 1 2
3 1 3...
4 2 1
5 2 2
6 2 3...
Click to expand...
Click to expand...
Click to expand...
 
A

Amy Blankenship

Heidi said:
I have a form [CATCH] with a subform [SAMPLE]. Each time I enter a new
catch, I need the sample number to start over at 1 and progress to 10 (we
always enter 10 samples for each catch). So, my SAMPLE table will look
like
this:

AutoNum CatchID SampleNumber
1 1 1
2 1 2
3 1 3...
4 2 1
5 2 2
6 2 3...
Click to expand...

If you have a form/subform structure where your main form is based on
CatchID, then you can put something like this in the Current event
procedure:

Me.SampleNumber.DefaultValue = Me.CurrentRecord

To limit it to 10 samples, you'll probably want to remove the default record
navigation controls and create your own that don't let the user add a new
record if the RecordCount on the recordset of the subform is >= 10.

HTH;

Amy
 
K

Klatuu

Okay, here is a way to do both. Use the Current event of the subform. Check
to see if the current record is a new record. If it is, get the highest
number. If the number is > 10, present a message box and undo the record.

Private Sub Form_Current()
Dim lngSampleNumber As Long
If Me.NewRecord Then
lngSampleNumber = NextSampleNumber
If lngSampleNumber > 10 Then
MsgBox "Only 10 Samples per Catch are Allowed", vbExclamation
Me.Undo
Else
'txtSampleNumber is made up. Use the name of your control on your form.
Me.txtSampleNumber = lngSampleNumber
End If
End If
End Sub

**************
Here is a function you can paste into your form module that finds the
highest sample number in the current catch.

Private Function NextSampleNumber() As Long
Dim lngHighSample As Long

With Forms!MyFormName!MySubformControlName.Form.RecordsetClone
.MoveFirst
lngHighSample = !SampleNumber
.MoveNext
Do While Not .EOF
If !SampleNumber > lngHighSample Then
lngHighSample = !SampleNumber
End If
.MoveNext
Loop
End With
NextSampleNumber = lngHighSample + 1
End Function
--
Dave Hargis, Microsoft Access MVP


Heidi said:
it could be a restriction on the table that prevents them from entering >10.
And I wanted this to automatically populate so there is not a chance of them
entering, lets say, "2" twice.

Klatuu said:
How do you prevent them from putting in Sample 11 is the question.
--
Dave Hargis, Microsoft Access MVP


Heidi said:
At the 10th sample, the user will either exit the program or enter another
catch. If they begin to enter another catch, there will be 10 samples to
enter along with that catch.

:

What you don't say is what you want to happen when you hit 10 samples.
--
Dave Hargis, Microsoft Access MVP


:

I have a form [CATCH] with a subform [SAMPLE]. Each time I enter a new
catch, I need the sample number to start over at 1 and progress to 10 (we
always enter 10 samples for each catch). So, my SAMPLE table will look like
this:

AutoNum CatchID SampleNumber
1 1 1
2 1 2
3 1 3...
4 2 1
5 2 2
6 2 3...
Click to expand...
Click to expand...
Click to expand...
 
H

Heidi

Dave,
I tried it and I got a run time error 438: Object does not support this
property or method. I don't know what I did - I plugged in my control name
where you specified. I have tried everything I could think of and googled
this all day. I give up! I'll have them type 1, 2, 3...

Thanks for your help. I notice you help lots of confused souls...

Amy - I tried yours too... didn't work but thanks anyway.


Heid


Klatuu said:
Okay, here is a way to do both. Use the Current event of the subform. Check
to see if the current record is a new record. If it is, get the highest
number. If the number is > 10, present a message box and undo the record.

Private Sub Form_Current()
Dim lngSampleNumber As Long
If Me.NewRecord Then
lngSampleNumber = NextSampleNumber
If lngSampleNumber > 10 Then
MsgBox "Only 10 Samples per Catch are Allowed", vbExclamation
Me.Undo
Else
'txtSampleNumber is made up. Use the name of your control on your form.
Me.txtSampleNumber = lngSampleNumber
End If
End If
End Sub

**************
Here is a function you can paste into your form module that finds the
highest sample number in the current catch.

Private Function NextSampleNumber() As Long
Dim lngHighSample As Long

With Forms!MyFormName!MySubformControlName.Form.RecordsetClone
.MoveFirst
lngHighSample = !SampleNumber
.MoveNext
Do While Not .EOF
If !SampleNumber > lngHighSample Then
lngHighSample = !SampleNumber
End If
.MoveNext
Loop
End With
NextSampleNumber = lngHighSample + 1
End Function
--
Dave Hargis, Microsoft Access MVP


Heidi said:
it could be a restriction on the table that prevents them from entering >10.
And I wanted this to automatically populate so there is not a chance of them
entering, lets say, "2" twice.

Klatuu said:
How do you prevent them from putting in Sample 11 is the question.
--
Dave Hargis, Microsoft Access MVP


:

At the 10th sample, the user will either exit the program or enter another
catch. If they begin to enter another catch, there will be 10 samples to
enter along with that catch.

:

What you don't say is what you want to happen when you hit 10 samples.
--
Dave Hargis, Microsoft Access MVP


:

I have a form [CATCH] with a subform [SAMPLE]. Each time I enter a new
catch, I need the sample number to start over at 1 and progress to 10 (we
always enter 10 samples for each catch). So, my SAMPLE table will look like
this:

AutoNum CatchID SampleNumber
1 1 1
2 1 2
3 1 3...
4 2 1
5 2 2
6 2 3...
Click to expand...
Click to expand...
Click to expand...
 
K

Klatuu

Heidi,
post back with your copy of the code.
In this case, I actually tested it on one of my applications and it worked.
Also, point out which line you get the error on, please.
--
Dave Hargis, Microsoft Access MVP


Heidi said:
Dave,
I tried it and I got a run time error 438: Object does not support this
property or method. I don't know what I did - I plugged in my control name
where you specified. I have tried everything I could think of and googled
this all day. I give up! I'll have them type 1, 2, 3...

Thanks for your help. I notice you help lots of confused souls...

Amy - I tried yours too... didn't work but thanks anyway.


Heid


Klatuu said:
Okay, here is a way to do both. Use the Current event of the subform. Check
to see if the current record is a new record. If it is, get the highest
number. If the number is > 10, present a message box and undo the record.

Private Sub Form_Current()
Dim lngSampleNumber As Long
If Me.NewRecord Then
lngSampleNumber = NextSampleNumber
If lngSampleNumber > 10 Then
MsgBox "Only 10 Samples per Catch are Allowed", vbExclamation
Me.Undo
Else
'txtSampleNumber is made up. Use the name of your control on your form.
Me.txtSampleNumber = lngSampleNumber
End If
End If
End Sub

**************
Here is a function you can paste into your form module that finds the
highest sample number in the current catch.

Private Function NextSampleNumber() As Long
Dim lngHighSample As Long

With Forms!MyFormName!MySubformControlName.Form.RecordsetClone
.MoveFirst
lngHighSample = !SampleNumber
.MoveNext
Do While Not .EOF
If !SampleNumber > lngHighSample Then
lngHighSample = !SampleNumber
End If
.MoveNext
Loop
End With
NextSampleNumber = lngHighSample + 1
End Function
--
Dave Hargis, Microsoft Access MVP


Heidi said:
it could be a restriction on the table that prevents them from entering >10.
And I wanted this to automatically populate so there is not a chance of them
entering, lets say, "2" twice.

:

How do you prevent them from putting in Sample 11 is the question.
--
Dave Hargis, Microsoft Access MVP


:

At the 10th sample, the user will either exit the program or enter another
catch. If they begin to enter another catch, there will be 10 samples to
enter along with that catch.

:

What you don't say is what you want to happen when you hit 10 samples.
--
Dave Hargis, Microsoft Access MVP


:

I have a form [CATCH] with a subform [SAMPLE]. Each time I enter a new
catch, I need the sample number to start over at 1 and progress to 10 (we
always enter 10 samples for each catch). So, my SAMPLE table will look like
this:

AutoNum CatchID SampleNumber
1 1 1
2 1 2
3 1 3...
4 2 1
5 2 2
6 2 3...
Click to expand...
Click to expand...
Click to expand...
 
A

Amy Blankenship

Heidi said:
Dave,
I tried it and I got a run time error 438: Object does not support this
property or method. I don't know what I did - I plugged in my control
name
where you specified. I have tried everything I could think of and googled
this all day. I give up! I'll have them type 1, 2, 3...

Thanks for your help. I notice you help lots of confused souls...

Amy - I tried yours too... didn't work but thanks anyway.
Click to expand...

What _did_ happen?
 
H

Heidi

Private Sub Form_Current()
Dim lngSampleNumber As Long
If Me.NewRecord Then
lngSampleNumber = NextSampleNumber
If lngSampleNumber > 10 Then
MsgBox "Only 10 Samples per Catch are Allowed", vbExclamation
Me.Undo
Else
'Below is where I typed my control name
Me.DMR_SAMPLE_IDENTIFIER = lngSampleNumber
End If
End If
End Sub

Private Function NextSampleNumber() As Long
Dim lngHighSample As Long

'Below is where I tpyed my form name and my control name. This is where the
error occurred... the first error said it couldn't find the form SAMPLE_SUB.
Because this is a subform, I thought I'd have to enter the names of all of my
forms, so I changed the code to the code at the very bottom ***
With Forms!SAMPLE_SUB!DMR_SAMPLE_IDENTIFIER.Form.RecordsetClone
.MoveFirst
lngHighSample = !SampleNumber
.MoveNext
Do While Not .EOF
If !SampleNumber > lngHighSample Then
lngHighSample = !SampleNumber
End If
.MoveNext
Loop
End With
NextSampleNumber = lngHighSample + 1
End Function

***so here is the new code I entered after it couldn't find SAMPLE_SUB. I
have three forms open: effort_sub, catch_sub and sample_sub... they are all
linked (one effort to many catches, one catch to many samples).

Private Function NextSampleNumber() As Long
Dim lngHighSample As Long

'Below is where I typed ALL my form names... and my control name and this is
where I got the run time error.
With Forms!EFFORT_SUB!CATCH_SUB!SAMPLE_SUB!
DMR_SAMPLE_IDENTIFIER.Form.RecordsetClone
.MoveFirst
lngHighSample = !SampleNumber
.MoveNext
Do While Not .EOF
If !SampleNumber > lngHighSample Then
lngHighSample = !SampleNumber
End If
.MoveNext
Loop
End With
NextSampleNumber = lngHighSample + 1
End Function

Klatuu said:
Heidi,
post back with your copy of the code.
In this case, I actually tested it on one of my applications and it worked.
Also, point out which line you get the error on, please.
--
Dave Hargis, Microsoft Access MVP


Heidi said:
Dave,
I tried it and I got a run time error 438: Object does not support this
property or method. I don't know what I did - I plugged in my control name
where you specified. I have tried everything I could think of and googled
this all day. I give up! I'll have them type 1, 2, 3...

Thanks for your help. I notice you help lots of confused souls...

Amy - I tried yours too... didn't work but thanks anyway.


Heid


Klatuu said:
Okay, here is a way to do both. Use the Current event of the subform. Check
to see if the current record is a new record. If it is, get the highest
number. If the number is > 10, present a message box and undo the record.

Private Sub Form_Current()
Dim lngSampleNumber As Long
If Me.NewRecord Then
lngSampleNumber = NextSampleNumber
If lngSampleNumber > 10 Then
MsgBox "Only 10 Samples per Catch are Allowed", vbExclamation
Me.Undo
Else
'txtSampleNumber is made up. Use the name of your control on your form.
Me.txtSampleNumber = lngSampleNumber
End If
End If
End Sub

**************
Here is a function you can paste into your form module that finds the
highest sample number in the current catch.

Private Function NextSampleNumber() As Long
Dim lngHighSample As Long

With Forms!MyFormName!MySubformControlName.Form.RecordsetClone
.MoveFirst
lngHighSample = !SampleNumber
.MoveNext
Do While Not .EOF
If !SampleNumber > lngHighSample Then
lngHighSample = !SampleNumber
End If
.MoveNext
Loop
End With
NextSampleNumber = lngHighSample + 1
End Function
--
Dave Hargis, Microsoft Access MVP


:

it could be a restriction on the table that prevents them from entering >10.
And I wanted this to automatically populate so there is not a chance of them
entering, lets say, "2" twice.

:

How do you prevent them from putting in Sample 11 is the question.
--
Dave Hargis, Microsoft Access MVP


:

At the 10th sample, the user will either exit the program or enter another
catch. If they begin to enter another catch, there will be 10 samples to
enter along with that catch.

:

What you don't say is what you want to happen when you hit 10 samples.
--
Dave Hargis, Microsoft Access MVP


:

I have a form [CATCH] with a subform [SAMPLE]. Each time I enter a new
catch, I need the sample number to start over at 1 and progress to 10 (we
always enter 10 samples for each catch). So, my SAMPLE table will look like
this:

AutoNum CatchID SampleNumber
1 1 1
2 1 2
3 1 3...
4 2 1
5 2 2
6 2 3...
Click to expand...
Click to expand...
Click to expand...
 
B

BruceM

You posted this same question in the forms group. Newsgroup courtesy is
that you cross post (one message to several groups) when necessary, but that
you do not multi-post (separate messages to several groups).
http://www.mvps.org/access/netiquette.htm


Heidi said:
Dave,
I tried it and I got a run time error 438: Object does not support this
property or method. I don't know what I did - I plugged in my control
name
where you specified. I have tried everything I could think of and googled
this all day. I give up! I'll have them type 1, 2, 3...

Thanks for your help. I notice you help lots of confused souls...

Amy - I tried yours too... didn't work but thanks anyway.


Heid


Klatuu said:
Okay, here is a way to do both. Use the Current event of the subform.
Check
to see if the current record is a new record. If it is, get the highest
number. If the number is > 10, present a message box and undo the
record.

Private Sub Form_Current()
Dim lngSampleNumber As Long
If Me.NewRecord Then
lngSampleNumber = NextSampleNumber
If lngSampleNumber > 10 Then
MsgBox "Only 10 Samples per Catch are Allowed", vbExclamation
Me.Undo
Else
'txtSampleNumber is made up. Use the name of your control on your form.
Me.txtSampleNumber = lngSampleNumber
End If
End If
End Sub

**************
Here is a function you can paste into your form module that finds the
highest sample number in the current catch.

Private Function NextSampleNumber() As Long
Dim lngHighSample As Long

With Forms!MyFormName!MySubformControlName.Form.RecordsetClone
.MoveFirst
lngHighSample = !SampleNumber
.MoveNext
Do While Not .EOF
If !SampleNumber > lngHighSample Then
lngHighSample = !SampleNumber
End If
.MoveNext
Loop
End With
NextSampleNumber = lngHighSample + 1
End Function
--
Dave Hargis, Microsoft Access MVP


Heidi said:
it could be a restriction on the table that prevents them from entering
10.
And I wanted this to automatically populate so there is not a chance of
them
entering, lets say, "2" twice.

:

How do you prevent them from putting in Sample 11 is the question.
--
Dave Hargis, Microsoft Access MVP


:

At the 10th sample, the user will either exit the program or enter
another
catch. If they begin to enter another catch, there will be 10
samples to
enter along with that catch.

:

What you don't say is what you want to happen when you hit 10
samples.
--
Dave Hargis, Microsoft Access MVP


:

I have a form [CATCH] with a subform [SAMPLE]. Each time I
enter a new
catch, I need the sample number to start over at 1 and progress
to 10 (we
always enter 10 samples for each catch). So, my SAMPLE table
will look like
this:

AutoNum CatchID SampleNumber
1 1 1
2 1 2
3 1 3...
4 2 1
5 2 2
6 2 3...
Click to expand...
Click to expand...
Click to expand...
 
H

Heidi

AH! I tried it again to reproduce the error and your code worked!!!

Thank you so much... I spent so much time on this, I'm so happy it worked.

Thanks again!!!
 
K

Klatuu

Okay, it is how you address subforms that is the problem. You don't use the
name of the form being used as the subform. You use the name of the subform
control followed by the word Form. Form in this context refers to the form
property of the subform control. In the subform control properties, it is
called the Source Object property. So a fully qualified subform control name
is:
Forms!MainFormName!SubformControlName.Form!NameOfControlOnSubform.

Now when you go two layers down, it would be:
Forms!Form1!SubCtl1.Form!SubCtl2.Form!ControlName.


But, you can also go in the other direction. If you want to refer to a
control on parent form, it is:

Me.Parent.ControlName

But in this case, the problem is you are not refering the the correct subform.
The NextSampleNumber function should be in the form module with the the
samples data, so Me.RecordsetClone is correct.
--
Dave Hargis, Microsoft Access MVP


Heidi said:
Private Sub Form_Current()
Dim lngSampleNumber As Long
If Me.NewRecord Then
lngSampleNumber = NextSampleNumber
If lngSampleNumber > 10 Then
MsgBox "Only 10 Samples per Catch are Allowed", vbExclamation
Me.Undo
Else
'Below is where I typed my control name
Me.DMR_SAMPLE_IDENTIFIER = lngSampleNumber
End If
End If
End Sub

Private Function NextSampleNumber() As Long
Dim lngHighSample As Long

'Below is where I tpyed my form name and my control name. This is where the
error occurred... the first error said it couldn't find the form SAMPLE_SUB.
Because this is a subform, I thought I'd have to enter the names of all of my
forms, so I changed the code to the code at the very bottom ***
With Forms!SAMPLE_SUB!DMR_SAMPLE_IDENTIFIER.Form.RecordsetClone
.MoveFirst
lngHighSample = !SampleNumber
.MoveNext
Do While Not .EOF
If !SampleNumber > lngHighSample Then
lngHighSample = !SampleNumber
End If
.MoveNext
Loop
End With
NextSampleNumber = lngHighSample + 1
End Function

***so here is the new code I entered after it couldn't find SAMPLE_SUB. I
have three forms open: effort_sub, catch_sub and sample_sub... they are all
linked (one effort to many catches, one catch to many samples).

Private Function NextSampleNumber() As Long
Dim lngHighSample As Long

'Below is where I typed ALL my form names... and my control name and this is
where I got the run time error.
With Forms!EFFORT_SUB!CATCH_SUB!SAMPLE_SUB!
DMR_SAMPLE_IDENTIFIER.Form.RecordsetClone
.MoveFirst
lngHighSample = !SampleNumber
.MoveNext
Do While Not .EOF
If !SampleNumber > lngHighSample Then
lngHighSample = !SampleNumber
End If
.MoveNext
Loop
End With
NextSampleNumber = lngHighSample + 1
End Function

Klatuu said:
Heidi,
post back with your copy of the code.
In this case, I actually tested it on one of my applications and it worked.
Also, point out which line you get the error on, please.
--
Dave Hargis, Microsoft Access MVP


Heidi said:
Dave,
I tried it and I got a run time error 438: Object does not support this
property or method. I don't know what I did - I plugged in my control name
where you specified. I have tried everything I could think of and googled
this all day. I give up! I'll have them type 1, 2, 3...

Thanks for your help. I notice you help lots of confused souls...

Amy - I tried yours too... didn't work but thanks anyway.


Heid


:

Okay, here is a way to do both. Use the Current event of the subform. Check
to see if the current record is a new record. If it is, get the highest
number. If the number is > 10, present a message box and undo the record.

Private Sub Form_Current()
Dim lngSampleNumber As Long
If Me.NewRecord Then
lngSampleNumber = NextSampleNumber
If lngSampleNumber > 10 Then
MsgBox "Only 10 Samples per Catch are Allowed", vbExclamation
Me.Undo
Else
'txtSampleNumber is made up. Use the name of your control on your form.
Me.txtSampleNumber = lngSampleNumber
End If
End If
End Sub

**************
Here is a function you can paste into your form module that finds the
highest sample number in the current catch.

Private Function NextSampleNumber() As Long
Dim lngHighSample As Long

With Forms!MyFormName!MySubformControlName.Form.RecordsetClone
.MoveFirst
lngHighSample = !SampleNumber
.MoveNext
Do While Not .EOF
If !SampleNumber > lngHighSample Then
lngHighSample = !SampleNumber
End If
.MoveNext
Loop
End With
NextSampleNumber = lngHighSample + 1
End Function
--
Dave Hargis, Microsoft Access MVP


:

it could be a restriction on the table that prevents them from entering >10.
And I wanted this to automatically populate so there is not a chance of them
entering, lets say, "2" twice.

:

How do you prevent them from putting in Sample 11 is the question.
--
Dave Hargis, Microsoft Access MVP


:

At the 10th sample, the user will either exit the program or enter another
catch. If they begin to enter another catch, there will be 10 samples to
enter along with that catch.

:

What you don't say is what you want to happen when you hit 10 samples.
--
Dave Hargis, Microsoft Access MVP


:

I have a form [CATCH] with a subform [SAMPLE]. Each time I enter a new
catch, I need the sample number to start over at 1 and progress to 10 (we
always enter 10 samples for each catch). So, my SAMPLE table will look like
this:

AutoNum CatchID SampleNumber
1 1 1
2 1 2
3 1 3...
4 2 1
5 2 2
6 2 3...
Click to expand...
Click to expand...
Click to expand...
 
A

Amy Blankenship

Heidi said:
AH! I tried it again to reproduce the error and your code worked!!!

Thank you so much... I spent so much time on this, I'm so happy it worked.

Thanks again!!!
Click to expand...

You're welcome ;-)
 
H

Heidi

Sorry-
I didn't know how to cross post and the other group didn't answer my
question. I'll know for next time.
Heidi

BruceM said:
You posted this same question in the forms group. Newsgroup courtesy is
that you cross post (one message to several groups) when necessary, but that
you do not multi-post (separate messages to several groups).
http://www.mvps.org/access/netiquette.htm


Heidi said:
Dave,
I tried it and I got a run time error 438: Object does not support this
property or method. I don't know what I did - I plugged in my control
name
where you specified. I have tried everything I could think of and googled
this all day. I give up! I'll have them type 1, 2, 3...

Thanks for your help. I notice you help lots of confused souls...

Amy - I tried yours too... didn't work but thanks anyway.


Heid


Klatuu said:
Okay, here is a way to do both. Use the Current event of the subform.
Check
to see if the current record is a new record. If it is, get the highest
number. If the number is > 10, present a message box and undo the
record.

Private Sub Form_Current()
Dim lngSampleNumber As Long
If Me.NewRecord Then
lngSampleNumber = NextSampleNumber
If lngSampleNumber > 10 Then
MsgBox "Only 10 Samples per Catch are Allowed", vbExclamation
Me.Undo
Else
'txtSampleNumber is made up. Use the name of your control on your form.
Me.txtSampleNumber = lngSampleNumber
End If
End If
End Sub

**************
Here is a function you can paste into your form module that finds the
highest sample number in the current catch.

Private Function NextSampleNumber() As Long
Dim lngHighSample As Long

With Forms!MyFormName!MySubformControlName.Form.RecordsetClone
.MoveFirst
lngHighSample = !SampleNumber
.MoveNext
Do While Not .EOF
If !SampleNumber > lngHighSample Then
lngHighSample = !SampleNumber
End If
.MoveNext
Loop
End With
NextSampleNumber = lngHighSample + 1
End Function
--
Dave Hargis, Microsoft Access MVP


:

it could be a restriction on the table that prevents them from entering
10.
And I wanted this to automatically populate so there is not a chance of
them
entering, lets say, "2" twice.

:

How do you prevent them from putting in Sample 11 is the question.
--
Dave Hargis, Microsoft Access MVP


:

At the 10th sample, the user will either exit the program or enter
another
catch. If they begin to enter another catch, there will be 10
samples to
enter along with that catch.

:

What you don't say is what you want to happen when you hit 10
samples.
--
Dave Hargis, Microsoft Access MVP


:

I have a form [CATCH] with a subform [SAMPLE]. Each time I
enter a new
catch, I need the sample number to start over at 1 and progress
to 10 (we
always enter 10 samples for each catch). So, my SAMPLE table
will look like
this:

AutoNum CatchID SampleNumber
1 1 1
2 1 2
3 1 3...
4 2 1
5 2 2
6 2 3...
Click to expand...
Click to expand...
Click to expand...
 
B

BruceM

Cross-post by adding another group name to the To line. In general you
should allow at least 24 hours (48 would be better) before concluding that
there will be no reply. If you do post again you should mention that you
are trying again because you did not receive a reply the first time.
No big deal; just pointing out some things.

Heidi said:
Sorry-
I didn't know how to cross post and the other group didn't answer my
question. I'll know for next time.
Heidi

BruceM said:
You posted this same question in the forms group. Newsgroup courtesy is
that you cross post (one message to several groups) when necessary, but
that
you do not multi-post (separate messages to several groups).
http://www.mvps.org/access/netiquette.htm


Heidi said:
Dave,
I tried it and I got a run time error 438: Object does not support this
property or method. I don't know what I did - I plugged in my control
name
where you specified. I have tried everything I could think of and
googled
this all day. I give up! I'll have them type 1, 2, 3...

Thanks for your help. I notice you help lots of confused souls...

Amy - I tried yours too... didn't work but thanks anyway.


Heid


:

Okay, here is a way to do both. Use the Current event of the subform.
Check
to see if the current record is a new record. If it is, get the
highest
number. If the number is > 10, present a message box and undo the
record.

Private Sub Form_Current()
Dim lngSampleNumber As Long
If Me.NewRecord Then
lngSampleNumber = NextSampleNumber
If lngSampleNumber > 10 Then
MsgBox "Only 10 Samples per Catch are Allowed",
vbExclamation
Me.Undo
Else
'txtSampleNumber is made up. Use the name of your control on your
form.
Me.txtSampleNumber = lngSampleNumber
End If
End If
End Sub

**************
Here is a function you can paste into your form module that finds the
highest sample number in the current catch.

Private Function NextSampleNumber() As Long
Dim lngHighSample As Long

With Forms!MyFormName!MySubformControlName.Form.RecordsetClone
.MoveFirst
lngHighSample = !SampleNumber
.MoveNext
Do While Not .EOF
If !SampleNumber > lngHighSample Then
lngHighSample = !SampleNumber
End If
.MoveNext
Loop
End With
NextSampleNumber = lngHighSample + 1
End Function
--
Dave Hargis, Microsoft Access MVP


:

it could be a restriction on the table that prevents them from
entering
10.
And I wanted this to automatically populate so there is not a chance
of
them
entering, lets say, "2" twice.

:

How do you prevent them from putting in Sample 11 is the question.
--
Dave Hargis, Microsoft Access MVP


:

At the 10th sample, the user will either exit the program or
enter
another
catch. If they begin to enter another catch, there will be 10
samples to
enter along with that catch.

:

What you don't say is what you want to happen when you hit 10
samples.
--
Dave Hargis, Microsoft Access MVP


:

I have a form [CATCH] with a subform [SAMPLE]. Each time I
enter a new
catch, I need the sample number to start over at 1 and
progress
to 10 (we
always enter 10 samples for each catch). So, my SAMPLE
table
will look like
this:

AutoNum CatchID SampleNumber
1 1 1
2 1 2
3 1 3...
4 2 1
5 2 2
6 2 3...
Click to expand...
Click to expand...
Click to expand...
 
H

Heidi

Thanks Bruce. Will do. Thanks for the heads-up.

BruceM said:
Cross-post by adding another group name to the To line. In general you
should allow at least 24 hours (48 would be better) before concluding that
there will be no reply. If you do post again you should mention that you
are trying again because you did not receive a reply the first time.
No big deal; just pointing out some things.

Heidi said:
Sorry-
I didn't know how to cross post and the other group didn't answer my
question. I'll know for next time.
Heidi

BruceM said:
You posted this same question in the forms group. Newsgroup courtesy is
that you cross post (one message to several groups) when necessary, but
that
you do not multi-post (separate messages to several groups).
http://www.mvps.org/access/netiquette.htm


Dave,
I tried it and I got a run time error 438: Object does not support this
property or method. I don't know what I did - I plugged in my control
name
where you specified. I have tried everything I could think of and
googled
this all day. I give up! I'll have them type 1, 2, 3...

Thanks for your help. I notice you help lots of confused souls...

Amy - I tried yours too... didn't work but thanks anyway.


Heid


:

Okay, here is a way to do both. Use the Current event of the subform.
Check
to see if the current record is a new record. If it is, get the
highest
number. If the number is > 10, present a message box and undo the
record.

Private Sub Form_Current()
Dim lngSampleNumber As Long
If Me.NewRecord Then
lngSampleNumber = NextSampleNumber
If lngSampleNumber > 10 Then
MsgBox "Only 10 Samples per Catch are Allowed",
vbExclamation
Me.Undo
Else
'txtSampleNumber is made up. Use the name of your control on your
form.
Me.txtSampleNumber = lngSampleNumber
End If
End If
End Sub

**************
Here is a function you can paste into your form module that finds the
highest sample number in the current catch.

Private Function NextSampleNumber() As Long
Dim lngHighSample As Long

With Forms!MyFormName!MySubformControlName.Form.RecordsetClone
.MoveFirst
lngHighSample = !SampleNumber
.MoveNext
Do While Not .EOF
If !SampleNumber > lngHighSample Then
lngHighSample = !SampleNumber
End If
.MoveNext
Loop
End With
NextSampleNumber = lngHighSample + 1
End Function
--
Dave Hargis, Microsoft Access MVP


:

it could be a restriction on the table that prevents them from
entering
10.
And I wanted this to automatically populate so there is not a chance
of
them
entering, lets say, "2" twice.

:

How do you prevent them from putting in Sample 11 is the question.
--
Dave Hargis, Microsoft Access MVP


:

At the 10th sample, the user will either exit the program or
enter
another
catch. If they begin to enter another catch, there will be 10
samples to
enter along with that catch.

:

What you don't say is what you want to happen when you hit 10
samples.
--
Dave Hargis, Microsoft Access MVP


:

I have a form [CATCH] with a subform [SAMPLE]. Each time I
enter a new
catch, I need the sample number to start over at 1 and
progress
to 10 (we
always enter 10 samples for each catch). So, my SAMPLE
table
will look like
this:

AutoNum CatchID SampleNumber
1 1 1
2 1 2
3 1 3...
4 2 1
5 2 2
6 2 3...
Click to expand...
Click to expand...
Click to expand...
 

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

Similar Threads

Excel Numbering rows by font color, but skipping blanks 1
Continuous form / sequential numbering 1
sequential number 5
Sequential Numbering 2
Require help on slight twist to auto-incrementing a number 7
Query problem - used in form 1
Sequential Numbering 2
Start sequential number over every September 8

Top