double criteria for vba delete record

[shutzy]

im wanting to delete a record based on 2 criteria. ive had a look on the net and it looked so simple.

i tried

CurrentDb.Execute _
    "DELETE FROM tblRecommendedProducts " & _
    "WHERE ClientDetailsID = " & [Forms]![frmClientSale]![ClientDetailsID] And "WHERE ItemsID = " & [Forms]![frmClientSale-Retail]![ItemsID], dbFailOnError

any help?

 

[pr2-eugin]

A SQL Query can have only one WHERE Clause, remove the other WHERE..

CurrentDb.Execute _
    "DELETE FROM tblRecommendedProducts " & _
    "WHERE ClientDetailsID = " & [Forms]![frmClientSale]![ClientDetailsID] & " And ItemsID = " & [Forms]![frmClientSale-Retail]![ItemsID], dbFailOnError

 

[shutzy]

thanks. can you help with this also. along similar lines

[Employee].Value = [tblRecommendedProducts].[Employee] "WHERE ClientDetailsID = " & [Forms]![frmClientSale]![ClientDetailsID] AND "WHERE ItemsID = " & [Forms]![frmClientSale-Retail]![ItemsID], dbFailOnError

is it the same principal?

 

[shutzy]

i got an error on the first 1

runtime error '3061':

too few parameters. expected 1.

 

[pr2-eugin]

Is Employee.Value also obtained from the same form as Client ID? If so,

CurrentDb.Execute _
    "DELETE FROM tblRecommendedProducts " & _
    "WHERE [tblRecommendedProducts].[Employee] = " & [Forms]![frmClientSale]![Employee] & " AND ClientDetailsID = " & [Forms]![frmClientSale]![ClientDetailsID] & " AND ItemsID = " & [Forms]![frmClientSale-Retail]![ItemsID], dbFailOnError

 

[shutzy]

no, the employee is actually on the form. but i have in my DB a tbl called RcommendedProducts. this is wher the staff have done all the sales patter and recorded what they have nearly sold. so when an item is added to the order and it has been effectively sold by someone else i want it to record this so the sale goes to the rightful person.

im not sure if the Employee will be changed to null if there is no record. ill zip my DB and you can have a look if you want.

 

[pr2-eugin]

Okay.. When you post your DB (preferably in an mdb format) please make sure you inform which form we are to look into..

 

[shutzy]

the forms that need to be open on this event are

frmClientSale
frmClientSale-Retail

from these the 2 criteria are

[frmClientSale].[ClientDetailsID]
[frmClientSale-Retail].[ItemsID]

the table that i am wanting to delete the record is tblRecommendedProducts

the table that i am wanting to set value of [frmClientSale-Retail].[Employee] is tblRecommendedProducts

i need to update the [Employee] before i delete the record

the part where i have started the vba is in this part starting from
'set employee value if recommended is true'

Private Sub btnSave_Click()
If IsNull([Employee]) Then
MsgBox "You Have Not Selected A Therapist, Please Ammend", vbOKOnly, "No Therapist Selected!."
       Exit Sub
        End If
If IsNull([lstTreatmentItems]) Then
MsgBox "You Have Not Selected A Product, Please Ammend", vbOKOnly, "No Product Selected!."
       Exit Sub
       End If
'set employee value if recommended is true'
'delete item from recommended products'
CurrentDb.Execute _
    "DELETE FROM tblRecommendedProducts " & _
    "WHERE ClientDetailsID = " & [Forms]![frmClientSale]![ClientDetailsID] & " And ItemsID = " & [Forms]![frmClientSale-Retail]![ItemsID], dbFailOnError
[Cost].Value = [lstTreatmentItems].Column(3)
'update tblItems with new stock amounts'
strSql = "UPDATE tblItems " & _
 "SET StockQTY = ([StockQTY]-1) " & _
 "WHERE ItemsID = " & ItemsID & ""
CurrentDb.Execute strSql, dbFailOnError
DoCmd.GoToRecord , , acNewRec
lstCurrentretail.Requery
End Sub

let me know if you need any more info.

thanks for this

 

[NeutronFlux]

You typoed. The field in the table is "ItemID" whereas your SQL statement is looking for "ItemsID".

 

[shutzy]

am i allowed to swear.

 

[shutzy]

