Delete query wont execute (1 Viewer)

George-Bowyer

Registered User.
Local time
Today, 14:46
Joined
Dec 21, 2012
Messages
177
I have a query to delete some records on the click of a button.

Code:
Private Sub cmdUnmark_Click()
 
DoCmd.OpenQuery ("qryUnmarkUser")

CurrentDb.Execute "qryUnmarkUser", dbFailOnError


MsgBox "Records Unmarked"

End Sub

(I use one option or the other, not both)

If use the DoCmd line, the query runs fine.

However, if I use the Execute line (because I want to avoid the warnings), I get a 3061 error "Too few parameters. Expected 1"


For info, here is the sql of qryUnmarkUser


Code:
DELETE tblMarkedPeople.*, tblMarkedPeople.fldUserID
FROM tblMarkedPeople
WHERE (((tblMarkedPeople.fldUserID)=[Forms]![NavigationForm]![cmbUser]));



Can anyone tell me why the Execute option is throwing an error, please?

Many thanks,

George
 

theDBguy

I’m here to help
Staff member
Local time
Today, 07:46
Joined
Oct 29, 2018
Messages
21,358
Hi George. It's because of the query parameter (form reference). You could try using Leigh's fExecuteQuery() function in place of the CurrentDb.Execute method.
 

isladogs

MVP / VIP
Local time
Today, 14:46
Joined
Jan 14, 2017
Messages
18,186
Alternatively just do this assuming fldUserID is a number field
Code:
CurrentDB.Execute "DELETE tblMarkedPeople.*, tblMarkedPeople.fldUserID" & _
" FROM tblMarkedPeople" & _
" WHERE (((tblMarkedPeople.fldUserID) = " & [Forms]![NavigationForm]![cmbUser] & "));", dbFailOnError

If it's a text field, then add single quotes
Code:
CurrentDB.Execute "DELETE tblMarkedPeople.*, tblMarkedPeople.fldUserID" & _
" FROM tblMarkedPeople" & _
" WHERE (((tblMarkedPeople.fldUserID) = '" & [Forms]![NavigationForm]![cmbUser] & "'));", dbFailOnError
 

The_Doc_Man

Immoderate Moderator
Staff member
Local time
Today, 09:46
Joined
Feb 28, 2001
Messages
26,999
George, I'll explain the background for what my colleagues just told you. Access has TWO potential environments. The SQL/Database Engine environment is where the Access user environment sends SQL to be executed. The two environments are different.

<database>.Execute runs the SQL in the database engine environment only. The engine (these days, ACE, but older systems ran JET) has its own separate process under Windows. I am using "process" in the strict operating system sense of that word. It is close but not identical to "task" and is related to but not identical to "session." Separate processes have separate memory areas. Since Access and the engine DO NOT share memory, Access "sends" the SQL to the engine and gets it back via inter-process communications techniques.

The engine CANNOT see forms or reports that might be open and acting as the driving force for the query action. Which means that the SQL you wanted to submit for execution MUST be complete. This is why for .Execute cases, you must build the complete SQL string before you try to execute it. You only have that one chance to get it right.

DoCmd.OpenQuery runs in the Access environment. It can see things that exist in that environment like forms, global variables, Tempvars, reports, etc. Access remains in control longer before it sends off the SQL. Therefore things like pulling a value from a form's control become feasible. I.e. The ".OpenQuery" action includes a parser to pick up and fill in Access user-side information. It is therefore SLIGHTLY more forgiving.
 

George-Bowyer

Registered User.
Local time
Today, 14:46
Joined
Dec 21, 2012
Messages
177
Hi George. It's because of the query parameter (form reference). You could try using Leigh's fExecuteQuery() function in place of the CurrentDb.Execute method.

Wow! That's complicated looking stuff :eek:

I started looking through it, thinking, "OK, I sort of see what you're doing here..."

Until I got to the bit where it says "Set rst = db.OpenRecordset("SELECT @@Identity".

