Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

Help needed with Object variable or With Block Variable not Set

Posted on 2011-09-07
13
Medium Priority
?
445 Views
Last Modified: 2012-05-12
I am trying this SQL query a different way this time.  I have written it directly into my VBA code of my Access 2007 ADP however I am missing the obvious as I am receiving an error of Object Variable or With Block Variable not set before even getting errors on the SQL coding and not well versed in VBA.

 I have tried running only one block of the SQL code at a time and receive the same error message.  I am sure someone has seen this before and can offer some wisdom.  Thank you.
Private Sub CreateHolidayScheduleButton_Click()
On Error GoTo Err_CreateHolidayScheduleButton_Click

    Dim db As Database
    Dim qdf As QueryDef
    Dim strSQL As String
    Dim rs As Object
           
    strSQL = " INSERT INTO AdjustedSchedule"
    strSQL = strSQL & " (FAMILY,PICKUP,LABEL,DOSE,HOLIDAY,PICKCODE,"
    strSQL = strSQL & " Mon,Tue,Wed,Thu,Fri,Sat,Sun)"
    strSQL = strSQL & " SELECT RegularSchedule.FAMILY,"
    strSQL = strSQL & " RegularSchedule.PICKUP,"
    strSQL = strSQL & " RegularSchedule.LABEL,"
    strSQL = strSQL & " RegularSchedule.DOSE,"
    strSQL = strSQL & " HolidayReference.+(left(DATENAME(WeekDay([forms]![Holiday]![DOW]),3))+ [forms]!Holiday]![Status],"
    strSQL = strSQL & " LookupPickupSchedule.PICKCODE,"
    strSQL = strSQL & " LookupPickupSchedule.Mon,"
    strSQL = strSQL & " LookupPickupSchedule.Tue,"
    strSQL = strSQL & " LookupPickupSchedule.Wed,"
    strSQL = strSQL & " LookupPickupSchedule.Thu,"
    strSQL = strSQL & " LookupPickupSchedule.Fri,"
    strSQL = strSQL & " LookupPickupSchedule.Sat,"
    strSQL = strSQL & " LookupPickupSchedule.Sun"
    strSQL = strSQL & " FROM (RegularSchedule"
    strSQL = strSQL & " INNER JOIN HolidayReference"
    strSQL = strSQL & " ON RegularSchedule.PICKCODE = HolidayReference.PickRef)"
    strSQL = strSQL & " INNER JOIN LookupPickupSchedule"
    strSQL = strSQL & " ON HolidayReference.+(left(DATENAME(WeekDay([forms]![Holiday]![DOW]),3))+ [forms]!Holiday]![Status]= LookupPickupSchedule.PICKCODE"
    strSQL = strSQL & " LEFT JOIN AdjustedSchedule ON LookupPickupSchedule.PICKCODE"
    strSQL = strSQL & " = AdjustedSchedule.PICKCODE"
    strSQL = strSQL & " WHERE RegularSchedule.Status = 'ACTIVE'"
        
    strSQL = strSQL & " Update AdjustedSchedule"
    strSQL = strSQL & " Set AdjustedSchedule.HOLADJUST = [forms]![Holiday]![DOW],"
    strSQL = strSQL & " AdjustedSchedule.HOLIDAY = [forms]![Holiday]![Holiday],"
    strSQL = strSQL & " AdjustedSchedule.STATUS = 'Holiday';"
    
    strSQL = strSQL & " Update RegularSchedule"
    strSQL = strSQL & " Inner Join AdjustedSchedule"
    strSQL = strSQL & " On RegularSchedule.Family = AdjustedSchedule.Family"
    strSQL = strSQL & " Set RegularSchedule.HolAdjust = [Forms]![Holiday]![DOW],"
    strSQL = strSQL & " RegularSchedule.STATUS = 'Holiday'"
    strSQL = strSQL & " Where(((AdjustedSchedule.Status)='Holiday'))"
       
    
    Set db = CurrentDb
    Set qdf = db.CreateQueryDef("")
    qdf.SQL = strSQL
    RefreshDatabaseWindow
    
    
Exit_CreateHolidayScheduleButton_Click:
    Exit Sub

