UtterAccess.com
X   Site Message
(Message will auto close in 2 seconds)

Welcome to UtterAccess! Please ( Login   or   Register )

Custom Search
 
   Reply to this topicStart new topic
> Delete Query To Delete A Record Based Off Two Criteria From A Form, Access 2013    
 
   
cjduncan
post Jul 17 2019, 01:40 PM
Post#1



Posts: 44
Joined: 30-April 19



Hi! Trying to create a delete query with either the query design or VBA and having a hard time with both. I've tried the following VBA, which is not working:

CurrentDb.Execute "Delete * from SOIMI WHERE MIIDfk = frmMIsubform.MIID AND SOIIDfk = frmMIsubform.refSOIID"

Any help is appreciated!
Go to the top of the page
 
DanielPineault
post Jul 17 2019, 01:42 PM
Post#2


UtterAccess VIP
Posts: 6,773
Joined: 30-June 11



Try something more like
CODE
CurrentDb.Execute "DELETE * FROM SOIMI WHERE ((MIIDfk = [Forms]![frmMIsubform]![MIID]) AND (SOIIDfk = [Forms]![frmMIsubform]![refSOIID]));"

or to make it easier to read/debug, you might prefer
CODE
Dim sSQL                  As String

sSQL = "DELETE * " & vbCrLf & _
       "FROM SOIMI " & vbCrLf & _
       "WHERE (" & _
       "   (MIIDfk = [Forms]![frmMIsubform]![MIID]) " & _
       "   AND (SOIIDfk = [Forms]![frmMIsubform]![refSOIID])" & _
       ");"
CurrentDb.Execute sSQL

--------------------
Daniel Pineault (2010-2019 Microsoft MVP)
Professional Help: http://www.cardaconsultants.com
Free MS Access Code, Tips, Tricks and Samples: http://www.devhut.net

* Design should never say "Look at me". It should always say "Look at this". -- David Craib
* A user interface is like a joke, if you have to explain it, it's not that good! -- Martin LeBlanc


All code samples, demonstration databases, links,... are provided 'AS IS' and are to be used at your own risk! Take the necessary steps to check, validate ...(you are responsible for your choices and actions)
Go to the top of the page
 
RJD
post Jul 17 2019, 01:51 PM
Post#3


UtterAccess VIP
Posts: 9,930
Joined: 25-October 10
From: Gulf South USA


Hi: PMFJI, but I noticed that the form name you used was "[frmMIsubform]". If this is truly a subform, you may need to use this approach ...

[Forms]!MainFormNameHere![frmMIsubform].Form![MIID]

But I don't think we have a good enough view of your db to know for certain. Perhaps you could share whether you are trying to get a value from a subform and what the main form name is.

Just some thoughts...

HTH
Joe

--------------------
"Each problem that I solved became a rule, which served afterwards to solve other problems."
"You just keep pushing. You just keep pushing. I made every mistake that could be made. But I just kept pushing."

Rene Descartes 1596-1650 (Mathematician and Philosopher)
Go to the top of the page
 
cjduncan
post Jul 17 2019, 02:39 PM
Post#4



Posts: 44
Joined: 30-April 19



I need to get the values from the subform -

Main form: 4_5_CTSMIs4SOI
Subform: frmMIsubform
Controls on subform for criteria: MIID and refSOIID
Table: SOIMI

Attached File(s)
Attached File  main_and_sub.PNG ( 24.35K )Number of downloads: 2
Attached File  SOIMI_table.PNG ( 10.27K )Number of downloads: 0
 
Go to the top of the page
 
RJD
post Jul 17 2019, 02:52 PM
Post#5


UtterAccess VIP
Posts: 9,930
Joined: 25-October 10
From: Gulf South USA


So, with the value being in the subform, then the syntax should be ...

[Forms]![4_5_CTSMIs4SOI]![frmMIsubform].Form![MIID]

... and using Daniel's suggestion, modified to recognize the value from the subform ...

CurrentDb.Execute "DELETE * FROM SOIMI WHERE ((MIIDfk = [Forms]![4_5_CTSMIs4SOI]![frmMIsubform].Form![MIID]) AND (SOIIDfk = [Forms]![4_5_CTSMIs4SOI]![frmMIsubform].Form![refSOIID]));"

Or you might try ...

CurrentDb.Execute "DELETE * FROM SOIMI WHERE MIIDfk =" & [Forms]![4_5_CTSMIs4SOI]![frmMIsubform].Form![MIID] " AND SOIIDfk = " & [Forms]![4_5_CTSMIs4SOI]![frmMIsubform].Form![refSOIID] & ";"

Both of these assume the IDs are numeric.

Try these and see if it gives you what you want - or let us know and perhaps provide a test db (no sensitive data, relevant objects, zipped) and someone could take a look.

