billcute
asked on
Renumbering Question
I have a Routine - "RenumberFDID" that is reusable by simply passing in two prefixes
such that it will renumber both based on the form's recordset.
However, I noted that the "RenumberFDID" is not working as expected.
For example, when user make changes to the FDID in tblMain, the routine is expected to renumber the "group series
in tblMain. This by my experience is not working.
For example:
If I have these series of numbers in 2 groups in tblMain.
SID FDID
1 C-W-TA-134-12-CM-01
4 C-W-TA-134-12-CM-02
5 C-W-TA-134-12-CM-03
2 P-W-PL-201-111-DM-01
3 P-W-PL-201-111-DM-02
Note: "SID" is an Autonumber it does not change.
"FDID" is a text type and it changes after re-ordering for reporting purposes.
' *********
If I change:
SID FDID
1 C-W-TA-134-12-CM-01
to:
SID FDID
1 P-W-PL-201-111-DM- '<=== Then after re-ordering the two groups in tblMain will become:
' ********
SID FDID
4 C-W-TA-134-12-CM-01
5 C-W-TA-134-12-CM-02
1 P-W-PL-201-111-DM-01
2 P-W-PL-201-111-DM-02
3 P-W-PL-201-111-DM-03
Note that re-numbering occurs in both groups
I have enclosed a sample db here as visual aid.
http://www.geocities.com/bombastikbill/Renumbering_db.zip
I have also placed the the "RenumberFDID" function in the code snippet for assistance.
To test my sample simply do the following when frmMain opens.
When SID#1 opens, make changes to the controls as follows:
a). Select "Parks Dept" from the combo
b). Select "Ply" from the next combo
c). Select "201" from the next combo
d). Type in "111" and..
e). Select "Dome" in that last combo
This should put in the scenerio described above and at the point the "RenumberFDID" function is expected to renumber the last two digits as shown above. This is why I posted this question because the routine is not working.
such that it will renumber both based on the form's recordset.
However, I noted that the "RenumberFDID" is not working as expected.
For example, when user make changes to the FDID in tblMain, the routine is expected to renumber the "group series
in tblMain. This by my experience is not working.
For example:
If I have these series of numbers in 2 groups in tblMain.
SID FDID
1 C-W-TA-134-12-CM-01
4 C-W-TA-134-12-CM-02
5 C-W-TA-134-12-CM-03
2 P-W-PL-201-111-DM-01
3 P-W-PL-201-111-DM-02
Note: "SID" is an Autonumber it does not change.
"FDID" is a text type and it changes after re-ordering for reporting purposes.
' *********
If I change:
SID FDID
1 C-W-TA-134-12-CM-01
to:
SID FDID
1 P-W-PL-201-111-DM- '<=== Then after re-ordering the two groups in tblMain will become:
' ********
SID FDID
4 C-W-TA-134-12-CM-01
5 C-W-TA-134-12-CM-02
1 P-W-PL-201-111-DM-01
2 P-W-PL-201-111-DM-02
3 P-W-PL-201-111-DM-03
Note that re-numbering occurs in both groups
I have enclosed a sample db here as visual aid.
http://www.geocities.com/bombastikbill/Renumbering_db.zip
I have also placed the the "RenumberFDID" function in the code snippet for assistance.
To test my sample simply do the following when frmMain opens.
When SID#1 opens, make changes to the controls as follows:
a). Select "Parks Dept" from the combo
b). Select "Ply" from the next combo
c). Select "201" from the next combo
d). Type in "111" and..
e). Select "Dome" in that last combo
This should put in the scenerio described above and at the point the "RenumberFDID" function is expected to renumber the last two digits as shown above. This is why I posted this question because the routine is not working.
Public Sub RenumberFDID(ByVal sOldPrefix As String, ByVal sNewPrefix As String, ByVal sTag As String)
Dim rs As DAO.Recordset
Dim iOldPrefix As Integer 'Count for renumbering original prefix
Dim iNewPrefix As Integer 'Count for renumbering new prefix
Dim sSql As String
Dim sFormat As String
'Track no change
' If sOldPrefix & sTag = sNewPrefix & sTag Then Exit Sub
DoCmd.Hourglass True
'Initialise
Set rs = Me.RecordsetClone
iNewPrefix = 0
iOldPrefix = 0
sFormat = "00"
'Filter on records that begin with the old and new letter
sSql = "FDID LIKE '" & sNewPrefix & sTag & "*' OR FDID LIKE '" & sOldPrefix & sTag & "*'"
Debug.Print "Initiating Search", sSql
rs.Filter = sSql
rs.MoveFirst
'Now loop through updating existing records
Do While rs.EOF = False
Debug.Print "Found ID=" & rs!SID, "FDID=" & rs!FDID
If Left$(rs!FDID, 2) = sOldPrefix & sTag Or Left$(rs!FDID, 2) = sNewPrefix & sTag Then
rs.Edit
If Left$(rs!FDID, 2) = sOldPrefix & sTag Then
iOldPrefix = iOldPrefix + 1
Debug.Print "Updating Record to " & sOldPrefix & sTag & Format(iOldPrefix, sFormat)
rs!FDID = sOldPrefix & sTag & Format(iOldPrefix, sFormat)
Else
iNewPrefix = iNewPrefix + 1
Debug.Print "Updating Record to " & sNewPrefix & sTag & Format(iNewPrefix, sFormat)
rs!FDID = sNewPrefix & sTag & Format(iNewPrefix, sFormat)
End If
rs.Update
End If
rs.MoveNext
Loop
'Closedown
rs.Close
Set rs = Nothing
Me.Refresh
DoCmd.Hourglass False
End Sub
Bill, if you are going to use a prefix like this
C-W-TA-134-12-CM
based from looking at the code in the afterupdate event
sNewPrefix = Left$(Me.cboTest.Column(0)
then there is no reason to specify a tag
Do you really want to specify strCorePinNo ? I guess you do in case you have different pin numbers but starting with the same two letters
C-W-TA-134-12-CM
based from looking at the code in the afterupdate event
sNewPrefix = Left$(Me.cboTest.Column(0)
then there is no reason to specify a tag
Do you really want to specify strCorePinNo ? I guess you do in case you have different pin numbers but starting with the same two letters
When I added strCorePinNo to the old prefix as well (to make it consistent) then removed the 3rd argument I got these results
Code in afterupdate event
'Old prefix is current prefix
sOldPrefix = Left$(Me.txtFDID, 1) & strCorePinNo
'New prefix is letter from drop down plus pin number
sNewPrefix = Left$(Me.cboTest.Column(0)
Previous question, we had only two letters to deal with so the Renumber code had a fixed length of 2. Now it is the length of the prefix passed in
Code in afterupdate event
'Old prefix is current prefix
sOldPrefix = Left$(Me.txtFDID, 1) & strCorePinNo
'New prefix is letter from drop down plus pin number
sNewPrefix = Left$(Me.cboTest.Column(0)
Previous question, we had only two letters to deal with so the Renumber code had a fixed length of 2. Now it is the length of the prefix passed in
Public Sub RenumberFDID(ByVal sOldPrefix As String, ByVal sNewPrefix As String)
Dim rs As DAO.Recordset
Dim iOldPrefix As Integer 'Count for renumbering original prefix
Dim iNewPrefix As Integer 'Count for renumbering new prefix
Dim sSql As String
Dim sFormat As String
'Track no change
' If sOldPrefix = sNewPrefix Then Exit Sub
DoCmd.Hourglass True
'Initialise
Set rs = Me.RecordsetClone
iNewPrefix = 0
iOldPrefix = 0
sFormat = "00"
'Filter on records that begin with the old and new letter
sSql = "FDID LIKE '" & sNewPrefix & "*' OR FDID LIKE '" & sOldPrefix & "*'"
Debug.Print "Initiating Search", sSql
rs.Filter = sSql
rs.MoveFirst
'Now loop through updating existing records
Do While rs.EOF = False
Debug.Print "Found ID=" & rs!SID, "FDID=" & rs!FDID
If Left$(rs!FDID, Len(sOldPrefix)) = sOldPrefix Or Left$(rs!FDID, Len(sNewPrefix)) = sNewPrefix Then
rs.Edit
If Left$(rs!FDID, Len(sOldPrefix)) = sOldPrefix Then
iOldPrefix = iOldPrefix + 1
Debug.Print "Updating Record to " & sOldPrefix & Format(iOldPrefix, sFormat)
rs!FDID = sOldPrefix & Format(iOldPrefix, sFormat)
Else
iNewPrefix = iNewPrefix + 1
Debug.Print "Updating Record to " & sNewPrefix & Format(iNewPrefix, sFormat)
rs!FDID = sNewPrefix & Format(iNewPrefix, sFormat)
End If
rs.Update
End If
rs.MoveNext
Loop
'Closedown
rs.Close
Set rs = Nothing
Me.Refresh
DoCmd.Hourglass False
End Sub
I see you have commented this out
If sOldPrefix = sNewPrefix Then Exit Sub
I think you will need it to save renumbering unnecessarily
If sOldPrefix = sNewPrefix Then Exit Sub
I think you will need it to save renumbering unnecessarily
Your form tag W, S or P is built into the prefix checking so no need to pass anymore
Does this make sense?
Instead of two letter checking, we are checking everything bar the last 3 digit number, during this renumbering process.
Does this make sense?
Instead of two letter checking, we are checking everything bar the last 3 digit number, during this renumbering process.
ASKER
A call to RenumberFDID sOldPrefix, sNewPrefix, "00" from the
the Afterupdate code is given me an error.....
compile error:
"Wrong number of arguments or invalid property assisgnements
at...
'Call renumbering function
RenumberFDID sOldPrefix, sNewPrefix, "00" '<==== Call to re-numbering
How do I fix this?
Regards
Bill
the Afterupdate code is given me an error.....
compile error:
"Wrong number of arguments or invalid property assisgnements
at...
'Call renumbering function
RenumberFDID sOldPrefix, sNewPrefix, "00" '<==== Call to re-numbering
How do I fix this?
Regards
Bill
it only takes 2 arguments now. Drop the "00"
ASKER
rocki:
I dropped the comma and the "00" yet the routine did not change the counter from "00" to "01".
For example:
If I change:
SID FDID
1 C-W-TA-134-12-CM-01
to:
SID FDID
1 P-W-PL-201-111-DM- '<=== Then after re-ordering the two groups in tblMain will become:
' ********
SID FDID
4 C-W-TA-134-12-CM-01
5 C-W-TA-134-12-CM-02
1 P-W-PL-201-111-DM-01 '<========= New member in this series
2 P-W-PL-201-111-DM-02
3 P-W-PL-201-111-DM-03
Note that re-numbering occurs in both groups
In my test, I started from:
SID FDID
1 C-W-TA-134-12-CM-01
and changed it to:
SID FDID
1 P-W-PL-201-111-DM-
After re-ordering the last two digit counter did not change as expected instaed I got:
SID FDID
1 P-W-PL-201-111-DM-00 '<==== This is wrong
I dropped the comma and the "00" yet the routine did not change the counter from "00" to "01".
For example:
If I change:
SID FDID
1 C-W-TA-134-12-CM-01
to:
SID FDID
1 P-W-PL-201-111-DM- '<=== Then after re-ordering the two groups in tblMain will become:
' ********
SID FDID
4 C-W-TA-134-12-CM-01
5 C-W-TA-134-12-CM-02
1 P-W-PL-201-111-DM-01 '<========= New member in this series
2 P-W-PL-201-111-DM-02
3 P-W-PL-201-111-DM-03
Note that re-numbering occurs in both groups
In my test, I started from:
SID FDID
1 C-W-TA-134-12-CM-01
and changed it to:
SID FDID
1 P-W-PL-201-111-DM-
After re-ordering the last two digit counter did not change as expected instaed I got:
SID FDID
1 P-W-PL-201-111-DM-00 '<==== This is wrong
did u make all the changes in the afterupdate event?
I was getting your error when testing until I realised you had put in the corenumber in the newprefix but not the old
'Old prefix is current prefix
sOldPrefix = Left$(Me.txtFDID, 1) & strCorePinNo
'New prefix is letter from drop down plus pin number
sNewPrefix = Left$(Me.cboTest.Column(0)
I was getting your error when testing until I realised you had put in the corenumber in the newprefix but not the old
'Old prefix is current prefix
sOldPrefix = Left$(Me.txtFDID, 1) & strCorePinNo
'New prefix is letter from drop down plus pin number
sNewPrefix = Left$(Me.cboTest.Column(0)
ASKER
rocki:
SID "1" before changes were made to it was: C-W-TA-134-12-CM-01
After re-ordering it as described in my original question (ID: 24360459 and ID: 24262976)
it changed to: "P-W-PL-201-111-DM-00" instead of "P-W-PL-201-111-DM-01"
All corrections to the code were made as per your suggestion.... I am sure there is still a glitch somewhere.
Here is the debug print from the immediate window:
Initiating Search FDID LIKE 'P-W-TA-134-12-CM-*' OR FDID LIKE 'C-W-TA-134-12-CM-*'
Found ID=1 FDID=P-W-TA-134-12-CM-00
Updating Record to P-W-TA-134-12-CM-01
Found ID=2 FDID=P-W-PL-201-111-DM-01
Found ID=3 FDID=P-W-PL-201-111-DM-02
Found ID=4 FDID=C-W-TA-134-12-CM-02
Updating Record to C-W-TA-134-12-CM-01
Found ID=5 FDID=C-W-TA-134-12-CM-03
Updating Record to C-W-TA-134-12-CM-02
IMPORTANT NOTE:
As you can see from the debug print....the third member in the "P-W-PL-201-111-DM-03" was missing
from the debug print that confirmed a flaw somewhere.
Regards
Bill
SID "1" before changes were made to it was: C-W-TA-134-12-CM-01
After re-ordering it as described in my original question (ID: 24360459 and ID: 24262976)
it changed to: "P-W-PL-201-111-DM-00" instead of "P-W-PL-201-111-DM-01"
All corrections to the code were made as per your suggestion.... I am sure there is still a glitch somewhere.
Here is the debug print from the immediate window:
Initiating Search FDID LIKE 'P-W-TA-134-12-CM-*' OR FDID LIKE 'C-W-TA-134-12-CM-*'
Found ID=1 FDID=P-W-TA-134-12-CM-00
Updating Record to P-W-TA-134-12-CM-01
Found ID=2 FDID=P-W-PL-201-111-DM-01
Found ID=3 FDID=P-W-PL-201-111-DM-02
Found ID=4 FDID=C-W-TA-134-12-CM-02
Updating Record to C-W-TA-134-12-CM-01
Found ID=5 FDID=C-W-TA-134-12-CM-03
Updating Record to C-W-TA-134-12-CM-02
IMPORTANT NOTE:
As you can see from the debug print....the third member in the "P-W-PL-201-111-DM-03" was missing
from the debug print that confirmed a flaw somewhere.
Regards
Bill
I cannot reproduce your error as I am not getting 00
After following your instructions from your initial post, I ended up with
FDID
P-W-PL-201-111-DM-01
P-W-PL-201-111-DM-02
P-W-PL-201-111-DM-03
C-W-TA-134-12-CM-01
C-W-TA-134-12-CM-02
After following your instructions from your initial post, I ended up with
FDID
P-W-PL-201-111-DM-01
P-W-PL-201-111-DM-02
P-W-PL-201-111-DM-03
C-W-TA-134-12-CM-01
C-W-TA-134-12-CM-02
Please confirm you have updated sOldPrefix to include strCorePinNo in the AllReorder code (called via AfterUpdate)
from
sOldPrefix = Left$(Me.txtFDID, 1)
to
sOldPrefix = Left$(Me.txtFDID, 1) & strCorePinNo
from
sOldPrefix = Left$(Me.txtFDID, 1)
to
sOldPrefix = Left$(Me.txtFDID, 1) & strCorePinNo
As a test I went to the next record SID 2
Change to Connect Dept and results are now this, which is what I expected
FDID
P-W-PL-201-111-DM-01
C-W-PL-201-111-DM-01
P-W-PL-201-111-DM-02
C-W-TA-134-12-CM-01
C-W-TA-134-12-CM-02
Change to Connect Dept and results are now this, which is what I expected
FDID
P-W-PL-201-111-DM-01
C-W-PL-201-111-DM-01
P-W-PL-201-111-DM-02
C-W-TA-134-12-CM-01
C-W-TA-134-12-CM-02
ASKER
rocki:
I dont know where I went wrong. But I have attached my working sample here for your review so you could correct what I may be doing wrong.
http://www.geocities.com/bombastikbill/Renumbering_db_v2a.zip
Regards
Bill
I dont know where I went wrong. But I have attached my working sample here for your review so you could correct what I may be doing wrong.
http://www.geocities.com/bombastikbill/Renumbering_db_v2a.zip
Regards
Bill
Ok, Ive looked at it again. My original code was based on what you did initially.
With checking just the first letter and tag, we didnt need to do the renumber if they was the same. Apparently now though with a bigger string, it seems that check is redundant.
Can you remove the 2 lines please (yes I know I said you needed it before, but that was in a different data format)
'Track no change
If sOldPrefix = sNewPrefix Then Exit Sub
With checking just the first letter and tag, we didnt need to do the renumber if they was the same. Apparently now though with a bigger string, it seems that check is redundant.
Can you remove the 2 lines please (yes I know I said you needed it before, but that was in a different data format)
'Track no change
If sOldPrefix = sNewPrefix Then Exit Sub
ASKER
rocki:
It seems to work but I need to test the code in my main db to test speed. I will close this question today after testing. Thanks for your efforts and good coding technique.
In what way can your existing code be utilized to create FDID into a table that does not contaiin the field prior to this new code.
I know that you are extremely busy I will appreciate your very last effort finalizing this particular series of question at the link below and I am sure that I wont bother you for awhile.
https://www.experts-exchange.com/questions/24368561/Creating-numbers-from-fields.html
Regards
Bill.
It seems to work but I need to test the code in my main db to test speed. I will close this question today after testing. Thanks for your efforts and good coding technique.
In what way can your existing code be utilized to create FDID into a table that does not contaiin the field prior to this new code.
I know that you are extremely busy I will appreciate your very last effort finalizing this particular series of question at the link below and I am sure that I wont bother you for awhile.
https://www.experts-exchange.com/questions/24368561/Creating-numbers-from-fields.html
Regards
Bill.
lol, Bill no worries. I have started a new project this week which is a little over 2 hours one way from home. Because of the long day, I dont get much time on EE. I sometimes pop in during lunch just for quick questions as dont have time to answer those longer questions.
I saw that question and will comment. coffeeshop is on the right lines.
The func we did wont work well here as it is used to renumber 2 prefixes. It would require multiple calls also
The func we did wont work well here as it is used to renumber 2 prefixes. It would require multiple calls also
ASKER
rockiroads:
So far so good the sample her works great but when I turned over your code to my fairly large database, it was very slow in updating the last two digits. For example, in the data entry form, it was a bit slow updating from one combo to the other when user was making changes to the controls.
One other observation, on two occassions, I received "Run-Time Erro '94": "Invalid use of Null at:
If Left$(rs!FDID, Len(sOldPrefix)) = sOldPrefix Or Left$(rs!FDID, Len(sNewPrefix)) = sNewPrefix Then
..this happened between controls as user was making changes to the controls in frmMain.
Do you think we need to add an "Nz" function or if you have another way to resolve the RunTime '94" so that the code would run fine inside the application once applied to the main office db.
Regards
Bill
So far so good the sample her works great but when I turned over your code to my fairly large database, it was very slow in updating the last two digits. For example, in the data entry form, it was a bit slow updating from one combo to the other when user was making changes to the controls.
One other observation, on two occassions, I received "Run-Time Erro '94": "Invalid use of Null at:
If Left$(rs!FDID, Len(sOldPrefix)) = sOldPrefix Or Left$(rs!FDID, Len(sNewPrefix)) = sNewPrefix Then
..this happened between controls as user was making changes to the controls in frmMain.
Do you think we need to add an "Nz" function or if you have another way to resolve the RunTime '94" so that the code would run fine inside the application once applied to the main office db.
Regards
Bill
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Once you are happy with it, remove the debug.print to improve the performance as this is writing to the immediate window
ASKER
rocki:
Your last posted code is faster and seems more like it.
What does this line code interpretes to?:
If Left$(rs!FDID, Len(sOldPrefix))
why did you use Left$...... here?
Your last posted code is faster and seems more like it.
What does this line code interpretes to?:
If Left$(rs!FDID, Len(sOldPrefix))
why did you use Left$...... here?
ASKER
Excellent !!!
defintely then must of been using recordsetclone and filtering that caused the delay then. It must of been going thru all the records. Dont know why when u do filtering. Oh well.
regarding use of left, that function renumbers two strings at the same time therefore we need to always differentiate between old and new prefixes. What we check is everything bar the last number
regarding use of left, that function renumbers two strings at the same time therefore we need to always differentiate between old and new prefixes. What we check is everything bar the last number
ASKER
rocki:
Just pondering about this question.
What if by any chance some of the FDID values in SID #1, 3 and 4 were missing in the table and user is in the process of adding or editing current record. The FDID that would be created in the current form may be wrong in the sense that part of the missing FDID values in the table could infact be part of the group series.
Is there anyway to sandwich somewhere in your "RenumberFDID" function (if it can be accomodated)such that this routine will first check and detect missing FDID values(if any), fix and re-order them then proceed to make changes to the current record that user is about to change to in the opened form.
something like...
So if you can write a line code and splice it inside the "CreateFDIDOnInsert code",
such as:-
if isnull (FDID.value) then
call some function
Else
process....."RenumberFDID"
...if possible, make this new added code portable so that it could even be placed in other places like the "On Close event handler of a form.
I am not sure if this possible...I am just guessing.
Regards
Bill
Just pondering about this question.
What if by any chance some of the FDID values in SID #1, 3 and 4 were missing in the table and user is in the process of adding or editing current record. The FDID that would be created in the current form may be wrong in the sense that part of the missing FDID values in the table could infact be part of the group series.
Is there anyway to sandwich somewhere in your "RenumberFDID" function (if it can be accomodated)such that this routine will first check and detect missing FDID values(if any), fix and re-order them then proceed to make changes to the current record that user is about to change to in the opened form.
something like...
So if you can write a line code and splice it inside the "CreateFDIDOnInsert code",
such as:-
if isnull (FDID.value) then
call some function
Else
process....."RenumberFDID"
...if possible, make this new added code portable so that it could even be placed in other places like the "On Close event handler of a form.
I am not sure if this possible...I am just guessing.
Regards
Bill
Regarding delete, here is the tweaked version. Hope you understood why it wasnt posted in your other question
Look at the comments for 'CHANGE HERE to see the differences
For updates, do as usual RenumberFDID oldprefix, newprefix
And for deletes, do this RenumberFDID oldprefix, ""
Look at the comments for 'CHANGE HERE to see the differences
For updates, do as usual RenumberFDID oldprefix, newprefix
And for deletes, do this RenumberFDID oldprefix, ""
Public Sub RenumberFDID(ByVal sOldPrefix As String, ByVal sNewPrefix As String)
Dim rs As DAO.Recordset
Dim iOldPrefix As Integer 'Count for renumbering original prefix
Dim iNewPrefix As Integer 'Count for renumbering new prefix
Dim sSql As String
Dim sFormat As String
DoCmd.Hourglass True
'Initialise
Set rs = Me.RecordsetClone
iNewPrefix = 0
iOldPrefix = 0
sFormat = "00"
'CHANGE HERE - ADD sNewPrefix TO FILTER ONLY IF SPECIFIED
'Filter on records that begin with the old
sSql = "SELECT * FROM tblMain WHERE FDID LIKE '" & sOldPrefix & "*'"
'If new prefix specified (specified when changing record) then include that as well
If sNewPrefix <> "" Then sSql = sSql & " OR FDID LIKE '" & sNewPrefix & "*'"
Debug.Print "Initiating Search", sSql
rs.Filter = sSql
Set rs = CurrentDb.OpenRecordset(sSql)
'Now loop through updating existing records
Do While rs.EOF = False
Debug.Print "Found ID=" & rs!SID, "FDID=" & rs!FDID
rs.Edit
If Left$(rs!FDID, Len(sOldPrefix)) = sOldPrefix Then
iOldPrefix = iOldPrefix + 1
Debug.Print "Updating Record to " & sOldPrefix & Format(iOldPrefix, sFormat)
rs!FDID = sOldPrefix & Format(iOldPrefix, sFormat)
'CHANGE HERE - CHECK sNewPrefix HAS A VALUE
ElseIf sNewPrefix <> "" Then
iNewPrefix = iNewPrefix + 1
Debug.Print "Updating Record to " & sNewPrefix & Format(iNewPrefix, sFormat)
rs!FDID = sNewPrefix & Format(iNewPrefix, sFormat)
End If
rs.Update
rs.MoveNext
Loop
'Closedown
rs.Close
Set rs = Nothing
Me.Refresh
DoCmd.Hourglass False
End Sub
In response to your question, the way the updating of records work is I populate FDID on the form with the new prefix but with a value of 00. So by the time it gets to renumering, a value already exists for that SID and will be captured in the renumbering process.
On your big table test, how long does it take to run?
On your big table test, how long does it take to run?
ASKER
rocki:
I deleted the FDID values for SID #1, 3 and 4 in tblMain. I then utilized this code per your last suggestion.
I expected the deleted values to be regenerated first but ths did not happen. Am I doing something wrong?
See the call to Functions in Code Snippet
Regards
Bill
I deleted the FDID values for SID #1, 3 and 4 in tblMain. I then utilized this code per your last suggestion.
I expected the deleted values to be regenerated first but ths did not happen. Am I doing something wrong?
See the call to Functions in Code Snippet
Regards
Bill
' ......................
' ......................
If Me.NewRecord = False Then
'Set txtFDID to hold the new prefix and tag in order for renumbering to pick it up
Me.txtFDID = sNewPrefix & "00"
'Save any changes
Me.Dirty = False
RenumberFDID sOldPrefix, "" '<------ New addition
'Call renumbering function
RenumberFDID sOldPrefix, sNewPrefix
End IfASKER
rocki:
You asked:
On your big table test, how long does it take to run?
Your added code did not show any effect so..I was not able to test.
Further Suggestion:
It might good just to limit your added code to check "only" for FDID Nulls and limit the updates to the FDID only to those FDID fields that were missing data...this way, the update will "not" take up much memory and freeze the db up.
Regards
Bill
You asked:
On your big table test, how long does it take to run?
Your added code did not show any effect so..I was not able to test.
Further Suggestion:
It might good just to limit your added code to check "only" for FDID Nulls and limit the updates to the FDID only to those FDID fields that were missing data...this way, the update will "not" take up much memory and freeze the db up.
Regards
Bill
ASKER
It might be a good idea to also make the RenumberingFDID a Public function rather than a Sub so that it's function can be called from lets say...An "On Close Event" handler of a form for the delete FDID.
What do you think?
What do you think?
In your code, why did you put the call when an update is made? Wrong place to make a call. Look at the code, there are two calls
RenumberFDID sOldPrefix, "" '<------ New addition
'Call renumbering function
RenumberFDID sOldPrefix, sNewPrefix
How do you go about deleting a record?
RenumberFDID sOldPrefix, "" '<------ New addition
'Call renumbering function
RenumberFDID sOldPrefix, sNewPrefix
How do you go about deleting a record?
ASKER
rocki:
In your comments under
You said:
Look at the comments for 'CHANGE HERE to see the differences
For updates, do as usual RenumberFDID oldprefix, newprefix '<=== This one I know where to place it.
And for deletes, do this RenumberFDID oldprefix, "" '<=== I dont know where to pace this one.
In addiditon,...this code does not stand on it's own... and I dont know how to call it.
RenumberFDID oldprefix, ""
I remarked "RenumberFDID oldprefix, newprefix" from the After Update Event, and placed....
RenumberFDID oldprefix, ""
...it wiped ou all the serial No values from the FDID fields...I had to refill the entire FDID in tblMain...using the other code....for prefilling the table.
If you tell me specifically what to I will do it.
Regards
Bill
In your comments under
You said:
Look at the comments for 'CHANGE HERE to see the differences
For updates, do as usual RenumberFDID oldprefix, newprefix '<=== This one I know where to place it.
And for deletes, do this RenumberFDID oldprefix, "" '<=== I dont know where to pace this one.
In addiditon,...this code does not stand on it's own... and I dont know how to call it.
RenumberFDID oldprefix, ""
I remarked "RenumberFDID oldprefix, newprefix" from the After Update Event, and placed....
RenumberFDID oldprefix, ""
...it wiped ou all the serial No values from the FDID fields...I had to refill the entire FDID in tblMain...using the other code....for prefilling the table.
If you tell me specifically what to I will do it.
Regards
Bill
ASKER
rocki:
This thoughts came to me this morning and I wanted to share it with you:
1). Whether in the add mode or Edit mode, if the code finds few (1 or 2 FDID fields in tblMain) contain Nulls, then ......
have the code add the serial numbers to the two Null FDID fields and re-number them in accordance
with the "group" the two FDID belongs to.
In other words:
I am anticipating that not more than two records of tblMain.FDID will contain Nulls in the table
at any given time and if such Nulls exist in tblMain.FDID then.....,
we may use these queries to specifically add the serial numbers back to to the two missing values in the FDID fields and then re-number them in accordance to their group(s) they belong:
e.g: (if using a checkbox in a query ' "qryFDIDCheckUpdate")
1). UPDATE tblMain INNER JOIN tblSimilarFDID ON tblMain.FDID = tblSimilarFDID.FDID SET
tblMain.FDIDcheck = Yes;
...and / or
2). SELECT SID, Left$(TestType;1) & "-" & "W" & "-" & CTypeID & "-" & BNo & "-" & LNo & "-" & SType & "-" & NewNo FROM qryMainNo
This is just a gathering of thoughts...I dont know anything about coding.....please pardon my suggestion.
Regards
Bill
This thoughts came to me this morning and I wanted to share it with you:
1). Whether in the add mode or Edit mode, if the code finds few (1 or 2 FDID fields in tblMain) contain Nulls, then ......
have the code add the serial numbers to the two Null FDID fields and re-number them in accordance
with the "group" the two FDID belongs to.
In other words:
I am anticipating that not more than two records of tblMain.FDID will contain Nulls in the table
at any given time and if such Nulls exist in tblMain.FDID then.....,
we may use these queries to specifically add the serial numbers back to to the two missing values in the FDID fields and then re-number them in accordance to their group(s) they belong:
e.g: (if using a checkbox in a query ' "qryFDIDCheckUpdate")
1). UPDATE tblMain INNER JOIN tblSimilarFDID ON tblMain.FDID = tblSimilarFDID.FDID SET
tblMain.FDIDcheck = Yes;
...and / or
2). SELECT SID, Left$(TestType;1) & "-" & "W" & "-" & CTypeID & "-" & BNo & "-" & LNo & "-" & SType & "-" & NewNo FROM qryMainNo
This is just a gathering of thoughts...I dont know anything about coding.....please pardon my suggestion.
Regards
Bill
In Edit mode, you will never find those nulls because we filter on finding specific prefixes only
In Add mode, we shouldnt find nulls because it is looking for a specific prefix
Maybe a suggestion is to write a seperate function that calls RenumberFDID
public sub CheckForNullFDID()
dim rs as dao.recordset
'Look for null FDID
set rs=currentdb.openrecordset
do while rs.eof = false
'For each one found, set the prefix accordingly with a number of 00
rs.edit
rs!FDID = left$(rs!TestType,1) & "-W-" & rs!CTypeID & "-" & rs!BNo & "-" & rs!LNo & "-" & rs!STypeID & "-00"
rs.update
'Call the renumber process
RenumberFDID rs!FDID, ""
rs.movenext
loop
rs.close
set rs=nothing
end sub
Now call the above when you feel it is necessary
In Add mode, we shouldnt find nulls because it is looking for a specific prefix
Maybe a suggestion is to write a seperate function that calls RenumberFDID
public sub CheckForNullFDID()
dim rs as dao.recordset
'Look for null FDID
set rs=currentdb.openrecordset
do while rs.eof = false
'For each one found, set the prefix accordingly with a number of 00
rs.edit
rs!FDID = left$(rs!TestType,1) & "-W-" & rs!CTypeID & "-" & rs!BNo & "-" & rs!LNo & "-" & rs!STypeID & "-00"
rs.update
'Call the renumber process
RenumberFDID rs!FDID, ""
rs.movenext
loop
rs.close
set rs=nothing
end sub
Now call the above when you feel it is necessary
In your edit code, you call the renumering process
RenumberFDID sOldPrefix, sNewPrefix
Now you must have code that handles deletes right? This is where you call RenumberFDID <<originalprefix>>, ""
RenumberFDID sOldPrefix, sNewPrefix
Now you must have code that handles deletes right? This is where you call RenumberFDID <<originalprefix>>, ""
ASKER
rocki:
Thanks for the latest Sub, I am already in the process of testing it.
In your last post you said:
Now you must have code that handles deletes right?
What do you meant by code that handle "delete" ??? '<========= Question
.....This is where you call RenumberFDID <<originalprefix>>, ""
I did not see any other sub that can substitute <<originalprefix>> ......, with..... >>, "" '<==== Question
I didnt quite understand these
Thanks for the latest Sub, I am already in the process of testing it.
In your last post you said:
Now you must have code that handles deletes right?
What do you meant by code that handle "delete" ??? '<========= Question
.....This is where you call RenumberFDID <<originalprefix>>, ""
I did not see any other sub that can substitute <<originalprefix>> ......, with..... >>, "" '<==== Question
I didnt quite understand these
ASKER
rocki:
The code in code snippet....(from one of the questions in series???)
...if it is the code you are talking about under ID: 24293263 dated 05/04/09 03:17 AM
..then, I dont know how to apply..this...RenumberFDID
within that code.. ???
The code in code snippet....(from one of the questions in series???)
...if it is the code you are talking about under ID: 24293263 dated 05/04/09 03:17 AM
..then, I dont know how to apply..this...RenumberFDID
within that code.. ???
Private Sub cmdDelete_Click()
Dim rs As DAO.Recordset
Dim cbo As String
Dim i As Integer
Dim bLoop As Boolean
Dim sPrefix As String
Dim sSql As String
On Error GoTo Err_cmdDelete_Click
bLoop = True
'Get the prefix letter
sPrefix = Left$(cboTest.Column(0), 1)
'Find next number from one about to be deleted
i = Val(Mid$(Me.FDID, 2)) + 1
'Do the delete of record
DoCmd.DoMenuItem acFormBar, acEditMenu, 8, , acMenuVer70
DoCmd.DoMenuItem acFormBar, acEditMenu, 6, , acMenuVer70
'Now update existing records
Set rs = Me.RecordsetClone
'Find first next highest number
sSql = "FDID = '" & sPrefix & Format(i, "000") & "'"
Debug.Print "Looking for", sSql
rs.FindFirst sSql
Do While bLoop
'If no records found, end loop
If rs.NoMatch = True Then
bLoop = False
Else
'Decrement number
Debug.Print "Updating ID", rs!SID
rs.Edit
rs!FDID = sPrefix & Format(i - 1, "000")
rs.Update
'Find next highest
i = i + 1
sSql = "FDID = '" & sPrefix & Format(i, "000") & "'"
Debug.Print "Finding next", sSql
rs.FindNext "FDID = '" & sPrefix & Format(i, "000") & "'"
End If
Loop
'Closedown
rs.Close
Set rs = Nothing
Me.Requery
Exit_cmdDelete_Click:
Exit Sub
Err_cmdDelete_Click:
MsgBox Err.Description
Resume Exit_cmdDelete_Click
End Sub
ASKER
rocki:
Just trying to recall:
Before delete, we have this tblMain
SID FDID
1 C-W-TA-134-12-CM-01
to:
SID FDID
1 P-W-PL-201-111-DM- '<=== Then after re-ordering the two groups in tblMain will become:
' ********
SID FDID
1 P-W-PL-201-111-DM-01
2 P-W-PL-201-111-DM-02
3 P-W-PL-201-111-DM-03
4 C-W-TA-134-12-CM-01
5 C-W-TA-134-12-CM-02
' **************
If user deletes SID #2 above ... P-W-PL-201-111-DM-02 '<===== DELETED !!!
Note that re-numbering occurs in both groups
SID FDID
1 P-W-PL-201-111-DM-01
'<========= SID DELETED
3 P-W-PL-201-111-DM-03 '<===== This is expected to re-order to P-W-PL-201-111-DM-02
4 C-W-TA-134-12-CM-01
5 C-W-TA-134-12-CM-02
' **************
to give this in tblMain....
SID FDID
1 P-W-PL-201-111-DM-01
'<========= SID #2 DELETED
3 P-W-PL-201-111-DM-02 '<===== New re-ordering
4 C-W-TA-134-12-CM-01
5 C-W-TA-134-12-CM-02
' **************
the cmdDELETE in code snippet you posted in the previous post may need an updated code... since there has been modifications to the code...because I wouldnt know how to connect your suggestion under ID: 24293263 dated 05/04/09 03:17 AM with this Delete command........
...RenumberFDID <<originalprefix>>, ""
Regards
Bill
Just trying to recall:
Before delete, we have this tblMain
SID FDID
1 C-W-TA-134-12-CM-01
to:
SID FDID
1 P-W-PL-201-111-DM- '<=== Then after re-ordering the two groups in tblMain will become:
' ********
SID FDID
1 P-W-PL-201-111-DM-01
2 P-W-PL-201-111-DM-02
3 P-W-PL-201-111-DM-03
4 C-W-TA-134-12-CM-01
5 C-W-TA-134-12-CM-02
' **************
If user deletes SID #2 above ... P-W-PL-201-111-DM-02 '<===== DELETED !!!
Note that re-numbering occurs in both groups
SID FDID
1 P-W-PL-201-111-DM-01
'<========= SID DELETED
3 P-W-PL-201-111-DM-03 '<===== This is expected to re-order to P-W-PL-201-111-DM-02
4 C-W-TA-134-12-CM-01
5 C-W-TA-134-12-CM-02
' **************
to give this in tblMain....
SID FDID
1 P-W-PL-201-111-DM-01
'<========= SID #2 DELETED
3 P-W-PL-201-111-DM-02 '<===== New re-ordering
4 C-W-TA-134-12-CM-01
5 C-W-TA-134-12-CM-02
' **************
the cmdDELETE in code snippet you posted in the previous post may need an updated code... since there has been modifications to the code...because I wouldnt know how to connect your suggestion under ID: 24293263 dated 05/04/09 03:17 AM with this Delete command........
...RenumberFDID <<originalprefix>>, ""
Regards
Bill
ASKER
rocki:
sorry...typo in the last post...it should be this one..
Before delete, we have this tblMain
SID FDID
1 P-W-PL-201-111-DM-01
2 P-W-PL-201-111-DM-02
3 P-W-PL-201-111-DM-03
4 C-W-TA-134-12-CM-01
5 C-W-TA-134-12-CM-02
' **************
If user deletes SID #2 above ... P-W-PL-201-111-DM-02 '<===== DELETED !!!
Note after re-numbering occurs then see below:
SID FDID
1 P-W-PL-201-111-DM-01
'<========= SID DELETED
3 P-W-PL-201-111-DM-03 '<===== This is expected to re-order to P-W-PL-201-111-DM-02
4 C-W-TA-134-12-CM-01
5 C-W-TA-134-12-CM-02
' **************
to give this in tblMain FINAL records....
SID FDID
1 P-W-PL-201-111-DM-01
'<========= SID #2 DELETED
3 P-W-PL-201-111-DM-02 '<===== New re-ordering
4 C-W-TA-134-12-CM-01
5 C-W-TA-134-12-CM-02
' **************
the cmdDELETE in code snippet you posted in the previous post may need an updated code... since there has been modifications to the code...because I wouldnt know how to connect your suggestion under ID: 24293263 dated 05/04/09 03:17 AM with this Delete command........
...RenumberFDID <<originalprefix>>, ""
Regards
Bill
sorry...typo in the last post...it should be this one..
Before delete, we have this tblMain
SID FDID
1 P-W-PL-201-111-DM-01
2 P-W-PL-201-111-DM-02
3 P-W-PL-201-111-DM-03
4 C-W-TA-134-12-CM-01
5 C-W-TA-134-12-CM-02
' **************
If user deletes SID #2 above ... P-W-PL-201-111-DM-02 '<===== DELETED !!!
Note after re-numbering occurs then see below:
SID FDID
1 P-W-PL-201-111-DM-01
'<========= SID DELETED
3 P-W-PL-201-111-DM-03 '<===== This is expected to re-order to P-W-PL-201-111-DM-02
4 C-W-TA-134-12-CM-01
5 C-W-TA-134-12-CM-02
' **************
to give this in tblMain FINAL records....
SID FDID
1 P-W-PL-201-111-DM-01
'<========= SID #2 DELETED
3 P-W-PL-201-111-DM-02 '<===== New re-ordering
4 C-W-TA-134-12-CM-01
5 C-W-TA-134-12-CM-02
' **************
the cmdDELETE in code snippet you posted in the previous post may need an updated code... since there has been modifications to the code...because I wouldnt know how to connect your suggestion under ID: 24293263 dated 05/04/09 03:17 AM with this Delete command........
...RenumberFDID <<originalprefix>>, ""
Regards
Bill
ASKER
rocki:
I thought it would be fair enough for me to create another thread since the orignal one that is related to this very last question is closed. The new question will now address the question posed within my last two or three post in this particular thread at:
https://www.experts-exchange.com/questions/24378302/Renumbering-Deleted-serials-in-tabe-records.html
This way we can start a clean slate.
Regards
Bill
I thought it would be fair enough for me to create another thread since the orignal one that is related to this very last question is closed. The new question will now address the question posed within my last two or three post in this particular thread at:
https://www.experts-exchange.com/questions/24378302/Renumbering-Deleted-serials-in-tabe-records.html
This way we can start a clean slate.
Regards
Bill
ASKER
rocki:
Since I could not resolved the issue of this code..
...RenumberFDID <<originalprefix>>, "" ot .....RenumberFDID sOldPrefix
I finally tweaked the "Public sub CheckForNullFDID" and utilized the sub to serve two purposes for the btnDelete as well and it works great.
Thanks for your efforts
Regards
Bill
Since I could not resolved the issue of this code..
...RenumberFDID <<originalprefix>>, "" ot .....RenumberFDID sOldPrefix
I finally tweaked the "Public sub CheckForNullFDID" and utilized the sub to serve two purposes for the btnDelete as well and it works great.
Thanks for your efforts
Regards
Bill
ASKER
rocki:
Below is the Sub I tried ...I am not too sure if I did the right thing...What do you think?
Below is the Sub I tried ...I am not too sure if I did the right thing...What do you think?
Public Sub DeletedReOrderFDID()
Dim rs As dao.Recordset
'Look for null FDID
Set rs = CurrentDb.OpenRecordset("select * from tblMain Where [SID] =" & Me.txtSID)
Do While rs.EOF = False
'For each one found, set the prefix accordingly with a number of 00
rs.Edit
rs!FDID = Left$(rs!TestType, 1) & "-W-" & rs!CTypeID & "-" & rs!BNo & "-" & rs!LNo & "-" & rs!STypeID & "-00"
rs.Update
'Call the renumber process
RenumberFDID rs!FDID, ""
rs.MoveNext
Loop
rs.Close
Set rs = Nothing
End Sub
Hi Bill, sorry for the lateness. Have to get up to date, catch up on your posts.
Looking at your last post, sample code, there is no reason to read a recordset as that information is available on screen, dont you agree?
Also no reason to loop as there will only ever be one record
But its on the right lines
Basically for the record that is about to be deleted, set its FDID to 00 with the built up prefixes from the values on the form.
Then call RenumberFDID
Also no reason to loop as there will only ever be one record
But its on the right lines
Basically for the record that is about to be deleted, set its FDID to 00 with the built up prefixes from the values on the form.
Then call RenumberFDID
ASKER
rockiroads:
I know you want me to learn this technique and in order for me to do so, please please post the correct code and I'll compare with mine to see where I made a booboo.
Regards
Bill
I know you want me to learn this technique and in order for me to do so, please please post the correct code and I'll compare with mine to see where I made a booboo.
Regards
Bill
ASKER
rocki:
If you would be kind to post a corrected version of my last code..it will be great. I need to compare yours with mine in order to see where I went wrong.
Regards
Bill
If you would be kind to post a corrected version of my last code..it will be great. I need to compare yours with mine in order to see where I went wrong.
Regards
Bill
Done in your other post
Ray got me thinking about the query approach. I may have a look at that, and report back if I find a better way to do this. You can compare in terms of performance then.
Ray got me thinking about the query approach. I may have a look at that, and report back if I find a better way to do this. You can compare in terms of performance then.
ASKER
rocki:
There is a minor problem with the code you suggested under ID: 24285869 dated 05/02/09 07:02 AM.
I have uploaded a sample db at the link as well to facilitate the understanding of the problem.
As such, I have listed a new question to address this problem at:
https://www.experts-exchange.com/questions/24469353/Re-ordering-Autonumber-Question.html
I will appreciate your assistance in tweaking the Sub further.
Regards
Bill
There is a minor problem with the code you suggested under ID: 24285869 dated 05/02/09 07:02 AM.
I have uploaded a sample db at the link as well to facilitate the understanding of the problem.
As such, I have listed a new question to address this problem at:
https://www.experts-exchange.com/questions/24469353/Re-ordering-Autonumber-Question.html
I will appreciate your assistance in tweaking the Sub further.
Regards
Bill
ASKER
rocki:
Trust me. I know that you are extremely busy. The link above will only take you not more than 5 minutes of your time. I will not be able to utilize the series of this code unless the code relevanent to this topic is amended moreso that I am now implementing all the accomplished thread codes in my main office db.
Regards
Bill
Trust me. I know that you are extremely busy. The link above will only take you not more than 5 minutes of your time. I will not be able to utilize the series of this code unless the code relevanent to this topic is amended moreso that I am now implementing all the accomplished thread codes in my main office db.
Regards
Bill
what comes out in the debug.print?