whey hey it works. as if i ever doubted it. now just for the update of [Employee]

 

[NeutronFlux]

Aside from the Typo, Strings need to be enclosed in double quotes. This means the Employee field values. So that part of the SQL query would look something like:

"WHERE [tblRecommendedProducts].[Employee] = """ & [Forms]![frmClientSale]![Employee] & """" &

Basically, when you want to do a double-quote within a string, you will use it twice like "".

Can't find this part of your code so can't test. Let us know how it goes.

 

[shutzy]

the code i need needs to have this

'set employee value if recommended is true'
[Employee].Value= [tblRecommendedProducts]![Employee] "WHERE [tblRecommendedProducts].[ItemID] = """ & [Forms]![frmClientSale]![OrderItemsID] & """" &

but i also need another condition

where tblRecommendedProducts.ClientDetailsID = forms!frmClientSale!ClientDetailsID

 

[NeutronFlux]

I still can't find the code in your project. Just to make sure I'm understanding you correctly, this is what I think you want to happen:
Actions
- Set Employee value in tblRecommendedProducts to the value of the ComboBox named Employee in frmClientSale-Retail

Conditions
- ClientsDetailsID from tblRecommendedProducts equals to ClientsDetailsID on frmClientSale
- ItemID from tblRecommendedProducts equals to OrderItemsID from frmClientSale

With this assumption, you'll have something like:

CurrentDB.Execute "UPDATE [tblRecommendedProducts] SET [Employee] = """ & [Forms]![frmClientSale-Retail]![Employee] & """" & " WHERE [ClientsDetailsID] = " & [Forms]![frmClientSale]![ClientsDetailsID] & " AND [ItemID] = " & [Forms]![frmClientSale]![OrderItemsID]

Just to clarify what I said before, in an SQL statement, you enclose Strings with quotes but not numbers. That's why Employee gets all those quotes but ClientsDetailsID and OrderItemsID do not.

 

[shutzy]

i want to set the [Employee] combo box in frmClientSale-Retail to the value in tblRecommendedProducts where ClientDetailsID=frmClientSale.ClientDetailsID AND ItemID=frmClientSale-Retail.OrderItemsID

 

[NeutronFlux]

Ah, in that case, you don't even need SQL. Just use a DLookup. Something like:

Forms!frmClientSale-Retail!Employee = Nz(DLookup("[Employee]", "[tblRecommendedProducts]", _
[ClientsDetailsID] = " & [Forms]![frmClientSale]![ClientsDetailsID] & _
" AND [ItemID] = " & [Forms]![frmClientSale]![OrderItemsID]), "")

DLookup is a function that is generally used to return a value from a table. You can use it like this:
something = DLookup(Field Name, Table Name, Conditions)

Nz is there in case you don't find your value and DLookup returns NULL. Nz takes a NULL and gives it another value. It's used like this:
Nz(value, value in case of null)

 

[shutzy]

i didnt know you can use a DLookup to set a value. ive got those all over the place in that database. never used them with 2 conditions tho.

so i suppose that i can do

IIF(IsNull(DLookup("[Employee]", "[tblRecommendedProducts]", _
[ClientsDetailsID] = " & [Forms]![frmClientSale]![ClientsDetailsID] & _
" AND [ItemID] = " & [Forms]![frmClientSale]![OrderItemsID])),,Forms!frmClientSale-Retail!Employee = Nz(DLookup("[Employee]", "[tblRecommendedProducts]", _
[ClientsDetailsID] = " & [Forms]![frmClientSale]![ClientsDetailsID] & _
" AND [ItemID] = " & [Forms]![frmClientSale]![OrderItemsID]), ""))

basically if isnull do nothing, set value. or is there a better way of doing this?

 

[shutzy]

i put the DLookUp straight in and its got an error

compile error
Expected: list operator or )

highlighting

" AND [ItemID] = "

 

[NeutronFlux]

Oops, missed a quotation mark.

Forms!frmClientSale-Retail!Employee = Nz(DLookup("[Employee]", "[tblRecommendedProducts]", _
"[ClientsDetailsID] = " & [Forms]![frmClientSale]![ClientsDetailsID] & _
" AND [ItemID] = " & [Forms]![frmClientSale]![OrderItemsID]), "")

 

[shutzy]

thanks, worked a treat.