Delete Button Problem

[andrejamih]

Hello everyone,

I am pretty new in Access programming and creating a forms, reports, etc.
I have a problem with a delete process on my form.
I have set a delete button on my main form, but for some reason when I start operation, the record won't deleted in the main table and other child tables which implement fields in subforms.

I tried this code:
Private Sub deletebutton_Click()On Error GoTo Err_deletebutton_Click

DoCmd.RunCommand acCmdSelectRecord
DoCmd.RunCommand acCmdDeleteRecord
Exit_deletebutton_Click:
Exit Sub
Err_deletebutton_Click:
MsgBox Err.Description
Resume Exit_deletebutton_Click

End Sub

... But it still doesn't work, just blank all fields of form and subforms.

Also, I have a Listbox which display record and on which I could navigate, but I couldn't delete record (on right click on specific record). It seems to be forbidden. I would like if someone know how to "unlock" it, that I could have alternative solution for deleting complete record.
Thanks for your answer!

[Bulzie]

Did you use the button wizard to set the button code? If not try doing that. When you click it should delete the current record. Also if you manually tried to delete the record by hitting the delete button on your keyboard, does it try to delete the record? Sounds like from your statement it is going to a new record and not deleting the current one maybe?

[andrejamih]

At first, I have tried with button wizard. After that, I use VBA code.. If that is not a problem, I attach a link with a .accdb file of my db, because i couldn't upload it on forum. I really appreciate if someone could help me. Best regard, Bulzie

Link to download file: https://drive.google.com/file/d/1u_2...ew?usp=sharing

[andrejamih]

I forgot to say that OBRISI W/O, DELETE W/O on English is the delete button on my form...

[June7]

Remove Naziv.Value from the query and then the delete will work. Or just reference the table directly as RecordSource.

Need to requery the listbox afterwards. Me.List150.Requery. There is no need for the acCmdSelectRecord line.


WorkOrderDetails table is not a normalized structure. Each task should be a record instead of 20 task fields. Otherwise, might as well just combine table with Radni nalozi GENERATOR.

Advise no spaces nor punctuation/special characters (underscore only exception). Better would be WO_Number.

Also suggest not using same exact field name in multiple tables.

Why do you have both ATA and Naziv in Radni nalozi GENERATOR? Whether or not to use multi-value fields (I don't) I will leave to another discussion.

Sugget you give buttons more meaningful names.

Have you run Debug>Compile? One sub has two End Sub lines.

[andrejamih]

Hello June 7
Thank you for your answer and for your advices! I will pay attention on all tips you gave me!
I have a problem with Naziv.Value cause I'd wanted to click on ATA Number and to implement (display) a Naziv of ATA on my form. I don't know how to do that, but I would like to change that, so when the numbers of items is selected (drop-down combo box with multiple values) all text values in "Naziv"(Name of ATA) displays on form.

[Missinglinq]

...But it still doesn't work, just blank all fields of form and subforms....

Your code is valid...this type of behavior (all fields of form and subforms becoming blank) will, however, occur if you're trying to Delete a New Record that hasn't been Saved. Is that the case, perhaps, when you're testing?

Linq ;0)>

[andrejamih]

Missinglinq, thank you for your reply!

It was a simple mistake where I have put in query some Naziv.Value next to complete table, etc... As June7 has noticed. Thank you all for help! I will try to work on access with more awareness and caution.

[June7]

You are using multi-value field for the ATA data. I never use them. The combobox ColumnWidths property can be set to hide the ATA and display the descriptive Naziv. But the text would be difficult to read.

Can have a listbox next to the combobox to display the Naziv descriptive info.

SELECT ATA.Naziv
FROM ATA INNER JOIN [Radni nalozi GENERATOR] ON ATA.ATA = [Radni nalozi GENERATOR].ATA.Value
WHERE ((([Radni nalozi GENERATOR].[W/O-Number])=[Forms]![Radni nalozi GENERATOR]![W/O-Number]));

Then need code in the form OnCurrent event as well as the combobox AfterUpdate to requery the listbox.

Or use a subform with that SQL (without the WHERE clause) and set the subform container as locked so users cannot edit in it. A subform/subreport container can reference a saved query or form (as well as table or report).

[andrejamih]

I have put a list box and select on row source query and criteria... Now,when I select some items, it display on list box their names but also show other names of selection before that, and I set a simple control row at table Work Order Generator at lstNaziv field, but now it won't implement in table.

If you could check my mistake, 'cause I don't know "how to say to access to pick my items from selection of cboATA and place their names at field lstNaziv, and all of that implement on table" Here is a link with a DB:https://drive.google.com/open?id=1O3...aTlaRNPWVF4g8M

[June7]

Little error on my part - change the listbox RowSource to:

SELECT ATA.Naziv FROM ATA INNER JOIN [Radni nalozi GENERATOR] ON ATA.ATA = [Radni nalozi GENERATOR].ATA.Value WHERE ((([Radni nalozi GENERATOR].[W/O-Number])=[txtWO]));

The listbox must be UNBOUND - no ControlSource. Not even supposed to be Naziv field in [Radni nalozi GENERATOR] table.

And as already described, need code that requeries the listbox. Have not fixed code as already suggested in post 5.

[andrejamih]

Thank you June7! I have fixed the thing. Now, it looks great and very informative.

[andrejamih]

Okay, now, when I navigate through the form (next record, previous record, new record) that field stays frozen and don't change. I understand that I need some code on cboATA and On current of the form, but I don't know exactly which one I should input. I find out somebody mention loop command due to items selection, but exactly I don't know how to coding to work.

[June7]

There is no looping required because this list box is not used for user input, just to display data.

Requery the listbox after combobox data entry as well as when moving to new record. So code in the combobox AfterUpdate as well as form OnCurrent: Me.List173.Requery.