CODEWITHSUNDEEP
vbams-accessms-access-2016
MyST3Ry_
MyST3Ry_

Reputation: 1

Create a new record in single form view without open arguments

I have two forms with different view linked to the same table: continuous and single form.

The continuous form lists all the records available in the table with add, edit, and delete options. The form is locked for additions, edits, and deletions.

The single form is used to load the record for addition and editing the record that the user selected in continuous form.

If the user wishes to edit a record, he has to select the record and click on edit button. Then I pass the ID (Primary key) in open arguments to "Single form" for editing.

For adding a new record, I am currently entering some null data in a column to generate ID and then send the ID to "Single form" similar to editing a record.

The problem with this method is: I am forcing the user to enter a record into the system. Currently, I don't have validation rules set and there are many blank records as the user clicks the add button by mistake. At some point, I want to put validation rules. This makes it difficult for the user to exit the form without entering some data.

Is there a way to navigate to a new record in the "single form" without creating one in "Continuous form" and undo if the user says no to "Save Changes?"

Thanks in advance!

Upvotes: 0

Views: 165

Answers (2)

MyST3Ry_
MyST3Ry_

Reputation: 1

I modified add and edit button code for continuous form to the following:

Private Sub AddButton_Click()
   DoCmd.Close acForm, "frmContinuous"
   DoCmd.OpenForm "frmSingle", OpenArgs:="Add"
End Sub
Private Sub EditButton_Click()
    ' Variables
    Dim ProdID As Integer
    Dim rs As Recordset
    ' Check if there is a record selected
    Set rs = Me.Recordset
    If rs.EOF = True Then
        MsgBox prompt:="No record selected", title:="Oops!"
        Set rs = Nothing
        Exit Sub
    End If
    ' Send Prod ID to Single form for editing
    ProdID = Me!ProdID
    DoCmd.Close acForm, "frmProduction"
    DoCmd.OpenForm "frmProductionReporting", OpenArgs:=ProdID
End Sub

Single form open event to the following:

Private Sub Form_Open(Cancel As Integer)
   If Nz(OpenArgs, "") = "Add" Then
        Me.RecordSource = "tbltemp"
        DoCmd.RunCommand acCmdRecordsGoToNew
    Else
        Me.RecordSource = "SELECT tbltemp.* FROM tbltemp WHERE (((tbltemp.ProdID)=" & Me.OpenArgs & "));"
    End If
End Sub

That is working for me.

Upvotes: 0

Gustav
Gustav

Reputation: 56016

If the user wishes to edit a record, he has to select the record and click on edit button. Then I pass the ID (Primary key) in open arguments to "Single form" for editing.

Don't do that. Just open the form on a new record without entering anything.

  • If the user regrets and closes the form, no new record will have been saved
  • If the user enters some data but regrets, one or more presses on Esc will cancel the record
  • If the user enters some data and closes the form, the record will be saved

Of course, the ID must be an AutoNumber.

Upvotes: 0

Related Questions