Solved

Renumbering Deleted serials in tabe records

Posted on 2009-05-04
25
542 Views
Last Modified: 2013-11-28
frmMain.txtSID is bounded to tblMain.SID. So whenever a record is to be deleted in tblMain, a message box will inform the user and upon user's response to "Yes" to go ahead and delete, then the current record with "txtSID" in frmMain is DELETED in tblMain with the corresponding "SID".

The cmdDelete button code can be found in the Code Snippet.

Now to my new request.
If a record is confirmed to be "deleted", I will appreciate a Function that would do the following if called from cmdDelete.

When user responds to a msgbox in the cmdDelete button by clicking the "Yes" to go ahead and delete the current record in frmMain.........then do the following:

1). Capture the "SID" number using the "txtSID" control that was deleted.

2). Use the captured "SID" or "txtSID" to "search" tblMain using an "SQL or sSQL" for the serial number "GROUP" of
     tblMain.FDID that matches the deleted "SID or txtSID"

3). If  a group of the deleted serial number (matching the deleted "SID or txtSID") is found then....
     Re-order / renumber the remaining FDID member(s) in that group using required fields in tblMain
      e.g:      
     rs!FDID = Left$(rs!TestType, 1) & "-W-" & rs!CTypeID & "-" & rs!BNo & "-" & rs!LNo & "-" & rs!STypeID & "-"

4). If "no" other member in the group of the deleted FDID serial number (matching the deleted "SID or txtSID") is found then....
     'Do nothing.

I am assuming that the call to re-order / renumber would be called right after user deleted an SID record.

For example:

Before user deletes the currently opened record in frmMain using the command button code shown below, we probably have the following in 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        
' **************

the new record after renumbering in tblMain FINAL records.... should be: -

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        
' **************

If a sample db is required, I'll be glad to upload one.
Private Sub btnDelete_Click()

On Error GoTo Err_btnDelete_Click

    Dim intReturn As Integer

 

intReturn = Msgbox("Warning: You are about to delete SID " & txtSID & ". " & _

                "Click Yes to delete or Cancel not to delete .", vbOKCancel)

    

    If intReturn = vbCancel Then

        GoTo Exit_btnDelete_Click

    End If

 

    If Me.Dirty Then

        Me.Undo

    Else

        DoCmd.RunCommand acCmdSelectRecord

        CurrentDb.Execute "Delete * From tblMain Where [SID] =" & Me.txtSID

        Me.Requery

 

	Call to Re-order / Renumber can be placed here.            '<=============Call to re-order / renumber here

 

    End If

    

Exit_btnDelete_Click:

    Exit Sub

 

Err_btnDelete_Click:

    Msgbox "Error# " & err.Number & " " & err.Description

    Resume Exit_btnDelete_Click

End Sub

Open in new window

0
Comment
Question by:billcute
  • 13
  • 8
  • 4
25 Comments
 
LVL 44

Expert Comment

by:GRayL
ID: 24300131
If you have a FDID prefix consisting of 'P-W-PL-201-111-DM' or 'C-W-TA-134-12-CM' you can always create a suffix of any number of them (less than 100) so long as the SID keeps incrementing normally.  You can make the FDID appear to be with the number 01 to 99, but it will always be incremented sequentially correctly.  I don't think you need all that baggage.
0
 
LVL 4

Author Comment

by:billcute
ID: 24300179
GRayL:
What code would make this work?
0
 
LVL 4

Author Comment

by:billcute
ID: 24303178
GRayl:
You said:
You can make the FDID appear to be with the number 01 to 99,...

I dont know how to go about experimenting your suggestion because I did not understand the metheod.

Regards
Bill
0
 
LVL 65

Accepted Solution

by:
rockiroads earned 500 total points
ID: 24303432
Bill, basically you just need to build up FDID without that number, then call your renumbering process
eg


