Open form in MS Access that does not get next autonumber

Several things that you can do:

1- Make mandatory "required" fields on the table.  If they exit the form w/o entering one of the required fields on the table, the users will get an error like "cannot have null values" "this record will not be saved"

2- Write data validation code to check for null values on the form on the OnClose event.  If there are null values let the user know and close the form w/o saving the records.  Enhance the code to allow the user to enter the missing data.

Here is a bit of code that you can use on the OnClose event

Private Sub Form_Close()

    If IsNull(Me.txtDate) Then
        Beep
        MsgBox "Missing Date", vbCritical, "TCMI"
        docmd.close
    end if
End Sub

I understand what you are saying computerwiz, but one thing comes to mind.  Since this is a multi-user db, the problem still exists.  If user x opens the form and gets autonumber 11 and user y (in another office) opens form while user x is filling out the form, user y will get autonumber 12.  User x changes mind and doesn't save form. Then you have a gap in numbering.  Autonumber 10 was added 11 not saved a 12 added.  Then you autonumber field will look like eg....5,6,7,.....10,12.

k, if that's a concern, then do not link the form to the table or query.  It will be an unbound form.

Create a "SAVE" button that executes a sql statement that inserts the record into the table.

Private Sub cmdSave_Click()
On Error GoTo Err_cmdSave_Click

    Dim sSQL As String

    sSQL = "INSERT INTO tbl_employees ( Last, First, Social) SELECT "
    sSQL = sSQL + Forms!frm_Input.txtLast & ","
    sSQL = sSQL + Forms!frm_Input.txtFirst & ","
    sSQL = sSQL + Forms!frm_Input.txtSocial & "';"
   
    DoCmd.RunSQL (sSQL)
   
   
Exit_cmdSave_Click:
    Exit Sub

Err_cmdSave_Click:
    MsgBox Err.Description
    Resume Exit_cmdSave_Click
   
End Sub

Computerwiz, is there a way to change a bound form, created from a query to an unbound form?...or do I have to create the unbound form from scratch??

I suppose you can remove the reference from the "Record Source" property of the form.  You'll have to make sure to remove the record source from each control field.

Computerwiz, I created a copy of my form, and have unbound all the bound fields.  I am now going to work on the SQL Statement to post the data to the table.  I will let you know.

Is there another way besides SQL Statement to accomplish the population?  

What I would probably do is take off the autonumber of Record ID.  Then, and this is where it might be tricky, read the last Record ID and then increase that number by 1.  In my opinion it would be best to do this once the "save" button was pressed.  

The logic would go something like this:
-Record ID is set as inactive
-User fills out all of form
-User presses save button
-Save button initiates a RecordID process which retrieves last record ID, adds 1 to it
-New RecordID value is set to equal the value of the RecordID field on the FORM(which is probably tied to the table already)  This sets your new record ID for this record.
-Then the completed form is saved a record.

I'll try physically work this out later.

What I would probably do is take off the autonumber of Record ID.  Then, and this is where it might be tricky, read the last Record ID and then increase that number by 1.  In my opinion it would be best to do this once the "save" button was pressed.  

The logic would go something like this:
-Record ID is set as inactive
-User fills out all of form
-User presses save button
-Save button initiates a RecordID process which retrieves last record ID, adds 1 to it
-New RecordID value is set to equal the value of the RecordID field on the FORM(which is probably tied to the table already)  This sets your new record ID for this record.
-Then the completed form is saved a record.

I'll try physically work this out later.

I am going to try and tackle the SQL statement and see what happens.  I will let you know the outcome.

Another option is to keep the form bounded. However you will have an attachment to a global database's table with a field that happens to be autonumbered and a userid (i.e. MyNumber and UserID). The premise is that when you save your data (i.e. BeforeUpdate event) you add a record to this table (insert a userid so that users don't pick up numbers generated for other users) then ask for the contents of it immediately after. Then you can add this value to the form's text box connected to the data source you are bound to. Then get rid of the record to keep table size small.

'Put in BeforeUpdate
   If Me.NewRecord Then
      Me.txtEID = CreateEID()
   End If

' Put in AfterUpdate
   ReleaseEID

Public Function CreateEID() As String
' Pop the tblGnum so it creates a new record and stuff your ID into it so no one else
' can use it. Now we can use it as the retrieval filter and the delete filter and it
' will ongoing basis clean it's own self! Wow!
' Primarily used by BeforeUpdate event in frmEvals*
   DoCmd.SetWarnings False
   DoCmd.RunSQL "INSERT INTO tblGnum ([User]) VALUES ('" & Evaluator & "')"
   DoCmd.SetWarnings True
   DoEvents
   CreateEID = DLookup("[EID]", "tblGnum", "[User]='" & Evaluator & "'")
   DoEvents
End Function

Public Function ReleaseEID() As String
' Remove the EID record that was created in CreateEID() now that it has been stored so
' that the gnum table in the corporate database stays small and quick! (Self cleaning)
' Primarily used by AfterUpdate event in frmEvals*
   DoCmd.SetWarnings False
   DoCmd.RunSQL "DELETE * FROM tblGnum WHERE [User]='" & Evaluator & "'"
   DoCmd.SetWarnings True
End Function

If you have problems with Access locking down the entire table (with the autonumber), you will have to coordinate that (retry). I have not had problems yet.

Ok... I got it working perfectly...

I made a database with a numeric field called RecordID.  I inserted a few records with test data, and put number 1 in the first record ID spot, #2 in the second record ID spot.  I then created a button on my Access form, which in turned popped open a button wizard.  I used the standard SAVE RECORD procedure it offers through this wizard.  Then, I went and greated a GetRecord procedure on my own to assist in the creation of the new ID.

Logic:
-Saves partially completed record (no ID yet)
-initiated GetRecord
-goes back to previous record, puts its recordID into a holder
-adds 1 to holder value and puts into new variable (NewID)
-goes forward to last record created (the one without the ID) and inserts value from NewID into RecordID field
-resaves COMPLETE record.
-done.
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
Option Compare Database
Dim holder As Integer
Dim newID As Integer

~~~~~~~~
Private Sub GetRecord()

DoCmd.GoToRecord , , acPrevious
holder = RecordID
newID = holder + 1
DoCmd.GoToRecord , , acNext
RecordID = newID

End Sub
~~~~~~~~~~~~~~~
Private Sub SaveButton_Click()

On Error GoTo Err_Command3_Click

    'first save
    DoCmd.DoMenuItem acFormBar, acRecordsMenu, acSaveRecord, , acMenuVer70
    'initiates get record procedure
    GetRecord
    'second save
    DoCmd.DoMenuItem acFormBar, acRecordsMenu, acSaveRecord, , acMenuVer70

Exit_Command3_Click:
    Exit Sub

Err_Command3_Click:
    MsgBox Err.Description
    Resume Exit_Command3_Click
   
End Sub

Let me know if you have questions, or want this sample database I created.

Computerwiz,  I get errors when I try and execute the sSQL script at the DoCmd.RunSQL (sSQL).  Any ideas?  It says I have a syntax in INSET INTO Statement.

Computerwiz,  cancel the last note.  

the quotation marks can give you a little trouble  " ' "

there is a single (') quote in the middle of the (") quotes

Computerwiz, I did not use you solution direcly.  I used a mix of your thoughts and ideas as well as my own to acieve the desired results.  I incresed the point to 40...enjoy.

TinLemon, I appreciate you ideas as well.  

Lots of good ideas from the Computerwiz, a truly knowledgable source.