Reset AutoNumber in a Form 1

[Valeriya]

Hi, guys. I've created a Form for Project Logs, that pulls data from a table that has primary key as AutoNumber data type and its field name - "Project #"...In the form, one of the objects is a bound object called "Project #" that pulls the project# from that table. Here is my problem: Say the last project that was added to the form is Project # 50...When I started adding the next project, number 51 shows in the "Project #" field. However, if I changed my mind and did not save that 51st record, the next time I try to add a project the "Project #" field shows 52, instead of 51. The bottom line is, somehow there is an internal counter that increments the the "Project #" as soon as I click "add new record" regardless of weather I save the record or not. So far the only solution I found, is to add a new "Project #" field to the table and delete the old one, but, it is extremely inpractical to do it that way every time. Another solution that I tried was to write ans SQL query in the vba code that would be something like this. To the click event on "add new record bottom" I added the following code:

Private Sub Add_Record_Click()
    Dim strSQL As String
        
    On Error GoTo Err_Add_Record_Click
         
    DoCmd.GoToRecord , , A_NEWREC
    
    strSQL = "SELECT Max(SupplyChainProjectList." _ & 

    "[Project"  #]) AS [MaxOfProject #]" _ &

    "FROM SupplyChainProjectList;"

    Me.Project_ = CLng(strSQL)+ 1

Exit_Add_Record_Click:
    Exit Sub

Err_Add_Record_Click:
    MsgBox Error$
    Resume Exit_Add_Record_Click
    
End Sub

This code is supposed to find the maximum value for the field "Project #" and add 1 to it, when adding new record to Form. For some reason it does not work and gives me a "mismatch error"

Thank you very mcuh!

Valeriya

 

[FoxProProgrammer]

Autonumber fields are often used when you need to relate one table to another table. You would normally make the Autonumber field the Primary Key in the table. If you want your project numbers to be sequential and without any gaps, you could create another field in [SupplyChainProjectList] that has data type "Number". This might create a problem, however, because you would have to keep track of the project numbers and make sure that they aren't duplicated. I'm just curious...why does it matter if the next project number is 50 or 51, and why does the user need to see this number? One would typically use the Autonumber field to relate the record to a child table, and use other fields to display information about the record to users. If [Project #] is Autonumber, why are you trying to add 1 to it? Access automatically increments this field when you add a new record.

dz
dzaccess@yahoo.com

 

[MajP]

This does not look right:
"[Project" #])
Me.Project_ = CLng(strSQL)+ 1

Also. This is not going to work anyways. Your field is currently an autonumber field, but you are trying to set the value. You can not set the value of an autonumber field. For this to have a chance of working, you need to delete field "Project #", add a new field "Project #", and set it to datatype long. Do not bother trying to change the datatype from AutoNumber to Long, you can not once there is data in it.

Do yourself a favor and to not name fields with spaces in them.
Bad "Project #"
Better: "Project#"
More better: "lngProjectNumber

 

[ZmrAbdulla]

Note: Pound Sign(#) is reserved in Access. Try to avoid that.

See more here

http://support.microsoft.com/kb/q209187/

________________________________________________________
Zameer Abdulla
Help to find Missing people
Sharp acids corrode their own containers.

 

[PHV]

Have a look here:

http://www.tek-tips.com/faqs.cfm?fid=5106

http://www.tek-tips.com/faqs.cfm?fid=184

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

 

[Valeriya]

hi, guys. I was not real clear when asked the question. I tried two approaches. One - to set primary key as AutoNumber but as you can tell it is giving me problems, and second approach - set Primary key as Number data type...That is wher I tried to use the code that I originally supplied to you guys.

Thanks!

Valeriya

 

[PHV]

Have you read the FAQs I suggested you ?
If you don't have multi-user issue you may consider the DMax function.

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

 

[Valeriya]

Many thanks to all of you!

PHV, the DMax was a great soultion. It works fine now.

Thanks again to everyone!!!

Valeriya