Private Sub btnDelete_Click()
 

    Dim intReturn As Integer

    Dim sOldPrefix As String

    

    

    'BUILD UP PREFIX (as in edit)

    sOldPrefix = Left$(Me.txtFDID, 1) & "-" & Me.Tag & "-" & Me.cboCType & "-" & Me.cboBNo & "-" & Me.txtLNo & "-" & Me.cboSType & "-"
 

    intReturn = MsgBox("Warning: You are about to delete SID " & txtSID & ". " & _

                "Click Yes to delete or No not to delete .", vbYesNo + vbQuestion, "Confirm Delete")

    

    If intReturn = vbCancel Or intReturn = vbNo Then Exit Sub

 

    On Error GoTo Err_btnDelete_Click
 

    If Me.Dirty Then

        Me.Undo

    Else

 

        'PERFORM RECORD DELETE

        DoCmd.DoMenuItem acFormBar, acEditMenu, 8, , acMenuVer70

        DoCmd.DoMenuItem acFormBar, acEditMenu, 6, , acMenuVer70

        

        'RENUMBER FDID

        RenumberFDID sOldPrefix, ""

 

    End If

    

Exit_btnDelete_Click:

    Exit Sub

 

Err_btnDelete_Click:

    'Trap cancel

    If Err.Number = 2501 Then

        Exit Sub

    Else

        MsgBox "Error " & Err.Number & " occurred when trying to delete " & Me.txtSID & vbCrLf & vbCrLf & Err.Description, vbCritical, "Delete"

        Resume Exit_btnDelete_Click

    End If

End Sub

Open in new window

0
 
LVL 44

Expert Comment

by:GRayL
ID: 24304600
From a query,  assuming a field prefFDID containing the values like P-W-PL-201-111-DM, and C-W-TA-134-12-CM, along with the SID field which is probably an autonumber, try this query:

SELECT a.SID, a.prefFDID, a.prefFDID & "-" & Format((SELECT Count(b.prefFDID) FROM tblMain b WHERE b.SID<=a.SID and b.prefFDID= a.prefFDID),"00") AS FDID FROM tblMain ORDER BY 3;

With this you can add and delete as you wish and the Query always gives you a re-ordered FDID.
 
0
 
LVL 4

Author Comment

by:billcute
ID: 24307809
GRayL:
I created a field (prefFDID) in tbMain and created and pasted your query as a string SQL in btnDelete.

I received a Compile error: Expected Case at
a.SID

from...
SELECT a.SID...............

Would you mind posting a string SQL version of your suggested query under for me to place in btnDelete
where a call to a routine is required?

Regards
Bill
0
 
LVL 4

Author Comment

by:billcute
ID: 24307825
rocki roads:
Your suggestion works fine....but I am receiving two msgboxes instead of one. Can we suppress one of the msgboxes?

Regards
Bill
0
 
LVL 65

Expert Comment

by:rockiroads
ID: 24308499
You got your own predefined msgbox, the 2nd one, I expect that would be Access prompting you.

If before the DoCmd line, add the following

docmd.setwarnings false

so basicallyy it looks like

       DoCmd.SetWarnings False

       'PERFORM RECORD DELETE
        DoCmd.DoMenuItem acFormBar, acEditMenu, 8, , acMenuVer70

0
 
LVL 44

Expert Comment

by:GRayL
ID: 24308993
Did you populate prefFDID as shown above?  Why are you attaching it to a Delete button.  Remember, you do not have to 'keep track' of addtions or deletetions - so long as SID auto-increments, the query will renumber accordingly.  If tblMain contains the data as per the first part of the snippet, when you run the query it will return what you see in the second part.






tblMain

=======

SID    prefFDID      

1       P-W-PL-201-111-DM

3       P-W-PL-201-111-DM

4       C-W-TA-134-12-CM      

5       C-W-TA-134-12-CM 
 

QryResults
 

SID    prefFDID             FDID   

1       P-W-PL-201-111-DM   P-W-PL-201-111-DM-01

3       P-W-PL-201-111-DM   P-W-PL-201-111-DM-02

4       C-W-TA-134-12-CM    C-W-TA-134-12-CM-01  

5       C-W-TA-134-12-CM    C-W-TA-134-12-CM-02

Open in new window

0
 
LVL 4

Author Comment

by:billcute
ID: 24309380
GRayL:
I will like to test the speed of your suggested query on my db. Would you mind posting a query such that if run will create prefFDID values in my tblMain?
0
 
LVL 65

Expert Comment

