Solved Delete Subform Records from a Command Button in the MainForm

[chickenwings23]

Hello! I have a MainForm with a Subform inside of it. The MainForm is filled out by a user and the data shows below in the subform(Datasheet)

I want a user to select a record below from the subform, and click on the Command Btn "Delete Record" in the MainForm in order for it to execute. I have tried multiple codes but have been receiving errors. I've attached pictures on the bottom of what I recently tried. Would appreciate the help!

And I did check my database is unchecked for "Read-only"

 

[theDBguy]

Hi. One approach is to run a DELETE query.

 

[Gasman]

Not sure you would need to MoveNext?, as the deletion would likely put you on the next record?
The query for the subform might be read only?, have you checked that?

 

[Gasman]

Run the query in the query editor, and see if you can alter a field.
Either take a copy of the tables in the query first, or be prepared to put it back as it was, if you can actually edit the data.
Even see if you can delete a record with the record selector.

Yes I know, select the record selector on the left of the query (grey box) and press delete.
Access will prompt you that you are about to delete 1 record, then say No, unless you want to delete it really.

 

[jdraw]

Can you post a copy of the database -even if it's only a couple of records? Enough so readers can see/test the issue and offer focused responses.

 

[jdraw]

Please provide some instructions to achieve the issue. Remember, we do not know your application; have no training on your forms or logic or what you did to populate the controls. Also, and perhaps I'm missing the obvious, but I see no Command Btn "Delete Record".

 

[theDBguy]

A user must use the frmEstimateBr to select options or enter numeric values in 1 field "Estimate Hours", complete the whole form and hit save record. This will save the record in the subform.
Now the user clicks the combo box BrNumber in the mainform, it generates all the records of that particular BrNumber down below in the subform. In order to delete, the user must click on a record down below in the subform and hit delete.

There should be a command delete button on the MainForm to Delete Records from the Subform. Hope this helps!

When I select "A" from the BrNumber dropdown, four (4) records show up in the subform. If I add a delete button on the main form, which record(s) should be deleted from the subform? All of them?

 

[jdraw]

I see no delete button on form or subform.
But I did see this


Also, I must admit I am not following the logic. It does not seem intuitive ---to me.
Hopefully you have some user training sessions.
I don't know how relevant that is???

I can delete the record if I go to TableEstimateBr.

 

[theDBguy]

With the Delete Button the Mainform it should be able to delete the Record Selected in the subform. It should only delete 1 Record, not all

So for example, a user selects BrNumber "A" it generates four data records or so. The user can select from the subform or highlight the one record they want to delete and hit the Command Button in the MainForm to Delete.

Hi. Thank you for the clarification. I took a look at your subform; and unfortunately, you are displaying the result of a totals query, which means there is no immediate way to identify which record to delete. For example, let's say in the table you have two records that are being totaled and displayed in the subform as one result. Are you saying those two records need to be deleted from the table now?

 

[jdraw]

There is some confusion with your database --it's purpose and the actual business process involved in the form and buttons where delete is not allowed.

Perhaps you could describe in plain English --no database jargon - the business process this form and subform support(s) so we understand what you are trying to do. For some reason, Access or some programming is causing records in your subform to be ReadOnly.

@theDBguy,
Do you see a command button "Delete"??

 

[theDBguy]

@theDBguy,
Do you see a command button "Delete"??

@jdraw No, I don't. I believe the request was to add one.

 

[Pat Hartman]

Trying to delete records showing on one form from another is problematic when the form you want to delete from is showing multiple records. If the user presses the delete button without selecting anything, the first visible record will be deleted because by default, that is the subform's Current record. If he scrolls to the 40th record and clicks on it making that record the Current record and then scrolls back to the top of the list before hitting the delete button, the 40th record will be deleted. Bad practice.

Better to put a delete button on the subform or let the user just use Access to select the record or records to delete and use the ribbon to delete them.