query for listbox criteria help

[icemonster]

hi, i know i posted this already and sorry. this is really driving me crazy.

i have a listbox that has an sql for a rowsource. that listbox sql is compsed of records from different tables. now the listbox is on a form from a main table, what i want is, the listbox to filter to the current record for that form.

lets say, the main table is for clients and the other table is for their schedules.

now the schedule table has alot of info, so when i put it on the listbox, it will show all of the records, how do i get the listbox to limit the records for the current record on the form? thanks you.

 

[dkinley]

Like this?

-dK

 

[icemonster]

actually its more like this on the picture.

 

[dkinley]

What I think you would want to do is put in a Where statement on your SQL. On the Load property of the form, you can specify the control source of the listbox. So it would something like ...

Dim sSQL as String
 
sSQL = "(... your SQL Code ...) Where [PersonID] = " & Me.txtPersonID
 
Me.lstListControl.RowSource = sSQL

This looks ugly but should get you where you need to be.

The 'your SQL Code' can be the code that is already in your list box, you just want to add a Where clause to filter it. The Where would use the primary key of the person so the only records in the list box are those that are linked to that person.

-dK

 

[icemonster]

yeah. it would work on the other listbox, but the one i drawed on was based on a query, sorry if i used query and SQL statements alike.

here is the row source for the listbox:


SELECT tblClientConferenceCall.ID, tblClientConferenceCall.ClientID, tblClientConferenceCall.DateTime, tblClientConferenceCall.StaffID, tblClientConferenceCall.ConferenceCalls
FROM tblClientConferenceCall
WHERE (((tblClientConferenceCall.ID) Like "'" & [Forms]![frmClientDetails]![Client Conference Call Subform].[Form]![Search2] & "'")) OR (((tblClientConferenceCall.DateTime) Like "*" & [Forms]![frmClientDetails]![Client Conference Call Subform].[Form]![Search2] & "*")) OR (((tblClientConferenceCall.StaffID) Like "*" & [Forms]![frmClientDetails]![Client Conference Call Subform].[Form]![Search2] & "*")) OR (((tblClientConferenceCall.ConferenceCalls) Like "*" & [Forms]![frmClientDetails]![Client Conference Call Subform].[Form]![Search2] & "*"))
ORDER BY tblClientConferenceCall.DateTime DESC;

 

[dkinley]

No, that is fine. The deal is, you can set it as an SQL statement.

I am not sure off the top of my head of the loading order since you use a query. You may be able to get away with using ...

Forms!frmFormName!txtControlNamewithID

In the critieria of the query under the ID column (this will create the Where for the query). This may throw an error and if the error is not due to reference issues then you will have to set the SQL through VBA as described below.

-dK