?
Solved

VBA update SQL Server tbl

Posted on 2009-05-13
7
Medium Priority
?
531 Views
Last Modified: 2013-11-25
New to VBA....  I am trying to make an update to a linked SQL Server table.  Thus far it can't seem to identify the table: "Variable not defined".  Do you see any syntax issues with the attached code?
I have a similar form and it finds and inserts a rec into the same tbl....
Thanks!
Dim db As dao.Database
   Dim rs As dao.Recordset
   Dim frm As Form
   
      
Set frm = Forms!PutBoxOnHold
Set db = CurrentDb
   BoxID = frm.BoxID
   
   
Set rs = db.OpenRecordset("dbo_BoxInformation", dbOpenDynaset, dbSeeChanges)
 
With rs
   If gbl_hold_box = 1 Then
      DoCmd.RunSQL "UPDATE BoxInformation SET BoxInformation.BoxStatusID = 3"
                   WHERE BoxInformation.BoxID = " & Forms![PutBoxOnHold]![BoxID] & ""';"
   ElseIf gbl_go_box = 2 Then
      DoCmd.RunSQL "UPDATE BoxInformation SET BoxInformation.BoxStatusID = 4"
                   WHERE BoxInformation.BoxID = " & Forms![PutBoxOnHold]![BoxID] & ""';"
  End If
End With
rs.Close
Set rs = Nothing
Set db = Nothing
End Sub

Open in new window

0
Comment
Question by:stang1
  • 4
  • 3
7 Comments
 
LVL 25

Expert Comment

by:reb73
ID: 24378265
"  BoxID = frm.BoxID"

The variable BoxID has not been defined. Comment out this line as you are not using BoxID anywhere in your code after this assignment or add a new Dim statement to declare BoxID..
0
 

Author Comment

by:stang1
ID: 24378330
Did i define it correctly?.... same error message

Option Compare Database
Public gbl_hold_box As Integer
Public gbl_go_box As Integer
Public glb_box_id As Integer
Option Explicit
   
Private Sub Frame62_AfterUpdate()
Select Case Me.Frame62
      Case 1
        gbl_hold_box = 1
      Case 2
     gbl_go_box = 2
 End Select
End Sub


Private Sub UpdateBoxStatus_Click()
   Dim db As dao.Database
   Dim rs As dao.Recordset
   Dim frm As Form
   
   Dim hold_value As Integer
   Dim release_Value As Integer
   
   Dim BoxID As Integer
   
Set frm = Forms!PutBoxOnHold
Set db = CurrentDb
  ' BoxID = frm.BoxID
   hold_value = 3
   release_Value = 4
   
Set rs = db.OpenRecordset("dbo_BoxInformation", dbOpenDynaset, dbSeeChanges)

With rs
   If gbl_hold_box = 1 Then
      DoCmd.RunSQL "UPDATE BoxInformation SET BoxInformation.BoxStatusID = 3"
                   WHERE BoxInformation.BoxID = " & Forms![PutBoxOnHold]![BoxID] & ""';"
   ElseIf gbl_go_box = 2 Then
      DoCmd.RunSQL "UPDATE BoxInformation SET BoxInformation.BoxStatusID = 4"
                   WHERE BoxInformation.BoxID = " & Forms![PutBoxOnHold]![BoxID] & ""';"
  End If
End With
rs.Close
Set rs = Nothing
Set db = Nothing
End Sub

0
 
LVL 25

Expert Comment

by:reb73
ID: 24378514
Isn't the table called dbo_BoxInformation? The Update statement refers to it as just 'BoxInformation' - change this to dbo_BoxInformation..

On an another note, if this is the complete code, there is no reason to define the rs variable and open the table.. The Update statements are based on variables populated before the recordset open and opening the recordset is irrelevant..
0
Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

 

Author Comment

by:stang1
ID: 24378552
In SQL Server it's refered to as dbo.BoxInformation...  I've tried it without the dbo, w/ dbo., w/dbo_  It doesn't seem to see it?
0
 

