Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
?
Solved

Renumbering Question

Posted on 2009-04-27
49
Medium Priority
?
474 Views
Last Modified: 2013-11-28
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.
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

Open in new window

0
Comment
Question by:billcute
  • 25
  • 23
49 Comments
 
LVL 15

Expert Comment

by:Simon Ball
ID: 24258091
what results are you getting?

what comes out in the debug.print?
0
 
LVL 65

Expert Comment

by:rockiroads
ID: 24259380
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), 1) & strCorePinNo

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

0
 
LVL 65

Expert Comment

by:rockiroads
ID: 24259405
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), 1) & strCorePinNo


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

Open in new window

0
Visualize your virtual and backup environments

Create well-organized and polished visualizations of your virtual and backup environments when planning VMware vSphere, Microsoft Hyper-V or Veeam deployments. It helps you to gain better visibility and valuable business insights.

 
LVL 65

Expert Comment

by:rockiroads
ID: 24259444
I see you have commented this out

If sOldPrefix  = sNewPrefix  Then Exit Sub


I think you will need it to save renumbering unnecessarily
0
 
LVL 65

Expert Comment

by:rockiroads
ID: 24259453
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.
0
 
LVL 4

Author Comment

by:billcute
ID: 24261641
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
0
 
LVL 65

Expert Comment

by:rockiroads
ID: 24261871
it only takes 2 arguments now. Drop the "00"
0
 
LVL 4

Author Comment

by:billcute
ID: 24262976
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
0
 
LVL 65

Expert Comment

by:rockiroads
ID: 24264768
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), 1) & strCorePinNo
0
 
LVL 4

Author Comment

by:billcute
ID: 24265411
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
0
 
LVL 65

Expert Comment

by:rockiroads
ID: 24268337
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

0
 
LVL 65

Expert Comment

by:rockiroads
ID: 24268371
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
0
 
LVL 65

Expert Comment

by:rockiroads
ID: 24268375
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
0
 
LVL 4

Author Comment

by:billcute
ID: 24268490
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
0
 
LVL 65

Expert Comment

by:rockiroads
ID: 24268749
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

0
 
LVL 4

Author Comment

by:billcute
ID: 24271708
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.

http://www.experts-exchange.com/Programming/Languages/SQL_Syntax/Q_24368561.html

Regards
Bill.
0
 
LVL 65

Expert Comment

by:rockiroads
ID: 24273952
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.
0
 
LVL 65

Expert Comment

by:rockiroads
ID: 24274325
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
0
 
LVL 4

Author Comment

by:billcute
ID: 24275241
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
0
 
LVL 65

Accepted Solution

by:
rockiroads earned 2000 total points
ID: 24277459
I think it has something to do with the filtering of recordsetclone. Looks like its going thru all records

Try this. Now we are creating sql that specify returning those matching rows only. FDID field is indexed already and must stay so.


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"
 
    'Filter on records that begin with the old and new letter
    sSql = "SELECT * FROM tblMain WHERE FDID LIKE '" & sNewPrefix & "*' OR FDID LIKE '" & sOldPrefix & "*'"
    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)
        Else
            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



With regards to the null problem. I would say preventive measures should be in place. The function works assuming FDID has been populated already and assumes the form would of done the validation to make sure user does not enter any nulls. So if any of the fields you use to generate the prefix are null then dont call renumber.
0
 
LVL 65

Expert Comment

by:rockiroads
ID: 24277464
Once you are happy with it, remove the debug.print to improve the performance as this is writing to the immediate window
0
 
LVL 4

Author Comment

by:billcute
ID: 24277800
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?
0
 
LVL 4

Author Closing Comment

by:billcute
ID: 31575315
Excellent !!!
0
 
LVL 65

Expert Comment

by:rockiroads
ID: 24277853
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
0
 
LVL 4

Author Comment

by:billcute
ID: 24285678
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
0
 
LVL 65

Expert Comment

by:rockiroads
ID: 24285869
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, ""

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

Open in new window

0
 
LVL 65

Expert Comment

by:rockiroads
ID: 24285882
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?
0
 
LVL 4

Author Comment

by:billcute
ID: 24286008
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
' ......................
' ......................
    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 If

Open in new window

0
 
LVL 4

Author Comment

by:billcute
ID: 24286022
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
0
 
LVL 4

Author Comment

by:billcute
ID: 24286038
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?
0
 
LVL 65

Expert Comment

by:rockiroads
ID: 24287400
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?

0
 
LVL 4

Author Comment

by:billcute
ID: 24288328
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
0
 
LVL 4

Author Comment

by:billcute
ID: 24288906
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      
         
0
 
LVL 65

Expert Comment

by:rockiroads
ID: 24293252
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("select * from tblMain where isnull(fdid) = true")
    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
0
 
LVL 65

Expert Comment

by:rockiroads
ID: 24293263
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>>, ""
0
 
LVL 4

Author Comment

by:billcute
ID: 24293713
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
0
 
LVL 4

Author Comment

by:billcute
ID: 24293860
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 <<originalprefix>>, ""
 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

Open in new window

0
 
LVL 4

Author Comment

by:billcute
ID: 24294012
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
0
 
LVL 4

Author Comment

by:billcute
ID: 24294025
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
0
 
LVL 4

Author Comment

by:billcute
ID: 24296398
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:

http://www.experts-exchange.com/Microsoft/Development/MS_Access/Q_24378302.html

This way we can start a clean slate.

Regards
Bill
0
 
LVL 4

Author Comment

by:billcute
ID: 24300164
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
0
 
LVL 4

Author Comment

by:billcute
ID: 24300488
rocki:
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

Open in new window

0
 
LVL 65

Expert Comment

by:rockiroads
ID: 24303331
Hi Bill, sorry for the lateness. Have to get up to date, catch up on your posts.
0
 
LVL 65

Expert Comment

by:rockiroads
ID: 24303352
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
0
 
LVL 4

Author Comment

by:billcute
ID: 24305505
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
0
 
LVL 4

Author Comment

by:billcute
ID: 24307865
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
0
 
LVL 65

Expert Comment

by:rockiroads
ID: 24308476
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.
0
 
LVL 4

Author Comment

by:billcute
ID: 24563406
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:

http://www.experts-exchange.com/Microsoft/Development/MS_Access/Q_24469353.html

I will appreciate your assistance in tweaking the Sub further.

Regards
Bill
0
 
LVL 4

Author Comment

by:billcute
ID: 24570128
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
0

Featured Post

Free Tool: SSL Checker

Scans your site and returns information about your SSL implementation and certificate. Helpful for debugging and validating your SSL configuration.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Instead of error trapping or hard-coding for non-updateable fields when using QODBC, let VBA automatically disable them when forms open. This way, users can view but not change the data. Part 1 explained how to use schema tables to do this. Part 2 h…
Windows Explorer lets you open cabinet (cab) files like any other folder. In VBA you can easily handle normal files and folders, but opening and indeed creating cabinet files takes a lot more - and that's you'll find here.
With Microsoft Access, learn how to start a database in different ways and produce different start-up actions allowing you to use a single database to perform multiple tasks. Specify a start-up form through options: Specify an Autoexec macro: Us…
Enter Foreign and Special Characters Enter characters you can't find on a keyboard using its ASCII code ... and learn how to make a handy reference for yourself using Excel ~ Use these codes in any Windows application! ... whether it is a Micr…

581 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question