Solved Combo Box Row Source Issue (1 Viewer)

Weekleyba

Registered User.
Local time
Today, 04:59
Joined
Oct 10, 2013
Messages
586
I have a combo box in a subform (cboContractNumber) that I want to be able to choose all the Contracts that are not closed.
i.e. CloseOut = False. See below.


1655089439214.png


The row source is Q_ContractCombochoose1 as shown below.

1655089635900.png


The issue I have is, when I create the a new record in the cboContractNumber, by dropping down the list, none of the CloseOut fields show up. This is good.

But when I choose the drop down for a cboContractNumber that already has a contract number in it, it shows all the contracts, both open and closed.

Any idea why it does this and how to correct it to show only those contract numbers that have CloseOut false?
 

arnelgp

..forever waiting... waiting for jellybean!
Local time
Today, 17:59
Joined
May 7, 2009
Messages
19,175
maybe Requery the combobox when it GotFocus.
 

The_Doc_Man

Immoderate Moderator
Staff member
Local time
Today, 04:59
Joined
Feb 28, 2001
Messages
27,001
Since you have a named query for the combo source, can you open that query directly in datasheet view to see if it shows you any records where CloseOut happens to be TRUE? This would demonstrate whether there is something wrong with the query. Also, can you convert the query to SQL View and show us the SQL for that combo's query? Should be easy since again, it is a named query.
 

Weekleyba

Registered User.
Local time
Today, 04:59
Joined
Oct 10, 2013
Messages
586
I tried requerying the combobox on GotFocus but it did not help. Still give all data where CloseOut is TRUE and FALSE. Again, only for the combo that has something in it already. For a new record in the combo, it works just fine, only showing CloseOut with FALSE.

Opening the query directly does not show any CloseOut of TRUE.

Here's the SQL for the Q_Contractcombchoose1.

Code:
SELECT DISTINCT T_Contract.ContractID, T_Contract.ContractNumber, T_ContractInfo.TaskOrderNum, T_Contract.CloseOut
FROM T_Contract LEFT JOIN T_ContractInfo ON T_Contract.ContractID = T_ContractInfo.ContractID
WHERE (((T_Contract.CloseOut)=False))
ORDER BY T_Contract.ContractNumber DESC , T_ContractInfo.TaskOrderNum DESC;

1655158250605.png
 

Art4481

New member
Local time
Today, 02:59
Joined
Jun 13, 2022
Messages
1
In the combo box properties, have you tried “Show only row source values = Yes“ ?
 

CarlettoFed

Member
Local time
Today, 10:59
Joined
Jun 10, 2020
Messages
119
Attach a sample file so you can see what's going on and make it easier for you to help.
 

Weekleyba

Registered User.
Local time
Today, 04:59
Joined
Oct 10, 2013
Messages
586
In the combo box properties, have you tried “Show only row source values = Yes“ ?
Tried that this morning. No difference. Still shows the CloseOut TRUE and FALSE records.
 

MajP

You've got your good things, and you've got mine.
Local time
Today, 05:59
Joined
May 21, 2018
Messages
8,463
Attach a sample file so you can see what's going on and make it easier for you to help.
You attach a sample file so we can see what's going on and make it easier for us to help you.
 

Weekleyba

Registered User.
Local time
Today, 04:59
Joined
Oct 10, 2013
Messages
586
You attach a sample file so we can see what's going on and make it easier for us to help you.
Ha. Read that wrong. I did find the problem however, and it's totally my fault here.
I have an On Current event on the SF_Contract that has the cboContractNumber in it.

Code:
Private Sub Form_Current()
'Q_ContractCombochoose1 only shows records that are not closed out and active.
'Q_ContractCombochoose shows all records (contracts).

If Me.NewRecord Then
   Me.cboContractNumber.RowSource = "Q_ContractCombchoose1"
Else: Me.cboContractNumber.RowSource = "Q_ContractCombchoose"
End If

End Sub

The Q_ContractCombchoose does not have criteria FALSE for the CloseOut and thus why I'm seeing all of them.

What was throwing me was the Row Source was showing Q_ContractCombchoose1, so I was convinced that this is what it was looking at all the time. Sorry for the goose chase here, but thanks for all the help.

