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
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:
...as shown below
The final result in tblMain will look like the one below