CODEWITHSUNDEEP
vbams-access
Tesel
Tesel

Reputation: 41

How to multiply all records in column by value from textbox?

I have a table with a NUMBER column called 'Price'. I also have a form with a textbox where the user enters a percentage (like 0.9) and a button which will multiply all records in Price with that number from the textbox, when pressed. I don't get this to work.

These first 2 examples are just to test the basic case, which is no variable at all.

Dim strSQL  As String
Dim dbs     As DAO.Database

Set dbs = CurrentDb

strSQL = "UPDATE table_name SET Price = 20;"
dbs.Execute strSQL

I have tried the above but multiply Price with 20 like this: (it works)

strSQL = "UPDATE table_name SET Price = Price * 20;"

I know i get the variable to work by writing the following lines:

Dim textVariable AS Double
Textbox.SetFocus
textVariable = Textbox

MsgBox (textVariable)

But it doesn't work inside the SQL statement:

Dim textVariable As Double
Dim strSQL  As String
Dim dbs     As DAO.Database

Set dbs = CurrentDb

Textbox.SetFocus
textVariable = Textbox

strSQL = "UPDATE table_name SET Price = Price * textVariable;"
dbs.Execute strSQL

Error message: "Run-time error '3061' - Too few parameters, expected 1". In debug mode "dbs.Execute strSQL" is highlighted yellow.

Upvotes: 0

Views: 284

Answers (1)

Dawn Rose
Dawn Rose

Reputation: 61

In order to put a variable into an SQL string, you need to construct it out of your variable, as currently you are multiplying the Price column by "textVariable" instead of the value stored within it. 

strSQL = "UPDATE table_name SET Price = Price * " & textVariable & ";"

Also, it would likely be a good idea to also check to ensure that the value inputted into the TextBox is a valid number, either in code or setting a restriction on the textbox itself, as currently your code will throw a type mismatch exception if the value in the Textbox is not a valid double.

Upvotes: 1

Related Questions