If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
synchronize two combo boxes
I Have looked at many examples but believe mine is a bit different.
Combobox1 lists services such as Food, Utilities, Clothing. I want the second Combobox2 to list those Agencies I can refer clients to. A given agency may show up for any or all the services. I do not want to create one table which lists each Agency (multiple times) for the different services they may or may not provide. I have one Table listing the agency with Yes/No columns for services provided. This will enable administrator to easilly check whether a given agency currently supports particular service. Therefore it appears I would need a separate row source query for each service in Combobox1 to show list of available Agencies in Combobox2. Have played a bit with using Select Case in the AfterUpdate event of Combobox1 without success. Even with one case scenario am unable to "tell" Combobox2 its rowsource comes from Combobox1. Do the Comboboxes need to be unbound? Would appreciate advice. Larry |
#2
|
|||
|
|||
synchronize two combo boxes
You are very close.
Both combos should be unbound. Using bound combo boxes for searching will cause the value in the current record to change if a combo is bound. A Select Case statement in the After Update event of combo1 would be an ideal way to create a row source for combo 2. I don't know the names of the Yes/No fields in your agency table, so I will just call them [Food], [Utilities], and [Clothing] For example sake. Private Sub Combo1_AfterUpdate() Dim strSQL As String Dim strReplace As String strSQL = "SELECT AgencyName FROM tblAgency WHERE [TheField] = True;" Select Case Me.Combo1 Case "Food" strReplace = "Food" Case "Utilities" strReplace = "Utilities" Case "Clothing" strReplace = "Clothing" End Select strSQL = Replace(strSQL, "TheField", strReplace) Me.Combo2.RowSource = strSQL End Sub -- Dave Hargis, Microsoft Access MVP "tuesamlarry" wrote: I Have looked at many examples but believe mine is a bit different. Combobox1 lists services such as Food, Utilities, Clothing. I want the second Combobox2 to list those Agencies I can refer clients to. A given agency may show up for any or all the services. I do not want to create one table which lists each Agency (multiple times) for the different services they may or may not provide. I have one Table listing the agency with Yes/No columns for services provided. This will enable administrator to easilly check whether a given agency currently supports particular service. Therefore it appears I would need a separate row source query for each service in Combobox1 to show list of available Agencies in Combobox2. Have played a bit with using Select Case in the AfterUpdate event of Combobox1 without success. Even with one case scenario am unable to "tell" Combobox2 its rowsource comes from Combobox1. Do the Comboboxes need to be unbound? Would appreciate advice. Larry |
#3
|
|||
|
|||
synchronize two combo boxes
Larry
You did say "appreciate advice"...g If you want to use "Yes/No columns for services provided", consider using a spreadsheet instead. If you are trying to let "administrators ... easily" work inside tables, consider using a spreadsheet instead. Access is a relational database ... you won't get easy use of its features/functions if you insist on feeding it 'sheet' data (i.e., your columns of Yes/No services). Access tables are great for storing data, but lousy for data entry/etc. ... that's what Access forms are for. Don't constrain yourself to table design to match administrator needs -- that's a spreadsheet! You did ask...! Good luck! Regards Jeff Boyce Microsoft Office/Access MVP "tuesamlarry" wrote in message ... I Have looked at many examples but believe mine is a bit different. Combobox1 lists services such as Food, Utilities, Clothing. I want the second Combobox2 to list those Agencies I can refer clients to. A given agency may show up for any or all the services. I do not want to create one table which lists each Agency (multiple times) for the different services they may or may not provide. I have one Table listing the agency with Yes/No columns for services provided. This will enable administrator to easilly check whether a given agency currently supports particular service. Therefore it appears I would need a separate row source query for each service in Combobox1 to show list of available Agencies in Combobox2. Have played a bit with using Select Case in the AfterUpdate event of Combobox1 without success. Even with one case scenario am unable to "tell" Combobox2 its rowsource comes from Combobox1. Do the Comboboxes need to be unbound? Would appreciate advice. Larry |
#4
|
|||
|
|||
synchronize two combo boxes
Thank you
My Combobox1 gets its "list" of data from "Needs" table and puts the selection into a "Service" table underlying the collection Form where the Comboboxes reside. If these Comboboxes are unbound then how do the selections I make get saved in the underlying table? Larry "Klatuu" wrote: You are very close. Both combos should be unbound. Using bound combo boxes for searching will cause the value in the current record to change if a combo is bound. A Select Case statement in the After Update event of combo1 would be an ideal way to create a row source for combo 2. I don't know the names of the Yes/No fields in your agency table, so I will just call them [Food], [Utilities], and [Clothing] For example sake. Private Sub Combo1_AfterUpdate() Dim strSQL As String Dim strReplace As String strSQL = "SELECT AgencyName FROM tblAgency WHERE [TheField] = True;" Select Case Me.Combo1 Case "Food" strReplace = "Food" Case "Utilities" strReplace = "Utilities" Case "Clothing" strReplace = "Clothing" End Select strSQL = Replace(strSQL, "TheField", strReplace) Me.Combo2.RowSource = strSQL End Sub -- Dave Hargis, Microsoft Access MVP "tuesamlarry" wrote: I Have looked at many examples but believe mine is a bit different. Combobox1 lists services such as Food, Utilities, Clothing. I want the second Combobox2 to list those Agencies I can refer clients to. A given agency may show up for any or all the services. I do not want to create one table which lists each Agency (multiple times) for the different services they may or may not provide. I have one Table listing the agency with Yes/No columns for services provided. This will enable administrator to easilly check whether a given agency currently supports particular service. Therefore it appears I would need a separate row source query for each service in Combobox1 to show list of available Agencies in Combobox2. Have played a bit with using Select Case in the AfterUpdate event of Combobox1 without success. Even with one case scenario am unable to "tell" Combobox2 its rowsource comes from Combobox1. Do the Comboboxes need to be unbound? Would appreciate advice. Larry |
#5
|
|||
|
|||
synchronize two combo boxes
Thanks Jeff, yes I know the value of a relational database. I am creating one
to replace a spreadsheet currently used. The Agency table I have with the multiple Yes/No columns for all the types of services offered is essentially a static lookup table. In looking at the various solutions for synchronizing ComboBoxes, they seem to be based on one table which lists all the possible combinations . Therefore I would have to create a table which would repeat Name of agency for each service it provided. If for some reason that agency did not offer service for a time, I would have to delete that record and then add it again when it restarted. Hence my choice of one table listing Agencies, addresses, etc. and the services they offer. Is there a better way for me to handle this? "Jeff Boyce" wrote: Larry You did say "appreciate advice"...g If you want to use "Yes/No columns for services provided", consider using a spreadsheet instead. If you are trying to let "administrators ... easily" work inside tables, consider using a spreadsheet instead. Access is a relational database ... you won't get easy use of its features/functions if you insist on feeding it 'sheet' data (i.e., your columns of Yes/No services). Access tables are great for storing data, but lousy for data entry/etc. ... that's what Access forms are for. Don't constrain yourself to table design to match administrator needs -- that's a spreadsheet! You did ask...! Good luck! Regards Jeff Boyce Microsoft Office/Access MVP "tuesamlarry" wrote in message ... I Have looked at many examples but believe mine is a bit different. Combobox1 lists services such as Food, Utilities, Clothing. I want the second Combobox2 to list those Agencies I can refer clients to. A given agency may show up for any or all the services. I do not want to create one table which lists each Agency (multiple times) for the different services they may or may not provide. I have one Table listing the agency with Yes/No columns for services provided. This will enable administrator to easilly check whether a given agency currently supports particular service. Therefore it appears I would need a separate row source query for each service in Combobox1 to show list of available Agencies in Combobox2. Have played a bit with using Select Case in the AfterUpdate event of Combobox1 without success. Even with one case scenario am unable to "tell" Combobox2 its rowsource comes from Combobox1. Do the Comboboxes need to be unbound? Would appreciate advice. Larry |
#6
|
|||
|
|||
synchronize two combo boxes
"tuesamlarry" wrote
In looking at the various solutions for synchronizing ComboBoxes, they seem to be based on one table which lists all the possible combinations . You are mistaken -- that is not the case. Synchronized Combo Boxes are almost always based on related tables, not a single table. I have never implemented synched Combos using just one table. Therefore I would have to create a table which would repeat Name of agency for each service it provided. As your premise is incorrect, you cannot draw a valid conclusion. (I guess, in symbolic logic, they'd say, it implies any conclusion.) Is there a better way for me to handle this? Definitely. You'll have a table of agencies, a table of services, and a table relating the two (called an intersection table or a junction table)... I'm guessing that you may have multiple agencies providing a service, and multiple services provided by an agency. That is a many-to-many relationship, easy to have a first combo for picking the agency, and use the value in constructing the query for a second combo listing the services it provides. If an agency adds or drops a service, it is only the record in the intersection table that will have to change, and only the one record -- a separate, trivially easy, form will do this in "jig time". Larry Linson Microsoft Office Access MVP |
Thread Tools | |
Display Modes | |
|
|