re-setting database...

[ArcherSteve]

is there an easy way to delete all records on the main table and re-setting the autonumber back to 1? right now i have to manually delete all the records, delete the autonumber field, create new field with the same name as the old one and then set it up to autonumber (btw...the autonumber is the primary key.)

I would also like a msg box pop up confirming the resetting of the database. I know that can be done in code but i haven't quite mastered it yet.

Any help is greatly appreciated.

 

[ghudson]

Did you search this forum for an answer?

Check this thread out... Create an Autonumber field

Use this to delete all the records from your table...

CurrentDb().Execute "DELETE * FROM YourTableNameHere"

HTH

 

[ArcherSteve]

i looked but must have skipped over that one....thanks. I'll look it over

 

[ArcherSteve]

Ok, well i'm trying to get the "delete all entries" part working first. For some reason, i can't get this to work and i can't figure it out. Any help is greatly appreciated.

 Private Sub Command4_Click()
Dim Msg, Style, Title, Response, MyString
Msg = "Do you want to reset the program?"    ' Define message.
Style = vbYesNo + vbCritical + vbDefaultButton2    ' Define buttons.
Title = "Reset Program"    ' Define title.
        ' Display message.
Response = MsgBox(Msg, Style, Title)
If Response = vbYes Then    ' User chose Yes.
    CurrentDb().Execute "DELETE * FROM [Archer's Data]" ' Resets Program.
Else    ' User chose No.
    DoCmd.Close ' Close form without resetting.
End If


End Sub

 

[ArcherSteve]

while i was at it...i was looking over your other post about creating a new autonumber field. using

Public Function AddTableField()
    Call CreateAutoNumberField("YourTableNameHere", "YourFieldNameHere")
End Function

as the code. How would I go about deleting the old field using code?

Thanks again.

 

[ghudson]

Not sure why it does not work for you. I tried your code as is and it worked for me.
You need to read up on using proper naming conventions with your db objects.
Using spaces and special characaters ('.@#$%^&* etc. is a big no no with
programming. For simplicity, use this instead of your code [five lines of code
instead of the wizards eleven. This will delete all the records in the table [not
delete the table].

    If MsgBox("Do you want to reset the program?", vbCritical + vbYesNo, "Reset Program") = vbYes Then
        CurrentDb().Execute "DELETE * FROM [Archer's Data]" ' Resets Program.
    Else 'User clicked No
        DoCmd.Close 'Close form without resetting.
    End If

HTH

 

[Oldsoftboss]

It may sound a bit simple but if you highlight the table in the database window and go Ctrl +C, Ctrl + V, a window will pop up asking you for a new name and whether you want the structure and data or not. Just select the structure only and then delete your original table and rename the new one to the original name.

Dave

 

[boblarson]

If you need an ID number to start from 1 and increment by 1 for each record, you need to consider writing something to do it for you instead of relying on Autonumber. Autonumber is intended to provide you with a unique number, not guaranteed to be sequential. If you start a new record and cancel the number has been assigned and is not recoverable. Also, if you delete a record then it is not recoverable.

If you search in this forum for AutoNumber and posts by Pat Hartman, you will find out more information regarding this.

For what you are asking, it is a common misuse of Autonumber and causes you more problems than it solves by using it that way.

Sorry for the negative type post, but I thought you should get some more information.

 

[Rich]

If you just delete all the records and compact the db, the Autonumber will reset to 1. Why delete the field?

 

[ghudson]

I know the in's and out's with AutoNumber but I did have one occasion that I needed to reset the AutoNumber back to 1 before the new records were imported. I was able to do this manually by cutting the AutoNumber field, saving the table and then pasting the AutoNumber field back into the table. I was lucky enough to find the answer to my need with the code I posted near the beginning of this thread. The code works and it allows you to use VBA to quickly reset the AutoNumber field by deleting the AutoNumber field and then recreating it.

Of course there are those who use the AutoNumber field incorrectly and they will soon discover the error of their ways [and they will probably post a question asking for help].

 

[ArcherSteve]

the reason i am using autonumber was to assign each archer a unique ID #. By using autonumber and setting it as primary key, i was able to search for the archer by using the ID #. Having the ID # equal the record number, this made it exponentially easier. Now, I want to be able to hit one button and it deletes all information from the ArcherData table (I took your advice about the spaces and special characters and changed everything to make it easier) and the resets the ID # to 1 for the first archer inputted (so that after resetting, the ID # still coresponds with the record #). I know that I can easily do this myself by compacting the db or any other "easy" ways of doing this. But I need to be able to give this database "program" to someone who has no experience in Access and have them be able to use it. So by making it "hit one button, does everything" is really important. I think i really should head over to the book store and get some MS Access VB books.....

Please, any help is greatly appreciated.

Thanks,
Steve

 

[ghudson]

What part is not working for you? Be specific [what is not happening, any error messages?] and post your code.

 

[ArcherSteve]

welp, i've finally figured it all out. It took a while, but i finally understand the DMax() function. By using that on the in put form, and setting the default value on the table for the new ID to "1", I am now able to create the ID #'s like i need without using the AutoNumber. To reset the database, all i simply need to do now is delete all the entries in the table.


THANK YOU ALL FOR EVERYTHING!

Steve