VBA SQL update statement

[shawby]

Can anyone help with this???

I am trying to create an update statement to find a field in a database and replace it with data from combo boxes and a text box.

This is the code that i am currently using.

Please Login or Register  to view this content.

any help would be appreciated

[Bob Phillips]

And the problem is ... what?

[DonkeyOte]

shawby - welcome to the board though please note that CODE tags should encase your VBA at all times (per Forum Rules).

Given first post etc I've modified your post for you but going forward please be sure to adhere to the above.

For more information about these and other tags, found here

[shawby]

And the problem is ... what?

The problem is that i get a syntax error for the update statement.

[DonkeyOte]

You might first try adding a space prior to the SET command

Thereafter, though you're seemingly trying to do something regards potential for apostrophe's (see Details variable) you're not as I see it catering for that possibility presently.
It's also impossible to determine from the code where the variable values are being generated (presumably they have Module/Global level scope)

Prior to the End If line add the following:

Please Login or Register  to view this content.

and review the output in the Immediate Window - this will show the SQL you're trying to perform (ie with variables inserted etc) - post it here.

[shawby]

This is what appears in the immediate window

Please Login or Register  to view this content.

[DonkeyOte]

I don't know which DB platform you're using but do you need a space prior to the WHERE command ?

[shawby]

I don't know which DB platform you're using but do you need a space prior to the WHERE command ?

unfortunately not that simple

[DonkeyOte]

well without more info. (ie platform, field types, error line etc) there's little we can suggest I'm afraid

if you step through the code using F8 does the connection actually open correctly - ie the debug occurs on the execution of the SQL itself.

(it's also difficult to error check given we can't see how the greater majority of the variables in the code are being generated in the first instance)

[shawby]

the error occurs when trying to execute the strsql.

basically all i am doing is taking data from a database, it is being output into a listbox using the following.

Please Login or Register  to view this content.

then when i click an item in lst_results it ouputs the data to combo and text boxes

Please Login or Register  to view this content.

I then change any of the data that has been output and click an update button which is where i am falling down with the code I have posted above.

unsure what you mean by the type of database though.

[shawby]

well without more info. (ie platform, field types, error line etc) there's little we can suggest I'm afraid

if you step through the code using F8 does the connection actually open correctly - ie the debug occurs on the execution of the SQL itself.

(it's also difficult to error check given we can't see how the greater majority of the variables in the code are being generated in the first instance)

As this is done entirely in excel 2007 would it help if i sent the file over?