UtterAccess.com
X   Site Message
(Message will auto close in 2 seconds)

Welcome to UtterAccess! Please ( Login   or   Register )

Custom Search
 
   Reply to this topicStart new topic
> Run-time error 2467 - object that is closed - Any Ideas?    
 
   
blackduck603
post Dec 14 2007, 12:21 PM
Post#1



Posts: 207
Joined: 4-October 07
From: NH


I am getting the following run-time error when executing a query on a listbox control.
The weird thing is it occurs the 2nd time the query is executed.
Run-time error 2467:
The expression you entered refers to an object that is closed or doesn't exist.
The error occurs on the following line of code in my LookupInventoryItemIdInDb() function:
Me.listboxInventoryItemID_HiddenCtrl = Null
Code shown in entirety below.
Here is what I am trying to do.
I have a listbox (listboxItemsToImport) with items that are going to be inserted into my db table InventoryItems.
I also need to add a record for each item to my ChangeHistory table.
I am parsing listboxItemsToImport to get the InvCtrlNum (unique desc assigned by user). I get this value and then call LookupInventoryItemIdInDb() to get it's ID inthe InventoryItems table.
LookupInventoryItemIdInDb() uses a listbox to get the ID via a query because I could NOT figure out any other way to store the result of a query in my code. so what happens is LookupInventoryItemIdInDb() gets called for each item in the listboxItemsToImport listbox and then is supposed to return the ID value. It works fine for the 1st item in the listbox. When the 2nd item calls listboxItemsToImport() I get the run-time error.
Any ideas?
I appreciate any comments, suggestions - especially ifthere is a better way to execute my SQL for the ID lookup - hopefully withouthaving to use controls.
THere is the code
NOTE: I added comments in UPPER CASE to point out where the problem arises
Private Sub AddChangeReasonForItems()
Dim intUpdateItemCount As Integer
Dim intRow As Integer
Dim sInvCtrlNumForImportItemBeingUpdated As String
Dim sInventoryItemID As String
Dim myInvItemIdsArray() As String
Dim intArrayRow As Integer
intUpdateItemCount = listboxItemsToImport.ListCount
ReDim myInvItemIdsArray(intUpdateItemCount)
' Put the listbox items in an array to avoid the run-time object error
For intRow = 0 To intUpdateItemCount - 1
sInvCtrlNumForImportItemBeingUpdated = listboxItemsToImport.Column(0, intRow)
myInvItemIdsArray(intRow) = sInvCtrlNumForImportItemBeingUpdated
Next intRow
Dim sInvCtrlNum As String
' Step thru the array and call LookupInventoryItemIdInDb() to get the ID for each item
' then add a change Reson record to the ChangeHistory table
For intArrayRow = 0 To intUpdateItemCount - 1
sInvCtrlNum = myInvItemIdsArray(intArrayRow)
' Get The InventoryItemID for this InvCtrlNum
sInventoryItemID = LookupInventoryItemIdInDb(sInvCtrlNum)
strSQL = "INSERT INTO [ChangeHistory]" _
& "(InventoryItemID, InvCtrlNum, ChangeDate, RequestorName, RequestorDivisionID," _
& "ApprovingDirectorName, ChangeReason, DateEnteredIntoDb) " _
& "VALUES ('" & sInventoryItemID & "','" & sInvCtrlNum & "', #" & Date & "#" _
& ",'Administrator', 'N/A', 'N/A', '" & Me.txtImportReason & "', #" & Date & "#);"
' Execute the SQL Query
DoCmd.SetWarnings(False)
DoCmd.RunSQL(strSQL)
DoCmd.SetWarnings(True)
' Close the Change History Form
DoCmd.Close()
Next intArrayRow
End Sub
'--------------------------------------------------------------------------------------
Public Function LookupInventoryItemIdInDb(ByVal sInvCtrlNum As String)
LookupInventoryItemIdInDb = -1
' Look up the InventoryItemID for this item
Dim strSQL As String
strSQL = "SELECT InventoryItemID FROM InventoryItems" & _
" WHERE InvCtrlNum = '" & sInvCtrlNum & "'"
'*******************************************************************************
' This should just return 1 item......sort of seems like overkill using a listbox
' BUT
' Access VBA does NOT seem to allow storage of Query results in any other control
'*******************************************************************************
' ERROR OCCURS HERE (Me.listboxInventoryItemID_HiddenCtrl = Null)
' ON THE 2ND CALL TO THE LookupInventoryItemIdInDb() FUNCTION
' 1ST TIME THRU IT WORKS GREAT.
Me.listboxInventoryItemID_HiddenCtrl = Null
Me.listboxInventoryItemID_HiddenCtrl.RowSource = strSQL
Me.listboxInventoryItemID_HiddenCtrl.Requery()
'*******************************************************************************
' ...And....furthermore....
' can't seem to just assign from a listbox
' ....need to step through
'*******************************************************************************
Dim sVal As String
Dim intRow As Integer
If listboxInventoryItemID_HiddenCtrl.ListCount > 0 Then
For intRow = 0 To listboxInventoryItemID_HiddenCtrl.ListCount - 1
sVal = listboxInventoryItemID_HiddenCtrl.Column(0, intRow)
LookupInventoryItemIdInDb = sVal
Next intRow
End If
End Function
Go to the top of the page
 
theDBguy
post Dec 14 2007, 12:31 PM
Post#2


UA Moderator
Posts: 77,712
Joined: 19-June 07
From: SunnySandyEggo


If this code is behind the form "Change History", then try flipping this part of the code
Close the Change History Form
DoCmd.Close()
Next intArrayRow
to
Next intArrayRow
' Close the Change History Form
DoCmd.Close()
HTH
Go to the top of the page
 
blackduck603
post Dec 14 2007, 01:19 PM
Post#3



Posts: 207
Joined: 4-October 07
From: NH


You pointed me right at the problem.
The DoCmd.Close should not have been in there.
It came from pasting some code from another form.
Thanks.....
Go to the top of the page
 
theDBguy
post Dec 14 2007, 01:22 PM
Post#4


UA Moderator
Posts: 77,712
Joined: 19-June 07
From: SunnySandyEggo


You're welcome. Good luck with your project.
Go to the top of the page
 


Custom Search


RSSSearch   Top   Lo-Fi    30th March 2020 - 11:43 AM