I have combo box's and I want the combo boxes to automatically fill in the box below them with the Gage Description. I got it to work but when I go to the next field the same information from the previous field is in the box. When I click the cbogage box it changes to the correct description for that particular gage but when I go back to the previous field it changed to the one I just changed. Sorry if this is confusing. As you can see below I inserted the Design view, Code and the live view. As you can see on the live view form the gage numbers are different but the Descriptions are the same.. I am sure it is some simple code but I cannot think of it.
Form AutoFill (1 Viewer)
- Thread starter Barley
- Start date
- Local time
- Today, 14:47
- Joined
- Oct 29, 2018
- Messages
- 21,476
Hi. Welcome to AWF!
I don't see the problem. Your screenshots show all the boxes have different values. You can also avoid using code to do the same thing by simply using an expression in the Control Source of each textbox. For example:
I don't see the problem. Your screenshots show all the boxes have different values. You can also avoid using code to do the same thing by simply using an expression in the Control Source of each textbox. For example:
Code:
=[cboGage].[Column](2)Screen shot 3 and 4 Gage numbers are different but the Descriptions (under the gage number) are the same (from being changed in the previous record) which should also be different
=[cboGage].[Column](2) Worked perfectly thank you for the compliment and the solution.
- Local time
- Today, 14:47
- Joined
- Oct 29, 2018
- Messages
- 21,476
Maybe try what I suggested though, in case it's the effect of using your code.
- Local time
- Today, 14:47
- Joined
- Oct 29, 2018
- Messages
- 21,476
Oops, I replied to your other post before seeing this one. Glad to hear you got it fixed. Good luck with your project.
If we need a gage 7 I would just enter it in "notes" box. We don't usually use 7 gages on a part.
- Local time
- Today, 17:47
- Joined
- Feb 19, 2002
- Messages
- 43,301
Let me see if I can explain the problem with your original solution. You have six unbound controls on the form which may or may not hold a value. Usually, we see the problem you are having on continuous forms where the problem is more obvious. We would only see it on a single record form if the schema violated first normal form which yours does as was alluded to by Gasman. So, although the suggestion made by @theDBguy gets around the issue, it still leaves you with an incorrect table schema and potential future problems if your product line changes to include products with more gages.
Anyway, each unbound control is a property of the form because it is not bound to the underlying data source. As such, each control can only hold a single value at one time. What you are seeing is "bleed" through. Record 1 has 5 gages, you scroll to record 2 which has 3 gages. You see the first three gages but you also see gages 4 and 5 from the previous record.
Although I prefer @theDBguy 's solution, a different way to solve the problem would be to add code to the form's Current event which clears all 6 controls. Then, if this is an existing record, repopulates the unbound controls for the gages that are populated.
The best solution, however, is to always normalize your schema. In the long run, you will have fewer "challenges" going forward and will NOT require application modification if you use the proper many-side table structure for your gages. Show them in a subform. with a combo and an unbound control using the technique @theDBguy suggested. The subform will show 0 - n rows for each device depending on how many gages are defined. There will be no bleed through because only the correct number of records ever show.
Anyway, each unbound control is a property of the form because it is not bound to the underlying data source. As such, each control can only hold a single value at one time. What you are seeing is "bleed" through. Record 1 has 5 gages, you scroll to record 2 which has 3 gages. You see the first three gages but you also see gages 4 and 5 from the previous record.
Although I prefer @theDBguy 's solution, a different way to solve the problem would be to add code to the form's Current event which clears all 6 controls. Then, if this is an existing record, repopulates the unbound controls for the gages that are populated.
The best solution, however, is to always normalize your schema. In the long run, you will have fewer "challenges" going forward and will NOT require application modification if you use the proper many-side table structure for your gages. Show them in a subform. with a combo and an unbound control using the technique @theDBguy suggested. The subform will show 0 - n rows for each device depending on how many gages are defined. There will be no bleed through because only the correct number of records ever show.
