Combo box to filter records in a subform

[dannybeaver]

Hi,

I have a combo box located on one of my forms, that is getting its information from a field in a table.

This table has all its records displayed in a subform.

I would like the combo box to basically requery the subform to only show records where the name matches what is in the combo box.



It is basically a list of customers, so if I was to select Person1 in the combo box, I would only want to see records for Person1 in the subform.

I would also need a way of clearing the search results, in order to get the subform to display all records again.

Any information will be greatly appreciated.

Thanks!!

D

[June7]

Why do you have form/subform arrangement? Is the main form bound to table? Are there several subforms?

If you bind the main form to Customers table, then set the subform container control Master/Child links properties, the subform will show only those records that associate with the customer record. Use the combobox to filter the main form. Options for filtering the form:

1. intrinsic Access filter/search tools

2. dynamic parameterized query as the form RecordSource http://www.datapigtechnologies.com/f...tomfilter.html

3. code constructs filter criteria and applies to form Filter property http://www.allenbrowne.com/ser-62.html

[dannybeaver]

Basically, the main form contains only the one sub form, that displays records from a linked table.
My combo box is linked to the correct field on the sub form, so it correctly displays the names of all customers, in ascending order.

When I make a selection from my combo box, it is not filtering the records down to only that selection.

Is there something that I can put in the after update event of the combo box to make it work?

Thanks for the response

[June7]

I suggest you use a single form set to Continuous View (arrange controls to look like Datasheet if you want) and put the combobox in the form header section. Then follow option 2 or 3.

If you really must use form/subform, example from my db:

'some code to build filter string
...
'apply to subform filter property
Me.ctrSampleList.Form.Filter = strSQL
Me.ctrSampleList.Form.FilterOn = True

[dannybeaver]

Hi June7,

Sorry for the extremely late reply, I have been busy with other stuff.

I have this code:

Option Compare Database



Private Sub Command17_Click()
On Error GoTo Err_Command17_Click
Forms!MainWindow!EPMSubForm.Requery



Exit_Command17_Click:
Exit Sub

Err_Command17_Click:
MsgBox Err.Description
Resume Exit_Command17_Click

End Sub

Private Sub CustomerValue_Exit(Cancel As Integer)
Forms!MainWindow!EPMSubForm!Customer.Requery
End Sub

Private Sub NameValue_Exit(Cancel As Integer)
Forms!MainWindow!EPMSubForm!EndUserName.Requery
End Sub

Private Sub SerialValue_Exit(Cancel As Integer)
Forms!MainWindow!EPMSubForm!SerialNumber.Requery
End Sub

Set at the OnExit event of the two text boxes and the one combo box, and also on the OnClick event of a button I use to requery the subform.

Can you spot anything majorly wrong with the code? When I click the button, or enter information into the boxes, I am not presented with any errors, maybe suggesting the code is okay, but the subform does not filter???

There is a screenshot of my Main form at: https://www.accessforums.net/access/...ain-46256.html a thread you have posted on.

Thanks for the help.

D

[June7]

Controls used to enter filter criteria must be UNBOUND, otherwise you change data in record.

Are you using dynamic parameterized query as form RecordSource?

If you want to requery a subform, why are you referencing fields in the Requery commands?

[dannybeaver]

This is the point June 7, I do not know. I have a very basic knowledge of Access and have been given these files to work on.

We have recently upgraded to Office 2013, and almost all of our access stuff was in ADP format. For now, we are using MS Access Run time 2003 to still use these ADP's, however I have been tasked with changing them all to mdb files.

Some guy who worked here before me set up all of these ADP files, using linked tables and stored procedures, all I have done is manage to import the forms into an mdb file, re-link the tables and configure a few of the queries. The code that I posted above was used in the places mentioned, in the ADP file, such as, the two text boxes, the combo box, and the refresh button. I have no idea how it is supposed to work, it just did, and now that I am trying to use it in an MDB file, it doesn't. Everything is basically the same, the control names are the same, so are the field names, the command buttons, the form names and table names. I have no idea how to get this to work.

[June7]

What are the two textboxes and which is combobox?

If the form does not show filtered records, then must not be using one of the suggested techniques. Since you want to apply requery I presume you have a dynamic parameterized query as the form's RecordSource. Did you review the video?

[dannybeaver]

Hi June7,

The two text boxes are name value, and serial value. The combo box is Customer.

I have just followed the video exactly, but it does not seem to be working for me. The one difference I noticed was that the guy in the video was only using one form, whereas I have the subform on my main form. It is the subform records that I need to filter. Is there anything you can suggest as to how I can adapt the video to my situation?

Thanks for the help.

[dannybeaver]

JUNE7 I GOT IT!!!!!!!!! FINALLY!!!!!!!

The problem with what I had followed in the video, was the fact that in the AfterUpdate event of the combo box, I did not reference my Subform.

I just had me.Requery, when I changed it to me.EPMSubform.requery, it worked perfectly!!

Hopefully, I should be able to work the rest out from here, thank you so much for your help, and for pointing me to that video.

Best regards,
Danny