I have seen similar things happen, but I am not sure why. Here is what I
would suggest. Delete the query from the rowsource property. Now using the
dropdown of the row source property box, select the table your query is based
on. Then click on the small command button to the right with the 3 dots ...
You will get a message box asking if you want to build a query. Click Yes
Now you are in familiar territory. Select the fields you want for your query
Click the X close button on the query builder. It will ask you if you want
to keep the changes. Click Yes
Now you have it set for the form opening, but you also will probably want
to be able to go back to all records after you have set a filter.
Leave the cusrsor in the property box and right click.
Select Zoom
You can now see the SQL the query builder built. Copy that. You will be
using it.
Now, in the form's code module, you will be modifying the SQL to do the
filter, so you will want to use the original to remove filtering. Modify the
Copy with a WHERE clause for the filtering.
I threw together this function for you. It is a suggestion that might help.
To set a filter you pass it the value to filter on. To move the filter, do
not pass anything. For example, if you wanted to search for the value in
List223, it would be:
FilterList(Me.List223)
To Remove the filter:
FlilterList()
'---------------------------------------------------------------------------------------
' Procedure : FilterList
' DateTime : 7/9/2009 20:44
' Author : Dave Hargis
' Purpose : Controls the Filtering for List224
' Arguments : FilterFor - Variant - The value to filter for
'---------------------------------------------------------------------------------------
'
Public Function FilterList(Optional FilterFor As Variant) As Boolean
Const FilterOn = "SELECT boo, foobar FROM tblBlah WHERE [SomeField] = ??? ;"
Const FilterOff = "SELECT boo, foobar FROM tblBlah;"
Dim strRowSource As String
On Error GoTo FilterList_Error
If Not IsMissing(FilterFor) Then
strRowSource = Replace(FilterOn, " ??? ", FilterFor)
Else
strRowSource = FilterOff
End If
With Me.List224
.RowSource = strRowSource
.Requery
End With
FilterList_Exit:
Exit Function
On Error GoTo 0
FilterList_Error:
MsgBox "Error " & Err.Number & " (" & Err.Description & _
") in procedure FilterList of Module modUtilities"
GoTo FilterList_Exit
End Function
--
Dave Hargis, Microsoft Access MVP
BrianPaul said:
Maby Thats my problem. What I did was create a new query, and then pasted
it. I Just checked it again and made sure it was all in one line. So it
looks like this now.
Me!List224.RowSource="SELECT QAllCustomersList.PhoneID,
QAllCustomersList.[Full Name], QAllCustomersList.Receiver FROM
QAllCustomersList;"
IF I take out the Me!List224.RowSource=
And I just have this, It returns my records in my query.
SELECT QAllCustomersList.PhoneID, QAllCustomersList.[Full Name],
QAllCustomersList.Receiver FROM QAllCustomersList;
Im trying and I thank you for your patience.
It wont let me even save the query, It wants something at the beginning of
the query in the statement. My question would be why does it want something
at the beginning, Or am I even doing it write.
:
I don't see the problem.
Is it all one one line like is should be?
A List Box row source is just a table or query. Nothing magic about it.
The idea, since you want to see all when the form opens, is to use a query
in the row source that is not filtered. Then when you want to filter it, you
assign the control a new row source and requery it. All you need to do is
get the syntax right.
--
Dave Hargis, Microsoft Access MVP
:
Oh Boy. I pasted it in.
Me!List224.RowSource="SELECT QAllCustomersList.PhoneID,
QAllCustomersList.[Full Name], QAllCustomersList.Receiver FROM
QAllCustomersList;"
gave me an error, " Invalid Sql Statement; Expected, "Insert, Delete,
procedure, select or update" When I ran It.
:
Remove the first SELECT
SELECT Me!List224.RowSource="SELECT QAllCustomersList.PhoneID,
QAllCustomersList.[Full Name], QAllCustomersList.Receiver FROM
QAllCustomersList;" AS Expr1;
It should be:
Me!List224.RowSource="SELECT QAllCustomersList.PhoneID,
QAllCustomersList.[Full Name], QAllCustomersList.Receiver FROM
QAllCustomersList;"
--
Dave Hargis, Microsoft Access MVP
:
Thanks for the Reply. I pasted your sql statement into A new Query, When I
Tried to Save the query it gave me the following:
SELECT Me!List224.RowSource="SELECT QAllCustomersList.PhoneID,
QAllCustomersList.[Full Name], QAllCustomersList.Receiver FROM
QAllCustomersList;" AS Expr1;
So I added the 2nd listbox based upon Query1 Which that is what I saved the
sql statement in. Nothing showed up in the list box.
:
It has to be an SQL statment.
Me!List224.RowSource = "SELECT QAllCustomersList.PhoneID,
QAllCustomersList.[Full Name], QAllCustomersList.Receiver FROM
QAllCustomersList;"
--
Dave Hargis, Microsoft Access MVP
:
I Did that but its still not working, getting error.
I took out [forms]![FMainMenu]![List263] That shows all Records, So I am
left with this In my Listbox called List224 WHich is built from
QAllCustomersList
SELECT QAllCustomersList.PhoneID, QAllCustomersList.[Full Name],
QAllCustomersList.Receiver
FROM QAllCustomersList;
The Above sql Statement runs fine and shows all records like you instructed
in the 2nd listbox. However my final goal as you know was to show all
records when the form loaded. Okay thats done by virtually removing the
[forms]![FMainMenu]![List263]
This Is the Query From the 1st List Box: List263
SELECT QReceiverCategory.test
FROM QReceiverCategory
ORDER BY QReceiverCategory.test;
QAllCustomersList.Receiver = QReceiverCategory.test (They have the same
Info) Its just that QReceiverCategory.test is a query based on the same Table
but put the field in twice and hit totals and used Count So it would only
show up one in the List box. I hope that explains the 1st List Box 263
So Trying to follow your instructions I Did the following:
Me!List224.RowSource = QAllCustomersList.PhoneID, QAllCustomersList.[Full
Name], QAllCustomersList.Receiver
FROM QAllCustomersList;
And I got the error. I have been miditating on it so long, I became more
confused then when I started. Thanks.
This would be the listbox I based my query on
:
You can do that by manipulating the list box's Row Source property. If you
want to open the form showing all records, write the query in design view
that will show all records. Then whenever you want to filter on the other
list box, it would be
Me!List224.RowSource = "SELECT ...."
--
Dave Hargis, Microsoft Access MVP
:
I have 2 Listboxes. the 2nd Listbox filters from the what is selected in the
first. Here is the code I used in the query:
[forms]![FMainMenu]![List263]
Now In List263 I added the codes to update my 2nd Listbox when I select an
Item in List263
Private Sub List263_AfterUpdate()
Me!List224 = Null
Me!List224.Requery
Me.List224.Selected(0) = False
End Sub
It works find like that, However I would like when I open the form to start
out viewing all the records in my 2nd Listbox224, I could use a command
button on click event to view all records if I already selected something to
filter the 2nd listbox. I had the code for doing this 2 years ago, but cant
find it anymore. Any help would be appreciated, Thanks.
