re: Easy SQL??

I have two tables one is a master list of products(Products) and the other is an ordered list of
products stored in a location (Loc1) I am trying to write a SQL query that if a user deletes a
product from the master(Products) then the item is removed from the location(Loc1). In the code the variable
DelId is used to store the ID number of the product you deleted. I tried the following:

SQLDelProd = "DELETE FROM Loc1 WHERE (ProdId='&DelId&')"
rsDelProd.Open SQLDelProd, cnCasaFood, adOpenKeyset, adLockOptimistic
I get the following message.
Data type mismatch in criteria expression.
I also tried the following SQL:
SQLDelProd = "DELETE FROM Loc1 WHERE (ProdId=DelId)"
but this didn't work either... What is wrong here??
Rick

SQLDelProd = "DELETE FROM Loc1 WHERE ProdId='" & DelId & "'"

You are astrayed.
It should have been Database.Execute rather than Recordset.Open

Dim db As Database
Dim SQLDelProd As String

SQLDelProd = "DELETE * FROM Loc1 WHERE ProdId='" & DelId & "'"
' SingleQuote, QuotationMark, Space, Ampersand, Space, VariableName
db.Execute SQLDelProd

AnhMy_Tran

I tried your method but I still got the Type mismatch...any Ideas?
Rick

I tried your method but I must be missing something. I get a compile error on the
Dim db As Database
the error is: User defined type not defined.
What am I doing wrong?
Rick

Database is an object in DAO and ADO library.
You get error when you do not have proper library in the project.
Click Project menu, select Reference, and check a DAO or ADO, then try the code until you find a correct one, for there are plenty of them. I just give you a hint, hoping you find out yourself, that makes you better programmer.
Good luck.

AnhMy_Tran

You could make your life easier by adding cascade delete to your tables. Then when a product is deleted from the master it would automagically be deleted from your Location tables, removing the need for your SQL statement.

Cheers.

Quote of the moment....
"When you cease to make a contribution you begin to die"

Val, You are the Man/Woman.
That was the best info. It also saved me about 30 lines of code since I had several Locations to delete from!! Is there a way to use a variable for a Table name in SQL? i.e. You have several tables and in the code you wish to select a table depending on conditions. Right now I am using a Select Case statement. Something in the nature of:
SelTable = lstTable ' Get the table name from a list box
SQLTable = "SELECT * from 'SelTable'"
rsTable.open SQLTable cnConnection

Would something like that work and what would be the syntax?
Thanks a million, I have been pulling my hair out for several days and it was something that easy!!
Rick

Glad to help.

Drop the CASE statement all together. Build your SQL statement as a string and then execute the call using the string as the input. eg.
<font color=blue><pre>
SQLTable = "Select * From " & lstTable & " ;"
rsTable.open SQLTable cnConnection</font color=blue>
....
<font color=red>
Of course doing "SELECT *" is always dangerous but that is an issue you can work with. </font color=red></pre>


Hope this helps.

P.S. Man would be the correct. /images/icons/smile.gif


Quote of the moment....
"Books think for me." - Charles Lamb

Valkyrie,
Thanks a million!!! I was curious if the Cascade Delete is a function of the Database itself. I was wondering if the Database would perform the Cascade Delete if the database was put on a computer that didn't have Acess installed?
Rick

It is a function of the DB. Can't confirm 100% that it would work as you suggest but I would be extremely surprised if it didn't (then again this is a MS product!)

Give it a whirl and lemme know. /images/icons/tongue.gif

Later..

Quote of the moment....
"Books think for me." - Charles Lamb