What??? @ signs?? Where did @ signs come from? You're just making stuff up for the heck of it now???

(I have since googled @@Indentity - and I'm still not really any wiser…)
 

George-Bowyer

Registered User.
Local time
Today, 14:46
Joined
Dec 21, 2012
Messages
177
Alternatively just do this assuming fldUserID is a number field
Code:
CurrentDB.Execute "DELETE tblMarkedPeople.*, tblMarkedPeople.fldUserID" & _
" FROM tblMarkedPeople" & _
" WHERE (((tblMarkedPeople.fldUserID) = " & [Forms]![NavigationForm]![cmbUser] & "));", dbFailOnError


Thanks. That's what I have done, although I put the sql in a string first, because I was feeling fancy...
 

isladogs

MVP / VIP
Local time
Today, 14:46
Joined
Jan 14, 2017
Messages
18,186
Excellent.
I was originally going to suggest putting the SQL in a string myself...:cool:

Perhaps the DBGuy will explain the other code later
 

Minty

AWF VIP
Local time
Today, 14:46
Joined
Jul 26, 2013
Messages
10,354
The @@Identity part of the code returns the PK ID of the inserted record, handy if you need to refer to it after a new record is added.
 

Frothingslosh

Premier Pale Stale Ale
Local time
Today, 10:46
Joined
Oct 17, 2012
Messages
3,276
I've never considered bundling it all into an execute query function - whenever I've had to run a query with form references built into it, I've just added this part to the code (with appropriate declarations up top)

Code:
For Each prm In qdf.Parameters
   prm.Value = Eval(prm.Name)
Next

qdf.Execute <OPTIONS>

That linked function certainly looks useful, though, and it's definitely smart to roll something I have to use so often into its own function than to keep adding that loop in lots of places.
 
Last edited:

George-Bowyer

Registered User.
Local time
Today, 14:46
Joined
Dec 21, 2012
Messages
177
The @@Identity part of the code returns the PK ID of the inserted record, handy if you need to refer to it after a new record is added.


Ooo, ok, I can see a use for that right away.

I have a button that adds a new order to one table and then immediately adds an invoice with that order number to another.


Currently I do this:


Code:
With RS1

     . addnew
     ! [fill the fields]
     .update

     .movelast

     intOrderID = rs1!OrderID '(which is the PK)


With RS2

     .addnew
     !fldOrderID = intOrderID
     etc
     .update

Could I use @@Identity to do that instead?
 

theDBguy

I’m here to help
Staff member
Local time
Today, 07:46
Joined
Oct 29, 2018
Messages
21,358
Wow! That's complicated looking stuff :eek:

I started looking through it, thinking, "OK, I sort of see what you're doing here..."

Until I got to the bit where it says "Set rst = db.OpenRecordset("SELECT @@Identity".

What??? @ signs?? Where did @ signs come from? You're just making stuff up for the heck of it now???

(I have since googled @@Indentity - and I'm still not really any wiser…)
Hmm. Not sure how I missed this thread's continuing discussion but I hope you already got your answer. If not, please let us know if there's anything else not clear.
 

Minty

AWF VIP
Local time
Today, 14:46
Joined
Jul 26, 2013
Messages
10,354
Code:
With RS1

     . addnew
     ! [fill the fields]
     .update

     .movelast

     intOrderID = rs1!OrderID '(which is the PK)


With RS2

     .addnew
     !fldOrderID = intOrderID
     etc
     .update

Could I use @@Identity to do that instead?
If someone else added a new record in the middle of your code it's feasible (though probably highly unlikely) that the Movelast goes to a different record.

@@Identity uses the current db object to return the record you just added, so as long as you don't reconnect to the db in the code @@Indentity will work correctly. Another method that would fit in with your code;

Code:
   .Update
    ' Move to New Record
    .Bookmark = .LastModified
    intOrderID = rs1!OrderID
    .Close
 

Users who are viewing this thread

Top Bottom