Full Version: Disappearing Fields
UtterAccess Forums > Microsoft® Access > Access Forms
PSUAlum
Two combobox fields on a form of mine disappear whenever the user leaves the record and comes back to it. They are both stored in the underlying table. Another thing they have in common is they both have Where clauses I've put in to narrow the choices based on another field. For instance, one field displays an address that corresponds to a ShipToID (from tblShipTo). Only addresses that are linked to a previously entered field (CustomerAccountID) are listed. When I go back into a record, the ShipToID combo displays the address for the first Customer Account (the Where clause basically resets).
Hope this makes sense. Any thoughts on the matter?
AQM_UK
Hi, a combo box is a control, not a field.
How you have cascading combo boxes here, so the value of one box drives what can be selected in the next. Correct?
I use queries for this and put my where clause there.
So when in the form, unless I have a wild card option on the first combo (so if null, allow all records in the next box) I do not let the user into the second combo.
On selecting the first combo, I make the second combo active and requery the bound control so that the user has the selection they require. (etc. etc)
Now I am getting to the coonfused stage.
What do you mean "dissappear" The actual control goes away? Why can the user navigate away from the record?
Might need some more info here.
Jim
HiTechCoach
In the Form's on current event, are you requerying each combo box? If not, you will need to do this. It may also fix
your issue.

Example:

Me.cboShipToID.Requery


Edited by: HiTechCoach on Wed Feb 27 12:19:14 EST 2008.
PSUAlum
The WHERE clause was created in a query. Initially, when a user selects a customer account (combo 1), the proper selections are available in the ShipToID combo (combo 2). It might be worth mentioning that I have a City, State, and Zip field that is filled in based on the ShipToID selected. Not sure if this is relevant but the code for this is:
Private Sub cboAddress_AfterUpdate()
Me.City = Me.cboAddress.Column(2)
Me.State = Me.cboAddress.Column(3)
Me.Zip = Me.cboAddress.Column(4)
End Sub
Anyway, when the order is entered initially, the cascading combo works fine. The ShipToID is stored in a tblOrders. If a user needs to view the order later, the ShipToID control is blank. If I re-select the customer account the ShipToID can once again be seen.
HiTech,
When you refer to requerying the comboboxes in the form's on current event, is this something I need to create or should it have already existed?
HiTechCoach
It is something that you have to manually add. It is not added automatically for you.

Sounds like you already have some code it in the After update event for the when you select the customer account. This will need to also be code in the On Current event.

Something like:

1) call the controls after update envent
CODE
Private Sub Form_Current()
If Not Me.NewRecord Then

   Call cboCustomer_AfterUpdate
End If


or

2) Just requery the Ship to the control
CODE
Private Sub Form_Current()
If Not Me.NewRecord Then

   Me.cboShipTo.requery
End If


Note: In the above examples, you will need to substitute the correct control names
PSUAlum
Worked perfectly. Thanks a lot for all your patience and help.
HiTechCoach
You're welcome!
Glad we could assist.
:uaruelz:
This is a "lo-fi" version of UA. To view the full version with more information, formatting and images, please click here.