Help with sql statment - ASAP please

[Ron]

Hi,

A user use a form to insert data into "Transaction" table.
Amoung other fields there are "Invoice" which is text and "InvoiceDate"
which is a date and "Company"(text).

I put a cancel button in case the user want to undo his entries.
I figured out that I need to delete these records from the table.

1. I need help with the sql statment DELETE FROM with these critiria of
"Invoice" and the "InvoiceDate" and the "Company", so only these records
that were entered will be deleted.

2.If there is a simpler way to undo these entries - it will be much better.


Thanks for the help and time,

Ron

 

[strive4peace]

Hi Ron

try this

(I am assuming transaction records are on a subform and
Invoice is the main form and you are in the code behind the
main form)

'~~~~~~~~~~~
if isnull(Invoice_controlname) then
msgbox "Invoice is not filled out",, _
"Don't know what to undo"
exit sub
endif

if isnull(InvoiceDate _controlname) then
msgbox "Invoice Date is not filled out",, _
"Don't know what to undo"
exit sub
endif

if isnull(Company_controlname) then
msgbox "Company is not filled out",, _
"Don't know what to undo"
exit sub
endif

dim strSQL as string
strSQL = "DELETE * " _
& " FROM Transaction " _
& " WHERE Invoice = '" & me.Invoice_controlname _
& "' AND InvoiceDate = #" & me.InvoiceDate_controlname _
& "# AND Company = '" & me.company_controlname & "';"

'remove next line after everything is ok
debug.pring strSQL

currentdb.execute strSQL, dbFailOnError

currentdb.tabledefs.refresh
DoEvents

me.subform_controlname.form.requery

'~~~~~~~~~~

** debug.print ***

debug.print strSQL

--> this prints a copy of the SQL statement to the debug
window (CTRL-G)

After you execute your code, open the Debug window
CTRL-G to Goto the debuG window -- look at the SQL statement

If the SQL statement has an error

1. Make a new query (design view)

2. choose View, SQL from the menu
(or SQL from the toolbar, first icon)

3. cut the SQL statement from the debug window
(select, CTRL-X)

4. paste into the SQL window of the Query
(CTRL-V)

5. run ! from the SQL window
-- Access will tell you where the problem is in the SQL


Warm Regards,
Crystal
Microsoft Access MVP 2006

*
Have an awesome day

remote programming and training
strive4peace2006 at yahoo.com

*

 

[Ron]

Thank you Strive for the quick response.
The form is not a sub form, this is how it goes:
First form: the user enter the invoice number(text),InvoiceDate and company
and press on button continue.
Second form: invoice#, date and company already populated from the first
form and the user need to complete the details of the invoice i.e. price,
products etc...
when the user press cancel, all that data has to be deleted.
Now, I tried your sql and it works fine, but it leaves the last record in
the table.

I hope I`m clear.

Thanks again,
Ron

 

[strive4peace]

Hi Ron,

you're welcome

perhaps the last record was not yet saved to the table...

before you run the code:

if me.dirty then me.undo
or
if forms!formname.dirty then forms!formname.undo

this will undo any changes to the record if any have been made

Warm Regards,
Crystal
Microsoft Access MVP 2006

*
Have an awesome day

remote programming and training
strive4peace2006 at yahoo.com

*