[2 days left] What’s wrong with your cloud strategy? Learn why multicloud solutions matter with Nimble Storage.Register Now

x
?
Solved

Object variable or with block variable not set

Posted on 2006-06-26
27
Medium Priority
?
398 Views
Last Modified: 2007-12-19
Hello All,

I'm getting the above error and can't seem to find the problem.

Here is my code...

Private Sub cmdNew_Record_Click()

On Error GoTo Err_cmdNew_Record_Click
[Form_LOC Data Entry Form].Facility_Entity_Code.SetFocus


If Len([Form_LOC Data Entry Form].Facility_Entity_Code.Text) > 0 Then

    Dim FEC As String
    Dim strSQL As String
     
    '---------Variable that gets the FEC that was input.---------------------
    FEC = [Form_LOC Data Entry Form].Facility_Entity_Code.Text
   
    '---------Looks for that FEC in the LOC table---------------------
    Dim dbs As Database, qdf As QueryDef
    Set dbs = CurrentDb
   
    For i = 0 To dbs.QueryDefs.Count - 1 'deletes the qryLocateFEc if it is there
      If dbs.QueryDefs(i).Name = "qryLocateFEC" Then
           dbs.QueryDefs.Delete ("qryLocateFEC")
      End If
   Next
       
        strSQL = "SELECT LOC.LOC_Number, LOC.Facility_Entity_Code, LOC.PM_Contract_ID, dbo_Contracts.Contract_Type, LOC.Status" & _
        " FROM dbo_Contracts INNER JOIN (dbo_Facility INNER JOIN LOC ON dbo_Facility.Facility_entify_code=LOC.Facility_Entity_Code) ON dbo_Contracts.PM_Contract_ID=LOC.PM_Contract_ID " & _
        " WHERE (LOC.Facility_Entity_Code)" & _
         " = """ + FEC + """" & _
        " AND(LOC.Status)=""Active""" + ";"
 
        MsgBox strSQL
   
    Set rst = dbs.OpenRecordset(strSQL)
   
    If rst.RecordCount > 0 Then
        '---------FEC is in the table---------------------
         'ADD THE NEW SQL COMMAND HERE TO PICK THE CONTRACTS THAT YOU WANT TO SHOW
         
         'Grabs all  records (and Contract types) already in the LOC for the FEC selected.
     
      strSQL = "SELECT LOC.LOC_Number, LOC.Facility_Entity_Code, LOC.PM_Contract_ID, dbo_Contracts.Contract_Type" & _
                " FROM dbo_Contracts INNER JOIN LOC ON dbo_Contracts.PM_Contract_ID = LOC.PM_Contract_ID" & _
                " WHERE LOC.Facility_Entity_Code" & _
                "= """ + FEC + """"
   
      Set qdf = dbs.CreateQueryDef("qryAlreadyselectedforcurrentFEC", strSQL)
           
      MsgBox strSQL
   
   'Uses the last query to remove those contract types already selected (in LOC table)
   'from the Available contracts to get what should be in the contract combo as available contracts.
     strSQL = "SELECT dbo_Contracts.PM_Contract_ID" & _
            " FROM dbo_Contracts LEFT JOIN qryAlreadySelectedforcurrentFEC ON " & _
            " dbo_Contracts.Contract_Type = qryAlreadySelectedforcurrentFEC.Contract_Type " & _
            " WHERE (((qryAlreadySelectedforcurrentFEC.Contract_Type) Is Null));"

          Set qdf = dbs.CreateQueryDef("qryContractChoicesforCurrentFEC", strSQL)

    MsgBox strSQL
        Me.combo_contractquery.Enabled = True 'combo box contract query becomes enabled
        combo_contractquery.RowSource = strSQL  'to refresh rowsource from new query
        combo_contractquery.Requery  'refresh
               
    Else 'creates new record
    '---------FEC was not found, so it will be a new record.--------------------
   
        strSQL = "SELECT PM_Contract_ID FROM dbo_Contracts "
        Me.combo_contractquery.Recordset = strSQL 'new record set
   
    '---------Add the query to the control source of Contract ---------
        Set qdf = dbs.CreateQueryDef("qryLocateFEC", strSQL)
       
        Me.cboStatus.Enabled = True
        Me.combo_contractquery.Enabled = True
        Me.ComboA.Enabled = True
        Me.ComboB.Enabled = True
        Me.ComboC.Enabled = True
       
        DoCmd.GoToRecord , , acNewRec  'add new FEC
       
    End If
Else
    '---------Add a notification and exit the code ---------
    MsgBox "NOTHING WAS ENTERED", vbOKOnly
    Exit Sub

End If

Exit_cmdNew_Record_Click:
    Exit Sub

Err_cmdNew_Record_Click:
    MsgBox Err.Description
    Resume Exit_cmdNew_Record_Click
   
End Sub



0
Comment
Question by:printmedia
[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
  • 8
  • 7
  • 7
  • +2
27 Comments
 
LVL 66

Assisted Solution

by:Jim Horn
Jim Horn earned 200 total points
ID: 16987792
You appear to be missing... Dim rst as Recordset
0
 
LVL 65

Expert Comment

by:rockiroads
ID: 16987807
What is [Form_LOC Data Entry Form].Facility_Entity_Code.SetFocus?

I think thats where its failing

Is [Form_LOC Data Entry Form]

another form or current form?

if current, just Me.Facility_Entity_Code.SetFocus

0
 
LVL 54

Expert Comment

by:nico5038
ID: 16987812
Hmm, hard to tell without knowing the error line.

It's however wise to check the recordset to be filled before checking a .recordcount after:
    Set rst = dbs.OpenRecordset(strSQL)

Best to add:

IF rst.eof and rs.bof then
   ' action for nothing found, like message and exit sub
end if

Nic;o)
0
Veeam Disaster Recovery in Microsoft Azure

Veeam PN for Microsoft Azure is a FREE solution designed to simplify and automate the setup of a DR site in Microsoft Azure using lightweight software-defined networking. It reduces the complexity of VPN deployments and is designed for businesses of ALL sizes.

 

Author Comment

by:printmedia
ID: 16987828
that is funny i just did that right before you put it but same error.... .
0
 
LVL 54

Expert Comment

by:nico5038
ID: 16987836
When it's a missing DIM, then check the "Require variable decalrations" property you can set in VBA mode with Tools/Options.
Will prevent that from happening.

Nic;o)
0
 

Author Comment

by:printmedia
ID: 16987899
Rockiroads, i try doing your code and now i get an error message says Else without if
0
 

Author Comment

by:printmedia
ID: 16987912
oh and it points to the part:

Else
    '---------Add a notification and exit the code ---------
    MsgBox "NOTHING WAS ENTERED", vbOKOnly
0
 
LVL 54

Expert Comment

by:nico5038
ID: 16987930
Did you add the .eof / .bof test before that recordcount if ?

Nic;o)
0
 
LVL 65

Expert Comment

by:rockiroads
ID: 16987963
printmedia, that code only there if its local form and it was only a suggestion
where did u change it?

I only pointed to 1st line as an example

u have it in other places
e.g

[Form_LOC Data Entry Form].Facility_Entity_Code.SetFocus


If Len([Form_LOC Data Entry Form].Facility_Entity_Code.Text) > 0 Then


can u try step thru the code, place a breakpoint on the 1st line

[Form_LOC Data Entry Form].Facility_Entity_Code.SetFocus

then step thru hitting F8, then it will tell u

otherwise comment out your error handle
it will then fall over the line it doesnt like, in runtime, the msgbox will show Debug, click on that

what dont it like
0
 
LVL 65

Expert Comment

by:rockiroads
ID: 16987968
another thing
this is good practice

on the top of this module, put in

option explicit

(below option compare database)

then do a debug/compile

it will complain about any undefined variables
this may help sort out your isses
0
 
LVL 44

Expert Comment

by:Leigh Purvis
ID: 16987976
Do you have Option Explicit specified at the top of your module?
0
 

Author Comment

by:printmedia
ID: 16987978
i am not too sure where do you mean.. where exactly?
0
 
LVL 44

Expert Comment

by:Leigh Purvis
ID: 16987981
lol I'd expected maybe one post before I pressed submit... but two?? :-)
0
 
LVL 54

Assisted Solution

by:nico5038
nico5038 earned 200 total points
ID: 16987989
Hmm, rocky, see my:
When it's a missing DIM, then check the "Require variable decalrations" property you can set in VBA mode with Tools/Options.
That way it's done automatically by Access :-)

Nic;o)
0
 
LVL 44

Expert Comment

by:Leigh Purvis
ID: 16988006
I'd personally expect a problem on the line
Me.combo_contractquery.Recordset = strSQL 'new record set
0
 
LVL 44

Expert Comment

by:Leigh Purvis
ID: 16988011
Me.combo_contractquery.Rowsource = strSQL

would be more appropriate
0
 
LVL 65

Expert Comment

by:rockiroads
ID: 16988059
Yea Nico, but Im so used to doing it in code
Still not used to all these Access shortcuts!
Thanks for the tip though

printmedia, its like I said

where u see Option Compare Database (1st line in your code)
put it beneath that

e.g.

Option Compare Database
Option Explicit


remember to comment out the error handler

'On Error GoTo Err_cmdNew_Record_Click

this is needed to help find the suspect line

0
 
LVL 65

Expert Comment

by:rockiroads
ID: 16988066
Shevshenko, Im sure he is overrated, lets see how well he does for Chelsea
He has just missed a penalty
Dull affair mind u
0
 
LVL 65

Expert Comment

by:rockiroads
ID: 16988072
printmedia

alternative, use the tip from Nico


another missed penalty, damn my granny can do better
0
 
LVL 65

Assisted Solution

by:rockiroads
rockiroads earned 800 total points
ID: 16988136
Dull game, dull penalty shootout. Swiss were so pathetic.
Anyways Im off, printmedia, your in safe hands, with Jim, Nico and LPurvis.

Hopefully the tips Ive suggested will help you narrrow down the problem
0
 
LVL 44

Expert Comment

by:Leigh Purvis
ID: 16988149
Yeah - but ultimately all "Require variable declarations" does is insert Option Explicit into new modules for you.
Option Explicit still needs to be there of course :-)

But I still say the problem should be on that line I mentioned :-D
0
 
LVL 44

Accepted Solution

by:
Leigh Purvis earned 800 total points
ID: 16988225
But once Option Explicit is in place I think you'll find a few problems that have been pointed out over the course of this question.
(For example Jim's  rst declaration)
0
 

Author Comment

by:printmedia
ID: 16992382
hi guys, i tried putting the option explicit , also check for the require variable declarations, also Me.combo_contractquery.Rowsource = strSQL but again it goes to the same error message as in the beginning....


 it keeps pointing at the part

DoCmd.GoToRecord , , acNewRec  'add new FEC
       
    End If
Else
    '---------Add a notification and exit the code ---------
    MsgBox "NOTHING WAS ENTERED", vbOKOnly
    End If
    Exit Sub

and it points at the "Else" and says that there is a Else without if.
0
 
LVL 44

Expert Comment

by:Leigh Purvis
ID: 16992727
OK - but that's a different error message than before surely?
Take heart - you've also fixed several other errors that _were_ there.

A quick count of your If's/Else/End If's looks OK to me though (in your original post that is - it looks like it's changed since then).
0
 

Author Comment

by:printmedia
ID: 16992800
you are right LPurvis i do another post for this other error , thanks!!!
0
 
LVL 44

Expert Comment

by:Leigh Purvis
ID: 16992812
I mean post your code as it currently stands here.
0
 

Author Comment

by:printmedia
ID: 16992875
it is ok, i just posted the other question. please let me know if you know the answer
0

Featured Post

Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

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

This article describes two methods for creating a combo box that can be used to add new items to the row source -- one for simple lookup tables, and one for a more complex row source where the new item needs data for several fields.
If you need a simple but flexible process for maintaining an audit trail of who created, edited, or deleted data from a table, or multiple tables, and you can do all of your work from within a form, this simple Audit Log will work for you.
With Microsoft Access, learn how to specify relationships between tables and set various options on the relationship. Add the tables: Create the relationship: Decide if you’re going to set referential integrity: Decide if you want cascade upda…
Do you want to know how to make a graph with Microsoft Access? First, create a query with the data for the chart. Then make a blank form and add a chart control. This video also shows how to change what data is displayed on the graph as well as form…

649 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