Link to home
Start Free TrialLog in
Avatar of SteveL13
SteveL13Flag for United States of America

asked on

Combobox issue

I don't think I've run into this before.  I have a form that has a combobox.  The combobox row source is a select query that gets its records from a table.  The table has a new yes/no field named "Inactive".  If a record has the yes/no field marked "True" then the record does not show up in the combo-box.  This is the way it should work for new records on the form.

But the problem is, for any existing records, when the form displays a record, the combo-box displays nothing.  I would like it to display the value if it is not a new record even if the combobox record is marked "True".

Ho do I get around this issue?
Avatar of crystal (strive4peace) - Microsoft MVP, Access
crystal (strive4peace) - Microsoft MVP, Access

There is only one RowSource for the combobox so the list is the same for all records. Perhaps instead of filtering out the inactive records, you can sort them to be at the bottom and show Inactive in another column. On the BeforeUpdate event of that control, test the Inactive column and if it is True, then give the user a message that they must pick an active value and Cancel the BeforeUpdate event to give them a chance to change their selection.

Optionally, you can modify the RowSource of the combobox depending on what you want for each record.  I prefer the above method.
Avatar of SteveL13

ASKER

I just had an idea and would like to know what you think.  In addition to the combobox I have created a text box with a DLookup control source to get the value of the combobox when the record was created.  I have the text box and the combo box on top of each other.

Then I have this code in the oncurrent event of the form...

    If Me.NewRecord = False Then
        Me.txtDeal.Visible = True
        Me.cboDeal.Visible = False
    Else
        Me.txtDeal.Visible = False
        Me.cboDeal.Visible = True
    End If

Open in new window

SOLUTION
Avatar of Jim Dettman (EE MVE)
Jim Dettman (EE MVE)
Flag of United States of America image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
ASKER CERTIFIED SOLUTION
Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial