Add & store Line number to subform

  • Thread starter Thread starter Russ via AccessMonster.com
  • Start date Start date
R

Russ via AccessMonster.com

How can I Add & store a Line number to subform?
I want to sore it so my form and report always match records.
Any easy ways of doing this?
 
This example shows how to automatically give the next available number at
the time you start entering the row in the subform. It assumes the main form
is for the CD album, and the subform for the tracks on the album. The tracks
need to be numbered with a TrackNum.

Private Sub Form_BeforeInsert(Cancel As Integer)
Dim strWhere As String

With Me.Parent
If .NewRecord Then
Cancel = True
MsgBox "Enter the main form record first."
Else
strWhere = "AlbumID = " & !AlbumID
Me.TrackNum = Nz(DMax("TrackID", "tblTrack", strWhere),0) + 1
End If
End Sub
End Sub
 
Allen,
Thanks that works!
I still have one problem, when I delete say tack 2 it leaves them out of sink.

Example, track1,2,3,4, then I delete 2. I get track 1, 3, 4
What can I do to automatically renumber so missing number.
I am trying to use it on my order details table, and sometimes they delete an
item from the order.
Any suggestions?
Thanks
Russ
 
Are you sure you want to renumber these for the user?

If 2 is missing, they will still sort correctly on the report. I think it's
valid to just provide the default value for a new record, and let the user
renumber them if they wish, even allowing duplicates so they can make the
renumbering easy.

If you really want to do it, it is possible, but there is much more to it
than just working through the records in the form. For example, the user may
have filtered the subform (so not all the records from the table are
currently showing), or changed the sort order in the form (e.g. by choosing
a different field, and then clicking the A-Z button on the toolbar.) This
means you have to renumber them in the original table, and then requery the
form so it gets to hear about the new numbers. Doing this in the events of
the form can have undesirable side effects.
 
Allen,
How hard would it be?
Can something be done like this;
If I have 8 line records and delete line 2 then -1 from all line nubers
larger then 2? Just a thought.
I would really like it to renumber the lines, I have a really picky client
and claims his work force makes a lot of data entry errors he likes
everything automated.


Allen said:
Are you sure you want to renumber these for the user?

If 2 is missing, they will still sort correctly on the report. I think it's
valid to just provide the default value for a new record, and let the user
renumber them if they wish, even allowing duplicates so they can make the
renumbering easy.

If you really want to do it, it is possible, but there is much more to it
than just working through the records in the form. For example, the user may
have filtered the subform (so not all the records from the table are
currently showing), or changed the sort order in the form (e.g. by choosing
a different field, and then clicking the A-Z button on the toolbar.) This
means you have to renumber them in the original table, and then requery the
form so it gets to hear about the new numbers. Doing this in the events of
the form can have undesirable side effects.
Allen,
Thanks that works!
Click to expand...
[quoted text clipped - 34 lines]
Click to expand...
 
If you are prepared to ignore the possibility that the subform might be
filtered, and to assume that if the user has reorder the records then she
wants them in that order, you could:
- Make sure the form is not dirty;
- Loop through the RecordsetClone of the form;
- Use the Edit method, assign the next number, and Update.

--
Allen Browne - Microsoft MVP. Perth, Western Australia.

Reply to group, rather than allenbrowne at mvps dot org.

Russ via AccessMonster.com said:
Allen,
How hard would it be?
Can something be done like this;
If I have 8 line records and delete line 2 then -1 from all line nubers
larger then 2? Just a thought.
I would really like it to renumber the lines, I have a really picky client
and claims his work force makes a lot of data entry errors he likes
everything automated.


Allen said:
Are you sure you want to renumber these for the user?

If 2 is missing, they will still sort correctly on the report. I think
it's
valid to just provide the default value for a new record, and let the user
renumber them if they wish, even allowing duplicates so they can make the
renumbering easy.

If you really want to do it, it is possible, but there is much more to it
than just working through the records in the form. For example, the user
may
have filtered the subform (so not all the records from the table are
currently showing), or changed the sort order in the form (e.g. by
choosing
a different field, and then clicking the A-Z button on the toolbar.) This
means you have to renumber them in the original table, and then requery
the
form so it gets to hear about the new numbers. Doing this in the events of
the form can have undesirable side effects.
Allen,
Thanks that works!
Click to expand...
[quoted text clipped - 34 lines]
I want to sore it so my form and report always match records.
Any easy ways of doing this?
Click to expand...
Click to expand...
Click to expand...
 
Allen,
What am I missing on the filtering part?
What can go wrong, do you have an example in mind?
Sorry, but I am confused...
Thanks for your help.

Allen said:
If you are prepared to ignore the possibility that the subform might be
filtered, and to assume that if the user has reorder the records then she
wants them in that order, you could:
- Make sure the form is not dirty;
- Loop through the RecordsetClone of the form;
- Use the Edit method, assign the next number, and Update.
Allen,
How hard would it be?
Click to expand...
[quoted text clipped - 30 lines]
Click to expand...
 
Using the CD example, user filters the subform so it only shows tracks by
Madonna, not all tracks on the album.

Now only some tracks are shown in the subform, so if you renumbered, you
would have an incomplete list, and potentially be duplicating other track
numbers that exist in the table for that album. Then when you print a report
for the tracks on the album, you would discover that your renumbering has
created duplicates, holes, ... more problems that it was supposed to fix.

--
Allen Browne - Microsoft MVP. Perth, Western Australia.

Reply to group, rather than allenbrowne at mvps dot org.

Russ via AccessMonster.com said:
Allen,
What am I missing on the filtering part?
What can go wrong, do you have an example in mind?
Sorry, but I am confused...
Thanks for your help.

Allen said:
If you are prepared to ignore the possibility that the subform might be
filtered, and to assume that if the user has reorder the records then she
wants them in that order, you could:
- Make sure the form is not dirty;
- Loop through the RecordsetClone of the form;
- Use the Edit method, assign the next number, and Update.
Allen,
How hard would it be?
Click to expand...
[quoted text clipped - 30 lines]
I want to sore it so my form and report always match records.
Any easy ways of doing this?
Click to expand...
Click to expand...
Click to expand...
 
Back
Top