Object Required!!!!!!

[crleacock]

My company is using an Access 2000 database for truck dispatching and last night I made a few changes. I added about 4 tables, one query and one subform. Now when they try to perform a function totally unrelated to what I added a small window pops up that says, "Object required" and that's it. I restored a version from three days ago and that one does it now too. It was working fine yesterday afternoon so what could have gone wrong? Any ideas at all would be wonderful.

Thanks,
CL

 

[vbajock]

Can you break into the code when the error message pops up by hitting Ctl/Break?

 

[cmmrfrds]

Somehow when you were working on the program the reference library was changed. Check that you have all the reference libraries set and the version you need. References are set under Tools ---> References

 

[vbajock]

That wouldn't explain why the earlier one wouldn't work, unless he manually unregistered something. He needs to post the line his code is bombing on.

 

[crleacock]

Here's the code. It's probably more than needed but since I'm such a novice I'll include everything. The line it seems to stop on is in red. Thanks for all your help on this. I really appreciate it.


Private Sub cmdSave_Click()
On Error GoTo Err_cmdSave_Click

Dim validAction As Boolean
Dim insertOrder As Boolean
Dim strSQL As String
Dim iWarehouse As Integer
Dim newStatus As String
Dim curStatus As String
Dim strOrderStatus As String
Dim isDone As Boolean
Dim firstPass As Boolean
Dim strOrderNum As String
Dim strTemp As String
Dim iTruckId As Integer

validAction = False
insertOrder = False
iWarehouse = 0

