By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
464,621 Members | 1,179 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 464,621 IT Pros & Developers. It's quick & easy.

Update Query in VBA updates field to -1!?

P: 34
Hi,

I'm trying to run an update query in vba and i'ts not working. It does the update but the [Limit Level 2] field is updated to "-1".

here's the query:

Expand|Select|Wrap|Line Numbers
  1.  
  2. Dim stDocName As String
  3. Set rst = New ADODB.Recordset
  4.         stDocName = "SELECT * FROM [Limit Table] WHERE [ID]=" & txtID.Value & ""
  5.         rst.Open stDocName, CurrentProject.Connection
  6.  
  7.         If txtID = "" Or IsNull(txtID) Then
  8.         stDocName = MsgBox("Click on the Row that you want to Update")
  9.     Else
  10.         stDocName = "UPDATE [Limit Table] SET [Limit Level 1]='" & ComboLevel1.Value & "',[Limit Level 2]='" & ComboLevel2.Value & "' AND [Primary/Secondary]='" & ComboPS.Value & "',[Limit Type]='" & ComboType.Value & "',[Limit Amount]='" & txtAmount.Value & "',[Limit Currency]='" & ComboCurrency.Value & "',[Limit Approval Date]='" & txtDate.Value & "' WHERE [ID]=" & txtID
  11.         DoCmd.RunSQL stDocName
  12.         lstTEST.Requery
  13.  
  14.     End If 
[Limit Level 2] is a combobox with a dropdown that depends on what has been chosen in [Limit Level 1]. I have put the code for this in the 'gotfocus' property of ComboLevel2. I think this might have something to do with the problem, although the dropdown works perfectly.

any ideas?

regards,
Riun
Jul 10 '07 #1
Share this Question
Share on Google+
1 Reply

MMcCarthy
Expert Mod 10K+
P: 14,534
Is txtID a text or a number field?

You haven't declared rst as a variable (i.e.)

Dim rst As ADODB.Recordset

In your update statement the syntax is wrong

"UPDATE [Limit Table] SET [Limit Level 1]='" & ComboLevel1.Value & "', [Limit Level 2]='" & ...."

it should be

"UPDATE [Limit Table] SET [Limit Level 1]='" & ComboLevel1.Value & "' AND [Limit Level 2]='" & ...."
Jul 17 '07 #2

Post your reply

Sign in to post your reply or Sign up for a free account.