Author Comment

by:stang1
ID: 24378835
Here's my latest effort...  User selects from option group on split form....  It gives a variable not defined message:

Option Compare Database
Public gbl_hold_box As Integer
Public gbl_go_box As Integer
Public glb_box_id As Integer
Option Explicit
   
Private Sub Frame62_AfterUpdate()
Select Case Me.Frame62
      Case 1
        gbl_hold_box = 1
      Case 2
        gbl_go_box = 2
 End Select
End Sub


Private Sub UpdateBoxStatus_Click()
   Dim db As dao.Database
   Dim rs As dao.Recordset
   Dim frm As Form
   Dim BoxID As Integer
   
Set frm = Forms!PutBoxOnHold
Set db = CurrentDb
   'BoxID = frm.BoxID
     
Set rs = db.OpenRecordset("dbo_BoxInformation", dbOpenDynaset, dbSeeChanges)

With rs
   If gbl_hold_box = 1 Then
      DoCmd.RunSQL "UPDATE dbo_BoxInformation SET dbo_BoxInformation.BoxStatusID = 3"
Blows Here -   WHERE dbo_BoxInformation.BoxID = " & Forms![PutBoxOnHold]![BoxID] & ""';"
   ElseIf gbl_go_box = 2 Then
      DoCmd.RunSQL "UPDATE dbo_BoxInformation SET dbo_BoxInformation.BoxStatusID = 4"
                   WHERE dbo_BoxInformation.BoxID = " & Forms![PutBoxOnHold]![BoxID] & ""';"
  End If
End With
rs.Close
Set rs = Nothing
Set db = Nothing
End Sub

0
 
LVL 25

Accepted Solution

by:
reb73 earned 2000 total points
ID: 24387416
I think you may have missed out the line concatenation operator " & _" at the end of the first lines.. Can you also try storing the SQL text in a variable and see if there's an error when assigning this variable?

Also using DB.Execute may be preferred as your query syntax does not contain any VBA functions..


Dim strSQL as string
.
.
.
If gbl_hold_box = 1 Then
     strSQL =  "UPDATE dbo_BoxInformation SET dbo_BoxInformation.BoxStatusID = 3 " & _
               "WHERE dbo_BoxInformation.BoxID = " & Forms![PutBoxOnHold]![BoxID] & ""';"
ElseIf gbl_go_box = 2 Then
     strSQL =  "UPDATE dbo_BoxInformation SET dbo_BoxInformation.BoxStatusID = 4 " & _
               "WHERE dbo_BoxInformation.BoxID = " & Forms![PutBoxOnHold]![BoxID] & ""';"
End If
DB.Execute strSQL, dbfailonerror

Open in new window

0
 

Author Closing Comment

by:stang1
ID: 31581139
It still isn't committing to DB, but I wanted to award you the pts for your efforts and we got past the error message.  Do you know why the form switches focus when the option button is selected?  I think it prepares to enter a new record?
0

Featured Post

Get expert help—faster!

Need expert help—fast? Use the Help Bell for personalized assistance getting answers to your important questions.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Confronted with some SQL you don't know can be a daunting task. It can be even more daunting if that SQL carries some of the old secret codes used in the Ye Olde query syntax, such as: (+)     as used in Oracle;     *=     =*    as used in Sybase …
This article describes how to use the timestamp of existing data in a database to allow Tableau to calculate the prior work day instead of relying on case statements or if statements to calculate the days of the week.
This is Part 3 in a 3-part series on Experts Exchange to discuss error handling in VBA code written for Excel. Part 1 of this series discussed basic error handling code using VBA. http://www.experts-exchange.com/videos/1478/Excel-Error-Handlin…
With just a little bit of  SQL and VBA, many doors open to cool things like synchronize a list box to display data relevant to other information on a form.  If you have never written code or looked at an SQL statement before, no problem! ...  give i…

580 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question