Web Development

Ask something
Discussion Locked

Access 2000 Combo Boxes (VBA involved)

0 helpful
3 replies

by johndjauch 1 view

Closed to new repliesThis discussion is closed. It stays readable, but it takes no new replies.
I have an Access 2000 database whose purpose is to calculate the price of a manufactured product. In one of the forms, a person would select the length of a product and then based on that length, queries (in VBA) would execute and supply values to the form?s combo boxes. This part works. The problem I have is when someone changes the length of the product after filling out the form. When the length changes, other features may changes as well. For example, if the length exceeds a certain number of feet then stronger supports need to be included which would add to the cost. When the value of the length changes, I want the form to update the values in the combo boxes if a change is needed. So far when I try to do this, the box just blanks out. However when I click the drop down arrow, the correct selection is there. Any suggestions on fixing this?

3 replies

by bob sellman

It sounds like the new length creates values for the combo box that may not match the previously selected value.

For example, let's say that for a length of 10' supports 1,2,3 are acceptable, while for 20' supports 3,4,5 are acceptable. Let's say the user first selected 10' and support 2. If the user later changes the length to 20', your programming changes the combo box list to include supports 3,4,5....but now support 2 is no longer a valid entry. Since the value contained in the field in back of the combo box is support 2, the box goes blank.

You need to add some code to the onUpdate property of the length control. To do a complete job, you basically need to determine if the (new)acceptable supports include the value already in the support combo box. If the value is not included, you should display a warning message to the user that the previously entered support can no longer be used and ask the user to confirm that the new length entered is correct.

If the user confirms that the new length is correct, then you might provide a second message requesting that the user select a new support, then set the support combo box to null and set focus to the support combo box.

I've focused on the supports, but obviously the same concept applies to any other affected values. I do think that no matter how many other features are affected, you should set all invalid controls to null (to avoid having an invalid value stored in the record, even though it's not displayedon your form) and warn the user that changes are needed because of the length change.

by johndjauch

I agree with your logic. I have already set up procedures that are called to check the new values (Length). These procedures should adjust the value of the combo box. In one situation a CASE statement was written to force a new value (iecmboSupport.value=?C?).
One additional piece of information, the combo box has 3 columns. The first 2 have a column width of 0? (hides the information from view) and the bound value is column is 1. The first column holds the values of ?A? ?B? ?C?, etc. The procedure is changing the value in the bound column. My gut feeling is my problem is lying somewhere around this ?area?.

This discussion is closed to new replies.