• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 599
  • Last Modified:

Renumbering Deleted serials in tabe records

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
billcute
Asked:
billcute
  • 13
  • 8
  • 4
1 Solution
 
GRayLCommented:
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
 
billcuteAuthor Commented:
GRayL:
What code would make this work?
0
 
billcuteAuthor 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
0
Get 10% Off Your First Squarespace Website

Ready to showcase your work, publish content or promote your business online? With Squarespace’s award-winning templates and 24/7 customer service, getting started is simple. Head to Squarespace.com and use offer code ‘EXPERTS’ to get 10% off your first purchase.

 
rockiroadsCommented:
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
 
GRayLCommented:
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
 
billcuteAuthor 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
0
 
billcuteAuthor 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
0
 
rockiroadsCommented:
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
 
GRayLCommented:
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
 
billcuteAuthor 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?
0
 
rockiroadsCommented:
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
 
billcuteAuthor 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
0
 
GRayLCommented:
No, the prefFDID is without the last three counting character '-01' etc.
0
 
billcuteAuthor 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?
0
 
GRayLCommented:
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
 
billcuteAuthor 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

0
 
billcuteAuthor 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
0
 
GRayLCommented:
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
 
GRayLCommented:
oops, the carat was supposed to be under the second a  in 'FROM tblMain a'
0
 
billcuteAuthor 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
0
 
GRayLCommented:
I did.  Given no spelling mistakes, my query at http:#a24319373 should run.
0
 
billcuteAuthor 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:

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
 
rockiroadsCommented:
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
 
billcuteAuthor 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
0
 
billcuteAuthor 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
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

The 14th Annual Expert Award Winners

The results are in! Meet the top members of our 2017 Expert Awards. Congratulations to all who qualified!

  • 13
  • 8
  • 4
Tackle projects and never again get stuck behind a technical roadblock.
Join Now