delete records using querydef with parameter deletes ALL records

[hpi]

Hello,

I have a table : Batch
It contains fields

batchnummer : Number (Long Integer)
datum : Date/Time
status : Number (Long Integer)
nr_records : Number (Long Integer)

It contains a number of records and I want to delete the record with a
specific batchnummer eg 89
I created a querydef "Delete Batch by Batchnummer"

PARAMETERS batchnummer Long;
DELETE *
FROM batch
WHERE batchnummer=[batchnummer];

This is the VB code I used

Sub doit()
Dim qd As QueryDef

Set qd = CurrentDb.QueryDefs("Delete Batch by Batchnummer")

qd.Parameters("batchnummer") = 89
qd1.Execute
End Sub

After running this procedure ALL records are gone from the Batch table.
I also tested this for other similar tables and I had the same problem.

If I use a 'normal' query which selects records (no deletion) AND I use a
recordset (qd.openrecordset(...))
then it seems to work. But I can't use a recordset when I want to do a
delete.
I know, I can use a construction where I create a string containing the
where clause and run it using
docmd.RunSQL but I'm just wondering why this doesn't work.

Thanks

Hans

[Mike Storr]

I don't use parameter queries much myself, but have you tried...

Sub doit(bNum As Long)
Dim sqlStr As String
Dim db As Databse

Set db=CurrentDB
sqlStr ="DELETE batchnummer.*, batchnummer.batchnummer FROM batchnummer
WHERE"
sqlStr =sqlStr & " (batchnummer.batchnummer = " & bNum & ");"

db.Execute sqlStr, dbFailOnError
End Sub

Add whatever error traping you need to ensure success.

Mike Storr
www.veraccess.com
"hpi" <no****@hotmail.com> wrote in message
news:40*********************@read.news.nl.uu.net.. .

Hello,

I have a table : Batch
It contains fields

batchnummer : Number (Long Integer)
datum : Date/Time
status : Number (Long Integer)
nr_records : Number (Long Integer)

It contains a number of records and I want to delete the record with a
specific batchnummer eg 89
I created a querydef "Delete Batch by Batchnummer"

PARAMETERS batchnummer Long;
DELETE *
FROM batch
WHERE batchnummer=[batchnummer];

This is the VB code I used

Sub doit()
Dim qd As QueryDef

Set qd = CurrentDb.QueryDefs("Delete Batch by Batchnummer")

qd.Parameters("batchnummer") = 89
qd1.Execute
End Sub

After running this procedure ALL records are gone from the Batch table.
I also tested this for other similar tables and I had the same problem.

If I use a 'normal' query which selects records (no deletion) AND I use a
recordset (qd.openrecordset(...))
then it seems to work. But I can't use a recordset when I want to do a
delete.
I know, I can use a construction where I create a string containing the
where clause and run it using
docmd.RunSQL but I'm just wondering why this doesn't work.

Thanks

Hans

[hpi]

Can anyone answer this question?

tx

Hans

"hpi" <no****@hotmail.com> wrote in message
news:40*********************@read.news.nl.uu.net.. .

Hello,

I have a table : Batch
It contains fields

batchnummer : Number (Long Integer)
datum : Date/Time
status : Number (Long Integer)
nr_records : Number (Long Integer)

It contains a number of records and I want to delete the record with a
specific batchnummer eg 89
I created a querydef "Delete Batch by Batchnummer"

PARAMETERS batchnummer Long;
DELETE *
FROM batch
WHERE batchnummer=[batchnummer];

This is the VB code I used

Sub doit()
Dim qd As QueryDef

Set qd = CurrentDb.QueryDefs("Delete Batch by Batchnummer")

qd.Parameters("batchnummer") = 89
qd1.Execute
End Sub

After running this procedure ALL records are gone from the Batch table.
I also tested this for other similar tables and I had the same problem.

If I use a 'normal' query which selects records (no deletion) AND I use a
recordset (qd.openrecordset(...))
then it seems to work. But I can't use a recordset when I want to do a
delete.
I know, I can use a construction where I create a string containing the
where clause and run it using
docmd.RunSQL but I'm just wondering why this doesn't work.

Thanks

Hans

[hpi]

Hi,

Thanks, this was the solution.

Pretty logical too. The parameter is simply a field with a fixed value.

Hans

Chuck Grimsby <c.*******@worldnet.att.net.invalid> wrote in
news:10********************************@4ax.com:

Sure. I'm surprised that the query doesn't delete all the records all
the time however.

Change your query to be:

PARAMETERS lngbatchnummer Long;
DELETE *
FROM batch
WHERE batchnummer = lngbatchnummer;

so that query engine won't confuse the parameter with the field name.
On Mon, 26 Jan 2004 14:19:01 +0100, "hpi" <no****@hotmail.com> wrote:

Can anyone answer this question?

"hpi" <no****@hotmail.com> wrote in message
news:40*********************@read.news.nl.uu.net ...

