By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
459,567 Members | 1,592 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 459,567 IT Pros & Developers. It's quick & easy.

Delete records from Table open in Form

P: 9
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:
Expand|Select|Wrap|Line Numbers
  1. Set rstdeleteuser = CurrentDb.OpenRecordset("Tbl_Users")
  2.         With rstdeleteuser
  3.             !UserID = gvSelectedUser
  4.             .Delete
  5.         End With
  6.     rstdeleteuser.Close
  7.     Set rstdeleteuser = Nothing
  8.  
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!
May 14 '14 #1
Share this Question
Share on Google+
6 Replies


Expert 100+
P: 1,240
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.

Expand|Select|Wrap|Line Numbers
  1.     DoCmd.DoMenuItem acFormBar, acEditMenu, 8, , acMenuVer70
  2.     DoCmd.DoMenuItem acFormBar, acEditMenu, 6, , acMenuVer70
  3.  
Jim
May 14 '14 #2

P: 9
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!
May 14 '14 #3

P: 9
Problem has been solved using the following code, seems I have made the problem seem more complex than it was.....
Expand|Select|Wrap|Line Numbers
  1.    Set rstdeleteuser = CurrentDb.OpenRecordset("Tbl_Users", dbOpenDynaset)
  2.         With rstdeleteuser
  3.             .FindFirst "UserID= '" & gvSelectedUser & "'"
  4.             If .NoMatch = False Then
  5.                 .Edit
  6.                 !UserID = gvSelectedUser
  7.                 .Delete
  8.             End If
  9.         End With
  10.     rstdeleteuser.Close
  11.     Set rstdeleteuser = Nothing
May 15 '14 #4

Expert 100+
P: 1,240
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
May 15 '14 #5

P: 9
So the .Edit in my code here does nothing? Because it keeps giving me an error without it.
Anyway, thanks for the help :)
Jens
May 15 '14 #6

Expert 100+
P: 1,240
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
May 15 '14 #7

Post your reply

Sign in to post your reply or Sign up for a free account.