Autonumber......again :(

Posted on 2005-02-24
Medium Priority
Last Modified: 2010-04-16
Guys i cant get this to work.......I need to make for each table a ID...like quoteID in the format 'Q0001'......ive did this in access.....format - \Q000....(autonumber) then when i insert new record....it does this perfect.  But problem occurs when i need to reference it in another table......like Booking table. - QuoteID.  It gives me not valid integer value.  I tried creating an autonumber in delphi self.... but none of the code wants to work.  Client wants to add a prefix coz of searching under any field.  I need this for today and been struggling for days now.

Please please help
Question by:whythetorment
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 4
  • 3
  • 2
  • +4
LVL 27

Expert Comment

ID: 13400658
i would left out the 'Q' or whatever prefix u use, and using only a real number as ID,
or leaving the the autonumber and take care self for numbering

btw. the format is only for display, the 'Q' self is not stored in the table (i guess)

meikl ;-)

Expert Comment

ID: 13400662
What is the datatype of the field? In access, it must be a Text field for storing in that format.

Author Comment

ID: 13400689
Ive made it autonumber.  i could make the primary key just ID....autonumber....then QuoteID as text....then as format Q000....but i cant get it to increase with every inserted record.....need to do it like this coz client is bugging me about it.

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

LVL 27

Expert Comment

ID: 13400707
maybe you could use the the onSetText-event of the field to trim away your prefix

(checked access -> its only for display)

Author Comment

ID: 13400717
ok but i need the prefix.....thats the entire idea.....
LVL 27

Expert Comment

ID: 13400756
>ok but i need the prefix.....thats the entire idea.....

what about to left out the format in the accesstable defintion,
and add it as format of your TField in your delphi Application?
LVL 10

Expert Comment

ID: 13403098
Hi Kretzchmar all the glory to you,

but whythetorment you need to do it otherwise
in all respect for you have to do it in a complete
different manner. Your database field is untouchable
so the format has to be integer. All the modification
towards client has to be made by visual ouput as
Kretzchmar suggested. Just set your field to integer
and let the user see (by OnSetText) whatever he
wants to see but don't mess with your internal data
that's not of concern of users anyway.

LVL 31

Expert Comment

ID: 13408003
Why not use an AfterInsert event?  In there you would get the last value, you would then segregate it into Alpha value and Serial No. value, turn the Serial No into an integer, increment the integer, turn it back into a string, glue it back onto the Alpha value and then assign the result to the ID field you wish to use.
LVL 46

Expert Comment

ID: 13409212
Here's an idea...
Add a new text column to the table.  It will be populated with the type of row ('Q' for questions, 'A' for answers, 'C' for clients, etc.)

When joining these two tables, use a string expression involving the new column and a formatted version of the autonumber column as the joining criteria.
MSAccess Example:
Select * From tblA Inner Join tblB
On (tblA.RowType & Format(tblA.ID,"00000")) =  tblB.QuoteID

My approach would be to state reasons to drop the string format as the primary key:
* Speed of searches
* Speed of table joins
* Self imposed limit on number of rows before the query fails.  In the above example, the largest autonumber value supported is 99999.

Author Comment

ID: 13409258
i really dont mind adding a new column and assigning it a value that increments and leaving the primary key alone.....im just having trouble with that.....another reason why i need this is because another field in another table i have is the ID....primary key....and a customernumber.....the customernumber will be the format yymmdd+1.....i cant it to add 1 after each record.  ill try what moorhouse and aikimark suggested:

