Delete Query syntax in VBA querydef

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I am working on a delete batch form that simply deletes assigned batches for
users. The records that need to be deleted are in the assignedbatches table.


I have only a little experience with delete queries, but this works in the
Access QDG:

DELETE AssignedBatches.*, Employee.UserId
FROM Employee INNER JOIN AssignedBatches ON
Employee.EmpNum=AssignedBatches.EmpNum
WHERE (((Employee.UserId)=Forms!DeleteBatch!txtuserid));

Rather than having a saved query, I'd rather create a temporary one in VBA
to execute on command. However, I am getting error 3061 - Too few
parameters. Expected 1. using the following vba:


strSQL = "DELETE AssignedBatches.*, Employee.UserId" & _
"FROM Employee INNER JOIN AssignedBatches ON
Employee.EmpNum=AssignedBatches.EmpNum" & _
strCriteria = "WHERE (((Employee.UserId)=Forms!DeleteBatch!txtuserid));"

Set qdf = CurrentDb.CreateQueryDef("View by UserId", strSQL & strCriteria)
qdf.Execute


What am I doing wrong?

Thanks in advance,
AA
 
LilMorePlease said:
What am I doing wrong?
Click to expand...

"Gimme more space please!"

strSQL = "DELETE AssignedBatches.*, Employee.UserId " & _
"FROM Employee INNER JOIN AssignedBatches ON
Employee.EmpNum=AssignedBatches.EmpNum " & _
strCriteria = "WHERE (((Employee.UserId)=Forms!DeleteBatch!txtuserid));"

It just needed a space between each concatenated string.
 
You're making this much more difficult than necessary...

strSQL = "DELETE AssignedBatches.*, Employee.UserId FROM Employee INNER
JOIN AssignedBatches ON Employee.EmpNum=AssignedBatches.EmpNum WHERE
(((Employee.UserId)=Forms!DeleteBatch!txtuserid));"

currentdb.Execute strsql. dbFailOnError

Why do you need a querydef at all?
 
Not sure why you have "Employee.UserId" in the Delete clause.

I would simply use:

Dim strSQL As String

strSQL = "DELETE AssignedBatches.* " & _
" FROM AssignedBatches INNER JOIN Employee " & _
" ON AssignedBatches.EmpNum = Employee.EmpNum " & _
" WHERE Employee.UserId = " & Forms!DeleteBatch!txtuserid

CurrentDb.Execute strSQL, dbFailOnError

assuming that UserID is a numeric Field.

Check Access VB Help on the dbFailOnError option so that you can trap for
errors.
 
Thank you all for your suggestions, but I am still getting the same too few
parameters error message. Van, maybe you are onto something...the UserId
field is actually a text field, not a number field. Also, you are right that
I do not need the "Employee.Userid" field in the Delete clause, Access set it
up that way in the QDG, but it can be removed and it will still execute.
 
If the UserID is a Text Field, use:

strSQL = "DELETE AssignedBatches.* " & _
" FROM AssignedBatches INNER JOIN Employee " & _
" ON AssignedBatches.EmpNum = Employee.EmpNum " & _
" WHERE Employee.UserId = '" & Forms!DeleteBatch!txtuserid & "'"
 
Thank you Van. That was my problem, I was missing the single quotes.

On an aside, there is a bug with the recordsaffected property when using the
execute command. I am wondering if you know of a workaround so that I can
show the users how many records they just deleted. I really don't want to
use ADO because I am using DAO for all the programming. I've found Access
can get confused when both references are used.

Thanks again!
AA
 
RecordsAffected only gives incorrect count for Pass-Through Queries, AFAIK.

I think your Query is "local" and it is processed by JET, RecordsAffected
should give the correct count.

Do a few tests on a copy of your database and you should be able to work out
if RecordsAffected gives correct count or not ...

I use both DAO and ADO in my databases. Provided that you fully qualify
your variables (especially for objects that exist in both DAO & ADO) like:

Dim rsd As DAO.Recordset
Dim rsa As ADODB.Recordset

the code should run fine.
 
Back
Top