Cascading Combo Box on Form Confusion

[synses]

---

I have a form with a field for "Client" (first) and a subsequent field for "Facility" - users select from choices that are from a table that has a Clients field in one column and the related Facility field for each client in another column.

I have got to learn to restrict the viewed choices on the form for "Facilty" for only the facilities that are facilities of the chosen "Client".

I also have to allow users to add - if needed - to both or either of them, and it automatically updates the tables with the add.

I tried putting this in the record source for the Facility box on the form:

"SELECT tblClientFacility.FacilityName FROM tblClientFacility WHERE (((tblClientFacility.ClientIdFK)=[Forms]![frmLessonLearned]![cboClient]));"

It produces nothing in the Facilities box.

I mimicked it from another solution to a similar question.



Searching for days, tons of books, webpages, and videos, and more confused now than when I started.

Any help appreciated!
Synthia

[andy49]

Try opening the onchange of the first combo and typing

Me.cbofacility.rowsource= then addyour select statement here


Sent from my iPhone using Tapatalk

[synses]

Thanks, how do you open the onchange..?

[pbaldy]

Here:

http://www.baldyweb.com/FirstVBA.htm

but I'd use the after update event. The change event fires with every keystroke. With your SQL all you should need is the requery, the first method here:

http://www.baldyweb.com/CascadingCombo.htm

[synses]

I cross posted this, watch both forums closely, and apologize if that is a problem.

[pbaldy]

Did you try the requery I mentioned?

[synses]

I appreciate the reply but I don't know how to enter the info on your website yet. I am new and still using the ACCESS controls, boxes, etc., and don't know how to alter the code on the website you sent the link to for my database and don't know how to enter the code even if I altered it correctly.

I can't attach the database because of size limits.

Table: "tblClientFacility" Three fields: "ClientFacilityId" "ClientIdFK" "FacilityName"
Form "frmLessonLearned" has these fields that I am working on:
1. "Client" that has source of "ClientIdFK" from "tblClientFacility" -"cboClient"
2. "Facility" that has source of "ClientFacilityIdFK" from "tblClientFacility" - "cboFacilityName"

The problem:

How do I make it so that when using the form, the user:
1. Selects a Client, then
2. Selects a Facility from a short list of facilities that are unique to the client and aligned with that client in the "tblClientFacility" with these fields: "ClientFacilityId" "ClientIdFK" "FacilityName"


This is the question: How do I make the facility list on the form show ONLY the facilities for the client that the user has just chosen?

Users also need to be able to enter new clients and facilities and have them update the "tblClientFacility" ...
Thanks for any help you can offer!
Synthia

[synses]

How do I do that? (requery)
I have seen it in some videos but have not tried to do it yet...not sure of the steps.

Videos jump through things and it is hard to connect my fields and combo boxes, etc., source versus look up, etc., to the ones they demo. I get lost.

I am not dull, have two master's and a doctorate-but in education and language-not computers or programming.

I have extensive Excel expeience, formulas, etc., became good with f=ormulas self taught, even a little VBA, and am determined to become proficient at ACCESS.

Proble is that I am smart enough to know what I don't know, but obviously not smart enough to synthesize all the different/differing advice and apply any of them with any efficacy.

Thanks for any step by step help you can offer.

Synthia

[pbaldy]

I gave you a link that showed where/how to enter the code. I gave you another link with a sample that gives the exact code. All you have to do is change the name of the combo to yours. That assumes the second combo still has the row source that refers to the first in its criteria.

[synses]

OK, thank yu, I will go back and do that. THANK YOU so much!