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 Records That Reference Controls On Form, Access 2013    
 
   
cjduncan
post May 22 2019, 05:36 PM
Post#1



Posts: 24
Joined: 30-April 19



I need a delete query where I would double click the button on form 3_6_MI4OBJ to delete fields all the fields in the SOIMI2OBJ table but only when the SOIMIID on the form is equal to the SOIMIIDfk record in the table. I have tried the following:

DELETE SOIMI2OBJ.[SOIMIOBJID], SOIMI2OBJ.[OBJIDfk], SOIMI2OBJ.[SOIMIIDfk] FROM SOIMI2OBJ WHERE [SOIMIIDfk] = [Forms]![3_6_MI4OBJ]![SOIMIID] -- I tried this as an expression

I also tried adding the part after WHERE into the query table.

Not sure what else to do or what is wrong.
Attached File(s)
Attached File  Relationship.PNG ( 9.75K )Number of downloads: 0
Attached File  Query.PNG ( 7.27K )Number of downloads: 1
Attached File  Form.PNG ( 43.01K )Number of downloads: 5
 
Go to the top of the page
 
theDBguy
post May 22 2019, 06:44 PM
Post#2


Access Wiki and Forums Moderator
Posts: 75,518
Joined: 19-June 07
From: SunnySandyEggo


DELETE queries delete records, not fields. Are you sure you didn’t need an UPDATE query instead?

--------------------
Just my 2 cents... "And if I claim to be a wise man, it surely means that I don't know" - Kansas
Access Website | Access Blog | Email
Go to the top of the page
 
cjduncan
post May 23 2019, 08:51 AM
Post#3



Posts: 24
Joined: 30-April 19



Oh, ok, not fields. That makes sense. Yes, I need to delete the whole record.
Go to the top of the page
 
theDBguy
post May 23 2019, 10:15 AM
Post#4


Access Wiki and Forums Moderator
Posts: 75,518
Joined: 19-June 07
From: SunnySandyEggo


Hi. Okay, a DELETE query could be as simple as "DELETE FROM TableName." If you want to add a criteria, it could be like "DELETE FROM TableName WHERE FieldName=Value." If you're using a Form reference in your criteria, then make sure the Form is open when you run the DELETE query.

--------------------
Just my 2 cents... "And if I claim to be a wise man, it surely means that I don't know" - Kansas
Access Website | Access Blog | Email
Go to the top of the page
 
GroverParkGeorge
post May 23 2019, 10:46 AM
Post#5


UA Admin
Posts: 35,129
Joined: 20-June 02
From: Newcastle, WA


Also, please note that the datatype of the control matters. You must use the correct delimiters for strings " and dates #

--------------------
My Real Name Is George. Grover Park Consulting is where I do business.
How to Ask a Good Question
Beginning SQL Server
Go to the top of the page
 
cjduncan
post May 23 2019, 12:31 PM
Post#6



Posts: 24
Joined: 30-April 19



Is there another way to do it rather than having the form open? I have it as a subform, I didn't want to have another form pop up.
Go to the top of the page
 
GroverParkGeorge
post May 23 2019, 12:40 PM
Post#7


UA Admin
Posts: 35,129
Joined: 20-June 02
From: Newcastle, WA


Forms do NOT have values in them when they are not open, so no, you can't use this type of criteria unless the form to which it refers is

a) open

and
b) has a valid value in the referenced control.

You could do something else, though.

Create a TempVar and assign to that TempVar the value you want to use in the criteria. Then you can use that TempVar in the query. Again, of course, the TempVar value must be assigned prior to running the query.

Here's an example:

SELECT M.MealID, M.MealDate, M.TestTimeOfDayID, M.Comment, TTOD.SortOrder, TTOD.TestTimeofDay, M.[MealID]
FROM tblMeal AS M INNER JOIN tblTestTimeOfDay AS TTOD ON M.TestTimeOfDayID = TTOD.TestTimeOfDayID
WHERE M.[MealID]=[TempVars]![lngMealID]
ORDER BY M.MealDate, TTOD.SortOrder;

--------------------
My Real Name Is George. Grover Park Consulting is where I do business.
How to Ask a Good Question
Beginning SQL Server
Go to the top of the page
 
cjduncan
post May 23 2019, 01:51 PM
Post#8



Posts: 24
Joined: 30-April 19



ok, i'll work on it. thank you
This post has been edited by cjduncan: May 23 2019, 02:02 PM
Go to the top of the page
 
tina t
post May 23 2019, 02:06 PM
Post#9



Posts: 5,956
Joined: 11-November 10
From: SoCal, USA


QUOTE
[Forms]![3_6_MI4OBJ]![SOIMIID]

is the blue text above the name of the mainform that you have open?
and is the green text above the name of a field in the mainform's RecordSource?

i ask because i'm wondering if the reference you're using in the query is correct.

hth
tina
This post has been edited by tina t: May 23 2019, 02:06 PM

