Solved

Help needed with Object variable or With Block Variable not Set

Posted on 2011-09-07
13
431 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 51

Expert Comment

by:Huseyin KAHRAMAN
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
Windows Server 2016: All you need to know

Learn about Hyper-V features that increase functionality and usability of Microsoft Windows Server 2016. Also, throughout this eBook, you’ll find some basic PowerShell examples that will help you leverage the scripts in your environments!

 

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 51

Expert Comment

by:Huseyin KAHRAMAN
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 51

Expert Comment

by:Huseyin KAHRAMAN
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 51

Expert Comment

by:Huseyin KAHRAMAN
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

The Eight Noble Truths of Backup and Recovery

How can IT departments tackle the challenges of a Big Data world? This white paper provides a roadmap to success and helps companies ensure that all their data is safe and secure, no matter if it resides on-premise with physical or virtual machines or in the cloud.

Question has a verified solution.

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

These days, all we hear about hacktivists took down so and so websites and retrieved thousands of user’s data. One of the techniques to get unauthorized access to database is by performing SQL injection. This article is quite lengthy which gives bas…
You need to know the location of the Office templates folder, so that when you create new templates, they are saved to that location, and thus are available for selection when creating new documents.  The steps to find the Templates folder path are …
Show developers how to use a criteria form to limit the data that appears on an Access report. It is a common requirement that users can specify the criteria for a report at runtime. The easiest way to accomplish this is using a criteria form that a…
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.

735 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