Combo Box

In MS Access 2013, I have a combo box on a form. It pulls two values, Name and ID (from Client table). It stores ID but displays Name. The query behind it is to only shows Names that have a status of Active. However, when a Name is set to inactive, it no longer displays the name in the field. The field source is set to ID (from the Claim table). I would think the field source would read from the claim table record, not from the row source on the combo box. I remember that there are some odd things that can happen with combo boxes. Any ideas how I maintain my unique row source, but display the name if the Client is inactive?
dgravittAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Dale FyeCommented:
YOu might try a query that looks something like:

SELECT ID, Name
FROM tbl_Clients
WHERE Status='Active'
OR [ID] = Forms!yourFormName.ClientID

You would have to requery this combo box in the Form_Current event, but that should do it for you.
0
dgravittAuthor Commented:
If I write code to check the value of the field on the form, it shows the correct ClientID, it just doesn't display anything in the field.
0
Dale FyeCommented:
Actually, that query I gave you might not work on a new record.  Try:

SELECT ID, Name
FROM tbl_Clients
WHERE Status='Active'
OR [ID] = NZ(Forms!yourFormName.ClientID, 0)

Then, in your Form_Current event, make sure you requery that combo box.

Private Sub Form_Current

    me.cbo_ComboName.Requery

End Sub
0
The Ultimate Tool Kit for Technolgy Solution Provi

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy for valuable how-to assets including sample agreements, checklists, flowcharts, and more!

PatHartmanCommented:
Comboboxes have two bound objects - The RowSource which provides the list of items to display and the ControlSource which specifies which field the combo.value will be saved to in the form's RecordSource.  If the RowSource query only selects Active IDs, the list will NEVER contain the inactive names and so the combobox will always appear blank.  But, it is only the display part that is blank, the ID value is still stored.  It is that the stored ID value has no corresponding value in the RowSource so the visible part of the combo has nothing to show.

To solve the problem, you need to include the inactive items.  Then in the BeforeUpdate event of the combo, you have to check the active flag (include it in the RowSource query) and if it is not active, do not allow the item to be selected.
If me.cboClientName.Column(2) = "Active" Then
Else
    Msgbox "You cannot select an inactive client.",vbOKOnly
    Cancel = True
    Me.cboClientName.Undo
    Exit Sub
End If

Open in new window


The RowSource of the combo is a zero based array so .Column(0) is the .value property and contains the ID field, .Column(1) contains the display value, .Column(2) should contain the ActiveFlg.  Make sure you modify the column count in the properties dialog or you wont' be able to reference this new column.

In combos where I include inactive items, I alter the sort so that the first sort field is the ActiveFlg (-1 for true and 0 for False) and so the Active items always sort to the top of the list.  I also show the active flag so it is easy for the user to see that he is attempting to select an inactive option.  Usually, I mark the false values as "Inactive" so only the Inactive ones actually have a value that shows in the list.  This reduces "clutter" and confusion.
0
dgravittAuthor Commented:
Thanks for the comments. After reading them, this is what I would like to. On form open, if the ClientID is null, use the row source query that only shows the Active Clients. If the ClientID in not null, remove the Active clause and show all Clients. Although, I can't find the correct VBA statement to change the combo field row source on the fly. Ideas?
0
Dale FyeCommented:
then your best bet is to use the Form_Current event, something like:

Private Sub Form_Current

    dim strSQL as string

    strSQL = "SELECT ID, Name FROM tbl_Clients WHERE [Active] = -1"
    if me.newRecord  = False then
        strSQL = strSQL & " OR [ID] = " & me.ClientID
    endif

End Sub
0
PatHartmanCommented:
I think this will do it.
Private Sub Form_Current
Dim strSQL as String
Dim strOrder as String
     strSQL = "Select ClientID, ClientName, ActiveFlg From tblClients 
    strOrder = " Order By ActiveFlg, ClientName;"
    If Me.NewRecord = True Then
        Me.cboClientID.RowSource = strSQL & " WHERE ActiveFlg = True " & strOrder
    Else
        Me.cboClientID.RowSource = strSQL & strOrder
    End If
End Sub

Open in new window

I changed some of the column names because
- "Name" is a reserved word and will cause you nothing but trouble in VBA.  
- The primary key of a table should have a descriptive name rather than simply "ID".  Once you start making a real schema where you define relatioships, it will be helpful if the foreign key name matches the primary key name to which it refers.  That way, new people (and yourself) won't have trouble simply looking at a table and identifying the foreign keys and referenced tables.
0
dgravittAuthor Commented:
Thanks for the suggestions. I'll try them this afternoon once I get back to the house.
0
dgravittAuthor Commented:
Pat, when I use your code and try to save it, I get unrecognized database format error. I am using Access 2013
0
dgravittAuthor Commented:
But, it is a mdb file not a accdb.
0
PatHartmanCommented:
Put a stop in the code so you can examine the contents of strSQL before it is applied to the RowSource.  If you don't see the error, post the SQL string here.
0
dgravittAuthor Commented:
Sorry for the delay in responding. I've got some issues on my machine causing the above error. I uploaded it to my client, I'm getting two errors, one is on the cbo (says it can't find that field), if I remove that, then I get an error that RowSource is not a valid property. I'm I possibly missing something in my References?
0
dgravittAuthor Commented:
I realize the cbo is a naming convention, so disregard that. But, I don't see rowsource as a property to change.
0
Dale FyeCommented:
RowSource is on the Data tab of the properties dialog.

If there is no RowSource, then the control is not a listbox or a combo box.
0
dgravittAuthor Commented:
The issue is, when in VBA, and I start typing Me.ClientId. , it starts a list of available actions or methods. RowSource is not in that list. On the data tab, it is set to table/query.
0
Dale FyeCommented:
In the data tab, you are looking at the RowSourceType.  The line above that should read RowSource.  

I'm not sure why the Intellisense is not displaying RowSource as an option.  This may be confusion within Access because you have not given the control a different name than the data source.  The field [ClientID] does not have a RowSource property, but the control [ClientID] does.  This is one of the reasons that professional Access developers always use a naming convention for their controls.  Change the control name to cbo_ClientID and try it again.
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
dgravittAuthor Commented:
This is a database I inherited. Man, I hope changing the name doesn't work. It will break so many things. But, I will try it.
0
dgravittAuthor Commented:
OK, I feel really dumb. I went to change the field name, and it's named Client rather than ClientID. No wonder it never worked. When I use that, it does give me the option for RowSource. I'm going to split the points with you and Pat, you both contributed. I appreciate all the comments. I've been working with Access since 1998, it still amazes me what I don't know, and what is so easily overlooked.
0
Dale FyeCommented:
glad we could help.
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Access

From novice to tech pro — start learning today.