delete records using querydef with parameter deletes ALL records

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

re: delete records using querydef with parameter deletes ALL records

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" <nomail@hotmail.com> wrote in message
news:40112c3e$0$259$4d4ebb8e@read.news.nl.uu.net.. .[color=blue]
> 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
>
>[/color]

re: delete records using querydef with parameter deletes ALL records

Can anyone answer this question?

tx

Hans

"hpi" <nomail@hotmail.com> wrote in message
news:40112c3e$0$259$4d4ebb8e@read.news.nl.uu.net.. .[color=blue]
> 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
>
>[/color]

re: delete records using querydef with parameter deletes ALL records

Hi,

Thanks, this was the solution.

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

Hans

Chuck Grimsby <c.grimsby@worldnet.att.net.invalid> wrote in
news:10ab10pib917rq23u0kk2illqqelthskmi@4ax.com:
[color=blue]
>
> 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" <nomail@hotmail.com> wrote:[color=green]
>>Can anyone answer this question?[/color]
>[color=green]
>>"hpi" <nomail@hotmail.com> wrote in message
>>news:40112c3e$0$259$4d4ebb8e@read.news.nl.uu.net ...[color=darkred]
>>> 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[/color][/color]
>[/color]

re: delete records using querydef with parameter deletes ALL records

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

[color=blue]
> 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 <hpi@hpi.com> wrote:[color=green]
>>Thanks, this was the solution.
>>Pretty logical too. The parameter is simply a field with a fixed value.[/color]
>[color=green]
>>Chuck Grimsby <c.grimsby@worldnet.att.net.invalid> wrote in
>>news:10ab10pib917rq23u0kk2illqqelthskmi@4ax.co m:[color=darkred]
>>> 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.[/color][/color]
>[color=green][color=darkred]
>>> On Mon, 26 Jan 2004 14:19:01 +0100, "hpi" <nomail@hotmail.com> wrote:
>>>>Can anyone answer this question?[/color][/color]
>[color=green][color=darkred]
>>>>"hpi" <nomail@hotmail.com> wrote in message
>>>>news:40112c3e$0$259$4d4ebb8e@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
>>>[/color][/color][/color]

re: delete records using querydef with parameter deletes ALL records

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
[color=blue]
> 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 <storrboy@sympatico.ca>
> wrote:
>[/color]