How to filter the contents of a combo box based on a field in a form

[kmsperry]

I have a combo box that allows a user to select the name of a partner contact. The combo box should only have those partner names that match the partner that I am currently viewing.

I can get the combo box to either display all values in the contacts table or I can get it to just show the names for the first partner but I can't get it to show the names of contacts that match the partner I am currently viewing. When I move from partner to partner the contents of the combo box should change to match the partner being viewed.

I've tried placing contents of a field on the form in the criteria of the query that is the row source of the combo box but I can't get it to behave the way I need.

Any suggestions?

Thanks.

 

[VilaRestal]

You'll need to requery the combobox (or reset its rowsource) in the form's current event and probably in the partner field's afterupdate event.

 

[JayJay00]

How are your tables set up? i had alot of fun with this myself so may be able to help

I'm also a little confused by what your trying to do.. are you trying to a show data on a form based on the selection in a combo box.

Or base options available in a combo box based on the data on the form?

 

[kmsperry]

"You'll need to requery the combobox (or reset its rowsource) in the form's current event and probably in the partner field's afterupdate event. "

Thanks VilaRestal. Sounds to me like you are exactly right. I'll give it a try.

 

[kmsperry]

How are your tables set up? i had alot of fun with this myself so may be able to help

I'm also a little confused by what your trying to do.. are you trying to a show data on a form based on the selection in a combo box.

Or base options available in a combo box based on the data on the form?

I am actually trying to base the options available in the combo box based on the data on the form.

 

[JayJay00]

You will need something like the following in the row source property of the combo.

SELECT [Table].[Partner Contact] FROM Table WHERE ((([Table 2].[Field in Table that matches that of the data held in textbox on form])=Forms!FormName!TextboxName)) ;

Then in the after update event of the Textbox you will need the following code:

Me.[I]ComboBoxName[/I].Requery

This will re query the combo box when a change is made in the text box to ensure it shows the correct values

 

[kmsperry]

You'll need to requery the combobox (or reset its rowsource) in the form's current event and probably in the partner field's afterupdate event.

That did it. For some reason I thought it would requery with each record that I navigated to. Definitely not the case.

 

[RenaG]

I would like to jump on this thread because I have a similar situation. I am trying to have the values displayed in field DiagnosticType (combobox) driven by what has been selected in the field DiagnosticTool (also a combobox). Here is the select stmt on the DiagnosticType field:

SELECT DiagnosticType.ID, DiagnosticType.Type
FROM (DiagnosticTools 
    INNER JOIN DiagTypeToolsXref 
    ON DiagnosticTools.ID = DiagTypeToolsXref.DiagToolID) 
    INNER JOIN DiagnosticType 
    ON DiagTypeToolsXref.DiagTypeID = DiagnosticType.ID
 
WHERE (((DiagTypeToolsXref.DiagToolID)=[form]![sfrmDiagBreast]![DiagnosticTools].[ID]))
 
ORDER BY DiagnosticType.Type;

And I put this in the VBA:

Private Sub DiagnosticTool_AfterUpdate()
    Me.DiagnosticType.Requery
End Sub

But it shows nothing when I click the drop down arrow on the DiagnosticType combobox. Can you tell what I have done wrong?

TIA!

~RLG

 

[JANR]

@RenaG

WHERE (((DiagTypeToolsXref.DiagToolID)=[form]![sfrmDiagBreast]![DiagnosticTools].[ID]))

The correct syntax to use is:

Forms!FormName!ControlName

Link to refrence of formcontrols and properties: http://access.mvps.org/access/forms/frm0031.htm

It looks like you are refrencing a control on a subform from the main form, if that's true then the syntax will be:

Forms!MainFormName!NameOfSubformControl.Form!ControlOnSubform

JR

 

[RenaG]

Hi JANR,

Thanks for taking the time to look at my question and finding my OOPS. I made that correction. Unfortunately, it still doesn't work. I'm still missing something.

BTW - I am trying to reference a control on the subform, from the subform, not the main form.

~RLG

 

[JANR]

The answer is in the link I gave you.

If the control you are looking for is embedded in a subform in your subform then this is the syntax:

