Sequentially numbering a field !

[Prakash]

Hi !

I have a field "sub_tran_no" in my form in continuous view.

When the user presses a button "Re-Number", I'd like to:

1) Save the current record pointer position
2) Save the current field which has focus
3) go to the top of the recordset & start re-numbering
the "sub_tran_no" field with the values 1,2,3, until eof.
4) go back to the previous record number
5) set focus to the last field.
6) Refresh the form to display the results

Should I do this using Recordset or RecordsetClone.

Could anyone please help with a little Air-Code ?

Thx & Best Rgds,
Prakash.

[Wayne Morgan]

You say sub_tran_no is a field. I just want to verify that it is a field and
that you have a bound textbox, not that it is just an unbound textbox. If
you do the work with the recordset clone, the form should never move,
therefore there should be nothing to save. If you requery the form, you will
lose the Bookmark and the cursor will return to the first record. If a
requery is necessary, you'll need to remember the value of the unique ID
field for the current record and then do a search back to that record.

Dim rst As DAO.Recordset, lngNumber As Long
Set rst = Me.RecordsetClone
If Not (rst.BOF And rst.EOF) Then
With rst
.MoveFirst
lngNumber = 0
Do Until .EOF
lngNumber = lngNumber + 1
.Edit
![FieldName] = lngNumber
.Update
.MoveNext
Loop
.Close
End With
End If
Set rst = Nothing
Me.Refresh

Refresh will get the new values for records that are already in the form's
recordset. It won't get new records that may have been added by someone else
if you have a multi-user database. Of course, if another user has added
records and you requery to get those records, you're numbers aren't going to
be sequential any longer.

Should you need to remember where you're at and move back to it:

Dim lngID As Long, strControl As String
lngID = Me.txtIDField
strControl = Me.ActiveControl.Name
'do what ever causes the cursor to move
Me.Recordset.FindFirst "[IDField]=" & lngID
Me.Controls(strControl).SetFocus

--
Wayne Morgan
MS Access MVP
"Prakash" <si****@omantel.net.om> wrote in message
news:11**********************@g44g2000cwa.googlegr oups.com...

Hi !

I have a field "sub_tran_no" in my form in continuous view.

When the user presses a button "Re-Number", I'd like to:

1) Save the current record pointer position
2) Save the current field which has focus
3) go to the top of the recordset & start re-numbering
the "sub_tran_no" field with the values 1,2,3, until eof.
4) go back to the previous record number
5) set focus to the last field.
6) Refresh the form to display the results

Should I do this using Recordset or RecordsetClone.

Could anyone please help with a little Air-Code ?

Thx & Best Rgds,
Prakash.

[Bas Cost Budde]

> Hi !

Ho! ;-)

I have a field "sub_tran_no" in my form in continuous view.

When the user presses a button "Re-Number", I'd like to:

1) Save the current record pointer position
What is the primary key of the table? Store its value(s) in (a) variable(s).
2) Save the current field which has focus
dim focuscontrol as control
set focuscontrol = screen.previouscontrol'because user clicks
button<-focus, don't use activecontrol
3) go to the top of the recordset & start re-numbering
the "sub_tran_no" field with the values 1,2,3, until eof.
What do you mean by "top of the recordset"? What order do you envision?

If you create sub_tran_no in code, which I sincerely hope you do, what
could be the reason to renumber? Ah, user deletes transactions (to
overcome your 255 limit) and tries to add new ones. That fails because
you've put 254 in the code. How about testing for the number of
transaction records, then, instead of testing the transaction number?
(pun intended)
4) go back to the previous record number
with me.recordsetclone
.findfirst yourprimarykeyexpression 'can help you as soon as I know the
field(s)
me.bookmark = .bookmark
end with
5) set focus to the last field.
focuscontrol.setfocus ' we've set that above
6) Refresh the form to display the results
Er, that's the wrong way around. It would be part of step 3: me.requery
Should I do this using Recordset or RecordsetClone.

I would have used a recordset, but I can't see a real reason not to use
the recordsetclone. Let's see if the Help sheds some light on this...
why, no, recordsetclone is even advised as the way to go if you want to
perform operations on the records in the form.

What is the order of the records? That is the question. I suppose for
now that the records per .recordsource are in the required order, and
that you want to renumber just to close gaps in the sequence. There must
be a mathematical term for that, which I don't know in English.

dim i as integer
i=0 ' starting value
with me.recordsetclone
do until .eof
!sub_tran_no = i
i=i+1
.movenext
loop
end with

might just work.

