Link to home
Start Free TrialLog in
Avatar of billcute
billcute

asked on

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    
SOLUTION
Avatar of coffeeshop
coffeeshop
Flag of Germany image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
SOLUTION
Avatar of rockiroads
rockiroads
Flag of United States of America image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
ASKER CERTIFIED SOLUTION
Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Avatar of billcute
billcute

ASKER

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
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
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.
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
rocki:
It's for future inserts.
You said use form_curent and determne max value..

how do I do this?
SOLUTION
Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
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")
rocki:
While waiting for your explanation for the above, I will test your latest suggestion and give you a feedback later.
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")

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
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
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
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.
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
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?
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.
rocki:
I will close this post if you think the function belongs to another question.

Regards
Bill
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