Want to win a PS4? Go Premium and enter to win our High-Tech Treats giveaway. Enter to Win

x
?
Solved

Renumbering Deleted serials in tabe records

Posted on 2009-05-04
25
Medium Priority
?
594 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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
Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
LVL 65

Accepted Solution

by:
rockiroads earned 2000 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
 
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

Free Tool: Site Down Detector

Helpful to verify reports of your own downtime, or to double check a downed website you are trying to access.

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

AutoNumbers should increment automatically, without duplicates.  But sometimes something goes wrong, and the next AutoNumber value is a duplicate.  This article shows how to recover from this problem.
This article shows how to get a list of available printers for display in a drop-down list, and then to use the selected printer to print an Access report or a Word document filled with Access data, using different syntax as needed for working with …
In Microsoft Access, learn how to use Dlookup and other domain aggregate functions and one method of specifying a string value within a string. Specify the first argument, which is the expression to be returned: Specify the second argument, which …
This lesson discusses how to use a Mainform + Subforms in Microsoft Access to find and enter data for payments on orders. The sample data comes from a custom shop that builds and sells movable storage structures that are delivered to your property. …

609 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