Deleting records in a linked table

[Roderick O'Regan]

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

 

[John Spencer]

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 =


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

 

[Roderick O'Regan]

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

 

[John Spencer]

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.

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

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 =