Open query in vba

[AOGFADUGBA]

Hi All.
I have two action query and i want to run/open them with VBA code but nothing seems to be working. the other part of the code is working fine except the query part. Below is the code i used.

Private Sub cmdDelete_Click()
  On Error GoTo Err_cmdDelete_Click


  DoCmd.SetWarnings False
  If MsgBox("This action will remove the selected record and all associated records from the Data base. This action is irreversible. Do you want to proceed?", vbQuestion + vbYesNo + vbDefaultButton2, "Delete?") = vbYes Then
    DoCmd.RunCommand acCmdSelectRecord
    DoCmd.RunCommand acCmdDeleteRecord
    'DoCmd.SetWarnings False


' OpenQuery executes a saved query
' cstrQueryName is a constant with the name of an action query
 
[B] DoCmd.OpenQuery "DeleteAllocationfromVoteBook"[/B]
[B] DoCmd.OpenQuery "DeleteAllocationfromGHANAAIEHISTORY"[/B]
  End If


Exit_cmdDelete_Click:


  DoCmd.SetWarnings True
  Exit Sub


Err_cmdDelete_Click:


  MsgBox Err.Description
  Resume Exit_cmdDelete_Click


End Sub

Can someone help me out???

 

[Joe4]

Are you sure that you are getting inside of your IF statement?
I would recommend commenting out your " DoCmd.SetWarnings False" statement at the top, so you get/see any warnings.

 

[Norie]

You might also want to comment out the On Error GoTo Err_cmdDelete_Click line.

 

[Micron]

nothing seems to be working

That statement doesn't help me much.

I'd agree that you should leave the warnings on if it's failing and giving no reason, but see no need to comment out the error handling. If you're not getting an error message from that, I'd say it means there are no errors, but there may be a failure that the warnings would announce if turned on. What I don't get is, are you trying to delete records with queries when you've already deleted one (and maybe others due to cascade delete option) with this:
DoCmd.RunCommand acCmdDeleteRecord
Maybe the record(s) is already gone? Again, not enough info for me.

 

[AOGFADUGBA]

Thanks a million +1. worked fine

 

[Joe4]

So what was the issue?

 

[AOGFADUGBA]

Hi Micron. this is the Scenario. i have a form that undateds 58 records and at the same time appends the updated record into another table. the 58 records can be identified by date in the append table. But again if a record is deleted or modified in the table that updates the 58 records it should affect the corresponding records in the append table.
Below is the code i used for the delete action.

Private Sub cmdDelete_Click()
  On Error GoTo Err_cmdDelete_Click


  'DoCmd.SetWarnings False
  If MsgBox("This action will remove the selected record and all associated records from the Data base. This action is irreversible. Do you want to proceed?", vbQuestion + vbYesNo + vbDefaultButton2, "Delete?") = vbYes Then
    DoCmd.OpenQuery "DeleteAllocationfromVoteBook"
    DoCmd.OpenQuery "DeleteAllocationfromGHANAAIEHISTORY"
    DoCmd.RunCommand acCmdSelectRecord
    DoCmd.RunCommand acCmdDeleteRecord
    'DoCmd.SetWarnings False


   End If


Exit_cmdDelete_Click:


  DoCmd.SetWarnings True
  Exit Sub


Err_cmdDelete_Click:


  MsgBox Err.Description
  Resume Exit_cmdDelete_Click


End Sub

Hope am making sense to you. otherwise u can advise.
Thanks

 

[Micron]

First, if the form is updating or deleting records from one table, I have to assume it is a temp table, otherwise why do that then modify another ("append") table using those changes? I think you are asking how to affect the 2nd table after these changes. In that case, either
- define relationships between these two tables and enable cascading updates/deletions (so that the changes happen to the second table also) or
- you will have to run two queries against the second table, a delete and an update.

You might want to use the query wizard to create an unmatched query and use it as the basis for creating a delete query. I think it should look like
DELETE * FROM tblAppend WHERE (((tblAppend.someField) Not In (SELECT someField from tblUpdates)));
tblAppend is your second table. You will need to figure out which field you can use that is in both tables - one should be enough.
As always, test suggestions on copies of tables/queries etc. so you can roll back changes.

 

[AOGFADUGBA]

Thanks Micron

 

[Micron]

You're welcome. As always, we like to know what resolves your issue so we can all learn.