Want to win a PS4? Go Premium and enter to win our High-Tech Treats giveaway. Enter to Win

x
?
Solved

VBA update SQL Server tbl

Posted on 2009-05-13
7
Medium Priority
?
530 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

 

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

[Webinar] Lessons on Recovering from Petya

Skyport is working hard to help customers recover from recent attacks, like the Petya worm. This work has brought to light some important lessons. New malware attacks like this can take down your entire environment. Learn from others mistakes on how to prevent Petya like worms.

Question has a verified solution.

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

If you find yourself in this situation “I have used SELECT DISTINCT but I’m getting duplicates” then I'm sorry to say you are using the wrong SQL technique as it only does one thing which is: produces whole rows that are unique. If the results you a…
Entering time in Microsoft Access can be difficult. An input mask often bothers users more than helping them and won't catch all typing errors. This article shows how to create a textbox for 24-hour time input with full validation politely catching …
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…
We’ve all felt that sense of false security before—locking down external access to a database or component and feeling like we’ve done all we need to do to secure company data. But that feeling is fleeting. Attacks these days can happen in many w…

604 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