Auto Letter instead of autonumber??

[LLBP]

How can I make the default value of a field in my subform start with 'A' and increment for each record?

Thanks in advance,
Brent

 

[LittleSmudge]

That depends - What do you expect to happen for the 27th record ?
Ie. What comes after Z


IF you are really sure that you will never exceed 26 records then set the field to Auto_Number, set the first record to 65 and then on the Forms have a text box with a ControlSource =Chr([FieldName])



'ope-that-'elps.



G LS
spsinkNOJUNK@yahoo.co.uk
Remove the NOJUNK to use.

 

[BNPMike]

Smudge

Autonumber wouldn't work. Well perhaps the very first time. You can't declare an autonumber in a query so you can't make sure that a chosen recordset starts at 1 or continues without gaps.

I've said before this inability of SQL to reference the preceding record is a drawback that should have been fixed decades ago.

 

[LLBP]

Thanks for the reply.

The base table is where all of the inpsections are recorded. The subform displays only those for the parent record. There won't be more than 26 per parent record but they are all in one table.

Pseudo code:
if record number is 0 then value is char(65)
else char(previous record +1)

Can this be done in VB?

 

[jimb0ne]

how is it that you are storing all the information in one table but keeping the values unique if you are using letters? are you going to AA after Z?

 

[LittleSmudge]

No Mike - but the originator is talking of it being in a FIELD - in a table not a query.

However from the latest reply it is clear that it is not that simple. There are many records in the table and what is needed is a separate reference for the records in the tambe on the many end for each record in the many end table.

The only way to do this reliably is to do it in code at the time when the record is created.
count the number of records in the sub table that are linked to the current record in the main table, Add 65, Convert the number to Chr and then insert that into a dedicated field of type Text(1)


In the subject of SQL referencing the 'previous record' .. ..
In RDB theory there is no concept of 'Next' and 'Previous' as the order of the records contain no information. So why should SQL be able to reference the 'previous' record ? Previoous today can ( and probobly will ) will completely different to 'previous' yesterday. What useful data could possibly be obtained.




G LS
spsinkNOJUNK@yahoo.co.uk
Remove the NOJUNK to use.

 

[LLBP]

Let me provide a little more information. I have a product table (form based on it), an inspection table (subform based on it), and an inspection detail table (subform of subform based on it). Both inspection and inspection detail tables have autonumber primary keys.

The inspection table (tblPartInspections) is linked to the product table (tblProducts) by VarnetNum - many to one. The inspection detail table (tblInspectionDetail) is linked to tblInspections by InspectionID - many to one. The said letter field (DimensionID) correlates to a Dimension on a drawing (A-Z) but mostly less than F. This is not a primary key it is only unique per InspectionID in tblInspections.

Users are carrying laptops in the production area and entering inspection data directly to the db. The less typing required the better. I just want to fill in the DimensionID field automatically.

I do ok with the db design, I just don't know VB very well.

Thanks to all,
Brent

 

[PHV]

You may take a look at the DMax function.

Hope This Help, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884

 

[BNPMike]

In RDB theory there is no concept of 'Next' and 'Previous' as the order of the records contain no information. So why should SQL be able to reference the 'previous' record ? Previoous today can ( and probobly will ) will completely different to 'previous' yesterday. What useful data could possibly be obtained.

Relations don't have any order but that of course ignores the fact the most relations we generate with SQL have order by clauses so the order is known for relations generated by SQL.

The idea that tables don't have order is to stop people hiding information in the record order eg having 4 records each for each quarters sales.

To be able to access the values of the prior record after ordering brings all sorts of advantages. You can number records for display. You can generate a year's set of dates just by incrementing the date of the prior record. And you can control grouping by watching when values change in fields.

The 4GL Focus has a function which gets the value of the previous record and I used it a lot when I was a developer. A number of times I've seen people ask questions on Tek-Tips and I think 'That'd be a doddle if you could just have an ability to compare current values to prior ones'.

 

[MichaelRed]

There are numerous references to creating a sequence of values in queries (a-la autonumber) in these fora. Advanced search w/ "AutoNumber" and "Query" (with ALL WORDS) should find at least a starting point. The conversion to Character is relatively trivial, as well as already addressed it this thread, albeit some what simplistically for use with an autosequence generatewd value.





MichaelRed
m.red@att.net

Searching for employment in all the wrong places

 

[BSman]

If your inspection items range from A through Z at the most, but the mix changes from product to product (i.e., and inspection for each product will require some mix of the items, ranging from all of them to only some of them), then you could set up a productInspectionItems table that would contain each productID and the inspection items (A through Z) that are required for that product. When a new inspection is being performed, add records for all of the items which should be inspected for that product to the inspection details table. Then the inspector can fill in the results.

 

[LLBP]

BSman,
That was a thought, but it is way too labor intensive to enter and maintain it. All the information is currently on ACAD drawings.