here is my code:
function TForm1.GetNextNumber : integer;
qryNumber.active = false;
qryNumber.SQL.Add('Select number from tblQuote);
qryNumber.Active = true;

if qryNumber.RecordCount >= 0 then
result := qryNumber.FieldByName('number').AsInteger + 1;
result := 1;

procedure TForm1.SetNextNumber;
tblQuote.FieldByName('number').AsInteger := GetNextNumber;

then i call the field when i insert a new record on the form.....ive used the OnChange event on one of my required textboxes

It keeps giving me the same last record......

Accepted Solution

Qosai_DBA earned 210 total points
ID: 13409349
Hi whythetorment,
I hope this code will help you:

function TfrmMessageOut.CreateMessageNo: String;
var vResult: String;
  vResult:= untFunctions.GetFormatedSysdate('yy', dmfMessageOut.qrFind); // this function return '05'
  vResult:= 'O' + vResult + '-';

    'SELECT LPAD(NVL(MAX(SUBSTR(MSG_No, 5, 4) + 1), 1), 4, ''0'') MaxNo ' + #13 +
    'FROM   Messages '                                                    + #13 +
    'WHERE  MSG_Type = :MsgType');
  dmfMessageOut.qrFind.DeclareVariable('MsgType', otInteger);
  dmfMessageOut.qrFind.SetVariable('MsgType', untConst.MSG_TYPE_OUT);

  vResult:= vResult + dmfMessageOut.qrFind.FieldAsString('MaxNo');

  Result:= vResult;

This is an Example on Oracle Database.

LVL 31

Expert Comment

ID: 13409401
>result := qryNumber.FieldByName('number').AsInteger + 1;

This is giving you the number stored in the currently focused record, not the Last record.  You need to be sitting at the Last record for this to give you the value: "highest value +1".

Be careful with using OnChange.  If you have an existing record in the table it will renumber that record.  My understanding is that you wish to give the next inserted record an Autonumber, which is then frozen (i.e., Read Only) for the life of that record.

Aikimark's idea will certainly also work.  It depends what kind of "baggage" you are comfortable working with.  In my case the baggage is the event code which is called ***only upon inserting a new record into the table#***.  Aikimark's solution splits one field into two which is (if I understand the idea correctly) baggage in the sense that when you need to report on that field, you need to remember that it is in fact two fields.  

The point about speed of operation of aikimark's method is agreed.

# as opposed to changing the content of an existing record.
LVL 46

Expert Comment

ID: 13409921
My comment had nothing to do with the customer number issue.  I only addressed the creation of an alphanumeric key from an autonumber field.

Just because it CAN be done doesn't mean that it SHOULD be done...
Although we don't know all the requirements and restrictions this software system imposes on us (our solutions), I would wager that all the experts would urge you to make all your primary keys numeric autonumber fields.  This just seems like poor database design to me.  The best course of action would be to convert all tables text keys into a purely numeric autonumber column and a text column.  The programs can still access this information as one field through a view/stored query/stored proc.

A good maxim to remember: The best coding in the world won't overcome poor design.  The corollary is also true.

<<another reason why i need this is because another field in another table i have is the ID....primary key....and a customernumber.....the customernumber will be the format yymmdd+1.....i cant it to add 1 after each record.>>

1. I'm not sure I understand this entirely or sufficiently.
2. Is this a different problem or somehow related to the original question?
3. Creating Customer numbers (key) values with this format is bad news.  Adding one to a date makes it the next day.
4. Creating such Customer numbers programmatically can lead to integrity problems when multiple Customer rows are being added at the same time from different locations.

Although there are multiple solutions to this problem, I refer you to the preceding design comment and ask you to help us better understand this new requirement.

Ask yourself and your boss a lot of "WHY?" questions.

Author Comment

ID: 13469875
nevermind.........called max on number in quote and added to that. PS:  had two fields...one auto (primary) - which i left alone.....one that is text....that i just changed to Q1,Q2 etc.

Featured Post

Free Tool: Path Explorer

An intuitive utility to help find the CSS path to UI elements on a webpage. These paths are used frequently in a variety of front-end development and QA automation tasks.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Introduction I have seen many questions in this Delphi topic area where queries in threads are needed or suggested. I know bumped into a similar need. This article will address some of the concepts when dealing with a multithreaded delphi database…
Introduction Raise your hands if you were as upset with FireMonkey as I was when I discovered that there was no TListview.  I use TListView in almost all of my applications I've written, and I was not going to compromise by resorting to TStringGrid…
Add bar graphs to Access queries using Unicode block characters. Graphs appear on every record in the color you want. Give life to numbers. Hopes this gives you ideas on visualizing your data in new ways ~ Create a calculated field in a query: …
Have you created a query with information for a calendar? ... and then, abra-cadabra, the calendar is done?! I am going to show you how to make that happen. Visualize your data!  ... really see it To use the code to create a calendar from a q…
Suggested Courses
Course of the Month9 days, 6 hours left to enroll

764 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question