Solved Using an UPDATE Statement without getting prompted for a Parameter Value

[Ryan142]

Hi again, I promise this is my last post for today!
I've been grinding away at my project hence the volume of posts

As the title above says, I'm using a SQL UPDATE Statement to update prices for an item in a table. However, I've tried tinkering and to no avail, when I click the activation button that starts the sub, it asks for Parameter Value with a pop up. I want to not use this and instead use a text box that user will input.

This is what I've tried and it still doesn't work so any input would be greatly appreciated.

Private Sub btnComplete_Click()

    FlyingFee = txtFlyingFee
    LandingFee = txtLandingFee
    InstFee = txtInstFee

    SQLPayment = "UPDATE tblPaymentPricing SET Price = FlyingFee WHERE ProductID = 1"
    SQLLanding = "UPDATE tblPaymentPricing SET Price = LandingFee WHERE ProductID = 2"
    SQLInst = "UPDATE tblPaymentPricing SET Price = InstFee WHERE ProductID = 3"

    DoCmd.RunSQL (SQLPayment)
    DoCmd.RunSQL (SQLLanding)
    DoCmd.RunSQL (SQLInst)
    
    Me.lblComplete.Visible = True

Thanks, Ryan

 

[CJ_London]

would be helpful to know which parameter but I'll take a guess it is flyingfee etc. If that is the case try

SQLPayment = "UPDATE tblPaymentPricing SET Price = " & txtFlyingFee & " WHERE ProductID = 1"

 

[Ryan142]

great that worked thanks, and my bad for not including the correct stuff, another follow up question: what would be the syntax to run all 3 of those statements at the same time? Just to save having 3 pop ups asking to change one row?

 

[CJ_London]

don't use runsql, use currentdb.execute.

Your three updates can't be combined because they are updating different records

 

[Ryan142]

don't use runsql, use currentdb.execute.

Your three updates can't be combined because they are updating different records

Sure just wanted to check - and will do. Thanks for your help

 

[pbaldy]

FYI, I suspect you could do it with one SQL statement but I'm not sure it's worth the bother. Along the lines of:

UPDATE tblPaymentPricing SET Price = Switch(ProductID = 1, 123, ProductID = 2, 456, ProductID = 3, 789)

You'd have to build the Switch() to insert your values. Again, I'm not sure it's even worth it, I just went "hmm" and wondered if it could be done in one go.

 

[arnelgp]

or you can add those Fees to the productTable or another table and link it to your update query, eg:

tblFees(table)
ProductID (long integer)
EffectiveDate (date/time)
FlyingFee (double)
LandingFee (double)
InstFee (double)

fill this table with pertinent infos.
on your query:

"UPDATE tblPaymentPricing AS A Inner Join tblFees AS B On A.ProductID = B.ProductID " & _
"Set A.Price = (B.FlyingFee + B.LandingFee + B.InstFee);"