Not sure to understand your exact problem here. Is this a bound control
or
an unbound control and what kind of rowsource is used for this ComboBox
and
is the function SelectNextIndex() directly called by the even click of
the
button?
With ADP, there are many requeries that are done excessively. One wayto
solve this is to use a full sql string (with a Select statement or with
an
EXEC statement) instead of using the name of a stored procedure.
There is also the possibility that it's your code that is making these
double calls to be made or a problem with the ADP interface (there are
many
of them).
Sorry for not being 100% concise.
1. The combo box is unbound.
2. The record source a select query
3. Above the combo box there is a button that calls the SelectNextIndex()
function.
I don't think it's code. If I update the Combo box manually The
Afterupdate event is called once and everything runs as expected. However,
when I click any one of my SelectNext buttons,debug and step through the
code (F8), I see that Afterupdate is called as normal everything runs as
it should then once the Sub returns Afterupdate is called again for no
reason. As far as I can tell it should work but it's just not at the
moment.
Below is a sample of the code from the form (please excuse the
Query_Builder() function. it works but it's a bit of a mess):
<code>------------------------------------------------------------------------------------------
'*************************************
'* SelectNextIndex - selects the next index based on a control
'*************************************
Private Function SelectNextIndex(ctl As Control)
' if there is 1 or less items or at last item, exit
If (ctl.ListCount <= 1) Or ((ctl.ListCount - 1) = ctl.ListIndex) Then
Exit Function
ctl.SetFocus
ctl.ListIndex = ctl.ListIndex + 1
End Function
'*************************************
' Button Events below
'*************************************
Private Sub Filter_Manufacturer_Next_Click()
SelectNextIndex Me.Filter_Manufacturer
End Sub
Private Sub Filter_Model_Next_Click()
SelectNextIndex Me.Filter_Model
End Sub
Private Sub Filter_Venue_Next_Click()
SelectNextIndex Me.Filter_Venue
End Sub
'*************************************
'* Filter_Manufacturer_AfterUpdate()
'*
'*************************************
Private Sub Filter_Manufacturer_AfterUpdate() ' Manufacturer
If IsNull(Me.Filter_Manufacturer) Then
Me.Filter_Model.RowSource = "SELECT Model, ManufacturerID FROM
Table_Equipment ORDER BY Model"
Else
Me.Filter_Model.RowSource = "SELECT Model, ManufacturerID FROM
Table_Equipment Where ManufacturerID=" & Me.Filter_Manufacturer.Value & "
ORDER BY Model"
Me.Filter_Model.Enabled = True
Me.Filter_Model.Requery
End If
Query_Builder
End Sub
'*************************************
'* Quick Filter
'*************************************
Private Sub Filter_Piece_AfterUpdate() ' Piece
Query_Builder
End Sub
'*************************************
'* Filter_TGA_AfterUpdate()
'*************************************
Private Sub Filter_TGA_AfterUpdate() ' TGA
Query_Builder
End Sub
'*************************************
'* Filter_Venue_AfterUpdate()
'*************************************
Private Sub Filter_Model_AfterUpdate() ' Model
Query_Builder
End Sub
'*************************************
'* Filter_Venue_AfterUpdate()
'*************************************
Private Sub Filter_Venue_AfterUpdate() ' Venue
Query_Builder
End Sub
'*************************************
'* Query_Builder()
'*
'* This function is responsable for building the query for Line Item
View and Summary View.
'*
'*
'*************************************
Function Query_Builder(Optional Shipment_Where As Integer = 0, Optional
Shipment_Type As String = "S") As String
On Error GoTo errorhandle
Dim Line_Item_Query As String
Dim SQLWhere As String
Dim EnableWhere As Boolean
Item_Query_WHERE2 = Null
'***** SELECT / FROM
Select Case Global_DisplayOption
Case emDisplayLineItem
Item_Query_SELECT = "SELECT" & _
" Table_EquipmentIndex.EquipmentID as PieceNr," & _
" Table_Equipment.ManufacturerID," & _
" Table_Equipment.Model," & _
" Table_Equipment.Description," & _
" Table_Equipment.TestProcedure," & _
" Table_EquipmentIndex.*"
Item_Query_FROM = " FROM Table_Equipment RIGHT OUTER JOIN
Table_EquipmentIndex ON Table_Equipment.EquipmentID =
Table_EquipmentIndex.EquipmentID"
Case emDisplaySummary
Item_Query_SELECT = "SELECT" & _
" Table_Equipment.EquipmentID," & _
" Table_Equipment.PieceNr," & _
" Table_Equipment.ManufacturerID," & _
" Table_Equipment.Model," & _
" Table_Equipment.Description," & _
" Table_Equipment.TestProcedure," & _
" COUNT(Table_EquipmentIndex.EquipmentIndexID) As Qty"
Item_Query_FROM = " FROM Table_Equipment INNER JOIN
Table_EquipmentIndex ON Table_Equipment.EquipmentID =
Table_EquipmentIndex.EquipmentID"
End Select
'***** WHERE
If Global_ShowHidden Then 'Show hidden items (Owner Deleted)
Select Case Global_DisplayOption
Case emDisplayLineItem
Item_Query_WHERE1 = " WHERE" 'if line item use WHERE
Case emDisplaySummary
Item_Query_WHERE1 = " WHERE (Table_EquipmentIndex.Status LIKE
'%') OR (Table_EquipmentIndex.Status IS NULL)" ' is summery then omit WHERE
End Select
Else
Item_Query_WHERE1 = " WHERE (((NOT Status = 'Owner') OR (Status
IS NULL)) AND ((NOT Status = 'Deleted') OR (Status IS NULL)))"
If Global_DisplayOption = 1 Then
Item_Query_WHERE1 = Item_Query_WHERE1 & " AND"
End If
End If
'Filter_Piece
If Not IsNull(Me.Filter_Piece) Then
Item_Query_WHERE2 = Item_Query_WHERE2 & " Table_Equipment.PieceNr
LIKE '" + escapestring(Me.Filter_Piece) + "' AND"
End If
'Filter_TGA
If Not IsNull(Me.Filter_TGA) Then
Item_Query_WHERE2 = Item_Query_WHERE2 & "
Table_EquipmentIndex.TGA LIKE '" + escapestring(Me.Filter_TGA) + "' AND"
End If
'Filter_Manufacturer
If Not IsNull(Me.Filter_Manufacturer) Then
Item_Query_WHERE2 = Item_Query_WHERE2 & "
Table_Equipment.ManufacturerID = '" + escapestring(Me.Filter_Manufacturer)
+ "' AND"
End If
'Filter_Model
If Not IsNull(Me.Filter_Model) Then
Item_Query_WHERE2 = Item_Query_WHERE2 & " Table_Equipment.Model
LIKE '" + escapestring(Me.Filter_Model) + "' AND"
End If
'Filter_Venue
If Not IsNull(Me.Filter_Venue) Then
Item_Query_WHERE2 = Item_Query_WHERE2 & "
Table_EquipmentIndex.VenueID = '" + escapestring(Me.Filter_Venue) + "'AND"
End If
'If Arg: Shipment_Where Value Set
If Not (Shipment_Where = 0) Then
'In Line Item mode
If Global_DisplayOption = emDisplayLineItem Then
'Show Transfer "X"
If Left(Me.ShipmentFilter.Text, 1) = "X" Then
Item_Query_WHERE2 = Item_Query_WHERE2 & "
Table_EquipmentIndex.ConsignmentID= " & Shipment_Where & " AND "
'Any Other Value
Else
'If Arg: Shipment_Type Set
Select Case Shipment_Type
Case "S"
'Show ShippingID
ItemQuery_ShipmentType = "
Table_EquipmentIndex.ShippingID = "
Case "R"
'Show ReceivedID
ItemQuery_ShipmentType = "
Table_EquipmentIndex.ReceivedID = "
End Select
'Make The Query
Item_Query_WHERE2 = Item_Query_WHERE2 &
ItemQuery_ShipmentType & Shipment_Where & " AND "
End If
End If
End If
'Where Check
If Not IsNull(Item_Query_WHERE2) Then
Select Case Global_DisplayOption
Case emDisplayLineItem
Item_Query_WHERE2 = Left(Item_Query_WHERE2,
Len(Item_Query_WHERE2) - 4)
Case emDisplaySummary
Item_Query_WHERE2 = Left(Item_Query_WHERE2,
Len(Item_Query_WHERE2) - 4)
End Select
Else
Item_Query_WHERE2 = " Table_Equipment.EquipmentID = 0"
End If
'***** Group By / Having / Order by
Select Case Global_DisplayOption
Case emDisplayLineItem
Item_Query_GROUPBY = Null
Item_Query_HAVING = Null
Item_Query_ORDERBY = " ORDER BY Table_Equipment.ManufacturerID,
Table_Equipment.Model, Table_Equipment.Description"
Case emDisplaySummary
Item_Query_GROUPBY = " GROUP BY Table_Equipment.EquipmentID,
Table_Equipment.PieceNr, Table_Equipment.ManufacturerID,
Table_Equipment.Model, Table_Equipment.Description,
Table_Equipment.TestProcedure"
Item_Query_HAVING = " HAVING"
End Select
'Debug.Print Item_Query
Select Case Global_DisplayOption
Case emDisplayLineItem
Item_Query = Item_Query_SELECT & Item_Query_FROM &
Item_Query_WHERE1 & Item_Query_WHERE2 & Item_Query_ORDERBY
Case emDisplaySummary
Item_Query = Item_Query_SELECT & Item_Query_FROM &
Item_Query_WHERE1 & Item_Query_GROUPBY & Item_Query_HAVING &
Item_Query_WHERE2 & Item_Query_ORDERBY
End Select
'Debug.Print Item_Query '' *****DEBUG*****
start = Timer
'*************************************************************************
Me.SF_Shipping_Receiving.Form.RecordSource = Item_Query 'TODO: This
function should really only return a string to the caller and not set the
recoresource in the function.
Debug.Print "SELECT
ostQuery> " & Timer - start
'*************************************
'you need to put all the locking/un locking stuff in the same place
Dim rst As Recordset
Set rst = Me.SF_Shipping_Receiving.Form.Recordset
If rst.RecordCount >= 1 Then
Me.EditEquipment.Enabled = True
Else
Me.EditEquipment.Enabled = False
End If
' Only display Status bar if in Line Item View
' TODO: This is an issue with the way that the Status bar is
calculated, It would be better to build a query to find the information
Select Case Global_DisplayOption
Case emDisplayLineItem
start = Timer
'*************************************************************************
UpdateStatusBar
Debug.Print "SELECT
OSTSTATUS> " & Timer - start
'*************************************
Case Else
Me.StatusBar.Value = "Total:N/A Sent:N/A Received:N/A
Outstanding:N/A FCPrice: N/A"
End Select
Exit Function
errorhandle:
Debug.Print "Query Builder [err.Description]> " & err.Description '
debug
If err.Description = "Object variable or With block variable not set"
Then
Debug.Print "Re applying Display Query"
DisplayOption_AfterUpdate
End If
Me.SF_Shipping_Receiving.Form.TestProcedure.Requery
End Function
</code>-----------------------------------------------------------------------------------------
Sorry to include so much code but I wanted to be clear.
The issue at hand starts at Sub Filter_*_Next_Click() -
Filter_Manufacturer_AfterUpdate() is called twice
However starting from Filter_Manufacturer_AfterUpdate() works fine -
Calling Query_Builder() only once.
I hope that is much more clear.
