Filter produces a blank form

[lawdy]

I have a form which contains all the companies we work for. It has a combo box named cboFilterCompany which I am trying to use to filter the company ID field (a text box named tbLngCompanyNamesID)

The cboFilterCompany has the RowSource as:
"SELECT DISTINCT tblSpecialBillingCompanies.fTxtCompany, tblSpecialBillingCompanies.fLngCompanyNamesID
FROM tblSpecialBillingCompanies
WHERE (((tblSpecialBillingCompanies.fTxtCompany) Is Not Null))
ORDER BY tblSpecialBillingCompanies.fTxtCompany;"
which creates 2 columns, fTxtCompany and fLngCompanyNamesID. The BoundColumn is set on 2.

The AfterUpdate event is:
Private Sub cboFilterCompany_AfterUpdate()
If Nz(cboFilterCompany, 0) > 0 Then
Me.Filter = "[tbLngCompanyNamesID] = " & cboFilterCompany
Me.FilterOn = True
Else
MsgBox "ID is missing"
End If
End Sub



It seems to work. At the bottom, it shows 'Record 1 of 1' and that it is filtered. It even shows "[tbLngCompanyNamesID] = 10" in the filter on the form. But it don't show a record. The form is blank when filtered. I have tried it on several different companies and all are the same. I will appreaciate any help.

[ItsMe]

I believe that if it shows one record then there is a record there. Are you dealing with a main form and subform? Is the combo bound?

[lawdy]

The combo box is unbound and it is on the main form. And I am trying to filter the main form. It does show 'Record 1 of 1' each time I filter it, but it is a blank record. I know the records are in the table.

[ItsMe]

Maybe

If Nz(cboFilterCompany.Column(1), 0) > 0 Then

The value of your combo may be equal to the text and not the ID number you are trying to match in your criteria

you could do something like this within your afterupdate to help you visualize

debug.print "First Column = " & cboFilterCompany.column(0)
debug.print "Second Column = " & cboFilterCompany.column(1)

[lawdy]

The results was:
First Column = American Assembly
Second Column = 102

Keep in mind, the filter on the form shows [tbLngCompanyNamesID] = 102

[ItsMe]

I would build a new form and a new combobox. Create a combobox without the wizard and paste you SQL into the RowSource. Set the properties for Combo, such as Width, Bound, Name, etc. You can paste this stuff.

Bind the form or subform to the table and add the ID field to the bound form. Paste you VBA in the afterupdate and see what you get. Strip it down simple and build from there.

[lawdy]

Ok, I have created a new simple form with only CompanyName and CimpanyID fields and a combo box. The filter works good. There is something else wrong with my original form. Thank you for your help. I am closing this. Thanks again

[ItsMe]

.... There is something else wrong with my original form. ...

There is something wrong. It may be a good idea to rebuild it entirely, carefully pasting the VBA into new sub procedures.