Welcome to SPN

Register and Join the most happening forum of Sikh community & intellectuals from around the world.

Sign Up Now!

Re: add all to combo box

Discussion in 'Information Technology' started by SteveF, Jul 28, 2006.

Tags:
  1. SteveF

    SteveF
    Expand Collapse
    Guest

    Hi Douglas,

    The 2nd row in my combo box would have a selection from the table. The table
    is an "Expense" table and has different categories of expenses, such as; Work
    related, vehicle, household, etc. Below is the union query code that I copied
    to get "All" to display at the top of the combo box list. "All" is definately
    not in my table though, so not sure if that means it is a zero length
    string. (I'm a bit of a novice at this).

    Code to add all:

    SELECT tblExpenseType.ExpenseTypeID, tblExpenseType.ExpenseType FROM
    tblExpenseType UNION Select Null as AllChoice, "(All)" as bogus From
    tblExpenseType
    ORDER BY [ExpenseType];

    I will try your below suggestions anyway and see how I go.


    Best Regards
    Steve


    "Douglas J Steele" wrote:

    > Your code seems to be assuming that the 2nd column of the combo box contains
    > a zero-length string ("") if the row containing All is selected. Is this the
    > case, or do you have Null in the 2nd column for the All row?
    >
    > If it's Null, try the following instead:
    >
    > If Len(Me!cboExpenseType.Column(1) & "") = 0 Then
    >
    > If that doesn't work, what are you doing to add All to the combo box?
    >
    > For your second question, where do you want to do this: in a query, or in
    > code?
    >
    > If you've got a query that's referring to date picker boxes, change the
    >
    > Where MyDateField Between Forms!MyForm!DatePicker1 And
    > Forms!MyForm!DatePicker2
    >
    > to
    >
    > Where (MyDateField >= Forms!MyForm!DatePicker1 Or Forms!MyForm!DatePicker1
    > Is Null)
    > And (MyDateField <= Forms!MyForm!DatePicker2 Or Forms!MyForm!DatePicker2 Is
    > Null)
    >
    > If you're looking for a VBA solution, what does your current code look like?
    >
    > --
    > Doug Steele, Microsoft Access MVP
    > http://I.Am/DougSteele
    > (no e-mails, please!)
    >
    >
    > "SteveF" <SteveF@discussions.microsoft.com> wrote in message
    > news:62A80940-49D6-4284-9D7D-4472E1D47D8E@microsoft.com...
    > > I recently posted a question to add "All" to a combo box bound to a

    > table -
    > > no prob's. I have the union query which now shows "All" in the list.

    > Problem
    > > is when I select "all", the below code does not update the list box. It

    > works
    > > ok when you make a single selection from the combo box, but not when I

    > choose
    > > "All"...
    > >
    > > I also have 2 x date picker boxes so that I can select date ranges for the
    > > items to be displayed. Is there a simple way to have all dates show up if
    > > there is no selection made? whatever is the easiest solution - even if I

    > have
    > > to add a check box or something to switch the date boxes on or off...
    > >
    > > Code:
    > >
    > > Private Sub cboExpenseType_AfterUpdate()
    > > ' Return record(s) that match value selected in cboExpenseType.
    > > If Len(Me!cboExpenseType.Column(1)) = 0 Then
    > > DoCmd.ShowAllRecords
    > > Else
    > > Me![lstExpenses].Requery
    > > End If
    > > End Sub

    >
    >
    >
     
  2. Loading...


  3. Douglas J Steele

    Douglas J Steele
    Expand Collapse
    Guest

    You're explicitly setting the corresponding value to Null for the "(All)"
    entry. That's why your code wasn't working. There's a significant difference
    between a zero-length string ("") and Null.

    --
    Doug Steele, Microsoft Access MVP
    http://I.Am/DougSteele
    (no e-mails, please!)


    "SteveF" <SteveF@discussions.microsoft.com> wrote in message
    news:C2EBC837-AF2C-4CF4-ABCF-E1EE16F13290@microsoft.com...
    > Hi Douglas,
    >
    > The 2nd row in my combo box would have a selection from the table. The

    table
    > is an "Expense" table and has different categories of expenses, such as;

    Work
    > related, vehicle, household, etc. Below is the union query code that I

    copied
    > to get "All" to display at the top of the combo box list. "All" is

    definately
    > not in my table though, so not sure if that means it is a zero length
    > string. (I'm a bit of a novice at this).
    >
    > Code to add all:
    >
    > SELECT tblExpenseType.ExpenseTypeID, tblExpenseType.ExpenseType FROM
    > tblExpenseType UNION Select Null as AllChoice, "(All)" as bogus From
    > tblExpenseType
    > ORDER BY [ExpenseType];
    >
    > I will try your below suggestions anyway and see how I go.
    >
    >
    > Best Regards
    > Steve
    >
    >
    > "Douglas J Steele" wrote:
    >
    > > Your code seems to be assuming that the 2nd column of the combo box

    contains
    > > a zero-length string ("") if the row containing All is selected. Is this

    the
    > > case, or do you have Null in the 2nd column for the All row?
    > >
    > > If it's Null, try the following instead:
    > >
    > > If Len(Me!cboExpenseType.Column(1) & "") = 0 Then
    > >
    > > If that doesn't work, what are you doing to add All to the combo box?
    > >
    > > For your second question, where do you want to do this: in a query, or

    in
    > > code?
    > >
    > > If you've got a query that's referring to date picker boxes, change the
    > >
    > > Where MyDateField Between Forms!MyForm!DatePicker1 And
    > > Forms!MyForm!DatePicker2
    > >
    > > to
    > >
    > > Where (MyDateField >= Forms!MyForm!DatePicker1 Or

    Forms!MyForm!DatePicker1
    > > Is Null)
    > > And (MyDateField <= Forms!MyForm!DatePicker2 Or Forms!MyForm!DatePicker2

    Is
    > > Null)
    > >
    > > If you're looking for a VBA solution, what does your current code look

    like?
    > >
    > > --
    > > Doug Steele, Microsoft Access MVP
    > > http://I.Am/DougSteele
    > > (no e-mails, please!)
    > >
    > >
    > > "SteveF" <SteveF@discussions.microsoft.com> wrote in message
    > > news:62A80940-49D6-4284-9D7D-4472E1D47D8E@microsoft.com...
    > > > I recently posted a question to add "All" to a combo box bound to a

    > > table -
    > > > no prob's. I have the union query which now shows "All" in the list.

    > > Problem
    > > > is when I select "all", the below code does not update the list box.

    It
    > > works
    > > > ok when you make a single selection from the combo box, but not when I

    > > choose
    > > > "All"...
    > > >
    > > > I also have 2 x date picker boxes so that I can select date ranges for

    the
    > > > items to be displayed. Is there a simple way to have all dates show up

    if
    > > > there is no selection made? whatever is the easiest solution - even if

    I
    > > have
    > > > to add a check box or something to switch the date boxes on or off...
    > > >
    > > > Code:
    > > >
    > > > Private Sub cboExpenseType_AfterUpdate()
    > > > ' Return record(s) that match value selected in cboExpenseType.
    > > > If Len(Me!cboExpenseType.Column(1)) = 0 Then
    > > > DoCmd.ShowAllRecords
    > > > Else
    > > > Me![lstExpenses].Requery
    > > > End If
    > > > End Sub

    > >
    > >
    > >
     
  4. SteveF

    SteveF
    Expand Collapse
    Guest

    Hi Again,

    Tried the last suggestions and still having no luck at all. I have included,
    as best I can, the queries and code as per below. I have tried "" instead of
    Null.

    I have created a form with an unbound combo box to populate a list box. The
    query for the list box I also use for a report so that whatever is finally
    displayed in the list box, will print on the report. The list box is
    restricted by choices made in the Expense Combo box and 2 x date pickers to
    choose a date range if required.

    I am trying to get 2 things to work:

    1) After getting the word “All†to show up in the combo box with the
    following union query, when I select “Allâ€, nothing will display in the list.
    2) Have the date picker boxes to be able to be turned on or off, or if left
    blank – they simply do not apply any date restrictions.


    In the unbound combo box which looks up to the Table Expenses, I have the
    following Row Source:

    SELECT tblExpenseType.ExpenseTypeID, tblExpenseType.ExpenseType FROM
    tblExpenseType UNION Select Null as AllChoice, "(All)" as bogus From
    tblExpenseType
    ORDER BY [ExpenseType];

    The “After Update†procedure on the combo box is as follows:

    Private Sub cboExpenseType_AfterUpdate()
    ' Return record(s) that match value selected in cboExpenseType.
    If Len(Me!cboExpenseType.Column(1)) = 0 Then
    DoCmd.ShowAllRecords
    Else
    Me![lstExpenses].Requery
    End If
    End Sub

    The query to populate the list box is as follows:

    SELECT tblExpenses.ExpenseID, tblExpenses.Description,
    tblExpenseType.ExpenseTypeID, tblExpenses.DateOfTransaction, tblExpenses.Cost
    FROM tblExpenseType INNER JOIN tblExpenses ON tblExpenseType.ExpenseTypeID =
    tblExpenses.ExpenseTypeID
    WHERE
    (((tblExpenseType.ExpenseTypeID)=[Forms]![frmDateOfTransaction]![cboExpenseType].[Value])
    AND ((tblExpenses.DateOfTransaction) Between
    [Forms]![frmDateOfTransaction]![BeginningDate] And
    [Forms]![frmDateOfTransaction]![EndingDate]))
    ORDER BY tblExpenses.DateOfTransaction DESC;


    "Douglas J Steele" wrote:

    > You're explicitly setting the corresponding value to Null for the "(All)"
    > entry. That's why your code wasn't working. There's a significant difference
    > between a zero-length string ("") and Null.
    >
    > --
    > Doug Steele, Microsoft Access MVP
    > http://I.Am/DougSteele
    > (no e-mails, please!)
    >
    >
    > "SteveF" <SteveF@discussions.microsoft.com> wrote in message
    > news:C2EBC837-AF2C-4CF4-ABCF-E1EE16F13290@microsoft.com...
    > > Hi Douglas,
    > >
    > > The 2nd row in my combo box would have a selection from the table. The

    > table
    > > is an "Expense" table and has different categories of expenses, such as;

    > Work
    > > related, vehicle, household, etc. Below is the union query code that I

    > copied
    > > to get "All" to display at the top of the combo box list. "All" is

    > definately
    > > not in my table though, so not sure if that means it is a zero length
    > > string. (I'm a bit of a novice at this).
    > >
    > > Code to add all:
    > >
    > > SELECT tblExpenseType.ExpenseTypeID, tblExpenseType.ExpenseType FROM
    > > tblExpenseType UNION Select Null as AllChoice, "(All)" as bogus From
    > > tblExpenseType
    > > ORDER BY [ExpenseType];
    > >
    > > I will try your below suggestions anyway and see how I go.
    > >
    > >
    > > Best Regards
    > > Steve
    > >
    > >
    > > "Douglas J Steele" wrote:
    > >
    > > > Your code seems to be assuming that the 2nd column of the combo box

    > contains
    > > > a zero-length string ("") if the row containing All is selected. Is this

    > the
    > > > case, or do you have Null in the 2nd column for the All row?
    > > >
    > > > If it's Null, try the following instead:
    > > >
    > > > If Len(Me!cboExpenseType.Column(1) & "") = 0 Then
    > > >
    > > > If that doesn't work, what are you doing to add All to the combo box?
    > > >
    > > > For your second question, where do you want to do this: in a query, or

    > in
    > > > code?
    > > >
    > > > If you've got a query that's referring to date picker boxes, change the
    > > >
    > > > Where MyDateField Between Forms!MyForm!DatePicker1 And
    > > > Forms!MyForm!DatePicker2
    > > >
    > > > to
    > > >
    > > > Where (MyDateField >= Forms!MyForm!DatePicker1 Or

    > Forms!MyForm!DatePicker1
    > > > Is Null)
    > > > And (MyDateField <= Forms!MyForm!DatePicker2 Or Forms!MyForm!DatePicker2

    > Is
    > > > Null)
    > > >
    > > > If you're looking for a VBA solution, what does your current code look

    > like?
    > > >
    > > > --
    > > > Doug Steele, Microsoft Access MVP
    > > > http://I.Am/DougSteele
    > > > (no e-mails, please!)
    > > >
    > > >
    > > > "SteveF" <SteveF@discussions.microsoft.com> wrote in message
    > > > news:62A80940-49D6-4284-9D7D-4472E1D47D8E@microsoft.com...
    > > > > I recently posted a question to add "All" to a combo box bound to a
    > > > table -
    > > > > no prob's. I have the union query which now shows "All" in the list.
    > > > Problem
    > > > > is when I select "all", the below code does not update the list box.

    > It
    > > > works
    > > > > ok when you make a single selection from the combo box, but not when I
    > > > choose
    > > > > "All"...
    > > > >
    > > > > I also have 2 x date picker boxes so that I can select date ranges for

    > the
    > > > > items to be displayed. Is there a simple way to have all dates show up

    > if
    > > > > there is no selection made? whatever is the easiest solution - even if

    > I
    > > > have
    > > > > to add a check box or something to switch the date boxes on or off...
    > > > >
    > > > > Code:
    > > > >
    > > > > Private Sub cboExpenseType_AfterUpdate()
    > > > > ' Return record(s) that match value selected in cboExpenseType.
    > > > > If Len(Me!cboExpenseType.Column(1)) = 0 Then
    > > > > DoCmd.ShowAllRecords
    > > > > Else
    > > > > Me![lstExpenses].Requery
    > > > > End If
    > > > > End Sub
    > > >
    > > >
    > > >

    >
    >
    >
     
  5. Douglas J. Steele

    Douglas J. Steele
    Expand Collapse
    Guest

    My fault: I missed the fact that you're using Column(1) in your routine. The
    Column collection starts at 0, not 1, so you're actually looking at the 2nd
    column (which, of course, is the one that contains (All)) That means that
    when you select the All entry, you're going to fall into the Else portion of
    your If statement.

    In the AfterUpdate routine, change:

    If Len(Me!cboExpenseType.Column(1)) = 0 Then

    to

    If Me!cboExpenseType.Column(1) = "(All)" Then

    or

    If IsNull(Me!cboExpenseType.Column(0)) Then


    --
    Doug Steele, Microsoft Access MVP
    http://I.Am/DougSteele
    (no private e-mails, please)


    "SteveF" <SteveF@discussions.microsoft.com> wrote in message
    news:5655246B-4CC3-41D2-99EE-2B7F4457D3B1@microsoft.com...
    > Hi Again,
    >
    > Tried the last suggestions and still having no luck at all. I have
    > included,
    > as best I can, the queries and code as per below. I have tried "" instead
    > of
    > Null.
    >
    > I have created a form with an unbound combo box to populate a list box.
    > The
    > query for the list box I also use for a report so that whatever is finally
    > displayed in the list box, will print on the report. The list box is
    > restricted by choices made in the Expense Combo box and 2 x date pickers
    > to
    > choose a date range if required.
    >
    > I am trying to get 2 things to work:
    >
    > 1) After getting the word "All" to show up in the combo box with the
    > following union query, when I select "All", nothing will display in the
    > list.
    > 2) Have the date picker boxes to be able to be turned on or off, or if
    > left
    > blank - they simply do not apply any date restrictions.
    >
    >
    > In the unbound combo box which looks up to the Table Expenses, I have the
    > following Row Source:
    >
    > SELECT tblExpenseType.ExpenseTypeID, tblExpenseType.ExpenseType FROM
    > tblExpenseType UNION Select Null as AllChoice, "(All)" as bogus From
    > tblExpenseType
    > ORDER BY [ExpenseType];
    >
    > The "After Update" procedure on the combo box is as follows:
    >
    > Private Sub cboExpenseType_AfterUpdate()
    > ' Return record(s) that match value selected in cboExpenseType.
    > If Len(Me!cboExpenseType.Column(1)) = 0 Then
    > DoCmd.ShowAllRecords
    > Else
    > Me![lstExpenses].Requery
    > End If
    > End Sub
    >
    > The query to populate the list box is as follows:
    >
    > SELECT tblExpenses.ExpenseID, tblExpenses.Description,
    > tblExpenseType.ExpenseTypeID, tblExpenses.DateOfTransaction,
    > tblExpenses.Cost
    > FROM tblExpenseType INNER JOIN tblExpenses ON tblExpenseType.ExpenseTypeID
    > =
    > tblExpenses.ExpenseTypeID
    > WHERE
    > (((tblExpenseType.ExpenseTypeID)=[Forms]![frmDateOfTransaction]![cboExpenseType].[Value])
    > AND ((tblExpenses.DateOfTransaction) Between
    > [Forms]![frmDateOfTransaction]![BeginningDate] And
    > [Forms]![frmDateOfTransaction]![EndingDate]))
    > ORDER BY tblExpenses.DateOfTransaction DESC;
    >
    >
    > "Douglas J Steele" wrote:
    >
    >> You're explicitly setting the corresponding value to Null for the "(All)"
    >> entry. That's why your code wasn't working. There's a significant
    >> difference
    >> between a zero-length string ("") and Null.
    >>
    >> --
    >> Doug Steele, Microsoft Access MVP
    >> http://I.Am/DougSteele
    >> (no e-mails, please!)
    >>
    >>
    >> "SteveF" <SteveF@discussions.microsoft.com> wrote in message
    >> news:C2EBC837-AF2C-4CF4-ABCF-E1EE16F13290@microsoft.com...
    >> > Hi Douglas,
    >> >
    >> > The 2nd row in my combo box would have a selection from the table. The

    >> table
    >> > is an "Expense" table and has different categories of expenses, such
    >> > as;

    >> Work
    >> > related, vehicle, household, etc. Below is the union query code that I

    >> copied
    >> > to get "All" to display at the top of the combo box list. "All" is

    >> definately
    >> > not in my table though, so not sure if that means it is a zero length
    >> > string. (I'm a bit of a novice at this).
    >> >
    >> > Code to add all:
    >> >
    >> > SELECT tblExpenseType.ExpenseTypeID, tblExpenseType.ExpenseType FROM
    >> > tblExpenseType UNION Select Null as AllChoice, "(All)" as bogus From
    >> > tblExpenseType
    >> > ORDER BY [ExpenseType];
    >> >
    >> > I will try your below suggestions anyway and see how I go.
    >> >
    >> >
    >> > Best Regards
    >> > Steve
    >> >
    >> >
    >> > "Douglas J Steele" wrote:
    >> >
    >> > > Your code seems to be assuming that the 2nd column of the combo box

    >> contains
    >> > > a zero-length string ("") if the row containing All is selected. Is
    >> > > this

    >> the
    >> > > case, or do you have Null in the 2nd column for the All row?
    >> > >
    >> > > If it's Null, try the following instead:
    >> > >
    >> > > If Len(Me!cboExpenseType.Column(1) & "") = 0 Then
    >> > >
    >> > > If that doesn't work, what are you doing to add All to the combo box?
    >> > >
    >> > > For your second question, where do you want to do this: in a query,
    >> > > or

    >> in
    >> > > code?
    >> > >
    >> > > If you've got a query that's referring to date picker boxes, change
    >> > > the
    >> > >
    >> > > Where MyDateField Between Forms!MyForm!DatePicker1 And
    >> > > Forms!MyForm!DatePicker2
    >> > >
    >> > > to
    >> > >
    >> > > Where (MyDateField >= Forms!MyForm!DatePicker1 Or

    >> Forms!MyForm!DatePicker1
    >> > > Is Null)
    >> > > And (MyDateField <= Forms!MyForm!DatePicker2 Or
    >> > > Forms!MyForm!DatePicker2

    >> Is
    >> > > Null)
    >> > >
    >> > > If you're looking for a VBA solution, what does your current code
    >> > > look

    >> like?
    >> > >
    >> > > --
    >> > > Doug Steele, Microsoft Access MVP
    >> > > http://I.Am/DougSteele
    >> > > (no e-mails, please!)
    >> > >
    >> > >
    >> > > "SteveF" <SteveF@discussions.microsoft.com> wrote in message
    >> > > news:62A80940-49D6-4284-9D7D-4472E1D47D8E@microsoft.com...
    >> > > > I recently posted a question to add "All" to a combo box bound to a
    >> > > table -
    >> > > > no prob's. I have the union query which now shows "All" in the
    >> > > > list.
    >> > > Problem
    >> > > > is when I select "all", the below code does not update the list
    >> > > > box.

    >> It
    >> > > works
    >> > > > ok when you make a single selection from the combo box, but not
    >> > > > when I
    >> > > choose
    >> > > > "All"...
    >> > > >
    >> > > > I also have 2 x date picker boxes so that I can select date ranges
    >> > > > for

    >> the
    >> > > > items to be displayed. Is there a simple way to have all dates show
    >> > > > up

    >> if
    >> > > > there is no selection made? whatever is the easiest solution - even
    >> > > > if

    >> I
    >> > > have
    >> > > > to add a check box or something to switch the date boxes on or
    >> > > > off...
    >> > > >
    >> > > > Code:
    >> > > >
    >> > > > Private Sub cboExpenseType_AfterUpdate()
    >> > > > ' Return record(s) that match value selected in
    >> > > > cboExpenseType.
    >> > > > If Len(Me!cboExpenseType.Column(1)) = 0 Then
    >> > > > DoCmd.ShowAllRecords
    >> > > > Else
    >> > > > Me![lstExpenses].Requery
    >> > > > End If
    >> > > > End Sub
    >> > >
    >> > >
    >> > >

    >>
    >>
    >>
     

Share This Page