Command Button to Delete 1

[LISAANN]

I would like to create a command button that takes my user from form #1 to Form #2 to enter information. When Form #2 is closed I would like to have the current record in Form #1 deleted. Is it possible to do this?

Lisa

 

[JerichoJ]

Yes. Sample code behind the button on form #1 that brings up form #2.

docmd.openform "frm2Name", windowmode:=acDialog
docmd.runcommand acCmdDeleteRecord

'acDialog causes this code to pause execution until
'the popup form is closed or made invisible

Using the "DeleteRecord" option of RunCommand causes the user to be prompted if they are sure they want to delete this record just like if they manually selected "Delete Record" from Records menu.

If you don't want to give them the choice, you will have to get a little fancier with something like:

dim db as dao.database
dim mssql as string

docmd.openform "frm2Name", windowmode:=acdialog
set db = currentdb()
mssql = "DELETE * FROM tblFeedingForm1 "
mssql = mssql & "WHERE ((tblFeedingForm1.Keyfieldname)"
mssql = mssql & "= " & me!Keyfieldname & ""
db.execute mssql
db.close
set db = nothing
me.requery

This will delete the current record on form1 directly from the table that it comes from based on the keyfield value as the where clause criteria. There is no confirmation displayed to the user. Then, the form is requeried so that it displays only the remaining records in your table.

 

[LISAANN]

Thank you very much for taking the time to help me out. However, I am having a few problemas. I need form #2 to open with a new record and the matching field from form #1. I also get a run time error when "no" is the response for deleting a record. If you could help me with these two things, I would be eternally grateful

 

[JerichoJ]

Sorry I haven't checked this forum in a while.

To open the popup form to a new record AND tell it to populate a field with a particular value, I would do the following:

Change the docmd.openform line in my first post to:

DoCmd.OpenForm "frm2Name", , , , acFormAdd, acDialog, "value"

Note: The above line of code is supposed to be 1 continuous line. Word wrapping turned it into 2 lines.

If your value from form 1 is a string, include it in quotes as I have shown. If it is numeric, remove the quotes.

This will pass a "command line argument" to form 2 that you will have to process. You can do that by using the following code in the "On Open" event of form 2:

If IsNull(Me.OpenArgs) Then
'If no parameter is given, close this popup
Cancel = True
Else
Me.MatchingField = Me.OpenArgs
End If

So, the effect is this: The acFormAdd on the OpenForm command puts the form on a new record. The On Open event of the popup form says that if it doesn't get passed a command line argument, cancel opening the form. Otherwise, set the value of a field on the popup form to the value that was passed to the form.

Voila!

Now as to your second problem with the runtime error. What is the error?

 

[LISAANN]

My error is "2501" Run Command Action was cancelled.

 

[JerichoJ]

Ok, that's an easy one.

On the button click event that is doing all this work (opening the popup form, then deleting the record) you need to have an error handler. For example, I will try to bring all of my code together:


'Initialize error handling
On Error GoTo Proc_Error

DoCmd.OpenForm "frm2Name", , , , acFormAdd, acDialog, "value"
docmd.runcommand acCmdDeleteRecord

Proc_Exit:
Exit Sub

Proc_Error:
Select Case Err.Number
Case 2501 'Action Cancelled
Resume Proc_Exit
Case Else
MsgBox Err.Number & vbCrLF & Err.Description, vbOkOnly + vbCritical, "Runtime Error"
End Select

 

[LISAANN]

Thank you so much for all of your help! It works fantastic, except for I still cannot get the new form to come up with the matching company from form 1. It is really no big deal, and eventually i will figure it out I am sure. Again, thank you so much!!

Lisa

 

[JerichoJ]

Lisa,

Please explain what it is you are trying to do with form 2 after it pops up. You previously said:

"I need form #2 to open with a new record and the matching field from form #1"

The example that I provided opens the popup to a new record and populates a field in that new record with the value passed from the main form. Your most recent comment suggests that you want the popup form to "come up with the matching company from form 1". This sounds like you want the popup form to find a specific existing company record?

Please explain and maybe I can help.

 

[LISAANN]

I think that my confusion lies in the following:

DoCmd.OpenForm "frm2Name", , , , acFormAdd, acDialog, "value"
(VALUE=FIELD FROM FORM1? EX. COMPANY NAME, COMPANYID?)
Me.MatchingField = Me.OpenArgs
(MATCHING FIELD = "VALUE" FROM FORM 1?)

If this is incorrect, please let me know. Otherwise I have a run-time error : 2147352567(80020009) You can't assign a value to this object

I really think I need to take a class, I feel like I have asked way too many questions. Thank you so much though!