Filter on a continuous form 1

[Noizz]

hi,

I have a continuous form called frmRandapparaatLijst (or frmHardware in english). This is a continuous form that shows the complete tblRandapparaten (tblHardware).

in the formheader i have placed a Textbox and a Search button having this code attached to it:

Private Sub cmdZoekMerk_Click()
Dim strMerk As String

strMerk = txtZoekMerk
Debug.Print strMerk

DoCmd.ApplyFilter , "Forms![frmRandapparaatLijst]![txtMerk] = '" & strMerk & "'"
End Sub

txtZoekMerk is the textbox that has to be filled in.
Why doesn't this filter work? I tried the whole day to change my WHERE syntax, what is wrong?

thx in advance
Noizz

 

[RoyVidar]

Hi!

Think you'll have to use the field name the control is bound to. So if the field txtMerk is bound to is Merk, then perhaps something like this:

[tt]DoCmd.ApplyFilter , "Merk = '" & strMerk & "'"[/tt]

Roy-Vidar

 

[Noizz]

thx man, i didn't know I had to fill in the fieldname instead of the control name !

thx again

Noizz

 

[Noizz]

I still got a problem:

when i execute this:

        strFilter = ""
        
        If Not IsNull(txtCompID) Then
            sngCompID = txtCompID
            strFilter = strFilter & "[CompID] = '" & sngCompID & "' AND "
        End If
        Debug.Print strFilter
        
        strFilter = Left(strFilter, Len(strFilter) - 5)
        Debug.Print strFilter
        
        DoCmd.OpenForm "frmComputerLijst", , , strFilter, acFormReadOnly

i get error 2501: Action Applyfilter has been cancelled

what is wrong with my code?

 

[RoyVidar]

As far as I can see, there is nothing wrong with that code. I'm also able to open a form, use the previous applyfilter thingie...

What line gives the error. Try to step thru i line by line (F8) and see what happens (set a breakpoing - F9 - on the first executable code line in the sub)

Are there any other routines/events that might be involved?

Roy-Vidar

 

[Noizz]

mjah,

the problem is:

my [COMPID] in the table has an input mask "COMP_"0000
so the last 4 numbers give the code (autonumber)
COMP_00021 is actually 21 (i think)

and in the "search-form", there is a textbox that also has an input mask "COMP_"0000 (txtCompID).

When you push the Search button a variable sngCompID is filled with txtCompID so the variable makes 0021 (from the inputmask) to just 21

and this value is used in the filter

(i hope you understand it)
greetz
Noizz

 

[RoyVidar]

The filter operates according to what's stored in the tables, so when passing a numeric just remove the quotes, perhaps something like this:

[tt] If Not IsNull(txtCompID) Then
sngCompID = txtCompID
strFilter = strFilter & "[CompID] = " & sngCompID & " AND "
End If
strFilter = Left(strFilter, Len(strFilter) - 5)
DoCmd.OpenForm "frmComputerLijst", , , strFilter, acFormReadOnly[/tt]

Roy-Vidar

 

[Noizz]

i still got a problem

situation: Continuous form frmComputerLijst (computerlist), in the formheader i placed some txtboxes and comboboxes.

code:

Private Sub cmdFilter_Click()
Dim strFilter As String

strFilter = ""

If Not IsNull(cboFunctie) Then
    strFilter = strFilter & "[Functie] = '" & cboFunctie & "' AND "
End If

Case cboOperatorCPU
        Case "Minder dan"
            strFilter = strFilter & &quot;[Processor] < '&quot; & txtCPU & &quot;' AND &quot;
        Case &quot;Gelijk aan&quot;
            strFilter = strFilter & &quot;[Processor] = '&quot; & txtCPU & &quot;' AND &quot;
        Case &quot;Meer dan&quot;
            strFilter = strFilter & &quot;[Processor] > '&quot; & txtCPU & &quot;' AND &quot;
    End Select
End If

