Error 3075 Syntax error in query expression

[5hadow]

Am I missing, or have I misplaced characters?

Private Sub lstProcess_AfterUpdate()
   
    On Error GoTo ErrorCfg
   
    Select Case lstProcess.Value
   
        ' All
        Case 0
            Me.FilterOn = False
       
        ' 4.1
        Case 1
            Me.Filter = "fldDocID = " & DLookup("fldDocID", "tblWIProcess", "fldProcess = '4.1'")
            Me.FilterOn = True

Error is

3075

Syntax error in query expression "fldDocID= ' ,

 

[theDBguy]

Hi. That means your DLookup() is not returning a matching result. Try this to avoid the error:

Me.Filter = "fldDocID = " & Nz(DLookup("fldDocID", "tblWIProcess", "fldProcess = '4.1'"),0)

Hope that helps...

 

[June7]

If you are using a listbox, is the code actually pulling value? Try:

Select Case Me.lstProcess.Column(0)

 

[5hadow]

Hi. That means your DLookup() is not returning a matching result. Try this to avoid the error:

Me.Filter = "fldDocID = " & Nz(DLookup("fldDocID", "tblWIProcess", "fldProcess = '4.1'"),0)

Hope that helps...

Thank you, unfortunately I still get the error.
However, I've changed the code a bit. It is now

Private Sub lstProcess_AfterUpdate()
    
    On Error GoTo ErrorCfg
    
    Select Case lstProcess.Value
    
        ' All
        Case 0
            Me.FilterOn = False
        
        ' 4.1
        Case 1
            Me.Filter = "fldProcessID = " & DLookup("fldDocID", "tblWIProcess", "fldProcess = '4.1'")
            Me.FilterOn = True

I'm trying to have my list box (lstWI) filter all items in it based on what I select from "lstProcess" drop-down.
"lstProcess" list is manually typed in row source.
"lstWI" list is based on my main query
SELECT qryWI.fldDocSerial FROM qryWI;
and it only shows "fldDocSerial" as a list.

 

[theDBguy]

Thank you, unfortunately I still get the error.
However, I've changed the code a bit. It is now

Private Sub lstProcess_AfterUpdate()
   
    On Error GoTo ErrorCfg
   
    Select Case lstProcess.Value
   
        ' All
        Case 0
            Me.FilterOn = False
       
        ' 4.1
        Case 1
            Me.Filter = "fldProcessID = " & DLookup("fldDocID", "tblWIProcess", "fldProcess = '4.1'")
            Me.FilterOn = True

I'm trying to have my list box (lstWI) filter all items in it based on what I select from "lstProcess" drop-down.
"lstProcess" list is manually typed in row source.
"lstWI" list is based on my main query
SELECT qryWI.fldDocSerial FROM qryWI;
and it only shows "fldDocSerial" as a list.

But you didn't apply my suggestion. Otherwise, I can't tell if the error you mentioned is the same as the previous/original problem.

 

[theDBguy]

I'm trying to have my list box (lstWI) filter all items in it based on what I select from "lstProcess" drop-down.

Not sure I understand this one. If you're trying to reduce the number of items in a dropdown, then using Me.Filter would be the wrong way to do it. Me.Filter will limit the records on a Form, not a Listbox.

 

[The_Doc_Man]

Stated another way, there is no filter for records in a listbox UNLESS you have a WHERE clause in the listbox's .RowSource query. As theDBguy said, Me.Filter filters the whole form. What's more, even if you set the filter, Access won't IMPLEMENT the filter until you do something like Me.Requery or until you attempt to navigate to another record. Not going to swear that turning on a filter (.FilterOn=TRUE) forces a .Requery because when I looked it up, the MS documentation was silent on that specific topic.

 

[June7]

Did you see post #3?

 

[arnelgp]

Private Sub lstProcess_AfterUpdate()
    
    On Error GoTo ErrorCfg
    With Me.lstWI
           .Value = ""
        Select Case lstProcess.Value
    
            ' All
            Case 0
                .RowSource = "SELECT qryWI.fldDocSerial FROM qryWI;"
        
            ' 4.1
            Case 1
                 .RowSource = "SELECT qryWI.fldDocSerial FROM qryWI Where fldProcessID = " & DLookup("fldDocID", "tblWIProcess", "fldProcess = '4.1'")
        End Select
    End With