hi,

I have put the format of an autonumber as "A0000" , in the table the results are "A0001, A0002 ....(and so on)" when i bound to a form it will display only "1, 2 ... (and so on)". Why i cannot get the display of "A0001" in the form? Actually my purpose is to let access generate a document number for each record. or if there is another way to generate a document number in stead?
Anyone can help me plz..?
Thanks !

Generate the document number in your queries, or forms and reports, not in the table. The PK of a table should NEVER be meaningful data.

mmcdonal

It is good practice to create a table only for the purpose of numbering documents, and there you might use an extra field for text identification.

It sounds redunctant, yet you should give it a try.

Thanks for your replies

To elaborate on what mmcdonal said, an autonumber (used as a primary key, i.e. PK) in a table is of type long integer and is created by the computer. It does not have any real meaning other than reference the record in question and relate it to other records in other tables. Users never see it. It works behind the scenes.

"A0001" is of type string and it seems to hold some meaning people want to see in reports and forms. Therefore it is NOT an autonumber. It's just a label that is consecutively numbered. In queries, you can find out what is next in line by using the DMAX function and adding one to the numerical portion of the label.


Greg Serrano
Michigan Dept. of Environmental Quality, Air Quality Division

Thank you SerranoG

i have successfully generated the continuously number by using Dmax function, but i dont know how to set the format to "A00001"..
anyone can help me plz?

the function looks like this:
--------------------------------------------------------------------------------------------------------------------------------
Public Function GetNextNum() As Long
    Dim db As DAO.Database
    Dim rs As DAO.Recordset
    Dim lngCurNum As Long

    Set db = CurrentDb
    Set rs = db.OpenRecordset("SELECT [DocNo] FROM [receive] ORDER BY [DocNo] ", dbOpenSnapshot)

    With rs
        .MoveNext
        GetNextNum = DMax("DocNo", "receive") + 1
    End With

exit_Routine:
    Set rs = Nothing
    Set db = Nothing
    Exit Function

err_Routine:
    MsgBox Err.Description
    Resume exit_Routine

End Function
--------------------------------------------------------------------------------------------------------------------------------

thanks a lotz...

To get a number into the format "A00001" you have to use the FORMAT statement.

GetNextNum = DMax("DocNo", "receive") + 1
NewDocNo = "A" & Format(GetNextNum, "00000")




Greg Serrano
Michigan Dept. of Environmental Quality, Air Quality Division

Thanks SerranoG..

Now I have applied the NewDocNo, but i got the error 13 : type mismatch.

NewDocNo = DMax("DocNo", "receive") + 1
GetNextNum = "A" + Format(NewDocNo, "00000")

I declare the GetNextNum and NewDocNo as Variant.
the code is doing well without the "A" in the NewDocNo .
my DocNo in the table is Text.
What is my mistake here?

Thanks ...

Your mistake is that you got it backwards. You assigned the number to NewDocNo and the label to GetNextNum. Reverse that, i.e.

GetNextNum = DMax("DocNo", "receive") + 1
NewDocNo = "A" + Format(GetNextNum, "00000")


Greg Serrano
Michigan Dept. of Environmental Quality, Air Quality Division