Require button to be clicked before adding new record on form

[Bcanfield83]

I'm working on a form to be used when submitting a work request for the team. It's currently bound to a related table, tblWork_Requests.
I added a "Submit Request" button to the form. In the "On_Click" property, I added an Event Procedure which uses a SQL String to insert the appropriate records into that table. The records inserted are based upon values from Combo Boxes and Text Boxes on that form.
Everything is working properly there, but I noticed that when closing out of the form, it's still trying to insert the record again. Since the record was already inserted into the table via the "Submit Button" and subsequent SQL insert through the VBA code, it's error'ing out since the Primary Key field is one of those that's inserted. In this case, the Primary Key is the Work_Request_ID field which is an AutoNumber. Thus, it's unable to insert another record for that same Work_Request_ID.
Is there an easy way to fix this, or can it only be done by removing the Record Source from the form and making all of the controls on the form unbound?



Thanks.

[pbaldy]

Well, you'd normally either use a bound form (easiest) or use code with an unbound form. Having both leads to the issue you're having.

[davegri]

You could also user error handling to ignore the error.

[pbaldy]

You could also user error handling to ignore the error.

I suspect there'd be more to it, and I'm surprised you'd support that setup anyway.

[davegri]

and I'm surprised you'd support that setup anyway.

I can be a bold and surprising guy...

[Bcanfield83]

Thanks, I can play around with it. As a test (and practice for myself), I removed the Data Source for the form, and then created the below VBA procedure which adds the necessary records into the table through SQL Insert.
This is all well and good, but now I can obviously no longer use the Record Selectors on the form to scroll through the existing records, and I don't really know how to restore that functionality without rebinding the table :-P (Other than adding a button which runs a basic 'Select' query to show the records in Datasheet mode)
So I'll re-bind it and see if I can make some updates to have it behave the way I want it to behave

Dim strDesc, strSubmitter As String
Dim strReqNum, strCat, strDB, strPri As Long
Dim strDate As String
Dim strSQL As String


'Populate with the current date
strDate = Format(Now(), "mm/dd/yyyy")


Me.cboCategory.SetFocus: strCat = Me.cboCategory.Value  'Category Dropdown List Selection
Me.cboDatabase.SetFocus: strDB = Me.cboDatabase.Value 'Database Dropdown List Selection
Me.cboPriority.SetFocus: strPri = Me.cboPriority.Value 'Priority Dropdown List Selection
Me.EDDP_Description.SetFocus: strDesc = Me.EDDP_Description.Value  'Description Text Box
Me.txtDateOpened.SetFocus: strDate = Me.txtDateOpened.Value 'Date Opened Text Box
Me.txtSubmittedBy.SetFocus: strSubmitter = Me.txtSubmittedBy.Value  'Submitted By Text Box


'Insert SQL - inserts records into tblEDG_Database_Development_Tracking
strSQL = "INSERT INTO tblEDG_Database_Development_Tracking (EDDT_Database_ID, EDDT_Category_ID, EDDT_Submitted_By, EDDT_Description, EDDT_Priority, EDDT_Date_Submitted)" & _
         "VALUES ('" & strDB & "', '" & strCat & "', '" & strSubmitter & "', '" & strDesc & "', '" & strPri & "', '" & strDate & "')"


DoCmd.SetWarnings False

[pbaldy]

Why not leave the form bound and get rid of the insert SQL? With a bound form you don't need any of that code.

[kd2017]

Agreed with pbaldy here.

That said, you may want to look into CurrentDb.execute rather than DoCmd for executing SQL statements, that way you want have to mess with the risk of turning warnings on and off