how to clear auto number fields and automatically increment the number for the next entry.

[mezta21]

Hi fellas,

I'm new to MS ACCESS, I would like to know if there are any possible solution or way to clear an auto number text field after entering data and automatically increment it before entering new data. I tried to read and watch tutorials on the internet and it seems like none of them show's the right way to do it. I even follow along with this tutorial on youtube ( https://www.youtube.com/watch?v=Ri2Y9-16AEo&t=184s ) but the video doesn't seem to be the standard way on how to create an ID text field that automatically assigns ID number because on the video it shows that he/she type ID number manually which it should automatically created and incremented.



This next image is the error code which I get when I clear the clear button.


The below codes are the codes which I saw from the video.

Option Compare Database

Private Sub Command31_Click()
    
    CurrentDb.Execute "INSERT INTO info(EmployeeID, EmployeeName, Gender, Age, DateOfBirth) " & _
            " VALUES(" & Me.txtEmployeeID & ",'" & Me.txtEmployeeName & "','" & Me.txtGender & "','" & Me.txtAge & "','" & _
            Me.txtDateOfBirth & "')"
    
    infoSubform.Form.Requery
    
End Sub


Private Sub Command36_Click()
    
    Me.txtEmployeeID = ""
    Me.txtEmployeeID = ""
    Me.txtEmployeeName = ""
    Me.txtGender = ""
    Me.txtAge = ""
    Me.txtGender = ""
    Me.txtDateOfBirth = ""
    
    Me.txtEmployeeID.SetFocus
    
End Sub


Private Sub Command37_Click()
    DoCmd.Close
End Sub

Thank you,

[ranman256]

to reset autnum back to zero, empty the table, compact the db.
or
if you want to pickup where you left off, ...
if the last record is 123,
then the next rec entered is 201, then compacting the db, will start the autnum at 124.

you cannot keep data in the table, and restart the autnum at 1.
it doesnt need to.
Its just an internal # for tables to relate.

[orange]

Read this article on Autonumbers.
to better understand

clear an auto number text field

You should research data types

Good luck

[Micron]

Definitely read up on autonumbers as you're trying to make meaningful use of them when you should not.
As for your error, you are trying to put letters where only numbers are allowed. At least that's what your sql statement is telling me. In addition, it is not a good idea to store calculations unless necessary, and it is not necessary to store age. You provide a calculated control on your form or report to do that.

[mezta21]

I reviewed my post and I found that I mistakenly made a double entry in the clear statements on the txtEmployeeID, but I re-construct it and re-run the form and also typed a number on the age and date of birth field which I also found that I mistakenly used text instead of numbers.

Here is the code which I reconstructed.

Option Compare Database

Private Sub Command31_Click()
    
    CurrentDb.Execute "INSERT INTO info(EmployeeName, Gender, Age, DateOfBirth) " & _
            " VALUES(" & Me.txtEmployeeName & " ,'" & Me.txtGender & "','" & Me.txtAge & "','" & _
            Me.txtDateOfBirth & "')"
    
    
    infoSubform.Form.Requery
    
End Sub


Private Sub Command36_Click()
    
    Me.txtEmployeeID = ""
    Me.txtEmployeeName = ""
    Me.txtGender = ""
    Me.txtAge = Null
    Me.txtGender = ""
    Me.txtDateOfBirth = Null
    
    Me.txtEmployeeID.SetFocus
    
End Sub


Private Sub Command37_Click()
    DoCmd.Close
End Sub

And here is here is the error code that I'm getting when I click the clear button.

[ArviLaanemets]

I don't know what is the type if EmployeeID field, but definitely it is not autonumeric! In subform in your last picture you have 1000 for next new record displayed, what is very strange - in any reasonable design all controls of this row must be empty until you start with entering data into it.

What is field type for EmployeeID?
What is default value for EmployeeID?
What is format of texbox displaying EmployeeID?
What is default value for texbox displaying EmployeeID?
Is there some function/procedure calculating EmployeeID for new record? When yes, then from where is it called (and can you post the code of function/procedure)?

[mezta21]

I just want to clarify that I only follow the video which I saw from youtube, but it doesn't quite satisfy me because the field type of the ID which they used is Number not an auto number.

[mezta21]

Hi,

What I want is very simple I just want to make a simple data entry, no calculations or what so ever, what I want to achieve from this exercise is when I fill up all the text fields and hit add button it should reflect directly into the subform and clear the fields automatically or I will hit the clear button to clear all the fields so I can enter new entry. And after all the fields are cleared then if I start typing in the text fields the ID should automatically generate a new number.

[ArviLaanemets]

Here is an autonumeric field setup. Autonumeric means, that whenever you start a new record for this table (directly in table, or through form), the to counter for autonumeric field is added 1, and the field gets this counter as value. User can't enter anything into autonumeric field - usually on form the control with autonumeric field as source is set to be hidden. The main purpose using autonumeric field is to create an unique PK Key for table without any hassle.




To add a new record into form you can simply click on New Record button (a tiny * shaped button on last position at bottom of form), use shortcut, or select 'New Record' from menu. I don't think at current level you need fancier ways to add a record.

[mezta21]

As you can see on the image I'd like to have these buttons ADD, EDIT, DELETE, CLEAR (Close is optional).

This is the video which I followed from youtube

https://www.youtube.com/watch?v=Ri2Y9-16AEo&t=184s

[davegri]

The problem is your code is trying to save a required field (EmployeeID) as "".

Change your table design so that EmployeeID is not required. This appears to be the approach taken by the video.
Then you might add code to ensure that EmployeeID is not left blank by the user.