Using Combo Box in main form to filter query in subform

[wilster98]

First off, I'm a novice at Access and VBA. I have some VBA experience, but it's sporadic.



I am making a form in an existing database. I'm trying to filter a query in the sub form using a combo box (combo61) The name of the field I'm trying to filter is Reference Number. I'm trying to use the code below:

 Private Sub Combo61_AfterUpdate()
Me.Filter = "Reference Number" & Me.Combo61
DoCmd.RunCommand acCmdApplyFilterSort
End Sub

When I run the code I get: Run-time error 3075
Syntax error (missing operator) in query 'reference number 11-01-03-01'.


11-01-03-01 is the value I'm trying to filter for in "Reference Number".

What am I doing wrong? Any help would be greatly appreciated.

[ssanfu]

Private Sub Combo61_AfterUpdate()
Me.Filter = "Reference Number" & Me.Combo61
DoCmd.RunCommand acCmdApplyFilterSort
End Sub

1) There is a space in the field name. Shouldn't use spaces in object names. If there is a space, need to use brackets.
2) Text fields need to have delimiters
3) The equals sign is missing.

Try this:

Private Sub Combo61_AfterUpdate()
   Me.Filter = "[Reference Number] = '" & Me.Combo61 & "'"
   Me.FilterOn = True
End Sub

Expanded, it looks like:
Me.Filter = "[Reference Number] = ' " & Me.Combo61 & " ' "

[wilster98]

The good news is, I no longer get the error message. The bad news is, my query does not filter. Any thoughts?

[ssanfu]

Are you sure there is "11-01-03-01" in the "Reference Number" field??

I would have to see the dB to do further testing.

[wilster98]

The combo box is pulling that value from the query. There are 3 possible values and I tried all 3.
The DB is full of proprietary information, unfortunately I can't share it.

[orange]

Try Me.Requery after your me.FilterOn = True

[ssanfu]

What is the ROW SOURCE for "Me.Combo61"? (the SQL)


Don't need the data. Just 1 or 2 records of munged data. Delete sensitive data and use made up data.
Do a Compact and Repair, then zip it.

[wilster98]

Try Me.Requery after your me.FilterOn = True

Nope, that didn't do it either. When I select a value from the combo box, the progress wheel comes up and it appears something is happening, but when it stops, the query in my sub form remains unfiltered.

[wilster98]

What is the ROW SOURCE for "Me.Combo61"? (the SQL)


Don't need the data. Just 1 or 2 records of munged data. Delete sensitive data and use made up data.
Do a Compact and Repair, then zip it.

I don't know if that would work either. I'm working on a front end. The back end has maybe 20 tables. It has been cobbled together by multiple "administrators" over the last 10 or 12 years.

[wilster98]

[QUOTE=ssanfu;319255]What is the ROW SOURCE for "Me.Combo61"? (the SQL)

The row source is: SELECT DISTINCT [Query for subform- Emp Name].[Reference Number] FROM [Query for subform- Emp Name]

[ssanfu]

Access FE & BE?
Do you have a recent back up of the BE? Make a copy of that, delete the data, C&R the FE and BE, then zip them.

Don't know what else to suggest without seeing the dB.

[orange]

I answered a similar set up and provided a sample database here
recently.
You have to requery the subform as in the sample db--But I didn't use Filter.

There is an example similar to what you're doing by steve bishop here.

Good luck.