Delete records from Table open in Form

[JenZzz]

Dear all,

I have started working with vba only three weeks ago so forgive me for any basic mistakes. I have a small problem, I am creating a database for work and I am stuck

I have made a search function to look up my User in "Tbl_Users". The form opens with a subquery showing the found records. So far so good.

What I am trying to implement is a 'Delete' Button, to delete the record selected in the SubQuery by the press of that button. This is the applicable part of my code so far:

Set rstdeleteuser = CurrentDb.OpenRecordset("Tbl_Users")
        With rstdeleteuser
            !UserID = gvSelectedUser
            .Delete
        End With
    rstdeleteuser.Close
    Set rstdeleteuser = Nothing
 

This should usually work, the problem is however that the table is open through the subquery on my form.
Is there a way to temporarily close the table without losing my 'gvSelectedUser' (=Global Variable), or is there perhaps another way to do this?

Thanks!

[jimatqsi]

Jen, it is not clear what you mean by "subquery.". If you mean only that the form is bound to a query and not the table itself, it should not be a problem.

You code is opening the table with no where clause so who knows what you're deleting. You could try this, but back up your work first. Get rid of all your code shown and use this instead.

    DoCmd.DoMenuItem acFormBar, acEditMenu, 8, , acMenuVer70
    DoCmd.DoMenuItem acFormBar, acEditMenu, 6, , acMenuVer70
 

Jim

[JenZzz]

I'm sorry, It seems I haven't been clear and made a few mistakes. Let's start from scratch.
I have a Table named 'Tbl_Users'.
I basically want to delete a record from that table.
I have a Form called 'Show Users'. On this Form, there is a subform called 'Qry_Users subform'. This subform shows a Datasheet on the main form and gets it's data from a query, which in turn gets its data from 'Tbl_Users'.

So we have a main Form -> SubForm -> Query -> Table.
I want to delete a record from the Table by pressing a button on the Main Form.
The error I get with the code depicted in my post is exactly this:

Run-Time Error '3008'
The Table 'Tbl_Users'is already openend exclusively by another user, or it is already open through the user interface and cannot be manipulated programmatically.

This led me to believe the reason the code does not work is due to the table being used by the qry -> subform -> form itself!

The question is, how do I solve this? Can it be done by momentarily closing the table, or do I need to take a whole other approach?

Thanks!

[JenZzz]

Problem has been solved using the following code, seems I have made the problem seem more complex than it was.....

   Set rstdeleteuser = CurrentDb.OpenRecordset("Tbl_Users", dbOpenDynaset)
        With rstdeleteuser
            .FindFirst "UserID= '" & gvSelectedUser & "'"
            If .NoMatch = False Then
                .Edit
                !UserID = gvSelectedUser
                .Delete
            End If
        End With
    rstdeleteuser.Close
    Set rstdeleteuser = Nothing

[jimatqsi]

Jen, the .Edit does nothing without a .Update. That is, every .Edit must be followed by a .Update to actually write the changes to disk.

But that's okay because there is no need to update a record you are about to delete. So within your If block you need only the .Delete instruction.

Jim

[JenZzz]

So the .Edit in my code here does nothing? Because it keeps giving me an error without it.
Anyway, thanks for the help :)
Jens

[jimatqsi]

It does? That's curious. I don't do a lot of record deleting but it seems to me I don't have to lock it with an .Edit before I do the delete. Might be some reason having to do with Access setting or form properties. I'll look into that but probably one of the more learned guys here can solve that mystery before me.

Jim