Is it possible to...

[SF]

Hi,

I have a main form with a subform, both link using ID. I had another field
to store sequentail number for each particular record in the main form (eg a
record of main for would have 5 sub record with a sequential listing of
1,2,3 ,4 and 5)

Right now, user type the sequestial number in the field. I am looking for an
automate fill in of the requetial number. Would it be possible here.

SF

 

[John W. Vinson]

Hi,

I have a main form with a subform, both link using ID. I had another field
to store sequentail number for each particular record in the main form (eg a
record of main for would have 5 sub record with a sequential listing of
1,2,3 ,4 and 5)

Right now, user type the sequestial number in the field. I am looking for an
automate fill in of the requetial number. Would it be possible here.

SF

Sure. Let's say you have a field named SEQ in the subform's recordsource, and
a field named ID which is the Child Link Field connecting to the mainform. In
the subform's BeforeInsert event you can use code like

Private Sub Form_BeforeInsert(Cancel as Integer)
Dim iNext As Integer
iNext = NZ(DMax("[Seq]", "[childtablename]", "[ID] = " & Me!ID))+1
' Use the following if you want to *limit* the subform to five or fewer rows
If iNext > 5 Then
Cancel = True
MsgBox "Only five records allowed", vbOKOnly
Else
Me!Seq = iNext
End If
End Sub

Note that this will not create five "placeholder" records - it's neither
necessary nor prudent to do so, it will create the sequence numbers as data is
added to the other fields.

 

[Al Campagna]

SF,
What do you use that number for?
How does the user jnow what number to enter?
A bit more information, might make a difference in the response...
but...

You could use an autonumber...

Or, you can make your own psuedo-autonumber.
Make the Default Value for your field (ex. [SeqNo])...
= NZ(DMax("[SeqNo]","tblYourTable),0) + 1
Every New record will increment SeqNo by one.
--
hth
Al Campagna
Microsoft Access MVP
http://home.comcast.net/~cccsolutions/index.html

"Find a job that you love... and you'll never work a day in your life."

 

[SF]

It work. Thank you for your prompt response.

SF

Hi,

I have a main form with a subform, both link using ID. I had another field
to store sequentail number for each particular record in the main form (eg
a
record of main for would have 5 sub record with a sequential listing of
1,2,3 ,4 and 5)

Right now, user type the sequestial number in the field. I am looking for
an
automate fill in of the requetial number. Would it be possible here.

SF

Sure. Let's say you have a field named SEQ in the subform's recordsource,
and
a field named ID which is the Child Link Field connecting to the mainform.
In
the subform's BeforeInsert event you can use code like

Private Sub Form_BeforeInsert(Cancel as Integer)
Dim iNext As Integer
iNext = NZ(DMax("[Seq]", "[childtablename]", "[ID] = " & Me!ID))+1
' Use the following if you want to *limit* the subform to five or fewer
rows
If iNext > 5 Then
Cancel = True
MsgBox "Only five records allowed", vbOKOnly
Else
Me!Seq = iNext
End If
End Sub

Note that this will not create five "placeholder" records - it's neither
necessary nor prudent to do so, it will create the sequence numbers as
data is
added to the other fields.