--
Bas Cost Budde, Holland
http://www.heuveltop.nl/BasCB/msac_index.html
For human replies, replace the queue with a tea

[Wayne Morgan]

Bas is correct about ActiveControl, use Screen.PreviousControl instead.

--
Wayne Morgan
MS Access MVP

[Prakash]

Thx Bas !

If you create sub_tran_no in code, which I sincerely hope you do, what
could be the reason to renumber? Ah, user deletes transactions (to
overcome your 255 limit) and tries to add new ones. That fails because
you've put 254 in the code. How about testing for the number of
transaction records, then, instead of testing the transaction number?
(pun intended)
Yes ... you are right ! In case the user deletes any transactions I'd
like to sequentially re-number them *just* prior to posting & saving
the entry. The active index here in the form is my field sub_tran_no
.... so a GOP TOP would position me at the lowest number & then I just
wanna filter out any gaps due to user deletions (again, as correctly
figured out by you). This would occur as my field sub_tran_no is
automatically incremented & disabled on the form (the user has no
access to this field).

How about testing for the number of
transaction records, then, instead of testing the transaction number?
(pun intended)

My field sub_tran_no is of type "Byte" & hence the 254 limit
restriction. Due to user row deletions the Count of records in the
table can be less than my sub_tran_no value. But honestly, even in a
worst case scenario, I cannot imagine any user entering more than 25
records into this table at a time. Then the user hits the "POST"
button, the transaction gets posted & all records in this table are
deleted. This is a sorta temp table.
One small question:
----------------------------
Wayne has used: .Edit & .Update which you have not used in your
loop.
I was just perusing the access help & it seems Edit & Update are
required.
Could you please confirm this ?
Thx again for the "hand-holding".

Best Rgds,
Prakash.

[Prakash]

Muchos Gracias Wayne !

Very lucidly explained !

I'll give it a shot & get back to you when it's done.
Best Rgds,
Prakash.

[Bas Cost Budde]

> Yes ... you are right ! In case the user deletes any transactions I'd

like to sequentially re-number them *just* prior to posting & saving
the entry.
You could do a background renumber on every Delete (there is an event
for that).
My field sub_tran_no is of type "Byte" & hence the 254 limit
restriction.
As it is a temp table, I guess it is under your control, so it doesn't
have to be Byte. Upon 'posting' you can do a type cast since at that
time you have no more than 255 records, as we've ascertained.
Due to user row deletions the Count of records in the
table can be less than my sub_tran_no value. But honestly, even in a
worst case scenario, I cannot imagine any user entering more than 25
records into this table at a time.
<shrug> even when the scenario is not normal, it is good to have solid
code. You're right in examining this issue.
One small question:
----------------------------
Wayne has used: .Edit & .Update which you have not used in your
loop.
I was just perusing the access help & it seems Edit & Update are
required.
Could you please confirm this ?

Certainly! That's what air code is air code for, right? :-)
--
Bas Cost Budde, Holland
http://www.heuveltop.nl/BasCB/msac_index.html
For human replies, replace the queue with a tea

[Prakash]

>You could do a background renumber on every Delete (there is an event

for that).

Thx Bas ! To be honest it had occurred to me along the way, but I
wanted to bullet-proof the renumbering snippet via a manual button on
the form before tying it into my delete event. I have done that finally
& it sems to work ... just 1 small glitch.

The Glitch:
---------------
If I delete a few or single records the re-numbering works fine ... but
if I delete all records, then the renumbering does not occur. On close
examination I found in the
Sub Cmd_ReNumber_Click, the recordsetclone id sort of disconnected from
the recordset. After deleting ALL records, now even if i add several
records, the condition (rst.BOF And rst.EOF) tests TRUE which leads me
to believe the recordset is getting populated whereas the
recordsetclone has been disconnected from the original recordset. Is
there any way of tying them back together again ?

My code is posted below.

Private Sub Cmd_ReNumber_Click() 'Code Snippet Courtesy of Wayne
Morgan
Dim rst As DAO.Recordset, lngNumber As Long
Set rst = Me.RecordsetClone

MsgBox rst.BOF & " " & rst.EOF
If Not (rst.BOF And rst.EOF) Then
With rst
.MoveFirst
lngNumber = 0
Do Until .EOF
lngNumber = lngNumber + 1
.Edit
![Sub_Tran_No] = lngNumber
.Update
.MoveNext
Loop
.Close
End With
End If
Set rst = Nothing