Forms!Mainform!Subform1.Form!Subform2.Form!ControlName

If it's on a different subform on you main form then:

Forms!Mainform!Subform1.Form!ControlName

JR

 

[RenaG]

Good morning JANR,

I did look at the link (and thanks btw for sending that; it, plus other pages on that site, look very helpful!).

Here is what the Where clause now looks like:

WHERE (((DiagTypeToolsXref.DiagToolID)=[Forms]![Patient]![sfrmDiagBreast].[Form]![DiagnosticTools].[ID]))

But I am still not getting anywhere with it .

I am very very new to Access (this is my second project). So, I don't know if this makes a difference or not but the main form consists of tabs and this subform is on one of the tabs. The tab is called Diagnostic. There is a field (date) between the DiagnosticTool field and the DiagnosticType field.

Any thoughts?

~RLG

 

[RenaG]

I'm still stuck on this issue. Does anybody have other suggestions to try? Do you need any more information?

Thanks so much!

~RLG

 

[JANR]

So, I don't know if this makes a difference or not but the main form consists of tabs and this subform is on one of the tabs. The tab is called Diagnostic

You don't refrence Tabs on a form, you only refrence the subformcontrol on the tab.

Try:

(((Where DiagTypeToolsXref.DiagToolID)= Forms!Patient!sfrmDiagBreast.Form!ID))

JR

 

[RenaG]

Hi JANR ~

I figured out some of my problem. First, the diagnostic table this form is updating had the field DiagnosticType instead of the DiagTypeID. Secondly, the DiagTypeToolsXref table had the DiagTypeID and the DiagToolID fields as Text instead of Number. So with those to problems fixed it is sorta working. When I select a value from the DiagnosticTools field it displays a window that says:

Enter Parameter Value
Forms!Patient!sfrmDiagBreast.Form!DiagnosticTools.ID

If I manually enter the correct DiagnosticTools.ID then the correct values appear in the DiagnosticType drop down list. So, it's not remembering the ID value from the DiagnosticTools field.

And now that this is working some-what, I see another problem. Once I go to the next record (there can be several different diagnostic tools / patient and they are all displayed as a continuous form in this subform) the values don't continue to display in the previous record. That's not good because the people using this database need to be able to open the diagnostic tab and see all the data at a glance. I hope that makes sense.

Any suggestions on any of this?

TIA!
~RLG

 

[JANR]

If you get a "Enter Parameter Value" promt it is Access way of telling you that there are somthing that access can't find, The usual suspect is misspelling of objects names. To make sure that you point to the correct control on your form use the wizzard to locate the right control.

1. Let your form stay open.
2. Open an new query
3. Put the cursor in any field in the query and start the wizzard, the little Magic wand on the tool menu
4. Select Forms -> Loaded Forms -> Navigate to the control you want to refrence
5. Copy/Paste the refrence access gives you to your SQL

Your second problem is related to Cascading combobox on Continious or Datasheet forms, it's is a known problem and the only solution seems to be to use a textbox that covers your combo and switch the visibility on and off.

Download the example called Continuous Form Combo from this site to give you some ideas on how to handle this problem. http://www.databasedevelopment.co.uk/examples.htm

JR

 

[RenaG]

Thank you for your great explanation. I am working in Access 2007. What version are you in? I couldn't follow the steps you outlined for setting up a query based on a form. I looked in my two books and couldn't find anything about it for Access 2007. So I am still trying to figure out that issue.

As for the code sample for the Continuous Form Combo. That makes a lot of sense. I am a little confused when I ran it (I saved it on my computer because it opens in read-only mode). It pops up the Enter Parameter Value window that says:

Forms!frmMain!sfmList.Form!CategoryID

The sfmList is not shown in the Forms object list (I did go into the Navigation Options to click on the "Show Hidden Objects" but it was already clicked on). And I couldn't find this reference anywhere. Anyway, once I get this figured out, it looks like the way for me to go.

Thanks again for all your help. Have a good weekend.

~RLG

 

[RenaG]

I figured out the sfmList issue. It is the name of the subform on the Main form and is populated with one of two forms.

Al-righty-o! Keep on digging .

~RLG