Populating controls based on input in others from the reverse

[Hagridore]

Hello. In a form I'm using I have a secondary control being populated by an entry from another control. I was able to find the code I needed to do this using a combo box here in this forum.

me.txtDependent=me.cboPrimary.column(#)
(specifics changed to protect my company's paranoid view of privacy)

I had an issue where this wasn't doing what I wanted. In most cases, the code itself was showing up in the secondary control instead of any value. I found though that moving the code from the On Change event for the primary control to the On Current event for the form itself everything is working beautifully.

However, I have a different task now that I need to be able to accomplish with the same two tables but in the opposite direction. I suspect that I'm going to need to create a second set of controls and maybe even a second relationship or table to do this. To use a set of specifics that I think will get this point across, if I had a master table which includes a field for "City" and a secondary table, CityState, which contains both "City" and "State", and two controls, a combo box cboCityState to look up the specific City and a text box, txtState to display the State, putting the following code in the City combo box's On Change event populates a text box with the State when the specific City is selected, or putting it in the form's On Current event will be sure that State shows up in all the copies of a form when the form is used in a search and the City is not changed :

me.txtState=me.cboCityState.column(2)

What I need to do now is to set up a control which allows me to type in the State and have the results show all records that include that State.

I'm using this in a Filter by Form environment. I know the objections to this method and the benefits to creating a dedicated search form, but this method appears to be working well in all other ways for us for now. I have several situations within this form where I have one control showing up at the initial load of the form and at the beginning of the filtering process, allowing selection of multiple values from a long list of values in a list box, for instance, (using the On Filter event) and other controls showing up as a result of the filtering process, to display the selected criteria only, for instance (using the On Apply Filter event), so I'm aware of how to make this work if that's required. I'm thinking I may need to create a second control, something like txtStateInput, to accept the State search criteria and another, something like txtCityResults, to show the cities that are part of the resultant records. But I really don't know if this is the way I need to go or not, and I don't have a clue what the coding needs to be to make this happen.

I'd really appreciate any help or advice.

 

[Ranman256]

To do the multi-combo box
each combo box has a query for its recordsource.
Here the user picks a state, then in the next combo picks a City (in that state)

The 1st combo,(say cboStates)... User picks a state, then picks a city from the cboCity box.
The cboStates AfterUpdate event will trigger when the user picks it, and this will update the next combo.

sub cboStates_AfterUpdate()
 cboCity.requery
end sub

The cboCity query (say qsCityViaState) will reference the cboStates in the query sql
Select [city] from tZipCodes where [ST] ='" & forms!frmMain!cboStates & "'"

The City combo must be refreshed (cboCity.requery action) after user picks the state so it can deliver the resulting dataset.

If there is another combo after this say cboEmps to pick employees, then the cboEmp must be refreshed after user picks cboCity.

sub cboCity_AfterUpdate()
 cboEmps.requery
end sub

 

[Hagridore]

I think this is getting awfully close. And maybe it's closer than I think. I'm feeling a little addle-brained just now.

In the OnFilter event, I have a list box with the drop-down list of cities (lstCity) and a text box that shows up as empty (txtState).

In the ApplyFilter event, lstCity is replaced with a text box that shows only the City selected (txtCity) and txtState fills with the state connected to the city, based on the City showing in the text box. (Originally this only happened if there was a change in the value of txtCity, but I moved the code from the control's OnChange event to the form's OnCurrent event. Now it happens no matter whether txtCity has been changed or not.)

When I open the form using my splash screen/menu screen, it is opened automatically in Filter by Form mode. If I select a City to use as a criteria for the filter, all the records show up that contain that City are displayed, and the State fills appropriately. If a City is not selected as a filter criteria, then the City and State appropriate to each record show up in the same manner, in txtCity and txtState.

What I want to do is have a list box with the States (lstState) show up at OnFilter, also pulling in all the corresponding Cities in txtCity at ApplyFilter.

It also just hit me that it might be advantageous to be able to select multiple Cities and/or States, though each record will only have one City actually stored in the master table.

it doesn't seem to me that the code in your message does quite what I'm looking for. Am I missing it? Also, won't it work without the need for refresh if it's in the form's OnCurrent event instead of the AfterUpdate event?

Thanks for your help and patience!

 

[jdraw]

See if this free video helps (cascading combo)

 

[Hagridore]

A very interesting video. It looks like it's doing what my original search/filter form does. Now what I need to be able to do is to select a location and have it pull up records showing the correct region (or the equivalent).

 

[Hagridore]

I have created a database that displays my issues so I can show what I'm trying to do. In my comments/questions above I referred to region and location. In this sample database I refer to department and building.

The database opens with the Faculty form showing. The text boxes txtOffice and txtBuilding pull the room number (Office) and the Building from the tblDepartment as selected in cboDepartment and display them automatically. This form presupposes using Filter by Form to search. Clicking the New Search button opens the form in that mode and hides the button. It also hides txtBuilding and displays cboBuilding, which populates with the unique values from tblDepartment...or at least it's supposed to. What happens is that cboBuilding does show up but is not accessible.

In addition to making it accessible, I want to be able to code it to display the unique values of the field Building from tblDepartment, and I want those values to be able to use whatever value selected in cboBuilding in the Filter by Form process as a filter/search criteria.

I thought replacing the textbox-and-combo-box solution with a single combo box might work, but I found the combo box inaccessible as well. This makes me think I've done something incorrectly in the setup of this control, but I can't find what in its properties.

Here's the database. I appreciate any help I can get on this.