?
Solved

Deleting / Editing Serial Numbers

Posted on 2009-04-26
33
Medium Priority
?
328 Views
Last Modified: 2013-11-28
tblMain contain a text type (FDID) for generating "serial numbers" from frmMain's btnAddNewRecord
"On Click" event handler as shown below:

Note that frmMain's form field are all bounded to tblMain.
frmMain.SID and frmMain.FDID controls are locked but enabled.

SID      FDID      TestType
1      C001      CONC
2      P001      PARK
3      C002      CONC
4      G001      GENE
5      G002      GENE
6      P002      PARK
7      G003      GENE

In the Editing mode, any record can be edited or deleted.
1). Case "EDIT"
        If a record is edited (by changing the value in cboTest then,
         I want a new code to re-order the edited record such that the serial number is re-alligned.
      For example:
(a).
   If SID #3's cboTest's value is changed from "CONC"  to "PARK"  then re-order current
    record in tblMain.SID such that it's FDID's record becomes "P002"

(b). Then run a query that will re-assign the increment number such that each category's
       incremental numbering (001) + is properly re-alligned

2). Case "DELETE"
        If a record is deleted (by removing a record from tblMain, it means that the both the SID,
        and FDID and other field records will be deleted as such, there will be a gap in the SID field
        but the FDID record can be reordered such that the FDID record can be recreated
         ...when a new record is created or a query is "run" that may re-create an FDID's serial
          number that may be similar to the one that was already deleted.
         I want a new code to re-order the edited record such that the serial number is re-alligned.
      For example:
(a).
   If SID #5's record is deleted from tblMain then the new tblMain will look like the one below.

SID      FDID      TestType
1      C001      CONC
2      P001      PARK
3      C002      CONC
4      G001      GENE
                                                              '<=== SID #5 is deleted
6      P002      PARK
7      G003      GENE

(b). Then run a query that will re-order / re-assign the increment number such that each
        category's incremental numbering (001) + is properly re-ordered / re-alligned
       In this case after re-ordering the deleted SID #5, the new table should look like the
        one below.
SID      FDID      TestType
1      C001      CONC
2      P001      PARK
3      C002      CONC
4      G001      GENE
6      P002      PARK
7      G002      GENE          '<----- G003 becomes G002 after re-ordering
                                                                         Note that the SID #5 was not re-ordered.
                                                                         it's no longer available in tblMain.\

If a sample db is required as visual aid, please let do not hesitate to request for one.

Below is my current code in btnAddNew of frmMain that generates the FDID serial numbers.
Option Compare Database
Option Explicit
Private Const FRM_TITLE = "AutoNumber"
' ***********
Public Sub AutoNumber()
Dim sVal As String
 
'If in addmode
    If Me.NewRecord = True Then
 
        sVal = Nz(DMax("FDID", "tblMain", "FDID Like '" & cboTest.Column(0) & "*'"), cboTest.Column(0) & "000")
 
        Me.txtFDID = cboTest.Column(0) & Format(Replace(sVal, cboTest.Column(0), "") + 1, "000")
        Me.Dirty = False
        
    'Otherwise edit mode and changes made
    ElseIf Me.Dirty = True Then
        Me.Dirty = False
    
    Else
        MsgBox "There are no pending changes that meet update criteria", vbInformation, FRM_TITLE
    End If
End Sub
' *********************
Private Sub cmdAddNew_Click()
On Error GoTo Err_cmdAddNew_Click
 
 
    Call AutoNumber                       '<===== Call Function
    DoCmd.GoToRecord , , acNewRec
 
Exit_cmdAddNew_Click:
    Exit Sub
 
Err_cmdAddNew_Click:
    MsgBox Err.Description
    Resume Exit_cmdAddNew_Click
    
End Sub

Open in new window

0
Comment
Question by:billcute
  • 19
  • 13
33 Comments
 
LVL 9

Expert Comment

by:borki
ID: 24239143
Hi

I did not fully understand what you exactly want, but let me try and rephrase this more generically..

You have a table which you always need to have in a particular sort order. When you insert, change or remove records you need the sort order to reflect this. Correct?

I note that you are using the field SID. Is this your primary key? I NEVER change primary keys! What I suggest is to add a new field, say "SortOrder" that contains the value you want to sort by in your form or query or report.

Now you can create VBA code triggered by a button or the update of your combo that checks and re-sorts the order. This can be done by looping through the table in code, eg:

Public Function IncrementSortOrder(sTable As String, sWhere As String, StartFrom As Integer)
   Dim sSql As String
   Dim rs As New ADODB.Recordset
   
   sSql = "SELECT SortOrder FROM " & sTable & " WHERE " & sWhere & " AND (SortOrder >= " & StartFrom & ") ORDER BY SortOrder"
   rs.Open sSql, CurrentProject.Connection, , adLockOptimistic, adCmdText
   Do While Not (rs.EOF)
      rs!SortOrder = rs!SortOrder + 1
      rs.Update
      rs.MoveNext
   Loop
   rs.Close
   Set rs = Nothing
   IncrementSortOrder = 0
End Function

While I am not giving you a complete solution to your problem, I hope I have given you a few pointers. Good luck

Felix Burkhard
0
 
LVL 65

Expert Comment

by:rockiroads
ID: 24239227
Considering its in a form and your form is not filtered, ie it shows all rows then you can do the code below. If it is filtered then instead of using recordsetclone, define the query to select the fields from the table.


    Dim rs As DAO.Recordset
    Dim cbo As String
    Dim i As Integer
    Dim bLoop As Boolean

'Find next number from one about to be deleted    
    i = Val(Mid$(Me.FDID, 2)) + 1
   

'Do your delete - whatever code you have for example
    DoCmd.DoMenuItem acFormBar, acEditMenu, 8, , acMenuVer70
    DoCmd.DoMenuItem acFormBar, acEditMenu, 6, , acMenuVer70

'Now update existing records    
    Set rs = Me.RecordsetClone
    rs.MoveFirst
    bLoop = True

'Find first next highest number
    rs.FindFirst "FDID = '" & cbo.Column(0) & Format(i, "000") & "'"
    Do While bLoop

'If no records found, end loop
        If rs.NoMatch = True Then
            bLoop = False
        Else

'Decrement number
            rs.Edit
            rs!Field1 = cbo.Column(0) & Format(i, "000")
            rs.Update

'Find next highest
            i = i + 1
            rs.FindNext "FDID = '" & cbo.Column(0) & Format(i, "000") & "'"
        End If
    Loop

'Closedown
    rs.Close
    Set rs = Nothing
0
 
LVL 4

Author Comment

by:billcute
ID: 24239571
borki:

Thanks for your posting.

"SID" is an autonumber type (not needed here).
"FDID" - textype is the sortorder field.
"tblMain" - tble name
"frmMain" - data entry form name
"cboTest.Column.(0)" - contains a list of three items: "Conc"; "Park"; "Gene"

(a). The word  "ConC" from Column(0) will be utilized to generate letter "C"
(b). The word  "Park" from Column(0) will be utilized to generate letter "P"
(d). The word  "Gene" from Column(0) will be utilized to generate letter "G"

I am a beginnner, if you could please use the "FDID" for your code, it would be great in understanding it.

Regards
Bill
0
Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

 
LVL 4

Author Comment

by:billcute
ID: 24239608
rocki:
Thank you for the original code...however, I should have noted in this post that cboTest.Column(0)
will store the following in tblMain ("Conc"; "Park", "Gene")

in my sample db for this test, I created another table (tblTest) which is now being used for cboTest.

So when user selects from it, your code should use the first letter of the TestID field to generate the "FDID" serial numbers.....

...sorry for the confusion.....it's my fault. ...I am using this tblTest the exact way it will be utilized in my main db.

In this case, my new design will likely affect your latest code and I may need an amendment to the very first code in this question's code snippet.

Regards
Bill
0
 
LVL 65

Expert Comment

by:rockiroads
ID: 24239638
No problem Bill

Create a variable

dim sPrefix as string

then assign it where you assign i. I am assuming its the first letter of cboTest
eg

sPrefix = left$(cboTest.Column(0),1)



Now in that code, replace the 3 instances of cbo.Column(0) with sPrefix
0
 
LVL 4

Author Comment

by:billcute
ID: 24239656
borki / rocki:
In order for you to fully understand the requirement of this question, I have decide to upload a sample db designed "only" to provide you a visual aid for the experimentation at the link below.

http://www.geocities.com/bombastikbill/DMax_SerialNo.zip 

I wamt to learn the process of doing this, so it would be appreciated if you would post "only" the working solution code and "not" a solution sample db.

Regards
Bill
0
 
LVL 4

Author Comment

by:billcute
ID: 24239805
rocki:
I made the necessary changes and tested your code using the CASE "EDIT" mode that I stated in my original question now brought forward for this scenerio.

1). Case "EDIT"
        If a record is edited (by changing the value in cboTest then,
         I want a new code to re-order the edited record such that the serial number is re-alligned.
      For example:
(a).
   If SID #3's cboTest's value is changed from "CONC"  to "PARK"  then re-order current
    record in tblMain.SID such that it's FDID's record becomes "P002"

Here is my original list in tblMain

SID  FDID      TestType
1      C001      CONC
2      P001      PARK
3      C002      CONC
4      G001      GENE
5      G002      GENE
6      P002      PARK
7      G003      GENE

By design, I placed frmMain "Data Entry"  property settings to "No"  and then navgate to SID "3".

I then open frmMain in view form, I changed cboTest from "CONC" to "PARK" then ran your code from an On Click event which I named "cmdReOrder".

Result.
Your code ran a query and prompted that it would delete a record. Upon clicking okay, SID 3 was deleted.

What I wanted is "not" to delete SID #3, I just want the FDID your code to "ReOrder" tblMain.FDID
such that it will re-order the numbering in tblMain with a view to changing tblMain.FDID from "C002"
to "P002" based on the changes I made in frmMain record #3.

I hope this is clearer.

Regards
Bill
0
 
LVL 4

Author Comment

by:billcute
ID: 24239831
rocki:
In other words, if your code works as espected then the original table will change as shown below:

tblMain before - Re-Ordering
Here is my original list in tblMain

SID  FDID      TestType
1      C001      CONC
2      P001      PARK
3      C002      CONC        '<==== Take note here
4      G001      GENE
5      G002      GENE
6      P002      PARK
7      G003      GENE
' *********************

If your code works as expected then tblMain should look like this..

tblMain After - Re-Ordering

SID  FDID      TestType
1      C001      CONC
2      P001      PARK
3      P002      PARK        '<==== Take note here
4      G001      GENE
5      G002      GENE
6      P002      PARK
7      G003      GENE

I have not tested for the "DELETION" of an SID from tblMain yet.

.... the principle should almost be as described here except if an SID record is deleted, the record no longer exist in the table but your code

.....must re-order so as to renumber tblMain such that each category is re-numbered in order of 001, 002, 003 etc..

Regards
Bill
0
 
LVL 4

Author Comment

by:billcute
ID: 24239861
rocki:
In order for us to be on the same page. I have posted your code as it is in my sample db in Code Snippet.

To be on the same page, create a command button and name it "cmdReOrder" and call the Function from the "On Click" command.

Regards
Bill
Public Function ReOrder()
Dim rs As DAO.Recordset
    Dim cbo As String
    Dim i As Integer
    Dim bLoop As Boolean
    
    Dim sPrefix As String
 
sPrefix = Left$(cboTest.Column(0), 1)
 
 
'Find next number from one about to be deleted
    i = Val(Mid$(Me.FDID, 2)) + 1
    
 
'Do your delete - whatever code you have for example
    DoCmd.DoMenuItem acFormBar, acEditMenu, 8, , acMenuVer70
    DoCmd.DoMenuItem acFormBar, acEditMenu, 6, , acMenuVer70
 
'Now update existing records
    Set rs = Me.RecordsetClone
    rs.MoveFirst
    bLoop = True
 
'Find first next highest number
    rs.FindFirst "FDID = '" & sPrefix & Format(i, "000") & "'"
    Do While bLoop
 
'If no records found, end loop
        If rs.NoMatch = True Then
            bLoop = False
        Else
 
'Decrement number
            rs.Edit
            rs!FDID = sPrefix & Format(i, "000")
            rs.Update
 
'Find next highest
            i = i + 1
            rs.FindNext "FDID = '" & sPrefix & Format(i, "000") & "'"
        End If
    Loop
 
'Closedown
    rs.Close
    Set rs = Nothing
 
End Function
' *************
 
Private Sub cmdReOrder_Click()
    Call ReOrder
End Sub

Open in new window

0
 
LVL 65

Expert Comment

by:rockiroads
ID: 24239926
Bill, create a button on your form called cmdDelete
then add this code to its click event


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



I put comments and debug.print statements to help you understand

Also, I do a requery in order to remove the display of the deleted record
0
 
LVL 65

Expert Comment

by:rockiroads
ID: 24239932
ha, keep forgetting to add as code snippet
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: 24239967
rocki:
I tested your command Delete button...it's not working as expected. Please see my comment under
ID: 24239831 for CASE EDIT.

The record is not expected to be deleted at all, it is expected to fucntion as I have noted under ID: 24239831.

Regards
Bill
0
 
LVL 65

Expert Comment

by:rockiroads
ID: 24240052
Sorry bill, I totally missed the edit. What I did was delete renumbering

Can you double check your expected results again as I see two P002, I assume you want P003 in ID 7?
0
 
LVL 4

Author Comment

by:billcute
ID: 24240345
rocki:
I am sorry....
In other words, if your code works as espected then the original table will change as shown below:

tblMain before - Re-Ordering
Here is my original list in tblMain

SID  FDID      TestType
1      C001      CONC
2      P001      PARK
3      C002      CONC        '<==== Take note here
4      G001      GENE
5      G002      GENE
6      P002      PARK
7      G003      GENE
' *********************

If your code works as expected then tblMain should look like this..

tblMain After - Re-Ordering

SID  FDID      TestType
1      C001      CONC
2      P001      PARK
3      P002      PARK        '<==== Take note here
4      G001      GENE
5      G002      GENE
6      P003      PARK       '<==== This will be re-ordered to P003
7      G003      GENE

Regards
Bill
0
 
LVL 4

Author Comment

by:billcute
ID: 24240494
rocki:
When user deletes a record in tblMain manually or through the data entry form. Your code will simply re-arrange the remaining records in tblMain such that each category is renumbered in the order of
001
002
003
etc..

I am not sure what may happen if the dtatabase grows big and how the code might slow the process of re-ordering down.....just pondering....

Regards
Bill
0
 
LVL 65

Expert Comment

by:rockiroads
ID: 24240631
Ive done some code but hit a problem

If you have

P001
G001
G002
P002

If you change G001 to a P, so you end up with G001 becoming P002 and P002 becoming P003? What do you do with G002?

P001
P002
G002
P003

Seems like you need to handle reordering of the G's as well.

Are you not expecting more than 999 per letter?

0
 
LVL 65

Expert Comment

by:rockiroads
ID: 24241735
Ok, Bill. from my last post, I believe you have to reorder the other series as well.

So what I did was to go thru the records reordering both at the same time

On the afterupdate event of the combo, call the new function eg

Private Sub cboTest_AfterUpdate()
   
    Dim sOldPrefix As String
    Dim sNewPrefix As String
   
    'Old prefix is current prefix
    sOldPrefix = Left$(Me.txtFDID, 1)
   
    'New prefix is letter from drop down
    sNewPrefix = Left$(Me.cboTest.Column(0), 1)
   
    If Me.NewRecord = False Then
        'Set txtFDID to hold the new prefix in order for renumbering to pick it up
        Me.txtFDID = sNewPrefix & "000"
       
        'Save any changes
        Me.Dirty = False
       
        'Call renumbering function
        RenumberFDID sOldPrefix, sNewPrefix
    End If
End Sub



Now the function takes two arguments, old prefix and new prefix


Private 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
    
    
    'Track no change
    If sOldPrefix = sNewPrefix Then Exit Sub
    
    DoCmd.Hourglass True
    
    'Initialise
    bLoop = True
    Set rs = Me.RecordsetClone
    iNewPrefix = 0
    iOldPrefix = 0
 
    '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, 1) = sOldPrefix Or Left$(rs!FDID, 1) = sNewPrefix Then
        
            rs.Edit
            
            If Left$(rs!FDID, 1) = sOldPrefix Then
                iOldPrefix = iOldPrefix + 1
                Debug.Print "Updating Record to " & sOldPrefix & Format(iOldPrefix, "000")
                rs!FDID = sOldPrefix & Format(iOldPrefix, "000")
            Else
                iNewPrefix = iNewPrefix + 1
                Debug.Print "Updating Record to " & sNewPrefix & Format(iNewPrefix, "000")
                rs!FDID = sNewPrefix & Format(iNewPrefix, "000")
            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
 
LVL 65

Expert Comment

by:rockiroads
ID: 24241744
This RenumberFDID is reusable, simply pass in two prefixes and it will renumber both based on the form's recordset.
0
 
LVL 4

Author Comment

by:billcute
ID: 24241970
rockiroads:
I have not tested your code...but I wanted to first answer your question under ID 24240631

You asked....
If you have

P001
G001        '<-- If you change this to P, this will temporarily become P001 until you render.
G002  '<-- When you render, this will change to G001 since there was no other Gs in the group of G(s).
P002

If you change G001 to a P, so you end up with G001 becoming P002 and P002 becoming P003? And G002 becoming G001 after rendering.

P001
P002    "<=== Changes after rendering
G001    "<=== Changes after rendering
P003

__________
You asked: "Are you not expecting more than 999 per letter"?

I am expecting more than that.....and you got me thinking.
Since this code may be deployed in multiple forms at the same time, identifying each specific form's trecords wil be of utmost importance.

Is it possivle to modify your code a little such that each form in the db has a "Tag" in it's properties, then add this tag to each serial numbers like this:
Assuming frmMain has "W" in it's Tag property...then is it possible to do something like this?

Dim sPrefix As String
sPrefix = Left$(cboTest.Column(0), 1) " & W ' & """

......such that each Group will look like the table below...WHERE:....the "Tag" property "W" becomes permanent Serial number for frmMain (see below)

SID   FDID      TestType
1      CW001      CONC
2      PW001      PARK
3      CW002      CONC
4      GW001      GENE
5      GW002      GENE
6      PW002      PARK
7      GW003      GENE

If I use another form...say frmSales and it's tag properties is identified as "S" then it's grouping serail numbers will become...something like this...

AppID    FDID      TestType
1           CS001      CONC
2           PS001      PARK
3           CS002      CONC
4           GS001      GENE
5           GS002      GENE
6           PS002      PARK
7           GS003      GENE

Is this possible? if so what part of your code do I need to amend?

I will test your latest code and give you a feedback later.

Regards
Bill
0
 
LVL 65

Expert Comment

by:rockiroads
ID: 24242167
Its possible to do as you say. Question is now are you going to assign a W? surely you cant be creating individual forms for each user. Dont you have a user database?

0
 
LVL 65

Expert Comment

by:rockiroads
ID: 24242171
I mean user table, not user db
0
 
LVL 4

Author Comment

by:billcute
ID: 24242863
I don't think we have User table
...if you mean tblUser..that stores all user names...yes,,..we do have something like...where each user has specific ID in tblUser
...which when user is logged on....his ID is loaded and we userly get that using something like:glaccessID = 3  (useid).

otherwise....we may use the following:

From my description in my previous comments, there are basically 3 main data entry forms in my db:
frmMain       '<========== we can use "W" for this Tag property
frmSales      <========== we can use "S" for this Tag property
frmProject    <========== we can use "P" for this Tag property

Regards
Bill
0
 
LVL 65

Expert Comment

by:rockiroads
ID: 24242966
So you will be using FDID in all three of these forms?
0
 
LVL 4

Author Comment

by:billcute
ID: 24243936
Yes,
FDID field is common to all the 3 tables. Each data entry form is bounded to each table.
For example
frmMain  ===> bounded to tblMain       '<========== we can use "W" for this Tag property
frmSales   ===> bounded to tblSales    <========== we can use "S" for this Tag property
frmProject   ===> bounded to tblProject <========== we can use "P" for this Tag property
0
 
LVL 65

Accepted Solution

by:
rockiroads earned 2000 total points
ID: 24243983
How about this then, function extended to pass in another string, so you can pass in either W, S or P or whatever else you want. Will work only if existing data is in that format


eg afterupdate event

Private Sub cboTest_AfterUpdate()
   
    Dim sOldPrefix As String
    Dim sNewPrefix As String
   
    'Old prefix is current prefix
    sOldPrefix = Left$(Me.txtFDID, 1)
   
    'New prefix is letter from drop down
    sNewPrefix = Left$(Me.cboTest.Column(0), 1)
   
    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 & "W" & "000"
       
        'Save any changes
        Me.Dirty = False
       
        'Call renumbering function
        RenumberFDID sOldPrefix, sNewPrefix, "W"
    End If
End Sub


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 = "000"
 
    '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
 
LVL 4

Author Comment

by:billcute
ID: 24244345
rocki:
I will give it a check and keep you abreast later ...may  be tomorrow.

Thanks for the effort.

Regards
Bill
0
 
LVL 4

Author Comment

by:billcute
ID: 24246693
rocki:
That was a brilliant job. It works great. However, the code here is just a prelude to the place I am going.
I will be posting the next challenging questions and provide links to it - It's very similar in principle and operation but I added two more controls for the test.

Regards
Bill
0
 
LVL 4

Author Comment

by:billcute
ID: 24247503
rocki:
With the success of this post, I have created another challenging question at the link below hoping for your assistance at:

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

The easy to follow and well commented code made it possible for me to go this next step.

Regards
Bill
0
 
LVL 4

Author Comment

by:billcute
ID: 24249160
rocki:
I need to have a learer understanding of the technique employed in your last posted code / routine
and I will appreciate it if you could provide some explanations because I want to apply it to multiple form fields.

 'Old prefix is current prefix
    sOldPrefix = Left$(Me.txtFDID, 1)
   
    'New prefix is letter from drop down
    sNewPrefix = Left$(Me.cboTest.Column(0), 1)
   
    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 & "W" & "000"

          'Save any changes
        Me.Dirty = False
       
        'Call renumbering function
        RenumberFDID sOldPrefix, sNewPrefix, "W"
0
 
LVL 4

Author Comment

by:billcute
ID: 24249267
rocki:
To better understand the new question, I have provided a sample db at the link that shows my further amendment to your code that works great.

Regards
Bill
0
 
LVL 4

Author Comment

by:billcute
ID: 24258592
rocki::
I successfully solved the AfterUpdate question and found out that a call to:
 .....RenumberFDID sOldPrefix, sNewPrefix, "W"          < ===== is not functioning as designed (that is, the counter does not change).

I have modified my new question specifically to address that part only. Since this is a continuous topic. I will appreciate your final assistance on this topic despite your busy schedule.

Regards
Bill
0
 
LVL 65

Expert Comment

by:rockiroads
ID: 24259435
explanation of code

Based on the db here, the first letter was the first letter from the combo
The second letter was this tag you wanted based on the form eg W, S or P

When we renumber, we have to renumber the original codes with the original prefix as well as the new one.

Here we are determining the original prefix, this is the current first letter in txtFDID

   'Old prefix is current prefix
    sOldPrefix = Left$(Me.txtFDID, 1)


Here we are determining the new prefix, the first letter from the combo
   
    'New prefix is letter from drop down
    sNewPrefix = Left$(Me.cboTest.Column(0), 1)


'Here we check the mode, we do a renumber only if we have edited (not created a new record)
   
    If Me.NewRecord = False Then


Here we update txtFDID so use the new prefix but give it a number of 000. This is so it gets picked up in the renumbering process

        'Set txtFDID to hold the new prefix and tag in order for renumbering to pick it up
        Me.txtFDID = sNewPrefix & "W" & "000"

          'Save any changes
        Me.Dirty = False
       

Here we call the renumber process passing in the original and new first letters as well as the form tag you wanted, W, S or P

        'Call renumbering function
        RenumberFDID sOldPrefix, sNewPrefix, "W"



Hope that is clear now
0
 
LVL 4

Author Comment

by:billcute
ID: 24303269
borki / rockiroads:
The issue on renumbering deleted FDID from tblMain is not resolved because the code I obtained here is giving me a Run-Time Error "91" and it's not working as expected.

I will appreciate it if someone could assist with it at:

http://www.experts-exchange.com/Microsoft/Development/MS_Access/Access_Coding-Macros/Q_24380780.html
0

Featured Post

Free Tool: IP Lookup

Get more info about an IP address or domain name, such as organization, abuse contacts and geolocation.

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…
If you’re using QODBC to update QuickBooks data from Microsoft® Access but Access is not showing the updated data, you could have set up QODBC incorrectly.
In Microsoft Access, learn the trick to repeating sub-report headings at the top of each page. The problem with sub-reports and headings: Add a dummy group to the sub report using the expression =1: Set the “Repeat Section” property of the dummy…
With Microsoft Access, learn how to specify relationships between tables and set various options on the relationship. Add the tables: Create the relationship: Decide if you’re going to set referential integrity: Decide if you want cascade upda…

839 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