Sequentially numbering a field !

[Prakash]

Hi !

I have a field "sub_tran_n o" 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_n o" 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]

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.ech o false and Application.ech o.true & the
dance stopped.

BTW, is Application.ech o 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.ech o 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.Ech o should both do
the same thing. Application.Ech o 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.*********@he uvelqop.nl> wrote in message
news:da******** **@localhost.lo caldomain...

BTW, is Application.ech o 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.Cont rolTipText = Nz(Me.DETAILS)
'MsgBox "Current Record Number: " & Me.CurrentRecor d
'LbL_Tran_No.Ca ption = "Transactio n 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.Previous Control.SetFocu s
End If

If Me.Recordset.Re cordCount = 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_Clic k()
On Error GoTo Err_Cmd_Delete_ Click

Dim stn As Byte, strControl As String
strControl = Screen.Previous Control.Name
'MsgBox strControl
Application.Ech o False

DoCmd.SetWarnin gs False
DoCmd.DoMenuIte m acFormBar, acEditMenu, 8, , acMenuVer70
DoCmd.DoMenuIte m acFormBar, acEditMenu, 6, , acMenuVer70
DoCmd.SetWarnin gs 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_Cl ick 'Re-Number the sub_tran_no field if any
record is DELETED

Me.Recordset.Fi ndFirst "[sub_tran_no]=" & stn
If Me.Recordset.No Match Then
Me.Recordset.Mo veLast
End If
Me.Controls(str Control).SetFoc us
Application.Ech o 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(str Control).SetFoc us
Application.Ech o True
Else
MsgBox Err.Description & " " & Err.Number
End If
Resume Exit_Cmd_Delete _Click

End Sub


Private Sub Cmd_ReNumber_Cl ick() '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.RecordsetClo ne.Requery
' MsgBox "RecordsetC lone 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.Re cordCount = 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.Ech o 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.Ech o 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 "disconnect ed" 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.B ookmark

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

Thx Wayne ! The moment I used Application.Ech o 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.Ech o 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.