If Not IsNull(cboOperatorRAM) And Not IsNull(txtRAM) Then
    Select Case cboOperatorRAM
        Case &quot;Minder dan&quot;
            strFilter = strFilter & &quot;[Geheugen] < '&quot; & txtRAM & &quot;' AND &quot;
        Case &quot;Gelijk aan&quot;
            strFilter = strFilter & &quot;[Geheugen] = '&quot; & txtRAM & &quot;' AND &quot;
        Case &quot;Meer dan&quot;
            strFilter = strFilter & &quot;[Geheugen] > '&quot; & txtRAM & &quot;' AND &quot;
    End Select
End If

If Not IsNull(cboOperatorHD) And Not IsNull(txtHD) Then
    Select Case cboOperatorHD
        Case &quot;Minder dan&quot;
            strFilter = strFilter & &quot;[HardeSchijf] < '&quot; & txtHD & &quot;' AND &quot;
        Case &quot;Gelijk aan&quot;
            strFilter = strFilter & &quot;[HardeSchijf] = '&quot; & txtHD & &quot;' AND &quot;
        Case &quot;Meer dan&quot;
            strFilter = strFilter & &quot;[HardeSchijf] > '&quot; & txtHD & &quot;' AND &quot;
    End Select
End If

If Not IsNull(txtMobo) Then
    strFilter = strFilter & &quot;[Moederbord] = '&quot; & txtMobo & &quot;' AND &quot;
End If

If Not IsNull(txtSound) Then
    strFilter = strFilter & &quot;[Geluidskaart] = '&quot; & txtSound & &quot;' AND &quot;
End If

If Not IsNull(txtNetwork) Then
    strFilter = strFilter & &quot;[Netwerkkaart] = '&quot; & txtNetwork & &quot;' AND &quot;
End If

If Not IsNull(txtCD) Then
    strFilter = strFilter & &quot;[OptischeSchijf] = '&quot; & txtCD & &quot;' AND &quot;
End If

If Not IsNull(txtScreen) Then
    strFilter = strFilter & &quot;[Beeldscherm] = '&quot; & txtScreen & &quot;' AND &quot;
End If

If Not IsNull(txtMouse) Then
    strFilter = strFilter & &quot;[Muis] = '&quot; & txtMouse & &quot;' AND &quot;
End If

If Not IsNull(txtKeyboard) Then
    strFilter = strFilter & &quot;[Keyboard] = '&quot; & txtKeyboard & &quot;' AND &quot;
End If

If strFilter <> &quot;&quot; Then
    strFilter = Left(strFilter, Len(strFilter) - 5)
    Debug.Print strFilter
    DoCmd.ApplyFilter , strFilter
Else
    MsgBox &quot;Er is geen criteria ingegeven&quot;
End If

End SubIf Not IsNull(cboOperatorCPU) And Not IsNull(txtCPU) Then
    Select

------------------

this doesn't work very properly. When I choose Minder dan (Less than) in the combo box, and fill in the txtbox.
i get no result, it just doesn't filter.

[/code]
If Not IsNull(cboOperatorCPU) And Not IsNull(txtCPU) Then
Select Case cboOperatorCPU
Case "Minder dan"
strFilter = strFilter & &quot;[Processor] < '&quot; & txtCPU & &quot;' AND &quot;
Case &quot;Gelijk aan&quot;
strFilter = strFilter & &quot;[Processor] = '&quot; & txtCPU & &quot;' AND &quot;
Case &quot;Meer dan&quot;
strFilter = strFilter & &quot;[Processor] > '&quot; & txtCPU & &quot;' AND &quot;
End Select
End If
[/code]

thx in advance
Noizz

 

[Noizz]

deleting the ' is no option. When I delete the ' i get an APPLYFILTER error

greetz
Noizz

 

[RoyVidar]

What isn't working? What's the errormsg? Without errormessages we'll have to guess...

Are you using >,< or = on text ('A' < 'B') - or are they numeric - se below.

Whats the datatype of [Prosessor] and what is entered in txtCPU (if numbers, remove the text qualifiers ('). If one is text the other numeric, then change to equal datatypes and amend the criterias accordingly.

You might try to create a query of the recordsource of the subform and try each of the values you are trying to use in the filter (and/or check the tables for datatypes).

' - qualifier for text criterias
# - qualifier for date criterias
numerics don't have any qualifiers

Roy-Vidar

 

[Noizz]

ok, thanks for the reply.

the datatypes were wrong in the table (it was Text, but it had to be numeric)

greetz
Noizz