Solved

Help needed with Object variable or With Block Variable not Set

Posted on 2011-09-07
13
440 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 56

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
Salesforce Has Never Been Easier

Improve and reinforce salesforce training & adoption using WalkMe's digital adoption platform. Start saving on costly employee training by creating fast intuitive Walk-Thrus for Salesforce. Claim your Free Account Now

 

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 56

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 56

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 56

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

What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

Question has a verified solution.

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

This post contains step-by-step instructions for setting up alerting in Percona Monitoring and Management (PMM) using Grafana.
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…
Video by: Steve
Using examples as well as descriptions, step through each of the common simple join types, explaining differences in syntax, differences in expected outputs and showing how the queries run along with the actual outputs based upon a simple set of dem…
In Microsoft Access, learn different ways of passing a string value within a string argument. Also learn what a “Type Mis-match” error is about.

635 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