Combo Box Problems

[mjb]

I have a combo box in a subform. When the user selects an account and then
goes to enter the contract for the account, they need to select the
associated contact. When they select the contact, the name appears just fine
and the information is stored in the underlying table, but the next time they
open the database and scroll through the account records, the selected
contacts do not show up in the field. The actual name does not appear until
you give the field focus. If you leave that field selected and scroll
through the records, the selected names appear. If you move focus to another
field, the names don't appear when changing records (on the main form or the
subform).

Any suggestions.

 

[Sprinks]

Please post:
- the RowSource & AfterUpdate event procedure of the 1st combo box
- the form's OnCurrent event procedure
- the RowSource of the 2nd combo box

Sprinks

 

[mjb]

On the main form (frmHTRContract)
combo box is cboAccount
row source is Account Query

no AfterUpdate event procedure

On Change event for cboAccount

I have two public variables
Public CurrentContract As Long
Public CurrentContact As String

cboAccount On Change Event
CurrentContract = ContractNumber.value
lblCurrentContract.Caption = CurrentContract

Private Sub-Form_Current()
If ContractNumber.Value <> "" Then
CurrentContract = Contract Number.Value
lblCurrentContract.Caption = CurentContract
Else
End if
End Sub

On the sub-form (frmContractsDetail)
cboContractContact is the combo box

cbo ContractContact Got Focus Event
cboContractContact.Requery

cboContractContact
Row Source = Main Contract and Contact Query
Control Source = ContractContact from the Contract Detail Table

 

[Sprinks]

MJB,

I believe you've stumbled onto the Disappearing Data Syndrome that occurs in
a continuous subform when the value stored in the field Bound to a combo box
cannot be displayed because it is not in the current value of RowSource.

The RowSource can have only one value for the entire subform. I'm assuming
that you are filtering it in your Main Contract and Contact Query so that you
show the subset of Contacts associated with this contract or client. If you
are in a control other than the cboContractContact control, and you scroll
through the records, the Requery action will not fire to reset the RowSource.
Access can only display records in the current RowSource, so if the value
currently stored in the underlying field isn't in the list, the data
disappears.

The only way around this that I'm aware of is to place a textbox Bound to
the ContractContact in lieu of your combo box, and use its OnGotFocus event
to pop up a form with a single combo box, filtered appropriately. Once the
selection is made, write the value to the textbox.

Hope that helps.
Sprinks