Autonumber......again :(

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 ;-)

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

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.

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

(checked access -> its only for display)

ok but i need the prefix.....thats the entire idea.....

>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?

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.

regards,
Kate

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.

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.

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;
begin
qryNumber.active = false;
qryNumber.sql.clear;
qryNumber.SQL.Add('Select number from tblQuote);
qryNumber.Active = true;

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

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

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......

>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.

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.

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.