HTH
Joe

--------------------
"Each problem that I solved became a rule, which served afterwards to solve other problems."
"You just keep pushing. You just keep pushing. I made every mistake that could be made. But I just kept pushing."

Rene Descartes 1596-1650 (Mathematician and Philosopher)
Go to the top of the page
 
cjduncan
post Jul 17 2019, 03:17 PM
Post#6



Posts: 44
Joined: 30-April 19



Is this SQL or VBA? Is there anything that is supposed to go before and/or after? Sorry, I can't always recognize where they go.
Go to the top of the page
 
RJD
post Jul 17 2019, 03:26 PM
Post#7


UtterAccess VIP
Posts: 9,930
Joined: 25-October 10
From: Gulf South USA


This is the execution of SQL (Action Query) within VBA. You can put this in a standalone module like this ...

CODE
Public Sub DeleteRecords()

CurrentDb.Execute "DELETE * FROM SOIMI WHERE MIIDfk =" & [Forms]![4_5_CTSMIs4SOI]![frmMIsubform].Form![MIID] " AND SOIIDfk = " & [Forms]![4_5_CTSMIs4SOI]![frmMIsubform].Form![refSOIID] & ";"

End Sub

... or put the execute command in a form module behind a command button.

Or you could simply create a saved query ...

DELETE * FROM SOIMI WHERE ((MIIDfk = [Forms]![4_5_CTSMIs4SOI]![frmMIsubform].Form![MIID]) AND (SOIIDfk = [Forms]![4_5_CTSMIs4SOI]![frmMIsubform].Form![refSOIID]));

... and run that.

Up to you.

If you are still having issues with this, you could post a db, as mentioned above, and someone could take a look and suggest a solution.

HTH
Joe

--------------------
"Each problem that I solved became a rule, which served afterwards to solve other problems."
"You just keep pushing. You just keep pushing. I made every mistake that could be made. But I just kept pushing."

Rene Descartes 1596-1650 (Mathematician and Philosopher)
Go to the top of the page
 
cjduncan
post Jul 17 2019, 03:33 PM
Post#8



Posts: 44
Joined: 30-April 19



Welp, I put the code in a module but the script turned red. I've zipped and attached the db if anyone would be so kind as to take a look at it.

For whoever takes a look, when you open it, go to the SOI section, click on the big orange button "Add/View MIs" and that gets you to the form.

Thank you in advance!
Attached File(s)
Attached File  16JUL.zip ( 147.86K )Number of downloads: 1
 
Go to the top of the page
 
RJD
post Jul 17 2019, 04:01 PM
Post#9


UtterAccess VIP
Posts: 9,930
Joined: 25-October 10
From: Gulf South USA


Well, that was partially my fault. Sorry. I left out an ampersand. So this is the corrected code (why it is good to have a db to test) ...

CurrentDb.Execute "DELETE * FROM SOIMI WHERE MIIDfk =" & [Forms]![4_5_CTSMIs4SOI]![frmMIsubform].Form![MIID] & " AND SOIIDfk = " & [Forms]![4_5_CTSMIs4SOI]![frmMIsubform].Form![refSOIID] & ";"

But you also named the Sub twice. I corrected that along with my error and it seems to work okay now. I also moved the code from the double-click event to the on-click event.

See if this gets you further along. I didn't try to understand everything you were doing, but just enough to get you to the next test.

HTH
Joe
Attached File(s)
Attached File  16JUL_Rev1.zip ( 110.87K )Number of downloads: 2
 

--------------------
"Each problem that I solved became a rule, which served afterwards to solve other problems."
"You just keep pushing. You just keep pushing. I made every mistake that could be made. But I just kept pushing."

Rene Descartes 1596-1650 (Mathematician and Philosopher)
Go to the top of the page
 
cjduncan
post Jul 17 2019, 04:08 PM
Post#10



Posts: 44
Joined: 30-April 19



Thank you so much! Now I can apply that to another area. I couldn't have done half of my database without you guys!
Go to the top of the page
 
RJD
post Jul 17 2019, 04:13 PM
Post#11


UtterAccess VIP
Posts: 9,930
Joined: 25-October 10
From: Gulf South USA


You are very welcome. Daniel and I are always happy to assist. thumbup.gif

Continued success ...

Regards,
Joe

--------------------
"Each problem that I solved became a rule, which served afterwards to solve other problems."
"You just keep pushing. You just keep pushing. I made every mistake that could be made. But I just kept pushing."

Rene Descartes 1596-1650 (Mathematician and Philosopher)
Go to the top of the page
 


Custom Search


RSSSearch   Top   Lo-Fi    21st August 2019 - 12:56 PM