filter by form refering to a column in a control or a variable

[sub5]

Hi all,

I have tried the following syntax in a query to filter it based on a column of a combo box, but it doesn't like it, the query won't open.

This works when placed in the criteria row of the query:

forms!frm1!control1

This doesn't:

forms!frm1!control1.column(1)

Is my syntax off, or is it not possible?

ps I am pretty sure I can't refer to a variable either, right? (I have been building SQL instead)

 

[Remou]

You cannot refer to the column property in the query design, as far as I know. You could build SQL, as you mention, or set a parameter from code.

 

[Remou]

... Or make a text box equal to the column you want and refer to that.

 

[sub5]

Hi remou, thanks for your input, I am trying to eliminate text boxes like that. But what do you mean set a parameter from code?

 

[Remou]

Something like:

Dim qdf As QueryDef

'Query
Set qdf = CurrentDb.QueryDefs("qryQuery")
'Debug.Print qdf.SQL
'PARAMETERS Descript Text ( 255 );
'SELECT tblTable.Field1, tblTable.Field2
'FROM tblTable
'WHERE (((tblTable.Field2)=[Descript]));

'Parameters
qdf.Parameters("Descript") = Me.cboCombo.Column(1)

Set rs = qdf.OpenRecordset

'Recordset
Do While Not rs.EOF()
    For i = 0 To rs.Fields.Count - 1
        Debug.Print rs.Fields(i)
    Next
    rs.MoveNext
Loop

 

[sub5]

Hi Remou, thanks for your input again. I see what you mean but I am using the "query" ie SQL as the record source on a sub form.

I have just tried writing the SQL and setting that as the recordsource and it didn't work. But ignore that for a second. And consider this - (if you have time of course)

I have form1 which opens form2. On form2 are two subforms.
subform1 has an on current event:
dim strCond as string
strCond = "ID = forms!form2!sub1.form!ID
forms!form2!sub2.form.filteron = true
forms!form2!sub2.form.filter = strCond
When I have the recordsource of sub1 including a criteria reference to a field ie forms!form2!ID, then everything works OK. But if I change the critera to a number say 257, which is an ID that exists, I get the following error when I try to open form2:
2455 you entered an expression that has an invalid reference to the property form/report
debug takes me to the ....filteron = true line of code

Any ideas?

 

[Remou]

Try putting in a debug.print or a msgbox to show what exactly is being put in as a filter. If what you have above is 'as is', there are a few errors, such as a missing quote in strCond and the wrong order of events: filter first and then set filter on.

 

[jkl0]

If I remember right, I think that filteron is not a valid property of a form, but it is for a report. So if the subform is already open you can requery the subform after setting the filter.

 

[Remou]

Hi jkl0
FilterOn is indeed a property of a form, and necessary, if you wish to apply a filter.

 

[sub5]

Sorry the quote is there in the real code. Tried changing the order but no difference.

 

[Remou]

This thread is continued in:
2455 error
thread705-1234083