DESPERATE: Need help with SQL! (1 Viewer)

[abhisheknag89]

Hey all

I've trying for weeks to be able to run SQL statements in my vba program but nothing has worked so far. I've come across all kinds of syntaxes, tried modeling my statements after access' generated SQL for queries, used record sets- nothing has yielded results. I always get a syntax error.

If I can run even one basic SQL line, I'll be able to take it from there. Can you help?

Here's what I Have:

Table Name: Dummy Table
Filed Name: line rating

CODE:

Docmd.RunSQL (UPDATE [dummy table] SET [dummy table].[line rating]=100)

I get the following ERROR:
"Compile Error: Expected list separator or )"

If I try the following CODE:

'DoCmd.RunSQL ("UPDATE Dummy Table " _& " SET Line Rating = 100 ")

I get an ERROR:
"UPDATE Syntax Error"

I've tried other syntaxes that i've found online too including using recordsets but haven't met any success.

I'm using Access 2007.

Help!

 

[pbaldy]

Try

DoCmd.RunSQL "UPDATE [Dummy Table] SET [Line Rating] = 100"

Note the inadvisable spaces in your names will require them to be bracketed; I'd change the names if it's not too late. I'd also use CurrentDb.Execute rather than DoCmd.RunSQL. It's more efficient and doesn't throw the warnings.

 

[abhisheknag89]

HOLY COW. It worked! Sheer genius. I've been trying all sorts of ways to get this thing to process SQL statements and this finally worked.

Thanks a ton! Now I can try and work on the more specific task I need to accomplish.

Thanks again!

 

[abhisheknag89]

Just one more quick tag question:

If I'm trying to specify a criteria, should it look like this?

DoCmd.RunSQL "UPDATE [Dummy Table] SET [Line Rating] = 100 WHERE [Sub Station] LIKE [crosby]"

When I do this, it prompts me for a parameter value with a box containing the word "Crosby". If i eliminate the square brackets from the word 'crosby' in the statement, it does the same.

Is there a way I can execute the command without being prompted for the parameter?

 

[pbaldy]

You might find this FAQ of interest:

http://www.baldyweb.com/BuildSQL.htm

If you're saying the specific criteria is "crosby":

DoCmd.RunSQL "UPDATE [Dummy Table] SET [Line Rating] = 100 WHERE [Sub Station] = 'crosby'"

 

[abhisheknag89]

Fantastic. Thank you so much sir. And your response was so speedy too!

 

[pbaldy]

Happy to help, and welcome to the site by the way!

 

[vbaInet]

And your response was so speedy too!

It's his fingers abhisheknag. They didn't crown him MVP for nothing

 

[abhisheknag89]

OK here I am again. What I actually need to do ultimately is grab the value from a text box and use that in this SQL statement.

The name of the textbox in question is "rating". This is what I tried:

var = Me.rating


DoCmd.RunSQL "UPDATE [Dummy Table] SET [Line Rating] = var WHERE [Sub Station] = 'crosby'"

Which works, but it prompts the user for the value of 'var'. Is there a way I can actually grab the data from the textbox 'rating' and use it without a prompt?

@vbaInet
Hahah I noticed the title and I do see why he has it!

 

[vbaInet]

Hehe!

You don't need the variable, you can pass it directly (but you need to concatenate it):

DoCmd.RunSQL "UPDATE [Dummy Table] SET [Line Rating] = " & Me.rating & " WHERE [Sub Station] = 'crosby'"

Or is there any reason why you're saving it into a variable? Maybe for later use?

 

[abhisheknag89]

Hmm...I tried both and neither worked.

When I used the variable:
SQL "UPDATE [Dummy Table] SET [Line Rating] = " & var & " WHERE [Sub Station] = 'crosby'"
It gave the error "Invalid use of Null" and pointed to this line:
var=me.rating

When I used your line, it gave the error "Syntax error in UPDATE statement"

Not really sure why either is happening. This whole SQL statement syntax business seems kinda dicey to me, I never know for sure how to go about it. I have seen way too many different syntaxes online.

Any idea what's up?

 

[pbaldy]

Both errors indicate that there is no value in the textbox. Double check the name of it and that it has a value in it (make sure to tab off the textbox if you're entering a value and then running the code).

 

[vbaInet]

Basically Me.Rating is returning Nothing, that's what is causing the error. Ensure that me.rating contains something.

Try this and see if anything shows:

Msgbox nz(me.rating, "Contains nothing")

 

[ChipperT]

If [Line Rating] expects a string you need to change the SQL to:

DoCmd.RunSQL "UPDATE [Dummy Table] SET [Line Rating] = '" & Me.rating & "' WHERE [Sub Station] = 'crosby'"

 

[abhisheknag89]

You're right, it's empty. How is it empty though, I enter text into it. I did make sure to tab out of the textbox and it still gave me the error. I deleted and readded the textbox too.

Also, the msgbox command did in fact return "Contains Nothing".

How odd. I succeeded in grabbing the value from a textbox once before to run a query designed in access by using a function -in the criteria box for the field i put in LIKE functionname()-; should I try something similar for this?

You guys are super nice for being this helpful. I'm inspired to go and contribute to Python forums now (python is what i do fairly well)

 

[pbaldy]

And that's the name, not "text1" like Access would give it by default? Can you post the db?

 

[abhisheknag89]

I'm back! Hope you all had a fantastic 4th of July weekend.

Aaaand I still have this problem. I checked the name (I changed the name of the textbox in the 'other' tab of its properties to 'rating') but it doesn't seem to work.

This textbox is on a report, by the way, not a form. I've grabbed values from textboxes on forms before. Would the fact that it's a report have something to do with this problem?

And unfortunately I won't be able to post the db, it has a some confidential company info =/

 

[vbaInet]

This textbox is on a report, by the way, not a form. I've grabbed values from textboxes on forms before. Would the fact that it's a report have something to do with this problem?

I think you're beginning to confuse things a bit. The impression we got from your other post is that the textbox is on a form? Can you explain exactly what you're trying to achieve?

You want to update a value based on a value on a form or a report or on a query?

 

[pbaldy]

Some clarity would help, but if the textbox is on a report (and more to the point not on the object containing this code) you would need the full reference:

Reports!ReportName.TextboxName

 

[chriscwilson]

Try var=Me.rating.Value or
var=Me.rating.Text
?
Chris