SECOND ISSUE.
When I choose a new second record in the cboContractNumber, the first record goes blank. If I click on the first record, it reappears.
How do I keep it from going blank in the first place?

Example:

1655211175837.png


1655211256818.png
1655211290654.png


1655211317073.png
 

Mike Krailo

Well-known member
Local time
Today, 05:59
Joined
Mar 28, 2020
Messages
1,030
Step through the code and see what line causes the upper combo to go blank. Post the code in context where it blanks out if you cannot figure it out.
 

CarlettoFed

Member
Local time
Today, 10:59
Joined
Jun 10, 2020
Messages
119
You attach a sample file so we can see what's going on and make it easier for us to help you.
Furthermore, it seems, from what can be seen from the structure of the two tables T_Contract and T_ContractInfo, that there are problems in their structuring.
 

CJ_London

Super Moderator
Staff member
Local time
Today, 09:59
Joined
Feb 19, 2013
Messages
16,553
Almost certainly it will be because the rowsource has been changed from showing everything to only showing those not closed out. Rowsource applies to all rows
 

Pat Hartman

Super Moderator
Staff member
Local time
Today, 05:59
Joined
Feb 19, 2002
Messages
42,981
I'm going to make a different suggestion that requires a small amount of VBA.

Rather than hide records that are closed, select all records. I'm confused about how you are determinizing active vs inactive so rather than using your column names, I'll use mine.

Select ContractID, ContractNumber, IIf(ActiveFlg = True, Null, "Inactive") as Inactive
From YourTable
order by ActiveFlg, ContractNumber;

This will sort the active items (assuming this is a Y/N data type to the top. It will also display the word "inactive" for those not active. When you set the properties of the combo, hide the key as usual but select the show column headers property so the Inactive column will show and be named.

Then in the BeforeUpdate event of the combo that selects the ContractNumber, add the following:
Code:
If Me.cboContractID.Column(2) = "Inactive" Then
    Msgbox "Inactive contracts may not be selected.", vbOKOnly
    Cancel = True
    Me.cboContractID.Undo
    Exit Sub
End If

The combo's RowSource always includes all values so they won't go blank on you when the selected item is inactive. Your code prevents any inactive item from being selected for a new record or to change an existing record.
 

Pat Hartman

Super Moderator
Staff member
Local time
Today, 05:59
Joined
Feb 19, 2002
Messages
42,981
@Weekleyba Thanks for the like but if my solution solved your problem, please use your words. It is ever so much more helpful to people who find this thread later to know for certain which solution worked.
 

Weekleyba

Registered User.
Local time
Today, 04:59
Joined
Oct 10, 2013
Messages
586
@Weekleyba Thanks for the like but if my solution solved your problem, please use your words. It is ever so much more helpful to people who find this thread later to know for certain which solution worked.
Oh it did and I'm working on it. I'll post shortly. Thanks Pat.
 

Weekleyba

Registered User.
Local time
Today, 04:59
Joined
Oct 10, 2013
Messages
586
Thanks Pat for the help.
I changed a few things only for what I wanted to see, but your solution is spot on!

The combo box, cboContractNumber, now only has one query as the row source. Below is the SQL for it.

SELECT DISTINCT T_Contract.ContractID, T_Contract.CloseOut, T_Contract.ContractNumber, T_ContractInfo.TaskOrderNum, IIf([T_Contract].[CloseOut]=True,"Inactive") AS Inactive
FROM T_Contract INNER JOIN T_ContractInfo ON T_Contract.ContractID = T_ContractInfo.ContractID
ORDER BY T_Contract.CloseOut DESC , T_Contract.ContractNumber;

The BeforeUpdate code for the cboContractNumber is:

Code:
Private Sub cboContractNumber_BeforeUpdate(Cancel As Integer)

If Me.cboContractNumber.Column(4) = "Inactive" Then
    MsgBox "Inactive contracts may not be selected.", vbOKOnly
    Cancel = True
    Me.cboContractNumber.Undo
    Exit Sub
End If

End Sub

The end result is fantastic!

1655245320875.png
1655245465336.png
 

Users who are viewing this thread

Top Bottom