Updating subforms as data is added/deleted

[philvalko]

I'm new to form design and do not know any VBA (but that won't stop me from messing with the code when need be).

I'm creating a form that will serve as the user-interface for viewing, adding, and deleteing data.

The product I want is a form with 3 items: a subform that displays data in a table (so users can see all the data), a button that links to a second form for adding data, and a button that allows users to delete data.

I've gotten close to accomplishing this with the exception of the delete option. The hang-up is due to the fact that the subform that displays all the data is the output of a query (can't delete data through the query). A friend who used to work with Access years back was trying to solve this issue by writing code that would identify the primary key of the record selected in the subform/query and then adding code that would use this extracted primary key to delete the record in the original table.

This seems like a slightly convoluted approach. Any suggestions how I can get the product I'm looking for?

Thanks,
Phil

 

[RuralGuy]

What makes you believe you cannot delete records through a query? I use at least two methods of deleting records.
1) Button on the SubForm with this code:

DoCmd.SetWarnings False
DoCmd.RunCommand acCmdDeleteRecord
DoCmd.SetWarnings True

2) Button on Main form with this code:

CurrentDb.Execute "DELETE FROM tblInvDetail WHERE DetailKey = " & ThisKey, dbFailOnError

...using your names and keys of course.

Post back with more details or questions if you have any.

 

[philvalko]

Foreign language

Since I don't know VBA (not even the basics), you'll have to help me interpret the code a little bit. The second option seems like it will suite my need (I want the delete button to be on the main form, above the subform).

This is the code you gave:
CurrentDb.Execute "DELETE FROM tblInvDetail WHERE DetailKey = " & ThisKey, dbFailOnError

I am assuming that I'll need to change "tblInvDetail" to "tbl(nameofmytable)".

Will I have to change "CurrentDb" or "DetailKey"? Will I have to enter a value after the "="?

I would like this delete button to work in a way such that users can highlight the record in the subform/query that they want to delete, click the delete button, then have the data deleted and the subform/query updated to reflect this deletion. For the later part of this, I'm assuming I can use
me.refresh
me.requery

Am I right?

 

[RuralGuy]

If your button is on the SubForm then you have all of the code needed except for some error handling. The user just pushes the button on the record they want deleted. You might want to double check with a question but then you are done. What is the key field of the SubForm table/query?

 

[philvalko]

The key field of the subform is an autonumber field "ID"

 

[RuralGuy]

The Bold items have to be supplied by you:

CurrentDb.Execute "DELETE * FROM [b]tblInvDetail[/b] WHERE [b][ID][/b] = " & [b]ThisKey[/b], dbFailOnError

You already figured out you need to change the Table name. You just supplied the field name of the key to look for. Now you need to get the key of the current record in the SubForm up to the Main form.

So that I can give you all of the code you'll need, how about posting the name of the Table that we want to delete the record from, the name of the SubFormControl and a ControlName or FieldName of something the user would recognize to identify the record so we can ask:
Are you sure you want to delete [ThisDataField] record?

 

[philvalko]

Table name: "noBldgPrmt"