If Me.Recordset.RecordCount = 0 Then
Me.Sub_Tran_No.DefaultValue = 1
Else
Me.Sub_Tran_No.DefaultValue = DMax("sub_tran_no", "JVTable") +
1
End If

Me.Refresh

Screen.PreviousControl.SetFocus
End Sub


Private Sub Cmd_Delete_Click()
On Error GoTo Err_Cmd_Delete_Click

DoCmd.SetWarnings False
DoCmd.DoMenuItem acFormBar, acEditMenu, 8, , acMenuVer70
DoCmd.DoMenuItem acFormBar, acEditMenu, 6, , acMenuVer70
DoCmd.SetWarnings True

Me.DrTotal = DSum("DEBIT", "JVTable") ' After deleting a Record
Totals shd be re-calculated
Me.CrTotal = DSum("CREDIT", "JVTable")

Screen.PreviousControl.SetFocus
Call Cmd_ReNumber_Click 'Re-Number the sub_tran_no field if any
record is DELETED

Exit_Cmd_Delete_Click:
Exit Sub

Err_Cmd_Delete_Click:
MsgBox Err.Description
Resume Exit_Cmd_Delete_Click

End Sub

Private Sub Form_Current()
Dim msg, msg1, msg2, msg3 As String

If Me.NewRecord = True And Me.Sub_Tran_No >= 254 Then
'MsgBox "New Record !"
msg1 = "MAXIMUM ROWS/TRANSACTIONS ALLOWED ARE 255 !" & Chr(13)
& Chr(13)
msg2 = "Please Delete Some Rows !"
MsgBox msg1 & msg2, vbOKOnly + vbCritical, "Error !"
SendKeys "^{PGUP}", True
Screen.PreviousControl.SetFocus
End If

If Me.Recordset.RecordCount = 0 Then
'MsgBox "Empty Table !"
Me.Sub_Tran_No.DefaultValue = 1
Else
Me.Sub_Tran_No.DefaultValue = DMax("sub_tran_no", "JVTable") +
1
End If

End Sub


Wayne, thank you for the code.

Best Rgds,
Prakash.

[Bas Cost Budde]

> If I delete a few or single records the re-numbering works fine ... but

if I delete all records, then the renumbering does not occur.
You mean, afterwards? After deleting all records, and inserting a few,
recordsetclone stays empty.
Private Sub Cmd_ReNumber_Click()
Set rst = Me.RecordsetClone
With rst
.Close

Hm. You don't Open the recordset anywhere, so I feel you shouldn't close
it either. That could be just it. Otherwise, do a Requery just after you
saved the current record.
--
Bas Cost Budde, Holland
http://www.heuveltop.nl/BasCB/msac_index.html
For human replies, replace the queue with a tea

[Prakash]

Bas I tried "not closing" the recordsetclone + a requery. It still
does'nt work :-(

May I Re-Iterate the problem ...
Say I have 10 records in this continuous form. I position the record
pointer on the 1st record and keep pressing the delete button until
there are no records left. sub_tran_no gets updated correctly.

At this point I test & ... MsgBox rst.BOF & " " & rst.EOF return
True & True respectively.

Now I start adding records (say another 10). Yet after every record,
BOF & EOF return True and sub_tran_no does not get updated. Just beats
me.

The problem lies with BOF & EOF ALWAYS returning True for the
recordsetclone (if all the records are deleted). Please note, that at
any point if I close the form & reopen it ... everything works fine
once again.

Which leads me to believe that when the recordsetclone gets emptied, it
sort of gets disconnected from the recordset & requires the form to be
closed & reopened to once again get synched.
Private Sub Cmd_ReNumber_Click() 'Code Snippet Courtesy of Wayne
Morgan
Dim rst As DAO.Recordset, lngNumber As Long
Set rst = Me.RecordsetClone

MsgBox rst.BOF & " " & rst.EOF

If Not (rst.BOF And rst.EOF) Then

... this part won't run as once all records are deleted (BOF and
EOF) = true

[Bas Cost Budde]

What did you requery? The rst variable (pointing to the recordsetclone)?
Or the form?

--
Bas Cost Budde, Holland
http://www.heuveltop.nl/BasCB/msac_index.html
For human replies, replace the queue with a tea

[Prakash]

I've tried every possible permutation & combination. ReQuerying the
Form, the RecordsetClone, even the Recordset, I've even tried SETting
the RecrdsetClone to the Recordset object & received some errors ...
basically everything conceiveable ... but to no avail.

The same problem persists: If I delete ALL records from the
RecordsetClone, it loses it's sync with the RecordSet & needs the form
to be closed & re-opened to be re-synched.

