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.

[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.RecordsetClo ne
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.ActiveContro l.Name
'do what ever causes the cursor to move
Me.Recordset.Fi ndFirst "[IDField]=" & lngID
Me.Controls(str Control).SetFoc us

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

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]

> Hi !

Ho! ;-)

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
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.previous control'because user clicks
button<-focus, don't use activecontrol
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.
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.recordsetclo ne
.findfirst yourprimarykeye xpression 'can help you as soon as I know the
field(s)
me.bookmark = .bookmark
end with
5) set focus to the last field.
focuscontrol.se tfocus ' 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.recordsetclo ne
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.Previous Control 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_Cl ick, 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_Cl ick() 'Code Snippet Courtesy of Wayne
Morgan
Dim rst As DAO.Recordset, lngNumber As Long
Set rst = Me.RecordsetClo ne

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.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

Screen.Previous Control.SetFocu s
End Sub


Private Sub Cmd_Delete_Clic k()
On Error GoTo Err_Cmd_Delete_ Click

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")

Screen.Previous Control.SetFocu s
Call Cmd_ReNumber_Cl ick '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.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


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_Cl ick()
Set rst = Me.RecordsetClo ne
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_Cl ick() 'Code Snippet Courtesy of Wayne
Morgan
Dim rst As DAO.Recordset, lngNumber As Long
Set rst = Me.RecordsetClo ne

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