[Form]![subfrmChooseTruckStatus]![CmbStatus].SetFocus
If IsNull([Form]![subfrmChooseTruckStatus]![CmbStatus].Text) Then
MsgBox ("Please select a new status.&quot
GoTo Exit_cmdSave_Click
End If

newStatus = [Form]![subfrmChooseTruckStatus]![CmbStatus].Text
curStatus = Text43.Value
If newStatus = "Home" Then
If curStatus = "Down" Or curStatus = "Inbound" Or _
curStatus = "" Then
validAction = True
End If

ElseIf newStatus = "Down" Then
If curStatus = "Home" Or curStatus = "Inbound" Then
validAction = True
End If

ElseIf newStatus = "Inbound" Then
If curStatus = "Outbound" Then
cmbWarehouse.SetFocus
If cmbWarehouse.Text = "" Then
MsgBox ("Please select a warehouse.&quot
GoTo Exit_cmdSave_Click
Else
insertOrder = True
iWarehouse = cmbWarehouse.Value
validAction = True
End If
End If

ElseIf newStatus = "Being Loaded" Then
MsgBox ("Must assign orders from the Order Assignment Form.&quot
GoTo Exit_cmdSave_Click

ElseIf newStatus = "Outbound" Then
If curStatus = "Being Loaded" Then
insertOrder = True
validAction = True
End If

End If

If validAction = False Then
If curStatus = "" Then
strTemp = "Home"
Else
strTemp = curStatus
End If

MsgBox ("Cannot change to the " & newStatus & " status from the " & strTemp & " status&quot
GoTo Exit_cmdSave_Click
Else
Set dbs = CurrentDb()

strSQL = "SELECT fkTruckId, fkDriverId, fkOrderNumber, Status FROM qryTruckHistory WHERE TruckNumber = " & TruckNumber.Value
Set rst = dbs.OpenRecordset(strSQL)
isDone = False
firstPass = True
If rst.EOF Then

AddTruckStatus Me.pkId, 0, "", 0, newStatus

Else

While ((Not (rst.EOF)) And (isDone = False))
If rst![Status] = curStatus Then

If IsNull(rst![fkOrderNumber]) Then
strOrderNum = ""
Else
strOrderNum = rst![fkOrderNumber]
End If

If firstPass = True Then
AddTruckStatus rst![fkTruckId], rst![fkDriverId], _
strOrderNum, iWarehouse, newStatus
End If

If newStatus = "Inbound" Then
firstPass = False
End If

If insertOrder = True Then
strOrderStatus = newStatus
If strOrderStatus = "Inbound" Then
strOrderStatus = "Delivered"
End If

AddOrderStatus strOrderStatus, _
rst![fkOrderNumber], rst![fkTruckId]
End If

rst.MoveNext
Else
isDone = True
End If
Wend

End If

' Close the result set
rst.Close

' Close the database.
dbs.Close

[Form]![subformTruckHistory].Requery
[Dispatch].Form_frmScheduler.subfrmAvailableTrucks.Requery
[Dispatch].Form_frmScheduler.subfrmOpenOrders.Requery

End If

Exit_cmdSave_Click:
TruckNumber.SetFocus
Exit Sub

Err_cmdSave_Click:
MsgBox Err.Description
Resume Exit_cmdSave_Click

End Sub
________________________________
Private Sub AddOrderStatus(strOrderStatus As String, _
strOrderNum As String, _
iTruckId As Integer)

Set dbs = CurrentDb()

strSQL = "SELECT pkId from SysLookUpOrderStatus WHERE " _
& "Status = '" & strOrderStatus & "'"
Set rst = dbs.OpenRecordset(strSQL)
If (rst.EOF) Then
rst.Close
Exit Sub
Else
iStatusId = rst![pkId]
End If
rst.Close

strInsert = "INSERT INTO OrderStatus (fkOrderNumber, DateRequired, TimeRequired, DateRequested, TimeRequested, fkOrderStatus, TruckType, fkTruckId)" _
& " Values ('" & SalesOrderNumber.Value & "', '" & Requiredshipdat.Value & "', '" & Timematerialreq.Value & "', '" & Datematrequest.Value & "','" & Timeordplaced.Value & "'," & iStatusId & ",'" & Trucktype.Value & "', " & iTruckId & &quot"

DoCmd.SetWarnings (False)
DoCmd.RunSQL (strInsert)
DoCmd.SetWarnings (True)

' Close the database.
dbs.Close

End Sub
__________________________________________
Private Sub AddTruckStatus(iTruckId As Integer, _
iDriverId As Integer, _
strOrder As String, _
iWarehouse As Integer, _
newStatus As String)

Dim iStatusId As Integer
Dim strTempNum As String

If IsNull(strOrder) Then
strOrderNum = ""
Else
strOrderNum = strOrder
End If

iStatusId = [Form]![subfrmChooseTruckStatus]![CmbStatus]
strTempNum = strOrderNum
If newStatus = "Inbound" Then
strTempNum = ""
End If

If iWarehouse = 0 Then
strInsert = "INSERT INTO TruckStatus (fkTruckId, fkTruckStatusId, fkDriverId, fkOrderNumber)" _
& " Values (" & iTruckId & ", " & iStatusId & ", " & iDriverId & ", '" & strTempNum & "')"
Else
strInsert = "INSERT INTO TruckStatus (fkTruckId, fkTruckStatusId, fkDriverId, fkOrderNumber, Warehouse)" _
& " Values (" & iTruckId & ", " & iStatusId & ", " & iDriverId & ", '" & strTempNum & "', " & iWarehouse & &quot"
End If

DoCmd.SetWarnings (False)
DoCmd.RunSQL (strInsert)
DoCmd.SetWarnings (True)

End Sub
____________________________________________
Private Sub cmdExit_Click()
On Error GoTo Err_cmdExit_Click

DoCmd.Close

Exit_cmdExit_Click:
Exit Sub

Err_cmdExit_Click:
MsgBox Err.Description
Resume Exit_cmdExit_Click

End Sub
__________________________________________
Private Sub cmdFind_Click()
On Error GoTo Err_cmdFind_Click


Screen.PreviousControl.SetFocus
DoCmd.DoMenuItem acFormBar, acEditMenu, 10, , acMenuVer70

Exit_cmdFind_Click:
Exit Sub

Err_cmdFind_Click:
MsgBox Err.Description
Resume Exit_cmdFind_Click

End Sub
_______________________________________
Private Sub Form_Current()

Dim strSQL As String

strSQL = "SELECT Status from qryLatestTruckDetails WHERE TruckNumber = " & TruckNumber.Value
Set dbs = CurrentDb()
Set rst = dbs.OpenRecordset(strSQL)
If (rst.EOF) Then
Text43.Value = ""
Else
Text43.Value = rst![Status]
End If

' Close the database.
rst.Close
dbs.Close

End Sub
__________________________________________
Private Sub TruckNumber_GotFocus()

Dim strSQL As String

strSQL = "SELECT Status from qryLatestTruckDetails WHERE TruckNumber = " & TruckNumber.Value
Set dbs = CurrentDb()
Set rst = dbs.OpenRecordset(strSQL)
If (rst.EOF) Then
Text43.Value = ""
Else
Text43.Value = rst![Status]
End If

' Close the database.
rst.Close
dbs.Close

End Sub
_________________________________________
Private Sub cmdUndo_Click()
On Error GoTo Err_cmdUndo_Click

Dim prevStatus As String
Dim orderPrevStatus As String
Dim strSQL As String
Dim delOrders As Boolean
Dim strStatusDate As String
Dim statusDate As Date

If MsgBox("Are you sure you want to undo the last action?", vbOKCancel) = vbOK Then
Set dbs = CurrentDb()

strSQL = "SELECT Date, pkTruckStateId, Status FROM qryTruckHistory WHERE TruckNumber = " & TruckNumber.Value
Set rst = dbs.OpenRecordset(strSQL)
prevStatus = rst.[Status]
orderPrevStatus = rst.[Status]
While ((Not (rst.EOF)) And (prevStatus = rst.[Status]))
If prevStatus = rst.[Status] Then
strStatusDate = rst.[Date]
statusDate = CDate(strStatusDate)

DeleteTruckStatus rst.[pkTruckStateId]

delOrders = False
If (orderPrevStatus = "Being Loaded&quot Or _
(orderPrevStatus = "Outbound&quot Then
delOrders = True
ElseIf orderPrevStatus = "Inbound" Then
delOrders = True
orderPrevStatus = "Delivered"
End If

If delOrders = True Then
DeleteOrderStatus statusDate, orderPrevStatus
End If

End If

rst.MoveNext
Wend

rst.Close
dbs.Close
Else
MsgBox ("Records not deleted.&quot
End If

[Form]![subformTruckHistory].Requery
[Dispatch].Form_frmCurrentOrderStatus.Requery
[Dispatch].Form_frmModifyOrder.Requery
[Dispatch].Form_frmOrderHistory.subfrmOrderHistory.Requery
[Dispatch].Form_frmScheduler.subfrmOpenOrders.Requery
[Dispatch].Form_frmScheduler.subfrmAvailableTrucks.Requery

Exit_cmdUndo_Click:
Exit Sub

Err_cmdUndo_Click:
MsgBox Err.Description
Resume Exit_cmdUndo_Click

End Sub
____________________________________________
Private Sub DeleteTruckStatus(TruckStateId As Long)

Set dbs = CurrentDb()
dbs.Execute ("DELETE * FROM TruckStatus WHERE pkTruckStateId =" & TruckStateId)
dbs.Close

End Sub

Private Sub DeleteOrderStatus(orderDate As Date, _
OrderStatus As String)

Dim strSQL As String
Dim iStatus As Integer

Set dbs = CurrentDb()

strSQL = "SELECT pkId FROM SysLookUpOrderStatus WHERE Status = '" & _
OrderStatus & "'"
Set rst = dbs.OpenRecordset(strSQL)
iStatus = rst.[pkId]

dbs.Execute ("DELETE * from OrderStatus WHERE Date =#" & _
orderDate & "# AND fkOrderStatus =" & iStatus)
dbs.Close

End Sub
____________________________________________
Private Sub Form_Open(Cancel As Integer)
If [Dispatch].globals.strCurrentUser <> &quot;SuperUser&quot; And _
[Dispatch].globals.strCurrentUser <> &quot;Dispatch&quot; Then
MsgBox &quot;You do not have permission to perform this function&quot;, vbExclamation
DoCmd.Close
End If
End Sub

 

[vbajock]

Most likely, one of these objects has been renamed or deleted:

SalesOrderNumber.Value
Requiredshipdat.Value
Timematerialreq.Value
Datematrequest.Value
Timeordplaced.Value
Trucktype.Value
Put the word Stop on the line immediately before the
strSql =blah blah statement

When the code hits the stop, it will pop into the editor and the stop statement will be highlighted. Open the immediate window (from the editor, View, Immediate window) and type

? SalesOrderNumber.Value
Hit enter. It will display the value of the object or give you the same Object Required error.

Do this for each one of these objects until you find the offending one.
Don't forget to delete the stop statement when you put it back in production.









Put the word Stop after the strSql code in red. Run the code. when it hits the stop