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

Creating numbers from fields

I have recently added a text type field (FDID) to tblMain.

I am now looking for a query or function or combination of query and function that could be fired from a Form Close or an On Click Event such that it go through all records in tblMain and grap values found in TestType, CTypeID, BNo, LNo and STypeID and utilize the values to create group of serial numbers in tblMain.FDID field (see below).

SID      FDID                                TestType  Tag  CTypeID  BNo    LNo  STypeID
1                                                   CONC      W       TA       134      12     CM
2                                                   CONC      W       TA       134      12     CM
3                                                   CONC      W       TA       134      12     CM

Exception Note:
In TestType field of tblMain grap only the first left letter value and combine them with other values to create groups of srial numbers in tblMain.FDID.

eg left$(TestType,1) & "-" & "W" "-" & CTypeID & "-" & BNo & "-" & LNo & "-" & SType & "-"

After creating the group add increment + (01) to each group such that each group is created in the order of:
01
02
03

...as shown below

The final result in tblMain will look like the one below

SID      FDID                                
1         C-W-TA-134-12-CM-01    
2         C-W-TA-134-12-CM-02    
3         C-W-TA-134-12-CM-03    
0
billcute
Asked:
billcute
  • 11
  • 8
  • 2
4 Solutions
 
coffeeshopCommented:
If SID is a primary key, you can do this without a slow individual function:

First create a query "qryMainNo" that delivers you the increment:

SELECT A.*, (SELECT COUNT(*) FROM tblMain AS B WHERE B.SID<=A.SID) AS NewNo FROM tblMain AS A;

Then create a new query with the above as base:

SELECT SID, Left$(TestType;1) & "-" & "W" & "-" & CTypeID & "-" & BNo & "-" & LNo & "-" & SType & "-" & NewNo FROM qryMainNo
0
 
rockiroadsCommented:
Bill, I take it this is for you to create the initial values?

The query approach is best which coffeeshop has started you out on.

The number on the end though needs to start from 1 for each combination, so from your initial query which you saved as qryMainNo, if you did this

SELECT A.*, (SELECT COUNT(*)
                     FROM tblMain AS B
                     WHERE B.SID<=A.SID
                     AND A.TestType = B.TestType
                     AND A.CTypeiD = B.CTypeID
                     AND A.BNo = B.BNo
                     AND A.LNo = B.LNo
                     AND A.STypeID = B.STypeID) AS NewNo
FROM tblMain AS A;

This will produce unique numbers per combination
eg
SID      TestType      CTypeID      BNo      LNo      STypeID      NewNo
1      PARK      PL      201      111      DM      1
2      CONC      PL      201      111      DM      1
3      PARK      PL      201      111      DM      2
4      CONC      TA      134      12      CM      1
5      CONC      TA      134      12      CM      2

Now taking coffeeshops next query, that should do the trick for you

If you run this, you should see generated fdid

SELECT SID, Left$(TestType,1) & "-" & "W" & "-" & CTypeID & "-" & BNo & "-" & LNo & "-" & STypeID & "-" & format(NewNo,"000")
FROM qryMainNo;

Your example has one leading zero, the query above will show two. This is based on your last question


Is this correct so far?


The other question is the tag ("W"). Is this what you definitely want for all records?
0
 
rockiroadsCommented:
If you are happy with this, then the only way I know is to update via vba. Using update statement will fail with not updateable query
eg
update tblMain, qryMainNo

approach