Err_CreateHolidayScheduleButton_Click:
    MsgBox Err.Description
    Resume Exit_CreateHolidayScheduleButton_Click
    
End Sub

Open in new window

0
Comment
Question by:JasBrad
[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
  • 5
  • 4
  • 4
13 Comments
 
LVL 59

Expert Comment

by:HainKurt
ID: 36497752
3 query in one string! does it work like that? Line 32 & 34, even they dont have ";" between... at least ";" should be added at the end of 32 & 44 I guess... maybe you should seperate tham into 3 sql and use Execute

Set db = CurrentDb
Set qdf = db.CreateQueryDef("")

strSQL = "insert ..."
qdf.SQL = strSQL
qdf.Execute

strSQL="Update..."
qdf.SQL = strSQL
qdf.Execute

strSQL="Update..."
qdf.SQL = strSQL
qdf.Execute
0
 

Author Comment

by:JasBrad
ID: 36497845
I separated them as you suggested, but still have the same error message.
0
 
LVL 120

Expert Comment

by:Rey Obrero (Capricorn1)
ID: 36497907
post your new code and indicate which line is raising the error..

before running the codes, comment this line

      ' On Error GoTo Err_CreateHolidayScheduleButton_Click


when you the error message click on Debug and take note of the lice or code higlighted, post it here
0
What Is Blockchain Technology?

Blockchain is a technology that underpins the success of Bitcoin and other digital currencies, but it has uses far beyond finance. Learn how blockchain works and why it is proving disruptive to other areas of IT.

 

Author Comment

by:JasBrad
ID: 36498059
I get a "Run Time error'91':  
Object variable or With block variable not set

and when I go to Debug it goes to    Set qdf = db.CreateQueryDef("")


Private Sub CreateHolidayScheduleButton_Click()
'On Error GoTo Err_CreateHolidayScheduleButton_Click

    Dim db As Database
    Dim qdf As QueryDef
    Dim strSQL As String
    Dim rs As Object
    
    Set db = CurrentDb
    Set qdf = db.CreateQueryDef("")
           
    strSQL = " INSERT INTO AdjustedSchedule"
    strSQL = strSQL & " (FAMILY,PICKUP,LABEL,DOSE,HOLIDAY,PICKCODE,"
    strSQL = strSQL & " Mon,Tue,Wed,Thu,Fri,Sat,Sun)"
    strSQL = strSQL & " SELECT RegularSchedule.FAMILY,"
    strSQL = strSQL & " RegularSchedule.PICKUP,"
    strSQL = strSQL & " RegularSchedule.LABEL,"
    strSQL = strSQL & " RegularSchedule.DOSE,"
    strSQL = strSQL & " HolidayReference.+(left(DATENAME(WeekDay([forms]![Holiday]![DOW]),3))+ [forms]!Holiday]![Status],"
    strSQL = strSQL & " LookupPickupSchedule.PICKCODE,"
    strSQL = strSQL & " LookupPickupSchedule.Mon,"
    strSQL = strSQL & " LookupPickupSchedule.Tue,"
    strSQL = strSQL & " LookupPickupSchedule.Wed,"
    strSQL = strSQL & " LookupPickupSchedule.Thu,"
    strSQL = strSQL & " LookupPickupSchedule.Fri,"
    strSQL = strSQL & " LookupPickupSchedule.Sat,"
    strSQL = strSQL & " LookupPickupSchedule.Sun"
    strSQL = strSQL & " FROM (RegularSchedule"
    strSQL = strSQL & " INNER JOIN HolidayReference"
    strSQL = strSQL & " ON RegularSchedule.PICKCODE = HolidayReference.PickRef)"
    strSQL = strSQL & " INNER JOIN LookupPickupSchedule"
    strSQL = strSQL & " ON HolidayReference.+(left(DATENAME(WeekDay([forms]![Holiday]![DOW]),3))+ [forms]!Holiday]![Status]= LookupPickupSchedule.PICKCODE"
    strSQL = strSQL & " LEFT JOIN AdjustedSchedule ON LookupPickupSchedule.PICKCODE"
    strSQL = strSQL & " = AdjustedSchedule.PICKCODE"
    strSQL = strSQL & " WHERE RegularSchedule.Status = 'ACTIVE';"
    qdfSQL = strSQL
    qdf.Execute
        
    strSQL = " Update AdjustedSchedule"
    strSQL = strSQL & " Set AdjustedSchedule.HOLADJUST = [forms]![Holiday]![DOW],"
    strSQL = strSQL & " AdjustedSchedule.HOLIDAY = [forms]![Holiday]![Holiday],"
    strSQL = strSQL & " AdjustedSchedule.STATUS = 'Holiday';"
    qdfSQL = strSQL
    qdf.Execute
    
    strSQL = " Update RegularSchedule"
    strSQL = strSQL & " Inner Join AdjustedSchedule"
    strSQL = strSQL & " On RegularSchedule.Family = AdjustedSchedule.Family"
    strSQL = strSQL & " Set RegularSchedule.HolAdjust = [Forms]![Holiday]![DOW],"
    strSQL = strSQL & " RegularSchedule.STATUS = 'Holiday'"
    strSQL = strSQL & " Where(((AdjustedSchedule.Status)='Holiday'));"
    qdfSQL = strSQL
    qdf.Execute
    
    
    RefreshDatabaseWindow
    
    
Exit_CreateHolidayScheduleButton_Click:
    Exit Sub

Err_CreateHolidayScheduleButton_Click:
    MsgBox Err.Description
    Resume Exit_CreateHolidayScheduleButton_Click
    
End Sub

Open in new window

0
 
LVL 120

Expert Comment

by:Rey Obrero (Capricorn1)
ID: 36498179
change these
    Dim db As Database
    Dim qdf As QueryDef

with

    Dim db As DAO.Database
    Dim qdf As DAO.QueryDef

you need to add reference to DAO object library
0
 
LVL 59

Expert Comment

by:HainKurt
ID: 36498221
give a name

Set qdf = db.CreateQueryDef("MyQuery")
0
 
LVL 120

Expert Comment

by:Rey Obrero (Capricorn1)
ID: 36498264
this is acceptable

Set qdf = db.CreateQueryDef("")
0
 

Author Comment

by:JasBrad
ID: 36498269
I tried both.  Unfortunately it still fails with the same message at the same place.
0
 
LVL 59

Expert Comment

by:HainKurt
ID: 36498371
   qdfSQL = strSQL
    qdf.Execute
-->
    qdf.SQL = strSQL
    qdf.Execute

I dont have any issues with this code...
0
 

Author Comment

by:JasBrad
ID: 36498412
Same error.  Same line.
0
 
LVL 59

Expert Comment

by:HainKurt
ID: 36498415
put a breakpoint on top and see what are the values of

Dim db As Database
Dim qdf As QueryDef
Dim strSQL As String
Dim rs As Object

when it comes to line 12
0
 
LVL 120

Accepted Solution

by:
Rey Obrero (Capricorn1) earned 2000 total points
ID: 36498462
i just notice that you are using this in an ADP not .mdb or .accdb

you need to use ADO not DAO and Stored procedure not queryDef

see this similar thread


http://www.experts-exchange.com/Microsoft/Development/MS_Access/Access_Coding-Macros/Q_24736693.html
0
 

Author Comment

by:JasBrad
ID: 36498566
I commented out the lines referencing queryDef and added in DoCmd.RunSQL (strSQL), now I just have problems with my SQL coding.  Thanks.
0

Featured Post

Visualize your virtual and backup environments

Create well-organized and polished visualizations of your virtual and backup environments when planning VMware vSphere, Microsoft Hyper-V or Veeam deployments. It helps you to gain better visibility and valuable business insights.

Question has a verified solution.

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

In this series, we will discuss common questions received as a database Solutions Engineer at Percona. In this role, we speak with a wide array of MySQL and MongoDB users responsible for both extremely large and complex environments to smaller singl…
Instead of error trapping or hard-coding for non-updateable fields when using QODBC, let VBA automatically disable them when forms open. This way, users can view but not change the data. Part 1 explained how to use schema tables to do this. Part 2 h…
What’s inside an Access Desktop Database. Will look at the basic interface, Navigation Pane (Database Container), Tables, Queries, Forms, Report, Macro’s, and VBA code.
In Microsoft Access, when working with VBA, learn some techniques for writing readable and easily maintained code.

688 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