Solved

Help needed with Object variable or With Block Variable not Set

Posted on 2011-09-07
13
418 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
U.S. Department of Agriculture and Acronis Access

With the new era of mobile computing, smartphones and tablets, wireless communications and cloud services, the USDA sought to take advantage of a mobilized workforce and the blurring lines between personal and corporate computing resources.

 
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

Enterprise Mobility and BYOD For Dummies

Like “For Dummies” books, you can read this in whatever order you choose and learn about mobility and BYOD; and how to put a competitive mobile infrastructure in place. Developed for SMBs and large enterprises alike, you will find helpful use cases, planning, and implementation.

Question has a verified solution.

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

Many companies are looking to get out of the datacenter business and to services like Microsoft Azure to provide Infrastructure as a Service (IaaS) solutions for legacy client server workloads, rather than continuing to make capital investments in h…
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…
Familiarize people with the process of utilizing SQL Server views from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Microsoft Access…
Familiarize people with the process of utilizing SQL Server stored procedures from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Micr…

911 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

22 Experts available now in Live!

Get 1:1 Help Now