so some vba which uses the query to generate numbers (note coffeeshop's idea which I just modified to return number starting from 1 for each combination)




Public Sub UpdateFDID()
 
    Dim rs As DAO.Recordset
    Dim sSql As String
    Dim sFdid As String
    
    
    'Query to generate rolling numbers per combination
    sSql = "SELECT A.*, (SELECT COUNT(*) " & _
                        "FROM tblMain AS B " & _
                        "WHERE B.SID <= A.SID " & _
                        "AND A.TestType = B.TestType " & _
                        "AND A.CTypeiD = B.CTypeID " & _
                        "AND A.BNo = B.BNo " & _
                        "AND A.LNo = B.LNo " & _
                        "AND A.STypeID = B.STypeID) AS NewNo " & _
                        "FROM tblMain AS A"
 
    'Output to immediate window to test
    Debug.Print sSql
    
    'Open recordset based on that
    Set rs = CurrentDb.OpenRecordset(sSql)
    
    'Whilst records exist
    Do While rs.EOF = False
    
        'Build out new FID
        sFdid = Left$(rs!TestType, 1) & "-" & "W" & "-" & rs!CTypeID & "-" & rs!BNo & "-" & rs!LNo & "-" & rs!STypeID & "-" & Format(rs!NewNo, "00")
        
        'Dump diagnostic to immediate window
        Debug.Print "Setting FDID Info", rs!SID, rs!FDID, sFdid
        
        'Now update that row in the table
        CurrentDb.Execute "UPDATE tblMain SET fdid = '" & sFdid & "' WHERE SID = " & rs!SID, dbFailOnError
 
        rs.MoveNext
    Loop
    
    rs.Close
    Set rs = Nothing
End Sub

Open in new window

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.

 
billcuteAuthor Commented:
coffeeshop:
I tried your suggested queries and I was about to respond when I saw "rockiroads" comment which actually provided explanations to questions I was about to ask you...which was about " series grouping" and renumbering of each group.

Also your query did not suggest how I could have populated tblMain with the FDID per my original question.

Regards
Bill
0
 
billcuteAuthor Commented:
rockiroads:
Your suggestions and corrections make things work out. I tested both the queries and function. It works as I have anticipated.

The only comment I have is that running the routine in a fairly large database could be cumnbersome as it would consume a lot system memory due to the function's slow speed.

What I was thinking of is to be able to run an sql or string sql behind an On Close event of the data enty form such that when user closes the data entry form it will update "ONLY" new changes that were made (using checkboxes as "archive") to tblMain since last updated, this way we could get away from slow speed of the current function which must runt through all the records.

From my test, the application was frozen until all the updates have been performed to tblMain.

If you have other options that are available to you in achieving the above, it would be great.

What do you think?

Regards
Bill
0
 
coffeeshopCommented:
billcute:
yes you're right, I didn't get this with the group per combination.

Saving the values only for ticked or new entries seems to me a bit difficult, because you must include the numbering of previous groups. One idea is to do the whole thing with a vba routine only for selected/empty values. This would be the fastest option.

Another possibility I see is to execute the whole first query and write only the checked items of this run into a temporary table (including userid cause of multiuser environment), then use the temp. table to update tblMain and add the new one.  All can be done with queries. But executing the first query slows down.
0
 
rockiroadsCommented:
Bill, is this for a one off run to populate FDID or for future inserts?

For future inserts, you should just use form_current and determine max value
0
 
billcuteAuthor Commented:
rocki:
It's for future inserts.
You said use form_curent and determne max value..

how do I do this?
0
 
rockiroadsCommented:
ok, dont use form_current, might be better to call it on each change of the values.
eg

Private Sub CreateFDIDOnInsert()

    Dim sFDID As String
    Dim sMaxVal As String
    Dim iMaxVal As Long
   
   
    'Validate, ensure values entered
    If Nz(Me.cboTest, "") = "" Or Nz(Me.Tag, "") = "" Or Nz(Me.cboCType, "") = "" Or Nz(Me.cboBNo, "") = "" Or Nz(Me.txtLNo, "") = "" Or Nz(Me.cboSType, "") = "" Then
        Exit Sub
    End If
   
    'Generate Prefix
    sFDID = Left$(Me.cboTest.Column(0), 1) & "-" & Me.Tag & "-" & Me!cboCType & "-" & Me!cboBNo & "-" & Me!txtLNo & "-" & Me!cboSType & "-"

    Debug.Print "New FID is " & sFDID
   
    'Find the highest current FDID with this prefix, default to 0 if not found
    sMaxVal = Nz(DMax("FDID", "tblMain", "FDID LIKE '" & sFDID & "*'"), "0")

    Debug.Print "Current Max Value is", sMaxVal
   
    'Obtain the number part
    iMaxVal = Val(Right(sMaxVal, Len(sMaxVal) - InStrRev(sMaxVal, "-")))
   
    'Set txtFDID on the form
    Me.txtFDID = sFDID & Format(iMaxVal + 1, "00")

End Sub



So on your afterupdate event
eg

Private Sub cboTest_AfterUpdate()
    If Me.NewRecord = true Then CreateFDIDOnInsert
End Sub

Obviously need to call that reorder to handle updates

Private Sub cboTest_AfterUpdate()
    If Me.NewRecord = true Then CreateFDIDOnInsert else AllReOrder
End Sub

do this for each of the controls where you need to determine the prefix

0
 
billcuteAuthor Commented:
rocki:
Would you please explain what the new rountine does and in addition this line code val(Right(sMaxVal;......what it meant.

i.e...
Val(Right(sMaxVal, Len(sMaxVal) - InStrRev(sMaxVal, "-")))

from this line code..

 'Obtain the number part
    iMaxVal = Val(Right(sMaxVal, Len(sMaxVal) - InStrRev(sMaxVal, "-")))
   
    'Set txtFDID on the form
    Me.txtFDID = sFDID & Format(iMaxVal + 1, "00")
0
 
billcuteAuthor Commented:
rocki:
While waiting for your explanation for the above, I will test your latest suggestion and give you a feedback later.
0
 
rockiroadsCommented:
first part should be easy as its commented

this part
Val(Right(sMaxVal, Len(sMaxVal) - InStrRev(sMaxVal, "-")))

is getting the number from the last -

instrrev is used to search for a string within a string but from the end. it returns the position in the string.
we need to know difference so we subtract the length to get the number of chars
this should work just as well

iMaxVal = val( mid$(sMaxVal,InStrRev(sMaxVal, "-")+1)

so from sMaxVal, find the last dash and return everything after it
Wrap with val to make it into anumber

Now that we have the latest number for that prefix, we need to add one to it as it is applicable to the row about to be added

and this is what this does
 Me.txtFDID = sFDID & Format(iMaxVal + 1, "00")

0
 
billcuteAuthor Commented:
rocki:
I must give you a credit for your comments, it makes things a lot easier for me to understand.

What does this mean....just curious...to know why we used the Left$(rs!FDID.....here...just this line code.

If Left$(rs!FDID, Len(sOldPrefix)) = sOldPrefix Then

I think I pretty much understood the rest of your code. Thanks for your last explanation.

Regards
Bill
0
 
rockiroadsCommented:
well you pass in the prefix without numbers (soldprefix) so we have to compare just that, FDID has number, so we strip that then compare

Note, the idea of populating the table initially using sql was done by coffeeshop. I just extended it to renumber by prefix. But this must not be used for general adding, it is designed to be a one off.

For adding, look at the dmax code I posted earlier
0
 
billcuteAuthor Commented:
rocki:
I tested your code extensively and ....Yes, I noted that the DMax code is for adding, the "CreateFDIDOnInsert" displays the record even before the record is added.

However, I think there is something missing...maybe I did not come across very well under ID: 24275201. As such, I have decided to provide you a scenerio as a sample of problem that could happen(see below).

a). tblMain.FDID contains all records of serial numbers that your code helps create in old tables.
b). If after creating the FDIDs and for any reason, one or more FDID fields were deleted in error
     say FDID for SID 1, 5, 7 10 were "set to Null" for reasons we dont know..(I just made this up
      by deleting the FDID values for SID 1, 5, 7 10 ......... because it's a scenerio that can happen).
c). User now wants to add new record or edit current record. He opens frmMain and created a record
       on the fly or edit a record using the controls on the form .
        ..........................your code created the new FDID without taking into account the other FDIDs
      which have been set to Null in error. The FDID under SID #1, 5, 7 and 10 may have been part of
     the records that should have been considered when re-ordering or renumbering...but because
     the fields SID #1, 5, 7 - were already set to Null; those SID #1, 5, 7 and 10 were left out during
     re-ordering.

This is the problem I want to address in your current code of "CreateFDIDOnInsert".

So, in this case itI will be appreciated if your code will do the following (before adding or updating old Prefix / newPrefix of the FDIDa: -

First, search and detect "Null" values in tblMain.FDID, if found (as in the case of SID #1, 5, 7 and 10 ); then update the FDID and re-order / renumber the FDID fields with the appropriate serial number values.

....then proceed to the next step...
In other words, first check if FDID is "null"" in tblMain, if found, correct them by adding the appropriate values.
...If "Null" is "not" found in tblMain.FDID then proceed to run the next step. I hope this is much clearer now.

If this could be added to your current code, it will address my concern.

Regards
Bill
0
 
rockiroadsCommented:
How would they of got deleted? If your forms control this then it shouldnt happen.
When you delete a record, you will need to renumber at that point. I dont believe it should be handled on an add.

You have the renumberdid function, if you call it passing in some dummy value for new prefix so it never gets picked up, but a valid old prefix, then it should renumber. It would be better to tweak it also so that you renumber from that deleted position, it will be quicker.
0
 
billcuteAuthor Commented:
Rocki,
    There are times that some supervisors (with permission to the DB) use open query - datasheet format  to edit, delete or add record. In that situation we may end up with records with no FDID values.

I have suggested sveral times in the past that is't not a good practice to edit data (in that manner). It  is simply something I have no control over......the authorization is from the company.

In my case, all I am doing now is to guide against future mishap (if any).

So if you can write a line code and splice it inside the "CreateFDIDOnInsert code",
such as:-

if isnull (FDID.value) then
call some function
Else
process....."CreateFDIDOnInsert"

If this could be done this should take care of any problem

Regards
Bill
0
 
billcuteAuthor Commented:
rocki:
You said:
You have the renumberdid function, if you call it passing in some dummy value for new prefix so it never gets picked up, but a valid old prefix, then it should renumber. It would be better to tweak it also so that you renumber from that deleted position, it will be quicker.

How do I implement this?
0
 
rockiroadsCommented:
Ok, bill I see what you mean. Let me get back to you on this

I will tweak that renumber func for you but in the previous thread where it was created as this is about add. Also need to be fair on coffeeshop.
0
 
billcuteAuthor Commented:
rocki:
I will close this post if you think the function belongs to another question.

Regards
Bill
0
 
billcuteAuthor Commented:
coffeeshop / rocki:
This question was actually in a series of that questions but I will split the points. I will award coffeeshop for the intial idea even thoogh he agreed under (ID: 24277373) he would have difficulty of finishing his idea and also I will credit rocki for excellently expanding on coffeeshop's suggestion towards a successful robust solution.

Regards
Bill
0

Featured Post

Technology Partners: 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!

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