Hi !
Your data you are giving us is not a very specific.
You should send to us SQl syntax of qselCPList
-------------------------------------------------
In frmCP Open event you have to
define CPList.Rowsource string.
Let say strCPlist
It can not be done later.
This string must be exactly as you would write in CPList.Rowsource property
And CPList.RowSource Type = "Table/Query"
And CPList.Rowsource= (Nothing)
To see what Exactly means
Look at this :
If your List on frmSearch have a name CustomerIdList
and you will output names from let say table tblCustomer and CustomerName
field then
In frmCP Open event
Private Sub Form_Open(Cancel As Integer)
Dim strCPlist$
'Spaces ,parenteses, and all sign must be exact
strCPlist= "SELECT [tblCustomer].[CustomerName] FROM tblCustomer WHERE " & _
"(([tblCustomer].[CustomerId])=[Forms]![frmSearch]![CustomerId]) ORDER BY "
& _
"[tblCustomer].[CustomerId]"
Or like one string
strCPlist= "SELECT [tblCustomer].[CustomerName] FROM tblCustomer WHERE
(([tblCustomer].[CustomerId])=[Forms]![frmSearch]![CustomerIdList]) ORDER
BY [tblCustomer].[CustomerId]"
CPList.Rowsource=strCPlist
End Sub
If you omit some sign CPList wil not responce and without error.
If you open form each time you pick up subject from
frmSearch CustomerId List ; if I understand this corectly
you dont need to requery the CPList, but
you have to close the frmCP form and then reopen.
I rather will use Click event of CustomerId List on frmSearch form
to perform CPList.Requery
DoCmd.OpenForm FormName:="frmCP", WhereCondition:=GetStrSearch
I do not know what your GetStrSearch is
but usually you should write
DoCmd.OpenForm "frmCP", acNormal, ,"CustomerName='" & CustomerIdList.Value &
"'"
'Then
Forms!frmCP.Form!CPList.Requery
You can also build a query and have
it in CPList.Rowsource permamently
and write in query field condition
Forms]![frmSearch]![CustomerId]
and run
Forms!frmCP.Form!CPList.Requery
Aleksander
Harmannus said:
Hallo,
I tried your code but cannot get it to work.
I have a searchform (frmSearch) that generates searchresult for the form
customer (frmCP). The frmSearch containts the code
DoCmd.OpenForm FormName:="frmCP", WhereCondition:=GetStrSearch
On performing a search on the frmCP, pop-up frmSearch, i would lik the
listbox (CPList) to show the search results.
I tried me.CPList.RowSource = GetStrSearch on the frmSearch. This has no
effect. I think i addressed the CPList wrong by adding
Forms![frmCP].form!CPList=GetStrSearch to the frmSearch. No effect either.
Could you assist my further?
Regards,
Harmannus
Naresh Nichani MVP said:
Hi:
If I understand this correctly you need to filter a ListBox based on some
SQL where clause.
Then for ListBox in design view make the "RowSource Type" = "Table/Query"
Then via code you can set the RowSource with a SQL Statement like and
Requery the ListBox to refresh the view.
In form code like this where ListBox is called say List0
Me.List0.RowsSource = strSQl 'valid sql statement with where clause as
needed
me.List0.requery
Regards,
Naresh Nichani
Microsoft Access MVP
