Deleting records in a linked table

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

Roderick O'Regan - 04 Jan 2008 19:54 GMT

Windows XP Pro - Access 2002

I have split a database and wish to delete a record from a linked
table.
The following code worked OK before the split:
===============
mySQLStr = "SELECT * FROM orders"
Set ts = db.OpenRecordset(mySQLStr, dbOpenDynaset)
pOrderValue = DMax("[PO_Nbr]", "orders")
If pOrderValue <> pcodevalue Then
With ts
.Index = "PrimaryKey"
.Seek "=", pOrderValue
.Delete
.Close
End With
End If
================

However, because it is now split the Index and Seek cannot be used. I
think, from what I've read in this newsgroup and in Help, that I might
have to create an SQL statement with a WHERE in it based on the value
as defined by 'pOrderValue' above.

I've had a number of attempts in creating this sort of statement but
it always ends in an error or won't compile.

Could someone point me in the right direction, please?

Roderick

Reply to this Message

John Spencer - 04 Jan 2008 21:06 GMT

pOrderValue = DMax("[PO_Nbr]", "orders")

If pOrderValue <> pcodevalue Then
mySQLStr = "DELETE FROM Orders WHERE PrimaryKey = " & pOrderValue
db.Execute mySQLStr, dbFailOnError
'That will delete all records from Orders where the Primary key =
pOrderValue
'of course since it is a primary key, that should be only one record
End If
================

strSQL =

Signature

John Spencer
Access MVP 2002-2005, 2007-2008
Center for Health Program Development and Management
University of Maryland Baltimore County
.

> Windows XP Pro - Access 2002
>
[quoted text clipped - 26 lines]
>
> Roderick

Reply to this Message

Roderick O'Regan - 05 Jan 2008 01:40 GMT

Thanks John.

The code works a treat! However, I'm getting the following error under
certain circumstances - but I cannot spot a pattern under WHICH
circumstances.

This is the error when the db.Execute etc. line is run: "Error 3061.
Too few parameters. Expected 1"

Help is no help. It only confirms that there are parameters missing
but not WHICH parameters.

This snippet is attached to a Cancel command button when a user is
filling in a purchase order form.

If halfway through completing the form they want to cancel the order
they select the command button and the PO numbers return to their last
value in one table and any record written to the'Orders' table is
deleted.

I'm selecting various comboboxes in the form for such things like Cost
Centres, Departments ordering etc. I can complete a random selection
of fields but generally not more than about four fields. It works OK
then. Give it one more and it fails

But there is no set order or number of items written or selected when
the error appears after pressing the Cancel button.

Any thoughts about this, please?

Roderick

>pOrderValue = DMax("[PO_Nbr]", "orders")
>
[quoted text clipped - 8 lines]
>
>strSQL =

Reply to this Message

John Spencer - 05 Jan 2008 20:35 GMT

I can't see anything in the sql statement that would cause the problem.
Is PrimaryKey a number field? Does pOrderValue have a value? Is the
value a number. If pOrderValue is something other than a number, then
Access could be seeing the value of pOrderValue as a parameter.

IF pOrderValue is a string and PrimaryKey is a text field then you may
need to use the following as your query string.

mySQLStr = "DELETE FROM Orders " & _
" WHERE PrimaryKey = " & Chr(34) & pOrderValue & Chr(34)

'====================================================
John Spencer
Access MVP 2002-2005, 2007-2008
Center for Health Program Development and Management
University of Maryland Baltimore County
'====================================================

> Thanks John.
>
[quoted text clipped - 40 lines]
>>
>> strSQL =

Reply to this Message