Solved

Help needed with Object variable or With Block Variable not Set

Posted on 2011-09-07
13
414 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
  • 5
  • 4
  • 4
13 Comments
 
LVL 51

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 119

Expert Comment

by:Rey Obrero
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
 

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 119

Expert Comment

by:Rey Obrero
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:HainKurt
ID: 36498221
give a name

Set qdf = db.CreateQueryDef("MyQuery")
0
Comprehensive Backup Solutions for Microsoft

Acronis protects the complete Microsoft technology stack: Windows Server, Windows PC, laptop and Surface data; Microsoft business applications; Microsoft Hyper-V; Azure VMs; Microsoft Windows Server 2016; Microsoft Exchange 2016 and SQL Server 2016.

 
LVL 119

Expert Comment

by:Rey Obrero
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: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 51

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 119

Accepted Solution

by:
Rey Obrero 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

Do You Know the 4 Main Threat Actor Types?

Do you know the main threat actor types? Most attackers fall into one of four categories, each with their own favored tactics, techniques, and procedures.

Join & Write a Comment

This article describes some very basic things about SQL Server filegroups.
Creating and Managing Databases with phpMyAdmin in cPanel.
Get people started with the utilization of class modules. Class modules can be a powerful tool in Microsoft Access. They allow you to create self-contained objects that encapsulate functionality. They can easily hide the complexity of a process from…
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.

746 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

8 Experts available now in Live!

Get 1:1 Help Now