MS Access: I want a data entry-enabled form to auto populate an autonumber, at the moment all I'm getting is (New)

Question

I have a popup form that fills in 4 fields that will be invisible in my form. Then I click a button, this opens a form with an autonumber field, before this was on a data entry form this autonumber field populated itself as I ran through the navigation, but since I only want the user to be able to have access to one field (I will have a button that will save and update the records instead of making a new one every time) I cannot have a record navigation.

From my little experience in access the only 3 ways I can imagine solving this (in the order I'd rather do them) are:

Have a way for a form to start up with a new record without data entry mode.

Having a way to populate the autonumber on the form whilst it generates in data entry mode.

Having a way for the button on the initial popup form to autoincrement the autonumber by 1 and set the autonumber as that.

MY update query is:

UPDATE AdvisorInput SET AdvisorInput.PSTNVolume = (([Forms]![frmInput]![txtPSTN])+([Forms]![frmInput]![txtPSTN2])), AdvisorInput.BBVolume = (([Forms]![frmInput]![txtBB])+([Forms]![frmInput]![txtBB2])), AdvisorInput.TVVolume = (([Forms]![frmInput]![txtTV])+([Forms]![frmInput]![txtTV2])), AdvisorInput.FibreVolume = (([Forms]![frmInput]![txtFibre])+([Forms]![frmInput]![txtFibre2])), AdvisorInput.C2FUVolume = (([Forms]![frmInput]![txtC2FU])+([Forms]![frmInput]![txtC2FU2])), AdvisorInput.PSTNCease = (([Forms]![frmInput]![txtPSTNCease])+([Forms]![frmInput]![txtPSTNCease2])), AdvisorInput.BBCease = (([Forms]![frmInput]![txtBBCease])+([Forms]![frmInput]![txtBBCease2])), AdvisorInput.TVCease = (([Forms]![frmInput]![txtTVCease])+([Forms]![frmInput]![txtTVCease2]))
WHERE (((AdvisorInput.AdvisorEIN)=[Forms]![frmInput]![txtEIN]) And ((AdvisorInput.CSS_WEEK)=[Forms]![frmInput]![txtCSSWeek]) And ((AdvisorInput.WeekdayName)=[Forms]![frmInput]![cboWeekday]));

Thanks very much for your time.

E Mett E Mett · Accepted Answer · 2014-03-12T11:08:45

The autonumber field only gets a value when at least one field has been entered. You could have hidden field and enter the value using code.

BTW Once the autonumber field has used a number, the next time it will used the next number even if the previous record was deleted. Compacting the database resets the seed number to the maximum currently in the database, so does using an INSERT statement and explicitly inserting a value for the autonumber field.

To open a form with existing records directly with a new record:

Private Sub Form_Open(Cancel As Integer)
    DoCmd.GoToRecord , , acNewRec
End Sub

MS Access: I want a data entry-enabled form to auto populate an autonumber, at the moment all I'm getting is (New)

1 Answers