--------------------
"the wheel never stops turning"
Go to the top of the page
 
cjduncan
post May 24 2019, 11:36 AM
Post#10



Posts: 24
Joined: 30-April 19



no, the blue text is the subform and green is the field. so a subform isn't "open" when the main form is?
This post has been edited by cjduncan: May 24 2019, 11:39 AM
Go to the top of the page
 
theDBguy
post May 24 2019, 11:45 AM
Post#11


Access Wiki and Forums Moderator
Posts: 75,518
Joined: 19-June 07
From: SunnySandyEggo


Good one Tina! The subform is "open," in a sense, but the reference addressing for it changes. It becomes something like: Forms!MainFormName.SubformName.Form!TextboxName

--------------------
Just my 2 cents... "And if I claim to be a wise man, it surely means that I don't know" - Kansas
Access Website | Access Blog | Email
Go to the top of the page
 
cjduncan
post May 24 2019, 11:51 AM
Post#12



Posts: 24
Joined: 30-April 19



DBGuy,

You had said to make sure the form is open when the query is run. so is it "open" when the main form is open or does it need to be opened by itself?
Go to the top of the page
 
theDBguy
post May 24 2019, 12:00 PM
Post#13


Access Wiki and Forums Moderator
Posts: 75,518
Joined: 19-June 07
From: SunnySandyEggo


Yes! smile.gif
If the main form is open, then the subform, by association, is also open. Opening the subform on its own will also work. However, the reference to the value you're trying to use in the query will differ if you open the subform on its own or simply open the main form, which then opens the subform too. The syntax I posted earlier is if you just open the main form. The syntax you were using was if you open the subform only.

Said anothe way, if you continue to use your old syntax, then open the subform individually. If you want to open the main form instead, then use the new syntax provided above. Either way, the query will not work unless the value is available, and it will only be available if the subform is open. Cheers!

--------------------
Just my 2 cents... "And if I claim to be a wise man, it surely means that I don't know" - Kansas
Access Website | Access Blog | Email
Go to the top of the page
 
cjduncan
post May 24 2019, 12:11 PM
Post#14



Posts: 24
Joined: 30-April 19



Ok. So disregard the tables above since I ended up doing something else. However, I need to do the delete qry for this other form that is set up the same. Please see the setup below. When I run this query, it wants me to enter a parameter. What's wrong here? And the frmSOICTSssubform and frmMIsubform are both on a tabbed control. does that change the syntax with the tabbed form in there?
This post has been edited by cjduncan: May 24 2019, 12:18 PM
Attached File(s)
Attached File  qrydelsoimi.PNG ( 8.27K )Number of downloads: 1
 
Go to the top of the page
 
theDBguy
post May 24 2019, 12:26 PM
Post#15


Access Wiki and Forums Moderator
Posts: 75,518
Joined: 19-June 07
From: SunnySandyEggo


No. The syntax ignores tabs. Or, in other words, tab controls don't affect the syntax for referencing form controls. So, if you're saying the control you want is on a subform, then the syntax remains: Forms!MainFormName.SubformName.Form!ControlName (the ones in bold are literals, use them as is, just replace the other ones with the proper names of your objects)

--------------------
Just my 2 cents... "And if I claim to be a wise man, it surely means that I don't know" - Kansas
Access Website | Access Blog | Email
Go to the top of the page
 
cjduncan
post May 24 2019, 02:15 PM
Post#16



Posts: 24
Joined: 30-April 19



As my query shows, i have the mainform and two subforms. but it's not working. it asks for a parameter.
Go to the top of the page
 
theDBguy
post May 24 2019, 02:27 PM
Post#17


Access Wiki and Forums Moderator
Posts: 75,518
Joined: 19-June 07
From: SunnySandyEggo


Hmm, what do you mean by two subforms? Is it something like this?

MainForm -> Subform1 -> Subform2

or like this?

MainForm -> Subform1
and
MainForm -> Subform2

If the first one, then the syntax becomes like this: Forms!MainForm.Subform1.Form!Subform2.Form!ControlName

--------------------
Just my 2 cents... "And if I claim to be a wise man, it surely means that I don't know" - Kansas
Access Website | Access Blog | Email
Go to the top of the page
 
cjduncan
post May 28 2019, 04:46 PM
Post#18



Posts: 24
Joined: 30-April 19



thank you!
Go to the top of the page
 
theDBguy
post May 28 2019, 07:26 PM
Post#19


Access Wiki and Forums Moderator
Posts: 75,518
Joined: 19-June 07
From: SunnySandyEggo


Hi. You're welcome. Good luck with your project.

--------------------
Just my 2 cents... "And if I claim to be a wise man, it surely means that I don't know" - Kansas
Access Website | Access Blog | Email
Go to the top of the page
 


Custom Search


RSSSearch   Top   Lo-Fi    17th June 2019 - 03:50 PM