N
Neil
Hello All,
I have a form that has a couple of list box's and a couple of subforms in
it. I also have a stored query (named qrySelected). This query gets updated
whenever anything changes in one of the listbox's on my form. The code for
this is as follows and simply stores all selected ID's in the List Box into
an array and then builds a string to include the items in the queries SQL
property:
------------------------------------------------------------------
Private Sub AddSelectedItems()
On Error GoTo Err_AddSelectedItems
Dim arrSelected() As String, arrExisting() As String, strTemp As String
Dim varItm As Variant
Dim intCounter As Integer
Dim blnNoRecord As Boolean
Dim db As DAO.Database
Dim rs As DAO.Recordset
Dim qryTmp As DAO.QueryDef
' Get database and table data
Set db = CurrentDb
Set rs = db.OpenRecordset("qrySelected", dbOpenDynaset)
' Re-dim the array to hold the selected items
ReDim arrSelected(Me.lstAvailable.ItemsSelected.Count - 1)
' Loop through the items selected
For Each varItm In Me.lstAvailable.ItemsSelected
' Add the ID for the option
arrSelected(intCounter) = Me.lstAvailable.ItemData(varItm)
' Increment the counter
intCounter = intCounter + 1
Next
' Get existing options (if at least one exists)
rs.MoveLast
If blnNoRecord = False Then
rs.MoveFirst
ReDim arrExisting(rs.RecordCount - 1)
Do Until rs.EOF
arrExisting(rs.AbsolutePosition) = rs!lngOptionID
rs.MoveNext
Loop
' Loop through existing items created and build string
For intCounter = 0 To UBound(arrExisting)
' Check to see if an existing item has been selected
If IsSelected(arrSelected, arrExisting(intCounter)) = False Then
' Add this item to the temp string
strTemp = strTemp & arrExisting(intCounter) & ","
End If
Next
End If
' Set the new string for selected items
For intCounter = 0 To UBound(arrSelected)
' Check if last
If intCounter = UBound(arrSelected) Then
' Dont add , seperator
strTemp = strTemp & arrSelected(intCounter)
Else
strTemp = strTemp & arrSelected(intCounter) & ","
End If
Next
' Get selected query
Set qryTmp = db.QueryDefs("qrySelected")
' Set SQL to only include records that have been selected
qryTmp.SQL = "SELECT tblOption.lngOptionID " & _
"FROM tblOption " & _
"WHERE (((tblOption.lngOptionID) In (" & strTemp & ")))"
End Sub
------------------------------------------------------------------
My question here is that some controls depend on this query. I have a sub
form for example which has it's RowSource set as follows:
SELECT DISTINCT tblCategory.lngCategoryID, tblCategory.strCode,
tblShortDescription.strShortDescription FROM (tblShortDescription RIGHT JOIN
tblCategory ON tblShortDescription.lngShortDescriptionID =
tblCategory.lngShortDescriptionID) INNER JOIN tblRequiredCats ON
tblCategory.lngCategoryID = tblRequiredCats.lngControlCategoryID WHERE
(((tblCategory.lngCategoryID) In (SELECT tblCategory.lngCategoryID FROM
tblCategory RIGHT JOIN tblOption ON tblCategory.lngCategoryID =
tblOption.lngCategoryID WHERE (((tblOption.lngOptionID) In (SELECT * FROM
qrySelected))))) AND
((tblRequiredCats.lngMachineID)=[forms]![frmQuoteBuilder]![cboMachine]) AND
((tblRequiredCats.lngRequiredCategoryID) Not In (SELECT
tblCategory.lngCategoryID FROM tblCategory RIGHT JOIN tblOption ON
tblCategory.lngCategoryID = tblOption.lngCategoryID WHERE
(((tblOption.lngOptionID) In (SELECT * FROM qrySelected)))))) ORDER BY
tblCategory.strCode;
One of the lines to notice here is
WHERE (((tblOption.lngOptionID) In (SELECT * FROM qrySelected))))))
If i run the code above and then requery a listbox or sub form that relies
on this query by doing
Me.ControlName.Requery
nothing happens. If I do
DoCmd.Requery Me.ControlName.Name
It requeries no problem unless the control is a Sub Form or is another
control in the Sub Form itself. Why does one way to requery work for some
controls and the other way never work? There must be another way to do this
but i am not too sure how?
Any info on this would be greatly appreciated.
TIA,
Neil.
I have a form that has a couple of list box's and a couple of subforms in
it. I also have a stored query (named qrySelected). This query gets updated
whenever anything changes in one of the listbox's on my form. The code for
this is as follows and simply stores all selected ID's in the List Box into
an array and then builds a string to include the items in the queries SQL
property:
------------------------------------------------------------------
Private Sub AddSelectedItems()
On Error GoTo Err_AddSelectedItems
Dim arrSelected() As String, arrExisting() As String, strTemp As String
Dim varItm As Variant
Dim intCounter As Integer
Dim blnNoRecord As Boolean
Dim db As DAO.Database
Dim rs As DAO.Recordset
Dim qryTmp As DAO.QueryDef
' Get database and table data
Set db = CurrentDb
Set rs = db.OpenRecordset("qrySelected", dbOpenDynaset)
' Re-dim the array to hold the selected items
ReDim arrSelected(Me.lstAvailable.ItemsSelected.Count - 1)
' Loop through the items selected
For Each varItm In Me.lstAvailable.ItemsSelected
' Add the ID for the option
arrSelected(intCounter) = Me.lstAvailable.ItemData(varItm)
' Increment the counter
intCounter = intCounter + 1
Next
' Get existing options (if at least one exists)
rs.MoveLast
If blnNoRecord = False Then
rs.MoveFirst
ReDim arrExisting(rs.RecordCount - 1)
Do Until rs.EOF
arrExisting(rs.AbsolutePosition) = rs!lngOptionID
rs.MoveNext
Loop
' Loop through existing items created and build string
For intCounter = 0 To UBound(arrExisting)
' Check to see if an existing item has been selected
If IsSelected(arrSelected, arrExisting(intCounter)) = False Then
' Add this item to the temp string
strTemp = strTemp & arrExisting(intCounter) & ","
End If
Next
End If
' Set the new string for selected items
For intCounter = 0 To UBound(arrSelected)
' Check if last
If intCounter = UBound(arrSelected) Then
' Dont add , seperator
strTemp = strTemp & arrSelected(intCounter)
Else
strTemp = strTemp & arrSelected(intCounter) & ","
End If
Next
' Get selected query
Set qryTmp = db.QueryDefs("qrySelected")
' Set SQL to only include records that have been selected
qryTmp.SQL = "SELECT tblOption.lngOptionID " & _
"FROM tblOption " & _
"WHERE (((tblOption.lngOptionID) In (" & strTemp & ")))"
End Sub
------------------------------------------------------------------
My question here is that some controls depend on this query. I have a sub
form for example which has it's RowSource set as follows:
SELECT DISTINCT tblCategory.lngCategoryID, tblCategory.strCode,
tblShortDescription.strShortDescription FROM (tblShortDescription RIGHT JOIN
tblCategory ON tblShortDescription.lngShortDescriptionID =
tblCategory.lngShortDescriptionID) INNER JOIN tblRequiredCats ON
tblCategory.lngCategoryID = tblRequiredCats.lngControlCategoryID WHERE
(((tblCategory.lngCategoryID) In (SELECT tblCategory.lngCategoryID FROM
tblCategory RIGHT JOIN tblOption ON tblCategory.lngCategoryID =
tblOption.lngCategoryID WHERE (((tblOption.lngOptionID) In (SELECT * FROM
qrySelected))))) AND
((tblRequiredCats.lngMachineID)=[forms]![frmQuoteBuilder]![cboMachine]) AND
((tblRequiredCats.lngRequiredCategoryID) Not In (SELECT
tblCategory.lngCategoryID FROM tblCategory RIGHT JOIN tblOption ON
tblCategory.lngCategoryID = tblOption.lngCategoryID WHERE
(((tblOption.lngOptionID) In (SELECT * FROM qrySelected)))))) ORDER BY
tblCategory.strCode;
One of the lines to notice here is
WHERE (((tblOption.lngOptionID) In (SELECT * FROM qrySelected))))))
If i run the code above and then requery a listbox or sub form that relies
on this query by doing
Me.ControlName.Requery
nothing happens. If I do
DoCmd.Requery Me.ControlName.Name
It requeries no problem unless the control is a Sub Form or is another
control in the Sub Form itself. Why does one way to requery work for some
controls and the other way never work? There must be another way to do this
but i am not too sure how?
Any info on this would be greatly appreciated.
TIA,
Neil.