Combobox filtering a form!?

[dn1980]

Hello everyone,

need your urgent help...

A standard form based on simple query displays a current record. On the same form there is a combobox that displays all records in a query.

I add the test code to BeforeUpdate event of combobox:

Private Sub cmboxProdList_BeforeUpdate(Cancel As Integer)

Dim strPName As String

strPName = Forms!frmProductAuthorize!cmboxProdList.Column(1)

MsgBox (strPName)

End Sub

Works perfectly! Every time I select another line in combobox I see message with appropriate strPName - value of column 1 of combobox.

Now I'm trying to filter the form to display the record filtered by value selected in combobox...

Private Sub cmboxProdList_BeforeUpdate(Cancel As Integer)

Dim strPName As String

strPName = Forms!frmProductAuthorize!cmboxProdList.Column(1)

If IsLoaded("frmProductAuthorize") Then
Forms!frmProductAuthorize.Filter = "[Product Name] = strPName"
Forms!frmProductAuthorize.FilterOn = True
End If

End Sub

... I receive run-time error 2115... I've checked alike threads on this forum, but still didn't understand what's wrong with my filtering...

Thanks a lot for any help you can provide

 

[Kempes]

Try looking into this.

Docmd.FindRecord

This works for me.You'll need to point it to the value in your combo box as to what to find.

Or

Docmd.GotoRecord

Regards
Kempes

 

[dn1980]

Thank you, Kempes!

I tried with FindRecord first - it generates the same run-time error...

GoToRecord seems closer to the solution, but...

I put this code:

Private Sub cmboxProdList_BeforeUpdate(Cancel As Integer)

Dim ColumnID As Integer

ColumnID = cmboxProdList.ListIndex

If IsLoaded("frmProductAuthorize") Then
DoCmd.GoToRecord acDataForm, "frmProductAuthorize", acGoTo, ColumnID

End If

End Sub

... and it generates another run-time error: 2105 - impossible to go to the particular record. Why?

 

[MStef]

Hello dn1980!

Look at "DemoFilterFormCA2000.mdb",
I think it can help you.
Open Form1.

 

[Kempes]

find record works ok for me.

Based on a table containing recordID, and Field1

Record ID Field1
1 A
2 B
3 C
4 D

I've just done a basic form with RecordID showing on the form and Field1.

By adding a combo box named Test showing each recordID (unbound), I added the following code to the "On Change" event.

RecordID.setfocus
Docmd.FindRecord Test
me.refresh

This seems to work OK and should give you a starting point to add to your form.

Kempes

 

[dn1980]

Kempes, thanks!

That's perfect! It works.

Just one note: code should be placed to AfterUpdate event of combobox.

Thanks a lot!