asked on

Object variable or with block variable not set

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

SOLUTION

Jim Horn

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

rockiroads

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

nico5038

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)

printmedia

ASKER

that is funny i just did that right before you put it but same error.... .

nico5038

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)

printmedia

ASKER

Rockiroads, i try doing your code and now i get an error message says Else without if

printmedia

ASKER

oh and it points to the part:

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

nico5038

Did you add the .eof / .bof test before that recordcount if ?

Nic;o)

rockiroads

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

rockiroads

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

Leigh Purvis

Do you have Option Explicit specified at the top of your module?

printmedia

ASKER

i am not too sure where do you mean.. where exactly?

Leigh Purvis

lol I'd expected maybe one post before I pressed submit... but two?? :-)

SOLUTION

nico5038

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

Leigh Purvis

I'd personally expect a problem on the line
Me.combo_contractquery.Recordset = strSQL 'new record set

Leigh Purvis

Me.combo_contractquery.Rowsource = strSQL

would be more appropriate

rockiroads

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

rockiroads

Shevshenko, Im sure he is overrated, lets see how well he does for Chelsea
He has just missed a penalty
Dull affair mind u

rockiroads

printmedia

alternative, use the tip from Nico

another missed penalty, damn my granny can do better

SOLUTION

rockiroads

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

Leigh Purvis

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

ASKER CERTIFIED SOLUTION

Leigh Purvis

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

printmedia

ASKER

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.

Leigh Purvis

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).

printmedia

ASKER

you are right LPurvis i do another post for this other error , thanks!!!

Leigh Purvis

I mean post your code as it currently stands here.

printmedia

ASKER

it is ok, i just posted the other question. please let me know if you know the answer