Combo box in VBA (1 Viewer)

slharman1

Member
Local time
Today, 03:01
Joined
Mar 8, 2021
Messages
453
I have a quote form that looks up a product we build to get all the pertinent info from such as price and description.
The combo box then takes the ID for the product and saves it to the quote table, the drop down list is based on the products table and only shows active products so someone can't put in a product that is inactive. The problem is this: my older quotes are still in process and I need the inactive product to show if it was put on the quote before the product became inactive. Currently the product id is blank because the item is inactive. I need it to show the inactive product but not let someone use it when adding an item to a quote.
Thanks
 

theDBguy

I’m here to help
Staff member
Local time
Today, 01:01
Joined
Oct 29, 2018
Messages
18,786
Hi. You have two options:

1. Overlay a bound Textbox on top of the Combobox to display the Product info, or
2. Adjust your Combo Row Source, so that it display all products, but sort the inactive ones to the bottom (mark them with asterisks) and add code to prevent (or reject) the user from selecting the inactive products.
 

CJ_London

Super Moderator
Staff member
Local time
Today, 09:01
Joined
Feb 19, 2013
Messages
14,213
or 3. exclude the where part of your rowsource and in the change event put code along the lines of

if instr(cbo.rowsource," WHERE Active")=0 then cbo.rowsource=replace(cbo.rowsource,";", WHERE Active")

and in the exit event put

cbo.rowsource=replace(cbo.rowsource," WHERE Active",";")

this assumes your rowsource is sql and active is a boolean.

If it is a query then

if instr(cbo.rowsource,"] WHERE Active")=0 then cbo.rowsource="SELECT * FROM [" & cbo.rowsource,"] WHERE Active")

and in the exit event put

cbo.rowsource=replace(replace(cbo.rowsource,"] WHERE Active",""),"SELECT * FROM [","")

Your query will need to include the Active field
 

Pat Hartman

Super Moderator
Staff member
Local time
Today, 04:01
Joined
Feb 19, 2002
Messages
35,914
I include the inactive flag in the rowSource and sort in it first so the inactive values drop to the bottom and out of the way. Make the column widths -- 0;2;1 so the word "inactive" shows and turn on the show headings if you want.
Code:
Select ProductID, ProductName, IIf(ActiveFlg = true, null, "Inactive") As Inactive
From YourTable
Order by IIf(ActiveFlg = true, null, "Inactive") , ProductName

You will need code in the combo's beforeUpdate event to prevent the user from choosing an inactive value.
Code:
If Me.cboProduct.Column(2) = "Inactive" Then
      Msgbox "Inactive items may not be selected"
    Cancel = True
    Me.cboProduct.Undo
    Exit Sub
End If
 

slharman1

Member
Local time
Today, 03:01
Joined
Mar 8, 2021
Messages
453
I include the inactive flag in the rowSource and sort in it first so the inactive values drop to the bottom and out of the way. Make the column widths -- 0;2;1 so the word "inactive" shows and turn on the show headings if you want.
Code:
Select ProductID, ProductName, IIf(ActiveFlg = true, null, "Inactive") As Inactive
From YourTable
Order by IIf(ActiveFlg = true, null, "Inactive") , ProductName

You will need code in the combo's beforeUpdate event to prevent the user from choosing an inactive value.
Code:
If Me.cboProduct.Column(2) = "Inactive" Then
      Msgbox "Inactive items may not be selected"
    Cancel = True
    Me.cboProduct.Undo
    Exit Sub
End If
My column #2 is my model number, #1 is the ID and width is set to 0, so #2 is what shows on the form, how can I sort on the Active Flag first and still keep the model # as what shows on the form once a record is selected.
Thanks
 

slharman1

Member
Local time
Today, 03:01
Joined
Mar 8, 2021
Messages
453
I include the inactive flag in the rowSource and sort in it first so the inactive values drop to the bottom and out of the way. Make the column widths -- 0;2;1 so the word "inactive" shows and turn on the show headings if you want.
Code:
Select ProductID, ProductName, IIf(ActiveFlg = true, null, "Inactive") As Inactive
From YourTable
Order by IIf(ActiveFlg = true, null, "Inactive") , ProductName

You will need code in the combo's beforeUpdate event to prevent the user from choosing an inactive value.
Code:
If Me.cboProduct.Column(2) = "Inactive" Then
      Msgbox "Inactive items may not be selected"
    Cancel = True
    Me.cboProduct.Undo
    Exit Sub
End If
Code is not working Whether "No" is in quotes or not in quotes - No
Code:
Private Sub cboEstID_BeforeUpdate(Cancel As Integer)

    If Me.cboEstID.Column(3) = No Then
      MsgBox "Inactive items may not be selected"
    Cancel = True
    Me.cboEstID.Undo
    Exit Sub
End If

End Sub
 
Last edited:

Pat Hartman

Super Moderator
Staff member
Local time
Today, 04:01
Joined
Feb 19, 2002
Messages
35,914
The columns collection of the RowSource for the combo is a ZERO based array. Therefore, the first column is referenced as .Column(0) and the third is .Column(2)
 

slharman1

