Error 91

[kronar30]

I keep getting "Object variable or With Block variable not set"
Is there any easy way to figure out which variable? I don't find any with blocks in this modules code.
Thanks

 

[MajP]

Most likely you are trying to use an object that is not yet set
ex:
dim frm as access.form
messagebox frm.name

The code should break and hilite the area. Show us the highlighted code and procedure.

 

[kronar30]

Here is the code as it fails.

Option Compare Database
Option Explicit

Dim db As Database
Dim temprec As Recordset
Dim JBrec As Recordset
Dim JBTrec As Recordset
Dim JBTdaterec As Recordset
Dim JBTjobrec As Recordset
Dim JBTemprec As Recordset
Dim EMrec As Recordset
Dim EMTrec As Recordset

Dim EMTrecNo As Integer

Dim strSQL As String

Dim saveBidNo As Integer
Dim BidNumError As Integer

Dim hldDate As Date
Dim hldJobNum As Integer
Dim hldTotSqrs As Integer
Dim hldFloors As Integer
Dim hldEmpNo As Integer
Dim hldName As String
Dim hldDOW As String
Dim hldWorkHrs As Integer
Dim RecFound As Boolean
Dim EditRecchk As Boolean
Const strQuote = """"
Const strDQuote = "" '""
Const ErrColor = True
Const MsgColor = False


Private Sub Form_Load()
Stop
MsgBox "Form_Load"
'txtJobBidNo.SetFocus

'Call GetRecsEnterTimes("")

'chkEDITrecs.Value = False
'chkAddTempRec.Value = False
'lsbEmpList.Visible = True

'EnterDate.SetFocus

'Call Open_JBJobBid_Table
txtErrorText.Visible = False

saveBidNo = 0
BidNumError = 0
hldJobNum = 0
hldDate = 0
hldTotSqrs = 0
hldFloors = 0
hldEmpNo = 0
hldWorkHrs = 0
hldDOW = ""
txtDumpTot = 0
txtBidBondTot = 0
txtGuarantyTot = 0
txtAccesTot = 0
txtOfficeTot = 0
txtPermitsTot = 0
txtRentalTot = 0
txtOH_TTot = 0

txtDateBid.Value = Date
DoCmd.OpenForm "frmFindNewBidNumber"


End Sub

In FormLoad the stop executes.
F5 and the msgbox displays.
F5 again and the error box appears.
txtErrorText is a text box on the form.

 

[MajP]

Normally this would hilite a specific line in the code. Which line does it break on? Without that it is pretty hard to tell. Not even sure if it is breaking in this code. Does not look like it would. If you have subforms they load prior to the main form. What happens if you put error checking in this code?
Could it be in the onload of frmFindBidNumber?

 

[kronar30]

This is the code that displays when it hangs up on the error. How do I do error checking??

Here is code for frmFindNewBidNumber:

Option Compare Database
Option Explicit

Dim db As Database
Dim JBrec As Recordset
Dim TRrec As Recordset

Dim NextBidNo As Integer
Const strQuote = """"



Private Sub Form_Load()
NextBidNo = 0
On Error GoTo Err_Open_TR_Tax
'Set db = CurrentDb()
'Set db = db.OpenRecordset("JB_Jobs", dbOpenDynaset)

Dim selstr As String
Set db = CurrentDb()

selstr = "Select DistinctRow TR_Tax.TR_TaxRate, TR_Tax.TR_BidUsedNo from TR_Tax"
Set TRrec = db.OpenRecordset(selstr, dbOpenDynaset)

If TRrec.RecordCount = 0 Then
MsgBox "There are no records from the Employee Time Query"
Exit Sub
End If

NextBidNo = TRrec(1) + 1

'update TRrec


'Verify that this bid does not exist
On Error GoTo Err_Open_JBrec
JBrec.FindFirst "JB_JobBidNo = " & strQuote & NextBidNo & strQuote
If JBrec.NoMatch Then
GoTo Exit_FormLoad
Else
MsgBox "Bid Number already in use - try again."
End If





Exit_FormLoad:
Exit Sub

Err_Open_JBrec:
MsgBox Err.Description
Resume Exit_FormLoad

Err_Open_TR_Tax:
MsgBox Err.Description
Resume Exit_FormLoad

End Sub

 

[randy700]

If you're getting an error that displays a portion of your code, one line should be hilited.
[COLOR=black yellow]It would look like this line.[/color]
We need to know which line is hilited.


Randy

 

[MajP]

Your code is a mess. You need to dimension all variables at the top of the code, not within the code. It will make seeing the problems a lot clearer. What is the need for all the module level variables? But again until you give us a hilited line I am just guessing.

Your first problem is here:
JBrec.FindFirst "JB_JobBidNo = " & strQuote & NextBidNo & strQuote
JBrec is never set

Problem two:
Set db = CurrentDb()
Set db = db.OpenRecordset("JB_Jobs", dbOpenDynaset)

The open recordset method returns a recordset and you are assinging it to db which is dimensioned as a database

No idea what this code does:
NextBidNo = TRrec(1) + 1

This is a waste of code
nextBidNo = 0
If you dimension it as an integer it defauls always to 0. No exception.

This is a waste of code
set db = currentdb
You only use it once, just use currentdb.

Explicitly dimenion dao and ado objects
dim rs as dao.recordset
not
dim rs as recordset

do not need the strQuote = """"
just use '

I have never seen multiple error statements.
'update TRrec

athough this is legit, I think you are begging for a problem with error checking:
TRrec(1)
call the field out so someone knows what is going on
TRrec.fields("fldName)
or
TRrec!FieldName

If you have a subroutine called update, that is a reserved word. Choose another name. Your code will fail:
update TRec
If you are trying to actually update the field

TRec.edit
TRrec!FieldName = somevalue
TRrec.update


{code]
Private Sub Form_Load()
On Error GoTo Err_Label
Dim JBrec As dao.Recordset
Dim TRrec As dao.Recordset
Dim NextBidNo As Integer
Dim selstr As String

selstr = "Select DistinctRow TR_Tax.TR_TaxRate, TR_Tax.TR_BidUsedNo from TR_Tax"
Set TRrec = CurrentDb.OpenRecordset(selstr, dbOpenDynaset)
Set JBrec = something

If TRrec.RecordCount = 0 Then
MsgBox "There are no records from the Employee Time Query"
Else
NextBidNo = TRrec!FieldName + 1
Call myUpdate(TRrec)
JBrec.FindFirst "JB_JobBidNo = '" & NextBidNo & "'"
If Not JBrec.NoMatch Then
MsgBox "Bid Number already in use - try again."
End If
End If

exit_FormLoad:
Exit Sub

Err_Label:
MsgBox Err.description
Resume exit_FormLoad

End Sub
[/code]