SubForm name: "Rpt_bldgprmts subform" (this is the name of the subform as it displays in the Access view window of "forms"--I'm not sure if this is what you mean by "name of the SubFormControl"

The control name for the delete button (I don't know if you need this):
"bldgprmt_delete_rec_Click()"

As for a ControlName or FieldName, it will suffice to display text that says "are you sure you want to delete the selected record?"

Thanks a lot for all your help.

 

[RuralGuy]

Give this a try for the code behind your Delete cmdButton.

Private Sub bldgprmt_delete_rec_Click()
On Error GoTo Err_bldgprmt_delete_rec_Click
Dim strMsg As String

strMsg = "Are you sure you want to delete the selected record?"

[COLOR=Green]'-- Warning message with Record Data
'strMsg = "Are you sure you want to delete" & vbCrLf & vbCrLf & _
         "[" & Me.[Rpt_bldgprmts SubForm].Form.[b]OtherField[/b] & "]?"[/COLOR]

If MsgBox(strMsg, vbQuestion + vbYesNo + vbDefaultButton2, _
         "Delete Warning?") = vbYes Then
   [COLOR=Green]'-- Use the next Delete if [ID] is numeric[/COLOR]
   CurrentDb.Execute "DELETE * FROM noBldgPrmt WHERE [ID] = " & _
         Me.[Rpt_bldgprmts SubForm].Form.ID, dbFailOnError
   [COLOR=Green]'-- Use this Delete if [ID] is a Text field.
   'CurrentDb.Execute "DELETE * FROM noBldgPrmt WHERE [ID] = '" & _
         Me.[Rpt_bldgprmts SubForm].Form.ID & "'", dbFailOnError[/COLOR]
End If

Me.[Rpt_bldgprmts SubForm].Form.Requery     '-- Requery the SubForm

Exit_bldgprmt_delete_rec_Click:
   Exit Sub
   
Err_bldgprmt_delete_rec_Click:
   MsgBox "Error No:     " & Err.Number & vbCr & _
              "Description: " & Err.Description
   [COLOR=Red]Resume Exit_bldgprmt_delete_rec_Click[/COLOR]

End Sub

Because of the limited highlight of the about to be deleted record (maybe none), I would suggest putting some of the data in the Delete Warning.

 

[philvalko]

Hey RG,

I've been away from my computer since you posted the code yesterday. I'll give it a try in an hour or so and let you know if it works. Thanks!

Phil

 

[philvalko]

Hey RG,

I finally tried the code you sent me (the last couple days have been mad!). Here's what happens: I click the delete record button and I get the dialogue, as I'm supposed to, asking "Are you sure you want to delete the selected record?"

When I click "yes", I get the following error message:
Error No: 2465
Description: Microsoft Office Access can't find the field "l" referred to in your expression.

The "l" within the error message appears to be a lower-case "L", but I suppose it could be an upper-case "I". I don't see a referenced field "l" in the expression. Any ideas?

 

[RuralGuy]

I don't think we have a valid reference to the SubForm yet. Try adding the Bold code at the beginning of the procedure as a diagnostic. The Red part will need to be adjusted until we stop getting errors.

Dim strMsg As String

[B]MsgBox "The ID to Delete is [" & [COLOR=Red]Me.[Rpt_bldgprmts SubForm].Form.ID[/COLOR] & "]"[/B]

strMsg = "Are you sure you want to delete the selected record?"

 

[philvalko]

Now, instead of first getting the "Are you sure you want to delete selected record?" dialogue first, it goes straight to:
Error No: 2465
Description: Microsoft Office Access can't find the field 'l' referred to in your expression.

 

[RuralGuy]

As I expected. SubForms are displayed on forms by means of a SubFormControl. This SubFormControl has a name. You know you are on the SubFormControl if the Data tab shows the Link Master/Child Field sections. THe 1st item on the "Other" tab has the Name of the control. What is it?

 

[philvalko]

Got it: "Rpt_bldgprmtsSubform"

 

[philvalko]

I have to leave the office for the weekend right now and won't be able to pick back up until Sunday afternoon. THANK YOU for all your help. I'll check in with you and try the code you're about to send me as soon as I have computer access on Sunday. I can't tell you how grateful I am for your help.

Phil

 

[RuralGuy]

Change:

CurrentDb.Execute "DELETE * FROM noBldgPrmt WHERE [ID] = " & _
         Me.[[b]Rpt_bldgprmts SubForm[/b]].Form.ID, dbFailOnError

To:
   CurrentDb.Execute "DELETE * FROM noBldgPrmt WHERE [ID] = " & _
         Me.[[b]Rpt_bldgprmtsSubForm[/b]].Form.ID, dbFailOnError

 

[philvalko]

Brilliant, that works. Thank you!

 

[RuralGuy]

You're welcome. Glad I could help.