Autonumber

[pgp]

Hi,

Is there a way to change the datatype of a field now from autonumber to number?

When I try changin this, it asks me to remove any relationships the table might be involved in . I did delete all the relationships, but too not able to change the data type from autonumber to number

Would appreciate your input/concerns
Thanks in advance

 

[ColinEssex]

You'd probably be better off creating a new field and leave the AutoNumber field.

Col

 

[jaydwest]

You should be able to change an AutoNumber to a number unless the autonumber field is in a relationship.

How are determining what the relationships with the table are? If you use the relationships window, try clicking on the Show All Relationships button. Often all the relationships are not automatically shown in the window.

It this doesn't show a relationship, you may also try to use the documenter and select the table and options to show relationships.

If these fail, next step is to repair and compact.

Good Luck! If you have any questions, please reply.

 

[pgp]

Thanks so much for your response!

I have 3 similar forms - Quote , ReviseQuote & LookUpQuote forms which have same set of fields to be displayed.

WHen I go to Quote form to create a quote - say I get quotenumber auto generated as 433

When Im done with quoting, go to ReviseQuote form, enter the quotenumber , say 433 to revise - this creates a blank record in quoteheader table with quotenumber 434.

Is it due to the form or due to the autonumber field?
In the quoteheader table, I do have more than one record for the same quotenumber( but diff revisionnumber), will this give me any problems since autonumber doesnt support primary key values?

I have both my quotenumber & rev number as primary field in the quoteheader table!

Thanks in advance for your inputs!!

 

[jaydwest]

NO! NO! NO! NO!

YES! YES! YES! YES!

1) Using an AutoNumber as your Quote Number with a Revision number is a NO! NO!.

2) I suggest you create a new field, independent of your Quote Number and Revision number. Call it QuoteRevID. This will uniquely identify each record. Some others on this site may disagree but single field primary keys are faster to use and easier to create queries.

3) In your QuoteNumber field. Set this as a number as you have done. We use a function with a query to automatically get the next quote number only when you want to use a new quote number.

---

Private Function GetNextQuoteNumber() as Long

Dim mySQL as String
Dim rst as DAO.Recordset

mySQL = "SELECT Max(QuoteNumber) AS MaxOfQuoteNumber FROM <Your Table Name>; "
set rst = CurrentDB().OpenRecordset(mySQL)
IF not rst.EOF then
GetNextQuoteNumber = NZ(rst!MaxOfQuoteNumber, 1)
Else
GetNextQuoteNumber = 1
End IF

-----------------------
Entering the Quote Number in a record to select the record is a NO! NO!. I suggest you check previous posts for instructions on how to use a Find Field (also known as For Field or Search Field). You need to create a separate unbound control on your form to find the record.

Jay

 

[pgp]

changing autonumber type

Thanks again for your input.

I had something similar in mind. To create a function that would increment the quote number. Unfortunately, the project that Im working on was already started by someone else, and now to patch that up with so many changes is a pain.

Im trying as much as I can to patch this system already created.

With your input, I tried changing the autonumber type..bt in vaib,
even though i delete the relationships, compact do everything, it says "first delete relationship in which this table is involved in"

How do I solve this...? Is there any othere way to find out the relationships?

I tried documenter too...!!

But, when I delete the relationship, save this, and come again to the relationship window, click show all, it shows the relationship still!!

how is this? thought i had deleted it?

Thanks soooooooo much for all your inputs! its of great help!