Any more ideas ... I'm really desperate now ...

-----------------------------------------------------------------------

Now here's something else ... instead of operating on RecordsetClone, I
tried RecordSet & now it works perfectly albeit I'd have preferred
RecordSetClone as the record pointer seems to "dance around" a lot with
the RecordSet.

There's another problem here now though: I'm losing my row position as
I delete records. I tried bookmark, but when I keep deleting rows I
eventually get the error msg: "No Current Record".

Any ideas ?
I'd really like to thank you sincerely for all the time & effort you've
put ino this.

Thx & Bst Rgds,
Prakash.

[Prakash]

OK !! I've FINALLY managed to get it working using RECORDSET (not
recordsetclone).

1 teeny-weeny irritance however:
The Record Pointer keeps dancing up & down in my continuous form (never
used to happen with recordsetclone). Is there any way to avoid this ?

Thx & Best Rgds,
Prakash

[Prakash]

Sorry for posting in a hurry.

I did an Application.echo false and Application.echo.true & the
dance stopped.

BTW, is Application.echo better or DoCmd.Echo & why ?

Also, any caveats to using either ?

Bas ... a big thanks to you & Wayne once again.
(Would you like me to post my final code here) ?

Rgds,
Prakash.

[Bas Cost Budde]

Dancing up and down... No idea. What event does this happen in? What is
the code for the corresponding event handler? If all else fails,
however, one trick I apply sparingly is docmd.echo false--offending
statements--docmd.echo true. Don't forget to include an error handler
that does docmd.echo true as well, in that case.

With Access, my directions have almost always been: use this object; if
it misbehaves, use another. So you've found that trail too. :-)

--
Bas Cost Budde, Holland
http://www.heuveltop.nl/BasCB/msac_index.html
For human replies, replace the queue with a tea

[Bas Cost Budde]

> BTW, is Application.echo better or DoCmd.Echo & why ?

Dunno. I'm still used to docmd, I guess.

Also, any caveats to using either ?

The general idea of trapping error and setting Echo on again. You have
no clue as to what happens/happened when an error occurs during echo
off. Can you type blindly? I learned :-)
--
Bas Cost Budde, Holland
http://www.heuveltop.nl/BasCB/msac_index.html
For human replies, replace the queue with a tea

[Wayne Morgan]

To the best of my knowledge, DoCmd.Echo and Application.Echo should both do
the same thing. Application.Echo is the newer command; therefore,
theoretically better.

The problem mentioned with the error trapping is why I include an Echo On in
my error handler whenever I use Echo Off in the code. That way if an error
occurs, I can get to the application to work on it.

--
Wayne Morgan
MS Access MVP
"Bas Cost Budde" <b.*********@heuvelqop.nl> wrote in message
news:da**********@localhost.localdomain...

BTW, is Application.echo better or DoCmd.Echo & why ?

Dunno. I'm still used to docmd, I guess.

Also, any caveats to using either ?

The general idea of trapping error and setting Echo on again. You have no
clue as to what happens/happened when an error occurs during echo off. Can
you type blindly? I learned :-)
--
Bas Cost Budde, Holland
http://www.heuveltop.nl/BasCB/msac_index.html
For human replies, replace the queue with a tea

[Prakash]

Here's the Final Working Code:

Private Sub Form_Current()
Dim msg As String, msg1 As String, msg2 As String, msg3 As String

Me.DETAILS.ControlTipText = Nz(Me.DETAILS)
'MsgBox "Current Record Number: " & Me.CurrentRecord
'LbL_Tran_No.Caption = "Transaction No: " & DMax("Tran_No",
"DPATDAT") + 1

If Me.NewRecord = True And Me.Sub_Tran_No >= 254 Then
'MsgBox "New Record !"
msg1 = "MAXIMUM ROWS/TRANSACTIONS ALLOWED ARE 255 !" & Chr(13)
& Chr(13)
msg2 = "Please Delete Some Rows !"
MsgBox msg1 & msg2, vbOKOnly + vbCritical, "Error !"
SendKeys "^{PGUP}", True
Screen.PreviousControl.SetFocus
End If

If Me.Recordset.RecordCount = 0 Then
'MsgBox "Empty Table !"
Me.Sub_Tran_No.DefaultValue = 1
Else
Me.Sub_Tran_No.DefaultValue = DMax("sub_tran_no", "JVTable") +
1
End If

End Sub


Private Sub Cmd_Delete_Click()
On Error GoTo Err_Cmd_Delete_Click

