passing value from report to query from form to display in report..

[ahvc]

Hello Access friends,

I have a query named qryMemberfilter like this:

SELECT Name FROM members where
Member_ID In ([Forms]![frmMemberfilter]![member_id_list]) and
program like ([Forms]![frmMemberfilter]![program]);

In a form called frmMemberfilter, upon an event completion, member_id_list (a text box) is generated in the form. This contains a value like '24,38,56,88'. After this I called a report from the form, and the control source for the report is the query 'qryMemberfilter'. The report is not able to understand '[Forms]![frmMemberfilter]![member_id_list]'.

But I manually ran the query like this to test it:


SELECT Name FROM members where
Member_ID In (24,38,56,88) and
program like '*';

and it works.

Also I changed the original query to

SELECT Name FROM members where
program like ([Forms]![frmMemberfilter]![program]); and it works. For some reason, The text value of member_id_list is not passed on ok to the query.

members.member_id is an autonumber. Could that be a reason in any way?

I really appreciate your help. I lost many hours on this.

Thank you
Vijay

 

[Pat Hartman]

The problem is that - [Forms]![frmMemberfilter]![member_id_list] is being interpreted as a string rather than an array. So instead of seeing - 24,38,56,88 - Access sees - "24,38,56,88"

You'll need to build the query in VBA to solve the problem.

Dim strSQL as String

strSQL = "SELECT Name FROM members where Member_ID In ( " &[Forms]![frmMemberfilter]![member_id_list] & ") and program like ('" & [Forms]![frmMemberfilter]![program] & "');"

Are you sure that you want to use the Like operator for program? Is the user inputting wildcards or do you need to put them in the SQL string?

 

[ahvc]

Thanks, Pat.

Regarding the Like operation for program, it is a drop down list. like has been working good. Is there anything I am overlooking by not using '='. Thanks.

I am still struggling to apply your suggestion. I am going all around but not getting there.

The example I gave was more simple. Let me put the whole thing in the picture.

In the Member filter form, I have around 30 criterias that the user chooses and that is how the member list is calculated.(The user does not need to choose from all the 30 criterias. The code I have writen selects only the criteria that has been touched) Apart from that, I have 3 other field, 2 date field, and one program field. The member info in the tblclient table, and the rest are in the monthly_log table. I use one another table (one row table with no linK) I even have a list in the form where I display the members and related info from monthly_log table based on the selection criteria. The problem I have is to take that to the report.

First I just had the member criterias on the form. It was easy then. THere was only one table. I just put the criteria as strfilter in the command "DoCmd.OpenReport stDocName, acViewPreview ', stfilter". In the source for the report, I mentioned it as 'select * from tblclient' and the strfilter just did the filtering. It worked.

Now, with the new logic I needed, I tried the same now with 3 tables. It is not working. Hence I tried passing the member_id_list directly to the query. That is where I met the problem, and I posted this thread. I have the member_id list in text format, in list format. I tried using the list also in the query. I could not make it work.

So I guess my question/help is I need to know how to pass what I can do in the form into the query or directly into the report. The problem in the query is it is just not simple querying based on selection from the form. There are 2 levels involved. First I do a big parsing and find out the member_id_list based on the 30 criterias, then I need to combine that with the 3 other input and do a query. So I do what Pat suggested in the form code, in 2 levels; but I struggling many wasted hours trying to get that to the report or query..

Thanks for your time. I really value it.

AHVC

 

[Pat Hartman]

If the user is selecting an option from a combo or listbox, it is wrong to use the like operator. The like operator should only be used if you need to provide the ablilty of doing partial searches. Your user wants to be able to input "Micro*" and see "Microsoft", "MicroGraphics", "Micro-management". In order to satisfy the selection criteria when you use Like, Jet needs to read EVERY record of the table being searched. NO index will be used. This operation is very inefficient and should only be used on small tables. Since your user is providing a complete value, you have no need to use Like.

Here is some code that uses the selections in a multi-select listbox to control the records selected for a report.

This is the code that calls the function which populates a global variable:

Call fMultiSelect(Forms!frmPreSPIPComp!lstProjects)

This is the function:

Public Function fMultiSelect(ctlRef As ListBox) As Variant
    Dim Criteria As String
    Dim i As Variant

   ' Build criteria string from selected items in list box.
    Criteria = ""
    For Each i In ctlRef.ItemsSelected
        If i > 8 Then
            MsgBox "Only first 9 selections were accepted.  Excess discarded.", vbOKOnly
            fMultiSelect = Criteria
            gMultiSelect = Criteria
            Exit Function
        End If
        If Criteria <> "" Then
            Criteria = Criteria & ","
        End If
        Criteria = Criteria & Format(ctlRef.ItemData(i), "0000000")
    Next i

fMultiSelect = Criteria
gMultiSelect = Criteria
End Function

The report references the global variable in its Open event to filter its recordsourse:

Private Sub Report_Open(Cancel As Integer)
If IsNull(gMultiSelect) Then
Else
    Me.Filter = "CMMLevelID IN(" & gMultiSelect & ")"
    Me.FilterOn = True
End If
End Sub