Delete Query syntax in VBA querydef

[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

 

[Granny Spitz via AccessMonster.com]

What am I doing wrong?

"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.

 

[pietlinden]

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?

 

[Van T. Dinh]

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.

 

[Guest]

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.

 

[Van T. Dinh]

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 & "'"

 

[Guest]

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

 

[Van T. Dinh]

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.

 

[Guest]

Thank you Vin! I appreciate your help : )

 

[Van T. Dinh]

You're welcome ... Glad you worked it out ...