Dim stn As Byte, strControl As String
strControl = Screen.PreviousControl.Name
'MsgBox strControl
Application.Echo False

DoCmd.SetWarnings False
DoCmd.DoMenuItem acFormBar, acEditMenu, 8, , acMenuVer70
DoCmd.DoMenuItem acFormBar, acEditMenu, 6, , acMenuVer70
DoCmd.SetWarnings True

Me.DrTotal = DSum("DEBIT", "JVTable") ' After deleting a Record
Totals shd be re-calculated
Me.CrTotal = DSum("CREDIT", "JVTable")

stn = Sub_Tran_No - 1 'Saving Current Record Pointer Position
Call Cmd_ReNumber_Click 'Re-Number the sub_tran_no field if any
record is DELETED

Me.Recordset.FindFirst "[sub_tran_no]=" & stn
If Me.Recordset.NoMatch Then
Me.Recordset.MoveLast
End If
Me.Controls(strControl).SetFocus
Application.Echo True

Exit_Cmd_Delete_Click:
Exit Sub

Err_Cmd_Delete_Click:
If Err.Number = 3021 Then 'user tries to delete an EOF or BOF
record
'MsgBox "No More Records To Delete !", vbOKOnly +
vbInformation, "Message..."
Me.Controls(strControl).SetFocus
Application.Echo True
Else
MsgBox Err.Description & " " & Err.Number
End If
Resume Exit_Cmd_Delete_Click

End Sub


Private Sub Cmd_ReNumber_Click() 'Code Snippet Courtesy of Wayne
Morgan
Dim rst As DAO.Recordset, lngNumber As Long
Set rst = Me.Recordset 'Clone
'MsgBox rst.BOF & " " & rst.EOF

'If (rst.BOF And rst.EOF) Then
' 'Set rst = Me.Recordset
' rst.Requery
' Me.RecordsetClone.Requery
' MsgBox "RecordsetClone is EMPTY ! REQUERYING !!"
'End If
If Not (rst.BOF And rst.EOF) Then
With rst
.MoveFirst
lngNumber = 0
Do Until .EOF
lngNumber = lngNumber + 1
.Edit
![Sub_Tran_No] = lngNumber
.Update
.MoveNext
Loop
'.Close
End With
End If
'Set rst = Nothing

If Me.Recordset.RecordCount = 0 Then
Me.Sub_Tran_No.DefaultValue = 1
Else
Me.Sub_Tran_No.DefaultValue = DMax("sub_tran_no", "JVTable") +
1
End If

'................................................. ....................................

Me.Refresh

'MsgBox "All Transactions Re-Numbered !"
End Sub
**** End of Post ****

[Prakash]

Thx Wayne ! The moment I used Application.Echo there was an error & my
screen went blank in a real weird manner. I had to somehow shut down
Access; Re-Start the program & realized immediately the necessity of
adding Application.Echo on in my error handler.

So when Bas mentioned it, I smiled ... (I'd already learned the "hard"
way).

Thx for the warning too Wayne. While we're at it, do you know of any
way to sync the RecordsetCLone with the Recordset once it gets
disconnected ? I'm just curious.

Also just rcvd an email request from Sabine Oebbecke ... he's requested
if I could post my code. I'll do that right away. It'd be great if
anyone else could also benefit from this.

Best Rgds,
Prakash.

[Wayne Morgan]

The only way I know of to "sync" the clone with the recordset once it has
become "disconnected" is to open a new clone. If they are "in sync" you can
move them both to the same record by setting the Bookmark of one equal to
the Bookmark of the other.

CloneRecordset.Bookmark = RealRecordset.Bookmark

--
Wayne Morgan
MS Access MVP
"Prakash" <si****@omantel.net.om> wrote in message
news:11**********************@o13g2000cwo.googlegr oups.com...

Thx Wayne ! The moment I used Application.Echo there was an error & my
screen went blank in a real weird manner. I had to somehow shut down
Access; Re-Start the program & realized immediately the necessity of
adding Application.Echo on in my error handler.

So when Bas mentioned it, I smiled ... (I'd already learned the "hard"
way).

Thx for the warning too Wayne. While we're at it, do you know of any
way to sync the RecordsetCLone with the Recordset once it gets
disconnected ? I'm just curious.

Also just rcvd an email request from Sabine Oebbecke ... he's requested
if I could post my code. I'll do that right away. It'd be great if
anyone else could also benefit from this.

Best Rgds,
Prakash.

[Prakash]

Thx Wayne ... I'll give it a shot sometime.

Rgds,
Prakash.