Weird behavior on form, fields go blank when clicking on row (1 Viewer)

[Sh8dyDan]

<SOLVED - This is an issue with using Continuous form or datasheet view forms. Those form modes are really ONE set of controls repeated multiple times. So when you change one control it affects all rows.

For example: If you have cascading combos with your Car makes and Models. You chose Ford in one combo and Edge in the model, then you go to the next row and choose Dodge, this changes the Rowsource of the Model combo so it shows only Dodge models. So the other row goes blank.>


I have a split form, record source is a query. The form opens with missing fields as pictured in the first picture.
If I click on a row that has "Widget Production" as the "Location Type" fields go blank that have "Office" as the "Location Type" and vice versa.
I recreated the form with a subform in the datasheet view and have the same behavior.
I'm at a loss for how to fix or work around this behavior.

SELECT IIf(IsNull([LastName]),IIf(IsNull([FirstName]),[Company],[FirstName]),IIf(IsNull([FirstName]),[LastName],[FirstName] & " " & [LastName])) AS [Contact Name], IIf(IsNull([LastName]),IIf(IsNull([FirstName]),[Company],[FirstName]),IIf(IsNull([FirstName]),[LastName],[LastName] & ", " & [FirstName])) AS [File As], Contact_tbl.*, Area_tbl.Area, ContactType_tbl.ContactType, Position_tbl.Position, Company_tbl.Company, Country_tbl.Country, Organization_tbl.Organization, LocationType_tbl.LocationType, Location_tbl.Location
FROM LocationType_tbl RIGHT JOIN (Location_tbl RIGHT JOIN (Organization_tbl RIGHT JOIN (Country_tbl RIGHT JOIN (Position_tbl RIGHT JOIN (ContactType_tbl RIGHT JOIN (Area_tbl RIGHT JOIN (Company_tbl RIGHT JOIN Contact_tbl ON Company_tbl.CompanyID = Contact_tbl.CompanyID) ON Area_tbl.AreaID = Contact_tbl.AreaID) ON ContactType_tbl.ContactTypeID = Contact_tbl.ContactTypeID) ON Position_tbl.PositionID = Contact_tbl.PositionID) ON Country_tbl.CountryID = Contact_tbl.CountryID) ON Organization_tbl.OrganizationID = Contact_tbl.OrganizationID) ON Location_tbl.LocationID = Contact_tbl.LocationID) ON LocationType_tbl.LocationTypeID = Contact_tbl.LocationTypeID
ORDER BY IIf(IsNull([LastName]),IIf(IsNull([FirstName]),[Company],[FirstName]),IIf(IsNull([FirstName]),[LastName],[FirstName] & " " & [LastName])), IIf(IsNull([LastName]),IIf(IsNull([FirstName]),[Company],[FirstName]),IIf(IsNull([FirstName]),[LastName],[LastName] & ", " & [FirstName]));

 

[theDBguy]

That sounds like you have a "cascading combo" effect, which is expected on a continuous or datasheet form.

 

[Sh8dyDan]

That sounds like you have a "cascading combo" effect, which is expected on a continuous or datasheet form.

I do! Why or how is this expected behavior? Can share some insight?

 

[theDBguy]

I do! Why or how is this expected behavior? Can share some insight?

Well, imagine you have a single view form where you can only see one record at a time. So, when you select something from the first combobox, you filter the choices from the second combobox, right? As a single view, this is fine, because the second combobox will naturally display what's applicable to the first combobox. But, when you use a continuous form or datasheet, you are viewing multiple records at the same time. But, when you filter the second combobox this time, it will be using the value of the first combobox of the current record. So, when the other records don't match the value of the first combobox of the current record, then the values in the second combobox for those records will be filtered out and therefore won't show up anymore. This is the issue with using continuous forms. When you use code, you only have access to a single value.

 

[Sh8dyDan]

Well, imagine you have a single view form where you can only see one record at a time. So, when you select something from the first combobox, you filter the choices from the second combobox, right? As a single view, this is fine, because the second combobox will naturally display what's applicable to the first combobox. But, when you use a continuous form or datasheet, you are viewing multiple records at the same time. But, when you filter the second combobox this time, it will be using the value of the first combobox of the current record. So, when the other records don't match the value of the first combobox of the current record, then the values in the second combobox for those records will be filtered out and therefore won't show up anymore. This is the issue with using continuous forms. When you use code, you only have access to a single value.

Thank you for explaining, makes sense.

 

[Sh8dyDan]

Found this on answers.microsoft.com

Scottgem
MVP | Volunteer Moderator | Article Author
Replied on May 20, 2018

This is an issue with using Continuous form or datasheet view forms. Those form modes are really ONE set of controls repeated multiple times. So when you change one control it affects all rows.

For example: If you have cascading combos with your Car makes and Models. You chose Ford in one combo and Edge in the model, then you go to the next row and choose Dodge, this changes the Rowsource of the Model combo so it shows only Dodge models. So the other row goes blank.

The way around this is to use a hybrid control. You would use a textbox to display the value chosen for that record, but activate the combo when you need to make a selection.

 

[GPGeorge]

I do! Why or how is this expected behavior? Can share some insight?

I have a sample demo accdb on my website which illustrates this to some extent. The purpose of the demo is actually to illustrate a potential solution. It's not the same solution Scott offers, which is layering a textbox on top of the combo. It also can be effective.

 

[Sh8dyDan]

I have a sample demo accdb on my website which illustrates this to some extent. The purpose of the demo is actually to illustrate a potential solution. It's not the same solution Scott offers, which is layering a textbox on top of the combo. It also can be effective.

I downloaded your demo file and have been looking at it. I am not fully understanding what's going on. Could you elaborate a bit on the theory of this approach and how I could implement it?

 

[CJ_London]

This is an example of cascading combos in a continuous form

enabling cascading comboboxes in datasheet or continuous form

Having been asked about this recently, I reinvented some code I developed to work with datasheets and which also works with continuous and single forms. The attachment was developed in 2010 but saved as 2003. The db demonstrated two techniques for using cascading comboboxes. Technique 1 is...

www.access-programmers.co.uk

 

[Sh8dyDan]

This is an example of cascading combos in a continuous form

enabling cascading comboboxes in datasheet or continuous form

Having been asked about this recently, I reinvented some code I developed to work with datasheets and which also works with continuous and single forms. The attachment was developed in 2010 but saved as 2003. The db demonstrated two techniques for using cascading comboboxes. Technique 1 is...

www.access-programmers.co.uk

Thanks, I'll take a look at it.

 

[GPGeorge]

I downloaded your demo file and have been looking at it. I am not fully understanding what's going on. Could you elaborate a bit on the theory of this approach and how I could implement it?

Boy, that would take a bit of explanation, but in a nutshell, it's based on the idea that cascading comboboxes in datasheet and continuous forms exhibit the problem identified in your OP and explained by theDBGuy.

In order to avoid that, there are two approaches. One is to use an overlaid text box to "hide" the blank combo boxes and display a read - only value that "should" be there.

The other is the method I adopted from another developer, Armen Stein, of J Street Tech. Rather than filter out non-appropriate values from the downstream combo box, you display all values, but those which are not appropriate are flagged and sort to the bottom of the list. Users could still use them, of course, but they would do so knowingly and illogically in many cases. In other cases, such as obsolete values, retroactive updates could be acceptable. This example shows the former scenario. Users could select a city from the wrong state, although it would be illogical.