End of Statement Error Message

[qwerty]

I am getting an end of statement error msg.

[pbaldy]

The opening quotes in your Forms(...) closes the quotes for the string you're building, leaving everything after twisting in the wind. Use single quotes there.

[qwerty]

The opening quotes in your Forms(...) closes the quotes for the string you're building, leaving everything after twisting in the wind. Use single quotes there.

I get expression error now when I change it to single quotes, I tried the following
1. Forms('...') and Forms('...')
2. Forms('...') and Forms("...")
3. Forms("...") and Forms('...')

    If Forms('002_Criteria').CKNFixer.ControlSource = True Then        strFilterString = strFilterString & " OR Forms('002_Criteria').NFixer = True"
    End If

[moke123]

CKNFixer.ControlSource = True

This doesnt look right to me. Is the name of the field True?

[pbaldy]

Good catch Moke, I didn't see that.

How about this, though it seems odd to refer to a form in the criteria like that.

strFilterString = strFilterString & " OR Forms!002_Criteria.NFixer = True"

[qwerty]

This is what I have written, but nothing is filtering out at all in the List Box.

Private Sub Refilter()    
    Dim strFilterString As String


    'BUILD THE FILTER STRING:
    If Forms("002_Criteria").CKNFixer.ControlSource = True Then
        strFilterString = strFilterString & " OR Forms!002_Criteria.NFixer = True"
    End If
    
    'REMOVE LEADING " OR " ON FILTER STRING:
    If strFilterString <> "" Then strFilterString = Mid(strFilterString, 5)
    
    'APPLY FILTER STRING TO 003_Species:
    If strFilterString <> "" Then
        Forms("003_Species").ListSciName.RowSource = "SELECT * FROM [qSciName] WHERE " & strFilterString
    Else
        Forms("003_Species").ListSciName.RowSource = "qSciName"
    End If


End Sub

[pbaldy]

Did you see Moke's post 4? Also, in words what are you trying to accomplish? Saying "where a form control is true" doesn't make any sense from a data perspective. I'd expect "where a field is true" or something similar.

[qwerty]

Did you see Moke's post 4? Also, in words what are you trying to accomplish? Saying "where a form control is true" doesn't make any sense from a data perspective. I'd expect "where a field is true" or something similar.

I have a list box that displays all species on Form 003_Species. On 002_Criteria Form there are check boxes, if NFixer is one of the check box. If selected it is only going to show the species = Yes on Form 003_Species.

[pbaldy]

Then I'd expect:

strFilterString = strFilterString & " OR Species = True"

[qwerty]

Then I'd expect:

strFilterString = strFilterString & " OR Species = True"

IT still does nothing, I even type it as Forms!003_Species.ListSciName, and gives me invalid character expression. With it type like this Forms("003_Species").ListSciName, the debug locates the correct part of the form.

Private Sub Refilter()    
    Dim strFilterString As String


    'BUILD THE FILTER STRING:
    If Forms("002_Criteria").CKNFixer.ControlSource = True Then
        strFilterString = strFilterString & " OR Forms!003_Species.ListSciName = True"
    End If
    
    'REMOVE LEADING " OR " ON FILTER STRING:
    If strFilterString <> "" Then strFilterString = Mid(strFilterString, 5)
    
    'APPLY FILTER STRING TO 003_Species:
    If strFilterString <> "" Then
        Forms("003_Species").ListSciName.RowSource = "SELECT * FROM [qSciName] WHERE " & strFilterString
    Else
        Forms("003_Species").ListSciName.RowSource = "qSciName"
    End If


End Sub

[pbaldy]

It needs to be the name of a field in the record source (table), not a form reference. Basically:


strFilterString = strFilterString & " OR FieldName = True"

If you're saying the listbox contains a field name, you have to concatenate that in so it results in the above:

strFilterString = strFilterString & " OR " & Forms!003_Species.ListSciName & " = True"

[qwerty]

It needs to be the name of a field in the record source (table), not a form reference. Basically:


strFilterString = strFilterString & " OR FieldName = True"

If you're saying the listbox contains a field name, you have to concatenate that in so it results in the above:

strFilterString = strFilterString & " OR " & Forms!003_Species.ListSciName & " = True"

The field name with yes/no values is NFixer, I was confused when told to change that. It still gives me the invalid expression written as Forms!002_criteria.nfixer

[pbaldy]

How about:

strFilterString = strFilterString & " OR NFixer = True"

[qwerty]

How about:

strFilterString = strFilterString & " OR NFixer = True"

I am totally lost, just writing NFixer doesn't.

I even put added the fields in the query. As soon as I add in the other Fields to correspond with the check box, it cause the scientific names to shrink in the list box until nothing is showing. It is just reading from the query, as I deleted the code to see if the results differ and it did not. It's not reading the code at from the vba.

[Micron]

It's not reading the code at from the vba

PMFJI but maybe the code isn't executing as expected. I suggest stepping through to see what happens, and check the variables to see if their values make sense. The focus seems to be on the filter string, but this CKNFixer.ControlSource = True makes no sense to me so the IF block might be bypassed. Maybe it should be
If Forms("002_Criteria").CKNFixer= True Then
or
If Forms!002_Criteria.CKNFixer= True Then