I have a table : Batch
It contains fields
batchnummer : Number (Long Integer)
datum : Date/Time
status : Number (Long Integer)
nr_records : Number (Long Integer)
It contains a number of records and I want to delete the record with
a specific batchnummer eg 89
I created a querydef "Delete Batch by Batchnummer"
PARAMETERS batchnummer Long;
DELETE *
FROM batch
WHERE batchnummer=[batchnummer];
This is the VB code I used
Sub doit()
Dim qd As QueryDef
Set qd = CurrentDb.QueryDefs("Delete Batch by Batchnummer")
qd.Parameters("batchnummer") = 89
qd1.Execute
End Sub

[Mike Storr]

On Wed, 28 Jan 2004 00:20:02 GMT, Chuck Grimsby wrote:

I have to disagree somewhat to keeping field and conrol names different.
Having them the same can be emmensly helpfull when doing record
manipulations through recordsets. Just loop through the table fields and
make tbl.FieldName = Form.Controls("FieldName") or vice-versa.
In one project a form had 68 fields on it, and the updates were done using
4 lines of code (not including trapping)
I think it's more important to remember to qualify objects than it is to
use unique names ie - tableName.Field in queries and Form( ! or . )Name in
code. Especially when you need to make things re-usable.

--
Mike Storr
veraccess.com

Just FYI....

It's *really* easy to confuse Access.
As much as you can, as *often* as you can, make everything unique.

Don't duplicate field names, control names (especially make sure that
the control name isn't the same as the field name!), object names,
variable names, etc., etc., etc..

Not only will doing the above help Access know what you are asking it
to do, it'll help you know what you're referring to.

On Tue, 27 Jan 2004 20:56:52 +0000 (UTC), hpi <hp*@hpi.com> wrote:

Thanks, this was the solution.
Pretty logical too. The parameter is simply a field with a fixed value.

Chuck Grimsby <c.*******@worldnet.att.net.invalid> wrote in
news:10********************************@4ax.co m:

Sure. I'm surprised that the query doesn't delete all the records all
the time however.
Change your query to be:
PARAMETERS lngbatchnummer Long;
DELETE *
FROM batch
WHERE batchnummer = lngbatchnummer;
so that query engine won't confuse the parameter with the field name. On Mon, 26 Jan 2004 14:19:01 +0100, "hpi" <no****@hotmail.com> wrote:
Can anyone answer this question?"hpi" <no****@hotmail.com> wrote in message
news:40*********************@read.news.nl.uu.n et...
> I have a table : Batch
> It contains fields
> batchnummer : Number (Long Integer)
> datum : Date/Time
> status : Number (Long Integer)
> nr_records : Number (Long Integer)
> It contains a number of records and I want to delete the record with
> a specific batchnummer eg 89
> I created a querydef "Delete Batch by Batchnummer"
> PARAMETERS batchnummer Long;
> DELETE *
> FROM batch
> WHERE batchnummer=[batchnummer];
> This is the VB code I used
> Sub doit()
> Dim qd As QueryDef
> Set qd = CurrentDb.QueryDefs("Delete Batch by Batchnummer")
> qd.Parameters("batchnummer") = 89
> qd1.Execute
> End Sub

[Mike Storr]

On Wed, 28 Jan 2004 10:33:47 GMT, Chuck Grimsby wrote:

I'm intrigued as to how you think I'm only "...starting to get the
idea...". I think I just have a different approach. For a number of the
scenarios we (my business) have come across, having the form bound to a
record source, was more complex than having it unbound and working with the
data through queries and recordsets, although there are cases where we do
bind them as well.
Without going into to many details, the four line example I used is only
part of a process that accepts arguments such as form instance, data
sources and targets, and as such is used against two unrelated forms.
--
Mike Storr
veraccess.com

It sounds like you're starting to get the idea behind what modular (or
"re-useable") code is all about. Here's another idea or two for you
however...

Keeping the control names the same across applications can make the
code you write re-useable across applications without changing the
code at all. This can make recordset manipulations even easier, and
application development even faster (and easier).

Another way to do this is to pass the control(s) (or form) to a
standardized module, then use the control's controlsource property to
do whatever it is you need to do. This method becomes even more
important when dealing with forms that are *really* similar, although
in a truly modular environment, there wouldn't even be a need for
similar forms!

You might also benefit from using a query, rather then the table
directly, as the source for your forms, and then refer to the ordinal
order of the fields to do your manipulation. Rather then worrying
about getting the field names right, all you have to do is to get them
in the right order once. As long as the field you want to manipulate
is field # 7, it really doesn't matter if it's named "DateLastUpdated"
or "DateLastChanged". In such cases is doesn't matter at all what the
control *or* field names are!

On Tue, 27 Jan 2004 23:07:34 -0500, Mike Storr <st******@sympatico.ca>
wrote: