SQL DELETE question

[woodyinoz]

So far so good!

My next problem is that of deleting rows specified by some user input.

DELETE FROM PartReport {PartReport is my main table}
WHERE cont = '778' {Cont is a field in my table holding Contract number}

The above code deletes all rows where the contract number is 778, however I would like the user to specify what contract number is to be deleted through the user of a dialog box or something similar.

Can anybody help with this?
Thanks,

Woody.

 

[JoeNg]

One simple way is to pop up a dialog box and ask the user to input the contract # for deletion.
I would do this:
Create a form
Put a unbound field
(ie a field that is not connected to a table)
One OK button.
One Cancel button.
Put codes in the OK button.
Verify if the user entered a value.
Execute the sql with user input in a variable
Inform user records with contract # XXX are deleted.
Put Close() in Cancel button.

The other way is extract all contract numbers from your table and create a pick list where the user can choose which contract # to delete. This needs a little bit more work.

 

[footpad]

Adding to JoeNG's suggestion:

Assuming you've named your unbound field something along the lines of fldContNo:

var
   sqlContNo SQL
endVar

; ... stuff deleted, as we've talked about it before.

   sqlContNo = SQL

      DELETE FROM 
         PartReport 
      WHERE 
         cont = ~( strContNo )
   endSQL

You can also use ~strContNo, if you prefer. The former is called a query expression and the latter is called a query variable.

I prefer the former because it automatically evaluates the expression and converts its result to a string. This can be very convenient, however, you need to be sure you're expression can be converted to a string value.

For more information, search Help for those terms. The topics discuss the technique in terms of QBE queries, but they work with SQL queries as well.

Hope this helps...

-- Lance