AutoNumber field in a table while in form view

[marcin2k]

I have an application where on my main form I have a subform acting on a table lets say called tTempItems. This subform is used so that people can add in as many items as they can. The table has the following fields:
ItemID
ItemNum
ItemDesc
Qty

I need to make it so that as they fill each record on the form the ItemID is automatically updated since it contains the number of items. Such as Item 1 or 2 or 3. If i make this field autonumber it works ok but once I close the form and clear the table for the next person to type in an order it does not start at 1 anymore. I use this data for output in a txt file so I don't store it in any tables for later use, its all temporary.

Any help would be great,
Marcin

 

[PHV]

I close the form and clear the table
Why not deleting and recreating the table instead ?

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

 

[TheAceMan1]

How are ya marcin2k . . . . .

In a module in the modules window, copy/paste the following routine:

[blue]Public Sub ResetTable(tblName As String, autoName As String)
   [green]'Clears the table and sets next autonumber to 1.
   '[b]autoName[/b] is autonumber fieldname of [b]tblName[/b].[/green]
   
   Dim sqlDel As String, sqlIns As String
   
   sqlDel = "DELETE * " & _
            "FROM " & tblName & ";"
   
   sqlIns = "INSERT INTO " & tblName & " (" & autoName & ") " & _
            "VALUES (0);"
   
   DoCmd.RunSQL sqlDel
   DoCmd.RunSQL sqlIns
   DoCmd.RunSQL sqlDel
   
End Sub[/blue]

Example routine call:

[blue] Call ResetTable([purple][b]YourTableName[/b][/purple], ItemID)[/blue]

See Ya! . . . . . .

 

[RoyVidar]

TheAceMan1 - is that version dependent? I can't seem to make that work on my setups, while running the following SQL does seem to work (as long as the field isn't used in relationship with other tables)

[tt]"ALTER TABLE myTable ALTER COLUMN myAutoColumn COUNTER(1, 1)"[/tt]

(remember to delete first, else you're getting trouble;-))

Here resetting to start with 1. This is picked from a Microsoft KB article, dealing with an Autonumber bug BUG: You may receive an error message when you try to insert a new record in a table that contains an Autonumber field in Access 2003

But - trying to use Autonumbers to get sequential/consecutive numbers is in my view using Autonumbers for something it's never intended to. It's OK to use Autonumbers to get a unique record number, to use for identifying the record (primary key), except for the above mentioned bug, which is reasonable easy to deal with, but assigning any business meaning to it and/or letting the user see it, is (in my view again) asking for trouble. What happens if the user hits cancel while registering? The "number" is lost, and the next record will get the next number ...

For this challenge, especially if consecutive numbers are important, a delete of records, and some DMax/Select Max(yourfield) + 1 thingie is perhaps a better approach?

Roy-Vidar

 

[TheAceMan1]

Howdy RoyVidar!

[blue] is that version dependent?[/blue]

[purple]Apparently Yes![/purple] The code was written/tested in 2k last night. I'd just sat down this mourning to check 2002 platform and saw your post. Since you've already tested, of course . . . no go!

The SQL you provided does work on both platforms, and yes, it matters if the autonumber is involved in a relationships (same as trying to delete indexed fields in a relationship).

I couldn't agree with you more on the use of autonumber, but [blue]marcin2k[/blue] is writing to a file per user, making this one rare case where its appropriate to restart at 1.

[blue]Take care Roy![/blue]

See Ya! . . . . . .