by:rockiroads
ID: 24309407
Bill, I suggest you post the columns in your table (how its defined is in the sample code anyway so it should help)

Ray, no need for prefFDID as the fields it takes its values from are in the db so you should be able to get them without having to create this col.

I will be offline now, speak later
0
 
LVL 4

Author Comment

by:billcute
ID: 24309539
GRayL:
Here is tblMain sample screen shot.

SID    PrefFDID        FDID                        TestType      CTypeID        STypeID
1              P-W-TA-DM-01      PARK      TA      DM
2              C-W-PL-CM-01      PARK      PL      CM
3              P-W-PL-CM-02      PARK      PL      CM
4              C-W-TA-DM-01      CONC      TA      DM
5              C-W-TA-CM-02      CONC      TA      DM
6              C-W-PL-CM-02      CONC      PL      CM
7              P-W-TA-DM-02      PARK      TA      DM
8              P-W-TA-CM-01      PARK      TA      CM
9              P-W-TA-DM-02      PARK      TA      DM
10              C-W-PL-CM-03      CONC      PL      CM

If you have query that will create the prefFDID values, I will still like to experiment your suggested query.

Regards
Bill
0
Backup Your Microsoft Windows Server®

Backup all your Microsoft Windows Server – on-premises, in remote locations, in private and hybrid clouds. Your entire Windows Server will be backed up in one easy step with patented, block-level disk imaging. We achieve RTOs (recovery time objectives) as low as 15 seconds.

 
LVL 44

Expert Comment

by:GRayL
ID: 24310134
No, the prefFDID is without the last three counting character '-01' etc.
0
 
LVL 4

Author Comment

by:billcute
ID: 24310352
GRayL:
My pasting was bad. There is no entry in PrefFDID in tblMain.

Would you mind posting a query such that if run will create prefFDID values in my tblMain?
0
 
LVL 44

Expert Comment

by:GRayL
ID: 24317008
prefFDID is merely FDID less the three right characters ('-nn').

Go thru the records first and ensure there are no leading or trailing spaces.

UPDATE tblMain SET FDID = Trim(FDID);

Now populate prefFDID and then delete the FDID field.

UPDATE tblMain SET prefFDID = Left(FDID,Len(FDID)-3);

Now you no longer have the complete FDID field in a table.  It is always available as a derived field using the query.
0
 
LVL 4

Author Comment

by:billcute
ID: 24317085
GRayL:
I succesfull created a query that sets value for prefFDID using values obtained from other fields in tblMain.

From there, I tested your suggested query: under ID: 24304600 dated 5/05/09 08:48 AM..
and upon launching the query I received an "Enter Parameter Error msgbox" for:

a.SID      and ....
a.prefFDID

SID is an autonumber and prefFDID now contains data...there is no mispell and I dont kniow why the error message.

What am I doing wrong?

Below is my query in code snippet.
SELECT a.SID, a.prefFDID, a.prefFDID & "-" & Format((SELECT Count(b.prefFDID) FROM tblMain b WHERE b.SID<=a.SID and b.prefFDID= a.prefFDID),"00") AS FDID

FROM tblMain

ORDER BY 3;

Open in new window

0
 
LVL 4

Author Comment

by:billcute
ID: 24317155
GRayL:
Here is the query I utilized yesterday
UPDATE tblMain SET prefFDID = Trim(CStr(([TestType],1) & "-" & "W" & "-" & [CTypeID] & "-" & [BNo] & "-" & [LNo] & "-" & [STypeID]));

And it works fine in creating values for prefFDID but upon testing your query under ID: 24304600, I received Enter Parameter error for a.SID and a.prefFDID
0
 
LVL 44

Expert Comment

by:GRayL
ID: 24319373
Bill:  You've been around here long enough to know I am always setting traps by making deliberate mistakes.  You never caught this one again:

SELECT a.SID, a.prefFDID, a.prefFDID & "-" & Format((SELECT Count(b.prefFDID) FROM tblMain b WHERE b.SID<=a.SID and b.prefFDID= a.prefFDID),"00") AS FDID FROM tblMain a ORDER BY 3;

---------^--notice the alias that was not in the first post ;-)
0
 
