We help IT Professionals succeed at work.

Renumbering Deleted serials in tabe records

billcute
billcute asked
on
642 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

Comment
Watch Question

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

Author

Commented:
GRayL:
What code would make this work?

Author

Commented:
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
CERTIFIED EXPERT
Top Expert 2006
Commented:
This one is on us!
(Get your first solution completely free - no credit card required)
UNLOCK SOLUTION

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

Author

Commented:
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

Author

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

Regards
Bill
CERTIFIED EXPERT
Top Expert 2006

Commented:
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

Commented:
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

Author

Commented:
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?
CERTIFIED EXPERT
Top Expert 2006

Commented:
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

Author

Commented:
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

Commented:
No, the prefFDID is without the last three counting character '-01' etc.

Author

Commented:
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?

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

Author

Commented:
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

Author

Commented:
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

Commented:
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 ;-)

Commented:
oops, the carat was supposed to be under the second a  in 'FROM tblMain a'

Author

Commented:
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

Commented:
I did.  Given no spelling mistakes, my query at http:#a24319373 should run.

Author

Commented:
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:

https://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
CERTIFIED EXPERT
Top Expert 2006

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

Author

Commented:
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

Author

Commented:
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

Gain unlimited access to on-demand training courses with an Experts Exchange subscription.

Get Access
Why Experts Exchange?

Experts Exchange always has the answer, or at the least points me in the correct direction! It is like having another employee that is extremely experienced.

Jim Murphy
Programmer at Smart IT Solutions

When asked, what has been your best career decision?

Deciding to stick with EE.

Mohamed Asif
Technical Department Head

Being involved with EE helped me to grow personally and professionally.

Carl Webster
CTP, Sr Infrastructure Consultant
Empower Your Career
Did You Know?

We've partnered with two important charities to provide clean water and computer science education to those who need it most. READ MORE

Ask ANY Question

Connect with Certified Experts to gain insight and support on specific technology challenges including:

  • Troubleshooting
  • Research
  • Professional Opinions
Unlock the solution to this question.
Join our community and discover your potential

Experts Exchange is the only place where you can interact directly with leading experts in the technology field. Become a member today and access the collective knowledge of thousands of technology experts.

*This site is protected by reCAPTCHA and the Google Privacy Policy and Terms of Service apply.

OR

Please enter a first name

Please enter a last name

8+ characters (letters, numbers, and a symbol)

By clicking, you agree to the Terms of Use and Privacy Policy.