Solved

VBA update SQL Server tbl

Posted on 2009-05-13
7
509 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
3 Use Cases for Connected Systems

Our Dev teams are like yours. They’re continually cranking out code for new features/bugs fixes, testing, deploying, testing some more, responding to production monitoring events and more. It’s complex. So, we thought you’d like to see what’s working for us.

 

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 500 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

DevOps Toolchain Recommendations

Read this Gartner Research Note and discover how your IT organization can automate and optimize DevOps processes using a toolchain architecture.

Question has a verified solution.

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

Suggested Solutions

After several hours of googling I could not gather any information on this topic. There are several ways of controlling the USB port connected to any storage device. The best example of that is by changing the registry value of "HKEY_LOCAL_MACHINE\S…
If you have heard of RFC822 date formats, they can be quite a challenge in SQL Server. RFC822 is an Internet standard format for email message headers, including all dates within those headers. The RFC822 protocols are available in detail at:   ht…
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…
Delivering innovative fully-managed cloud services for mission-critical applications requires expertise in multiple areas plus vision and commitment. Meet a few of the people behind the quality services of Concerto.

919 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

Need Help in Real-Time?

Connect with top rated Experts

18 Experts available now in Live!

Get 1:1 Help Now