Solved

VBA update SQL Server tbl

Posted on 2009-05-13
7
506 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
How to improve team productivity

Quip adds documents, spreadsheets, and tasklists to your Slack experience
- Elevate ideas to Quip docs
- Share Quip docs in Slack
- Get notified of changes to your docs
- Available on iOS/Android/Desktop/Web
- Online/Offline

 

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

Why You Should Analyze Threat Actor TTPs

After years of analyzing threat actor behavior, it’s become clear that at any given time there are specific tactics, techniques, and procedures (TTPs) that are particularly prevalent. By analyzing and understanding these TTPs, you can dramatically enhance your security program.

Join & Write a Comment

Introduction Hopefully the following mnemonic and, ultimately, the acronym it represents is common place to all those reading: Please Excuse My Dear Aunt Sally (PEMDAS). Briefly, though, PEMDAS is used to signify the order of operations (http://en.…
As more and more people are shifting to the latest .Net frameworks, the windows presentation framework is gaining importance by the day. Many people are now turning to WPF controls to provide a rich user experience. I have been using WPF controls fo…
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…
Access reports are powerful and flexible. Learn how to create a query and then a grouped report using the wizard. Modify the report design after the wizard is done to make it look better. There will be another video to explain how to put the final p…

762 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

21 Experts available now in Live!

Get 1:1 Help Now