Is it possible to...

  • Thread starter Thread starter SF
  • Start date Start date
S

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
 
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
Click to expand...

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.
 
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."
 
It work. Thank you for your prompt response.

SF

John W. Vinson said:
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
Click to expand...

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.
Click to expand...
 
Back
Top