LVL 44

Expert Comment

by:GRayL
ID: 24319386
oops, the carat was supposed to be under the second a  in 'FROM tblMain a'
0
 
LVL 4

Author Comment

by:billcute
ID: 24320506
GRayL:
Unfortunately, I am not an SQL guy. Using aliases is one of my worse nightmare.
If you could post the correct SQL, I'll appreciate it.

Regards
Bill
0
 
LVL 44

Expert Comment

by:GRayL
ID: 24320624
I did.  Given no spelling mistakes, my query at http:#a24319373 should run.
0
 
LVL 4

Author Comment

by:billcute
ID: 24320808
GRayL:
There are no spelling mistakes from my end either. I still couldnt get to run. I also think that your answer might be closer to the answer I received in another post at the following link:

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

the link above focuses on generating new FDID into an old table that did not contain any FDID field in the table prior to this new code....

What I need here Ray is a function that will update "just" the "group" of similar serial numbers when one of the member in that specific group is deleted from a form based btnDelete code.

In this case, rocki's code seems to be very close to what I was looking for......no offense please.

Regards
Bill
0
 
LVL 65

Expert Comment

by:rockiroads
ID: 24333360
Howdo all. sorry got very busy with work plus lappy wasnt working yesterday.

Anyways, the generate the sequence number, expanding on from what GRayL has been trying to do, this is probably along the lines

SELECT FDID, Left$(TestType,1) & "-" & "W" & "-" & CTypeID & "-" & BNo & "-" & LNo & "-" & STypeID & "-" & format((select count(*) from tblMain where TestType=a.TestType AND CTypeID=a.CTypeID AND BNo=a.BNo AND LNo=a.LNo AND STypeID=a.STypeID and SID <= a.SID),"00") as NewFDID
FROM tblMain AS a
ORDER BY SID;

Above query would need to be expanded to have your qualifiers, this would depend on the old and new prefix values.

Im now not sure which would be quicker as each row requires a count to be done.

Also, Im not sure this can be used in an update statement

UPDATE tblMain as a SET NFDID = Left$(TestType,1) & "-" & "W" & "-" & CTypeID & "-" & BNo & "-" & LNo & "-" & STypeID & "-" & format((select count(*) from tblMain where TestType=a.TestType AND CTypeID=a.CTypeID AND BNo=a.BNo AND LNo=a.LNo AND STypeID=a.STypeID and SID <= a.SID),"00")

due to operation must use an updateable query problem. Maybe workaround is to save select statement as query then try. Still not convinced though.

0
 
LVL 4

Author Comment

by:billcute
ID: 24334529
rockiroads:
Thank you for following up on this.
Your first query ran well but on the second "UPDATE query" I ran into a little problem.

First added "NFDID" field to tblMain and ran your "UPDATE query" and I received this error message.

"Operation must be an Updeatable Query"

What that means I dont know.

Regards
Bill
0
 
LVL 4

Author Comment

by:billcute
ID: 24334617
rocki:
I ran  a maketable query on your very first query and was able to create a table of incremental values for FDID field.
......Without getting out of the question, I needed something that would re-order a specific group after a record is deleted using btndelete code in a form that is bounded to a table. Your code under ID: 24303432 has satisfied that question.

What I am trying to get away from is a repetition of the other post that addressed the creation of new FDID values where those values were not present in the old tblMain.

Regards
Bill
0

Featured Post

How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

Join & Write a Comment

I originally created this report in Crystal Reports 2008 where there is an option to underlay sections. I initially came across the problem in Access Reports where I was unable to run my border lines down through the entire page as I was using the P…
Experts-Exchange is a great place to come for help with solutions for your database issues, and many problems are resolved within minutes of being posted.  Others take a little more time and effort and often providing a sample database is very helpf…
Using Microsoft Access, learn some simple rules for how to construct tables in a relational database. Split up all multi-value fields into single values: Split up fields that belong to other things into separate tables: Make sure that all record…
In Microsoft Access, when working with VBA, learn some techniques for writing readable and easily maintained code.

746 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

Need Help in Real-Time?

Connect with top rated Experts

12 Experts available now in Live!

Get 1:1 Help Now