[Last Call] Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 352
  • Last Modified:

The command or action 'DeleteRecord' is not available now. Please help

Hello,

In my for I have a subfor with a data from some tables.
for delete a record from subform I use a button where I put simple code in On Click event:
DoCmd.RunCommand acCmdDeleteRecord
When I press this button, record is deleted from subform, but when I sort record in this subform, this button doesn't work anymore. And not only for those form's record.
I got an error message that " The command or action 'Deleterecord' is not available now"
Have to recover my latest version...
Please help me, what to do.
That sort is nessacary

Solve

 
0
Solve
Asked:
Solve
  • 4
  • 4
1 Solution
 
harfangCommented:
This message normally means that Access finds nothing to delete: the form is read-only, the cursor is at the new record, etc.

Your code should look like this:

Private Sub cmdDeleteFromSubform()

On Error Goto Delete_Error

    ' Important: Setfocus to where the delete record will take place!
    subSubformControl.SetFocus
    DoCmd.RunCommand acCmdDeleteRecord
    Exit Sub

Delete_Error:
    Select Case Err.Number
        Case 2046
            ' do nothing...
        Case Else
            MsgBox Err.Description
    End Select
    Exit Sub

End Sub


The next time "delete record" isn't available, the focus should simply return to the subform. There you can try "Edit  / Delete Record" to see if *should* have worked.

I don't know if this helps, good luck!
0
 
SolveAuthor Commented:
It didn;t help me:((

I got message
data is read only... but why does is happens when i sort data in subform?

s.
0
 
SolveAuthor Commented:
I found out, that in my subform is a combobox, witch row source is a union from two query and when i sort data, they became read only
Is it possible to manage such thing?

Solveiga
0
Visualize your virtual and backup environments

Create well-organized and polished visualizations of your virtual and backup environments when planning VMware vSphere, Microsoft Hyper-V or Veeam deployments. It helps you to gain better visibility and valuable business insights.

 
harfangCommented:
Oh, I have an idea...

* You press [AZ] button in a combo box, it sorts according to the content of the combo, right?
* Open "Records / Filter / Advanced filter" and look at what has been added to the query.

Of course, if the query now contains a UNION QUERY in its FROM clause, the whole thing becomes read only.

Give us some details about the UNION QUERY and the subform. There might be a better way to filter it.

Cheers:)
0
 
SolveAuthor Commented:
Hello,

True, when I press [AZ] button standing on combobox, it sorts according to the content of the combo.
How to open "Records/Filter/Advanced filter" to look what has been added?
In properties pallet of this subform i see no filter has been added, only see in
ORDER by field is written such thing: Lookup_cmbPrekPav.Pavadinimas
here cmbPrekPav is my combo's name and Pavadinimas the first column in a query

And what about my union query: here I made a union from two tables

part1:
SELECT T_ASM_PREK.PREK_ID, T_PREK_PAV.PREK_PAV, T_ASM_PREK.TUR_KIEK, T_ASM_PREK.ASM_ID, Null AS Nurkiek
FROM (T_PREK_PAV RIGHT JOIN T_PREK ON T_PREK_PAV.PREK_PAV_ID = T_PREK.PREK_PAV_ID) RIGHT JOIN T_ASM_PREK ON T_PREK.PREK_ID = T_ASM_PREK.PREK_ID
WHERE (((T_ASM_PREK.ASM_ID)=[Forms]![F_NUR]![ASM_ID]));

part2:
SELECT T_NUR_PREK.PREK_ID, T_PREK_PAV.PREK_PAV, T_NUR_PREK.NUR_KIEK, T_NUR.ASM_ID, Null AS Turkiek
FROM (T_PREK_PAV RIGHT JOIN T_PREK ON T_PREK_PAV.PREK_PAV_ID = T_PREK.PREK_PAV_ID) RIGHT JOIN (T_NUR RIGHT JOIN T_NUR_PREK ON T_NUR.NUR_ID = T_NUR_PREK.NUR_ID) ON T_PREK.PREK_ID = T_NUR_PREK.PREK_ID
WHERE (((T_NUR.ASM_ID)=[Forms]![F_NUR]![ASM_ID]) AND ((T_NUR.NUR_ID)=[Forms]![F_NUR]![NUR_ID]));

union:
SELECT PREK_ID,( PREK_PAV) as Pavadinimas, (TUR_KIEK) as turi, (nurkiek) as nurašyta  FROM part1 ALL select PREK_ID, PREK_PAV, turkiek,  NUR_KIEK  from  part2
ORDER BY Pavadinimas;

s.
0
 
harfangCommented:
> How to open "Records/Filter/Advanced filter" to look what has been added?

Provided the default menu bar is available, and that the cursor is in the subform, do just that: Menu "Records", "Filter", "Advanced filter"...

As for the UNION query, I don't know its main purpose. But when used as sort order, it will, I think, use PREK_PAV (As Pavadinimas) from table T_PREK_PAV, in both cases.

If you want to order the subform accoring to PREK_PAV, you will need some other way to include that field directly in the recordsource. Sorting on the data displayed in a combo box is generally a bad idea... as the current problem demonstrates.

Good Luck
0
 
SolveAuthor Commented:
ok.. thanks a lot
I changed the row source for that combobox and problem is gone
Only one bad thing that I have all records form a table when I need to have just few.
There is no order problem, even I order by data displayed in a combobox...
I will leave this problem it for some time :)

Solveiga
0
 
harfangCommented:
If you need to let the user sort by the [AZ] buttons, that is where you stand.
If you select the order yourself, leave the combo with selective UNION query data, and add the field "Pavidinimas" to your recordsource only for sorting.
But you can't use the combo both ways...

Cheers :)
0

Featured Post

[Webinar] Cloud and Mobile-First Strategy

Maybe you’ve fully adopted the cloud since the beginning. Or maybe you started with on-prem resources but are pursuing a “cloud and mobile first” strategy. Getting to that end state has its challenges. Discover how to build out a 100% cloud and mobile IT strategy in this webinar.

  • 4
  • 4
Tackle projects and never again get stuck behind a technical roadblock.
Join Now