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.
any help would be appreciatedPrivate Sub cmd_update_Click() Dim Details As String 'make sure the type is defined Dim Description As String 'make sure the type is defined Dim WT_category As String Dim clicked_item As String Dim pos_of_space As Integer Dim the_ref As Integer clicked_item = lst_results pos_of_space = InStr(clicked_item, " ") the_ref = Val(Left(clicked_item, pos_of_space - 1)) Details = Replace(Details, "'", "") If cbo_type_update = "Working Together" Or cbo_type_update = "Double Whammy" Then strsql = "UPDATE [Main Table]SET([Detail],[Description],[WT_Category]) " _ & "VALUES('" & txt_details_update & "','" & cbo_type_update & "','" & cbo_wt_update & "')" _ & "WHERE Action_Number = " & the_ref & "" End If db.Open CONNECTION_STRING db.Execute strsql, , adCmdText db.Close MsgBox "Data updated" End Sub
Last edited by DonkeyOte; 11-26-2010 at 12:43 PM.
And the problem is ... what?
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
My Recommended Reading:
Volatility
Sumproduct & Arrays
Pivot Intro
Email from XL - VBA & Outlook VBA
Function Dictionary & Function Translations
Dynamic Named Ranges
The problem is that i get a syntax error for the update statement.Originally Posted by Bob Phillips
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:
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.Debug.Print strsql
My Recommended Reading:
Volatility
Sumproduct & Arrays
Pivot Intro
Email from XL - VBA & Outlook VBA
Function Dictionary & Function Translations
Dynamic Named Ranges
This is what appears in the immediate window
UPDATE [Main Table] SET([Detail],[Description],[WT_Category]) VALUES('quality checked Zoes Fraud pack test','Working Together','Trusted')WHERE Action_Number = 134
Last edited by DonkeyOte; 11-29-2010 at 05:47 AM. Reason: removed unnecessary quote
I don't know which DB platform you're using but do you need a space prior to the WHERE command ?
My Recommended Reading:
Volatility
Sumproduct & Arrays
Pivot Intro
Email from XL - VBA & Outlook VBA
Function Dictionary & Function Translations
Dynamic Named Ranges
unfortunately not that simple
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)
My Recommended Reading:
Volatility
Sumproduct & Arrays
Pivot Intro
Email from XL - VBA & Outlook VBA
Function Dictionary & Function Translations
Dynamic Named Ranges
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.then when i click an item in lst_results it ouputs the data to combo and text boxeslst_results.Clear db.Open CONNECTION_STRING 'output to listbox rs.Open "SELECT Action_Number, Created, Detail, Name, WT_Category " _ & "FROM [Main Table] " _ & "WHERE Description = '" & cbo_type & "' " _ & "AND [Name] = '" & my_name & "'" _ & "AND Created between #" & from_date_us & "# AND #" & to_date_us & "#;", db, adOpenStatic, adLockReadOnly Do While Not rs.EOF lst_results.AddItem rs.Fields("Action_Number") & " " & rs.Fields("Created") & " " & rs.Fields("Detail") rs.MoveNext Loop rs.Close db.CloseI 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.Private Sub lst_results_Click() update_frame.Visible = True Dim clicked_item As String Dim pos_of_space As Integer Dim the_ref As Integer clicked_item = lst_results pos_of_space = InStr(clicked_item, " ") the_ref = Val(Left(clicked_item, pos_of_space - 1)) db.Open CONNECTION_STRING rs.Open "SELECT Action_Number, Created, Detail, Description, WT_category " _ & "FROM [Main Table] " _ & "WHERE Action_Number = " & the_ref & "", db, adOpenStatic, adLockReadOnly cbo_type_update = rs.Fields("Description") cbo_wt_update = rs.Fields("WT_category") txt_details_update = rs.Fields("Detail") rs.Close db.Close If cbo_type_update = "Working Together" Then cbo_wt_update.Visible = True Else cbo_wt_update.Visible = False End If End Sub
unsure what you mean by the type of database though.
As this is done entirely in excel 2007 would it help if i sent the file over?
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
Reply With Quote
Bookmarks