Member
Local time
Today, 03:01
Joined
Mar 8, 2021
Messages
453
The columns collection of the RowSource for the combo is a ZERO based array. Therefore, the first column is referenced as .Column(0) and the third is .Column(2)
Thanks, But I changed it to two and still doesn't work, I also have the following code for the cbo - could that be the problem?
Code:
Private Sub cboEstID_AfterUpdate()

    newestimateid = cboEstID

    Dim dblPrice As Currency
    Dim strDescription As String
    Dim strModelNo As String
    'Dim accesstotal


    ' accesstotal = DLookup("SumofExPrice", "qryAccTotalByQuoteDetail", "[QuoteDetailID]= [forms]![frmQuotes]![SfrmQuoteDetails]![QuoteDetailID]")
    strModelNo = DLookup("ModelNumber", "qryEstimateTotalPriceAll", "[EstimateID]= [cboEstID]")
    dblPrice = DLookup("Price", "qryEstimateTotalPriceAll", "[EstimateID]= [cboEstID]")
    strDescription = DLookup("Description", "qryEstimateTotalPriceAll", "[EstimateID]= [cboEstID]")

    If currentestimateid = newestimateid Then

    Else
        Me.Description.Value = strDescription
        Me.Price.Value = dblPrice
        Me.ModelNo = strModelNo
        'Me.AccessPrice.Value = accesstotal

    End If

End Sub


Private Sub cboEstID_BeforeUpdate(Cancel As Integer)

    If Me.cboEstID.Column(2) = No Then
      MsgBox "Inactive items may not be selected"
    Cancel = True
    Me.cboEstID.Undo
    Exit Sub
End If

End Sub


Private Sub cboEstID_Click()

    currentestimateid = cboEstID

End Sub
 

slharman1

Member
Local time
Today, 03:01
Joined
Mar 8, 2021
Messages
453
The columns collection of the RowSource for the combo is a ZERO based array. Therefore, the first column is referenced as .Column(0) and the third is .Column(2)
Also, my RowSource is a query not SQL - does that matter?
 

Pat Hartman

Super Moderator
Staff member
Local time
Today, 04:01
Joined
Feb 19, 2002
Messages
35,914
If the combo is a yn data type, use False rather than No.
 

Pat Hartman

Super Moderator
Staff member
Local time
Today, 04:01
Joined
Feb 19, 2002
Messages
35,914
SQL is a query. A querydef is a saved query. They are equivalent.
 

Pat Hartman

Super Moderator
Staff member
Local time
Today, 04:01
Joined
Feb 19, 2002
Messages
35,914
Sorry, looked again, if the query is returning "yes" or "no" then enclose no in quote. If it is returning the flag, then use false
 

Pat Hartman

Super Moderator
Staff member
Local time
Today, 04:01
Joined
Feb 19, 2002
Messages
35,914
Maybe you are returning both in the query. Post the RowSource query and I'll help you figure it out.
 

oleronesoftwares

Passionate Learner
Local time
Today, 01:01
Joined
Sep 22, 2014
Messages
1,113
Code is not working Whether "No" is in quotes or not in quotes - No
Code:
Private Sub cboEstID_BeforeUpdate(Cancel As Integer)

    If Me.cboEstID.Column(3) = No Then
      MsgBox "Inactive items may not be selected"
    Cancel = True
    Me.cboEstID.Undo
    Exit Sub
End If

End Sub
it should be
If Me.cboEstID.Column(3) = "No"
 

slharman1

Member
Local time
Today, 03:01
Joined
Mar 8, 2021
Messages
453
Maybe you are returning both in the query. Post the RowSource query and I'll help you figure it out.
Code:
SELECT tblEstimates.EstimateID, tblEstimates.ModelNumber, tblEstimates.Active, IIf(IsNull([MatDetLineTot]),Round([SumOfLabDetLineTot]*2),Round(IIf([MatDetLineTot]<[SumOfLabDetLineTot],[MatDetLineTot]+[SumOfLabDetLineTot]+[SumOfLabDetLineTot],([MatDetLineTot]+[SumOfLabDetLineTot])*1.5),2)) AS Price, tblEstimates.Description
FROM (tblEstimates INNER JOIN qryLaborTotalByEstimate ON tblEstimates.EstimateID = qryLaborTotalByEstimate.EstimateID) INNER JOIN qryMaterialTotalByEstimate ON tblEstimates.EstimateID = qryMaterialTotalByEstimate.EstimateID
ORDER BY tblEstimates.ModelNumber, tblEstimates.Active;
 

Pat Hartman

Super Moderator
Staff member
Local time
Today, 04:01
Joined
Feb 19, 2002
Messages
35,914
I would order by Active first to drop all the inactive items to the bottom of the list.
Active is the third column of the RowSource so in code, you refer to it as .Column(2)
 

slharman1

Member
Local time
Today, 03:01
Joined
Mar 8, 2021
Messages
453
I would order by Active first to drop all the inactive items to the bottom of the list.
Active is the third column of the RowSource so in code, you refer to it as .Column(2)
Cool, works perfectly! Thank you Pat!
 

Pat Hartman

Super Moderator
Staff member
Local time
Today, 04:01
Joined
Feb 19, 2002
Messages
35,914
Glad to help. The zero based array always throws people off if they don't know that.
Merry Christmas:)
 

Users who are viewing this thread

Top Bottom