Solved

Creating numbers from fields

Posted on 2009-04-30
264 Views
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
Question by:billcute

LVL 7

Assisted Solution

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

LVL 65

Assisted Solution

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

LVL 65

Accepted Solution

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

0

LVL 4

Author Comment

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

LVL 4

Author Comment

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

LVL 7

Expert Comment

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

LVL 65

Expert Comment

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

LVL 4

Author Comment

rocki:
It's for future inserts.
You said use form_curent and determne max value..

how do I do this?
0

LVL 65

Assisted Solution

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

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

LVL 4

Author Comment

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

LVL 4

Author Comment

rocki:
While waiting for your explanation for the above, I will test your latest suggestion and give you a feedback later.
0

LVL 65

Expert Comment

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

LVL 4

Author Comment

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

LVL 65

Expert Comment

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

LVL 4

Author Comment

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.

Regards
Bill
0

LVL 65

Expert Comment

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

LVL 4

Author Comment

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

LVL 4

Author Comment

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

LVL 65

Expert Comment

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

LVL 4

Author Comment

rocki:
I will close this post if you think the function belongs to another question.

Regards
Bill
0

LVL 4

Author Comment

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

Composite queries are used to retrieve the results from joining multiple queries after applying any filters. UNION, INTERSECT, MINUS, and UNION ALL are some of the operators used to get certain desired results.â€‹
This article describes how to use the timestamp of existing data in a database to allow Tableau to calculate the prior work day instead of relying on case statements or if statements to calculate the days of the week.
Show developers how to use a criteria form to limit the data that appears on an Access report. It is a common requirement that users can specify the criteria for a report at runtime. The easiest way to accomplish this is using a criteria form that aâ€¦
Basics of query design. Shows you how to construct a simple query by adding tables, perform joins, defining output columns, perform sorting, and apply criteria.