I have a form where in the underlying table the primary key is set to autonumber. Then I have 6 other fields on this form for data input by the users.

On this form I have 3 action buttons. The first one the go to a new record, the second one to save the record and navigate back to the previous menu and a thirth one to navigate back without saving the record the user is working on.

This thirth action button is the one that I have a problem with. Because the primary key is autonumber, the record is automatically saved on exit if the user has put some information in either one of the 6 fields. What I would like is that the current record that the user is filling in is NOT saved on exit, but only navigation back to the previous menu.

The code I have so far is:

Private Sub cmdTerugZonderBewaren_Click()
On Error GoTo Err_cmdTerugZonderBewaren_Click

    Dim stDocName As String
    Dim stLinkCriteria As String

Select Case Me.Afdeling.Value
    Case "BA01"
        stDocName = "frmInlogBA01"
    Case "BA02"
        stDocName = "frmInlogBA02"
    Case "BA03Bed"
        stDocName = "frmInlogBA03Bed"
    Case "BA03Pax"
        stDocName = "frmInlogBA03Pax"
    Case "BA04"
        stDocName = "frmInlogBA04"
    Case "BA05"
        stDocName = "frmInlogBA05"
    Case "BA06"
        stDocName = "frmInlogBA06"
    Case "BA08Bed"
        stDocName = "frmInlogBA08Bed"
    Case "BA08Textil"
        stDocName = "frmInlogBA08Textil"
    Case "BA09"
        stDocName = "frmInlogBA09"
    Case "BA10"
        stDocName = "frmInlogBA10"
    Case "BA40"
        stDocName = "frmInlogBA40"
    Case "BA50"
        stDocName = "frmInlogBA50"
    Case "Family"
        stDocName = "frmInlogFamily"
    Case "ZB"
        stDocName = "frmInlogZB"
End Select

    DoCmd.SetWarnings False
    DoCmd.OpenForm stDocName, , , stLinkCriteria
    DoCmd.Close acForm, "frmToDoComin"
    DoCmd.Maximize
    DoCmd.SetWarnings True

Exit_cmdTerugZonderBewaren_Click:
    Exit Sub

Err_cmdTerugZonderBewaren_Click:
    MsgBox Err.Description
    Resume Exit_cmdTerugZonderBewaren_Click

End Sub

Where do I have to change the code so the users exits the form without saving the current record? The autonumber primary key is set to random (don't know if this is important or not). The record is always saved, because the first field is a date type with a default value. This date field is also locked...

You have to add code to delete the record. Once the user enters text in one of the fields, it is saved.

Take the PK field in a variable, and then run a delete query.

Create a delete query, but select all the fields, rather than *.
Then in the criteria line for the delete query, put this code:

[Forms]![frmMyFormName].[PKFieldName]

Call this query something like qryDELETE_CurrentRecord

Then on the navigate back without saving button, do this:

DoCmd.SetWarnings False
DoCmd.OpenQuery "qryDELETE_CurrentRecord"
DoCmd.SetWarnings True

This may not work, but give it a try. If it doesn't work, we can put in on another event.


mmcdonal

Also, in this code:

    DoCmd.SetWarnings False
    DoCmd.OpenForm stDocName, , , stLinkCriteria
    DoCmd.Close acForm, "frmToDoComin"
    DoCmd.Maximize
    DoCmd.SetWarnings True

You don't need the DoCmd.SetWarnings statements.

mmcdonal

I changed the end to

    DoCmd.OpenForm stDocName, , , stLinkCriteria
    DoCmd.SetWarnings False
    DoCmd.OpenQuery "qryToDoCominHuidigeRecordWissen"
    DoCmd.SetWarnings True
    DoCmd.Close acForm, "frmToDoComin"
    DoCmd.Maximize

but the record is still not deleted.

I first put the docmd.openquery after the docmd.close acform, "frmToDoComin", but then off course the query asked me the autonumber cause the form was already closed...

Well, I knew this would be problematic, but it can be done.
"The first one the go to a new record, the second one to save the record and navigate back to the previous menu and a thirth one to navigate back without saving the record the user is working on."
Why do you have these buttons on a bound form? Why not add a button to Add a new record, and make the On Current event of the form Me.AllowAdditions = False unless they click the button?

mmcdonal

So if I understand correctly what you mean is that I have to set the default value of the form to allowadditions=no and then on the first 2 action buttons I have to change the value back to yes before going to a next record an then putting it back immediately to no after it has moved to the new record.

I am still not sure why a user would start entering data when they were not creating a new record. Basically yes, you would have the form set to allow additions no unless they clicked a button to set this.

mmcdonal

I believe I'm going to remove the thirth button. It will make it more easy for me...

Thanks for trying to help