Cascading Combo Box confusion... (1 Viewer)

[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.

Some clients have only one facility, some have many.

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

Any help appreciated!
Synthia

 

[sneuberg]

What's the problem or question?

 

[CJ_London]

cross posted here

http://www.accessforums.net/showthread.php?t=65091

@synses - please take a look at this link http://www.excelguru.ca/content.php?184

 

[Synses]

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.
Table "tblClientFacility" has 3 fields: "ClientFacilityId" "ClientIdFK" "FacilityName"
Form "frmLessonLearned" has these fields:
1. "Client" that has source of "ClientIdFK" from "tblClientFacility" -"cboClient"
2. "Facility" that has source of "ClientFacilityIdFK" from "tblClientFacility" - "cboFacilityName"

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

Sorry if unclear earlier, any help appreciated.
Synthia

 

[Synses]

I cross posted this, still have no answer, watch both forums closely, and apologize if that is a problem, but I am desperate and trying to do this for several days. It seems I get advice that is not really complete or just doesn't work, but I have to keep trying. If you are mad because I cross-posted, I believe that is misplaced anger, I am very grateful and realize the efforts helpers volunteer, and I need that help, lots of it, and don't crosspost out of disregard for the value or generosity of the help but out of desperate need for the help, I am very grateful for help and can't give up until I get it correctly done.

I still have no answer so becoming angry and reprimanding me for cross-posting seems a little.....odd...when I do not have the amswer from either post.

Seems harsh for this forum, not the usual helpfulness I have been awed by..up till now.

 

[sneuberg]

Normally the bound column of the combo box for the Client would have the primary key of the client table. The row source query of the facilities combo box would also have this as a foreign key. In the criteria of this field you would put a reference to the client combo box. Something like

Forms!TheNameOfTheForm!TheNameOfTheCombo

which would limit the selection of that combo box to that client.

But I can't see how you have this set up. It would help if you uploaded your database so that I could give you specifics.

 

[sneuberg]

I still have no answer so becoming angry and reprimanding me for cross-posting seems a little.....odd...when I do not have the amswer from either post.

Seems harsh for this forum, not the usual helpfulness I have been awed by..up till now.

I don't think anyone gets angry about this. We just don't want to waste our time if someone on another forum has already taken care of the problem.

 

[Synses]

Thank you. I attached the file.
I think that there are several ways to do it, and I am probably getting mixed advice, all good, but most not complete enough (due to contributor's time constraints or assumption of a highier skill level than I have) for me to figure out how to completely achieve what I am trying to do.

The thing is, I have to learn ONE good, simple, way to do this, because I promised my sister I would do this for her, by Wednesday, and I owe her greatly, and there are several places where there will eventually be forms and reports on this database that combo boxes will be needed for data entry/database maintenance forms and various report generations.

So, I have to do it quckly and I have to learn to do it quickly.
So, any help is appreciated!!!
Synthia

 

[sneuberg]

First there's some strange expression in the on change event of the cboClient combo. I suggest just deleting it. Let us know what you were trying to do and we'll figure something out.

The cboClientFacilitycombo needs a row source that depends on the selection in the cboClient combo. I believe the following query would work for you. Aside from missing this the combo box appears to be set up correctly, e.g., Column Width, Column Count etc.

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

I noticed the cboClientFacility didn't have a control source. I suspect you will want to assign it.

Whenever the selection in the cboClient combo changes it will change what needs to be in the drop down of the cboClientFacility combo. To get it in sync you need to requery the cboClientFacility combo in the afterupdate of the cboClient. It should look like this:

Private Sub cboClient_AfterUpdate()

Me.cboClientFacility.Requery

End Sub

I noticed that you are using lookup fields. When you get a spare moment I suggest reading this web page.

 

[moke123]

One possible reason you are struggling is the use of lookup fields in your tables.

take a look here...http://access.mvps.org/access/lookupfields.htm

 

[Synses]

Thanks, for all, here are some replies
1. "First there's some strange expression in the on change event of the cboClient combo"

yes, from a different suggestion, will delete, thanks​

2. "The cboClientFacilitycombo needs a row source that depends on the selection in the cboClient combo. I believe the following query would work for you. Aside from missing this the combo box appears to be set up correctly, e.g., Column Width, Column Count etc.
Code:"

Thank you I know that is valuable and exactly what is needed just not sure exactly how to use it. Can you direct me where to enter that code?​

3. "I noticed the cboClientFacility didn't have a control source. I suspect you will want to assign it."

I set it to ClientFacilityIdFK...but worry if it is right...will that show the Facility Name the way it does in the table?​

4. "Whenever the selection in the cboClient combo changes it will change what needs to be in the drop down of the cboClientFacility combo. To get it in sync you need to requery the cboClientFacility combo in the afterupdate of the cboClient. It should look like this:
Code:"

Thanks, not sure how to enter it...do I click in afterupdate and get the chance to paste it in..?​

5. "I noticed that you are using lookup fields. When you get a spare moment I suggest reading this web page."

THANK YOU! I will read the webpage asap. If it answers some of these questions, feel free to ignore them. I realize you are being generous with your expertise and will look up/learn from your suggestions.

Thanks again,
Synthia

 

[Synses]

Thanks, I see I need to learn not to use look up boxes but it was all I could figure out how to do intuitively, I will look at the website and hope I can learn how to do cascading boxes otherwise.

It is difficult to learn these things, it seems everything (books, videos, websites) either take me too far back to "What is a database?" or too far ahead to "use this code" ...I am somewhere in between and need to get up to speed fast.

I did learn and do some VBA pretty quickly in Excel, hope I can do this quickly, too.

One possible reason you are struggling is the use of lookup fields in your tables.

take a look here...]

 

[sneuberg]

Thank you I know that is valuable and exactly what is needed just not sure exactly how to use it. Can you direct me where to enter that code?​

Go to the cboClientFacility Properties, Data tab, click on the Row Source and then the ellipsis (...) on the right. This will put you in the query designer. You can close the Show Table dialog, switch to SQL view and copy and paste the SQL in. Click Yes to save when you close the query designer.

3. "I noticed the cboClientFacility didn't have a control source. I suspect you will want to assign it."

I set it to ClientFacilityIdFK...but worry if it is right...will that show the Facility Name the way it does in the table?
​

I guess if the look field don't foul it up.

Thanks, not sure how to enter it...do I click in afterupdate and get the chance to paste it in..?​

Go to the cboClient Properties, Event tab, click on the ellipses in the AfterUpdate event, in the Choose Builder dialog, select Code builder, click Ok,
and paste the following into the subroutine

Me.cboClientFacility.Requery

​

 

[Synses]

Thank you, am in awe, will do and repost.
Synthia

 

[Synses]

I put it in and it works perfectly. I am in awe, very grateful. Is that VBA...what programming language is that, or system, or whatever it is called!?! Is there a book or book series or online resource you would suggest that I could read to learn that?

Thank you immensely.
Synthia

 

[sneuberg]

I was just thinking that for the cboClientFacility combo to show the correct facility for existing records you will probably need to put

Me.cboClientFacility.Requery

in the form's On Current event too.

A good start for VBA is this video series.

 

[Synses]

Is there a way on the form, when the user selects a client, then is given the option to select a facility, that they can select
"ALL" and the info is saved to all the facilities tables that it is connected to?
or select a few different ones?

 

[sneuberg]

Is there a way on the form, when the user selects a client, then is given the option to select a facility, that they can select
"ALL" and the info is saved to all the facilities tables that it is connected to?
or select a few different ones?

What info would be saved to all the facilties tables? And is there more than one facilities table?

 

[Synses]

Right now the form feeds info from the lesson learned field on the form to the Lesson Learned table.
(I incorrectly stated that it fed info to the "Faciltiies tables" in my question, there are no "Facilities tables" ther is one "Client Faciliies" table).

There is
one Client table (lists all clients) and
one Client Facilties table (lists all clients in one field and then all facilities for each client is a record in the "Client Facilities" field) and
one Lesson Learned table (lists all clients in one field and then all facilities for each client is a record in the "Client Facilities" field, then a Lesson Learned field for every record).

The main thing that is needed is that when the user selects a client, and then see choices for faciltiies for that client, they can choose "ALL" or select "SOME" of the facilities that the info they are going to enter on the form relates to. (Multiple values)

I think the answer to my question about how to connect the lesson learned info to the client table and to the facilities table is going to be in report generation - and I learning that and going to make those tomorrow. (the part about connecting the lesson learned to the client and to the facilities)

Sorry for typing so fast without thinking it through enough when I first posted.
Thank you

 

[Synses]

What info would be saved to all the facilties tables? And is there more than one facilities table?

Right now the form feeds info from the lesson learned field on the form to the Lesson Learned table.
(I incorrectly stated that it fed info to the "Faciltiies tables" in my question, there are no "Facilities tables" ther is one "Client Faciliies" table).

There is
one Client table (lists all clients) and
one Client Facilties table (lists all clients in one field and then all facilities for each client is a record in the "Client Facilities" field) and
one Lesson Learned table (lists all clients in one field and then all facilities for each client is a record in the "Client Facilities" field, then a Lesson Learned field for every record).

The main thing that is needed is that when the user selects a client, and then see choices for faciltiies for that client, they can choose "ALL" or select "SOME" of the facilities that the info they are going to enter on the form relates to. (Multiple values)

I think the answer to my question about how to connect the lesson learned info to the client table and to the facilities table is going to be in report generation - and I learning that and going to make those tomorrow. (the part about connecting the lesson learned to the client and to the facilities)

Sorry for typing so fast without thinking it through enough when I first posted.
Thank you