Results 1 to 8 of 8
  1. #1
    2 Star Lounger
    Join Date
    Apr 2002
    Location
    Evansville, Indiana, USA
    Posts
    132
    Thanks
    0
    Thanked 0 Times in 0 Posts

    List View on Form (2007)

    I have a list box that displays a unique PO number and 5 boolean fields from the table. Currently the booleans show up as Yes or No. Is there a way to make them display as checked or unchecked boxes instead of Yes or No? If that isn't possible, is there any other way I can display the booleans?
    Thanks,

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: List View oin Form (2007)

    A list box displays text values, you cannot make it display check boxes. You can make the Yes/No fields display differently by using the Format function in the row source of the list box. For example to make Yes display as an "x" and No as a blank, use <code>Format([FieldName],";x")</code> instead of FieldName.

  3. #3
    2 Star Lounger
    Join Date
    Apr 2002
    Location
    Evansville, Indiana, USA
    Posts
    132
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: List View oin Form (2007)

    When I put Format([Submitted],";x") in the row source, the heading shows up as Expr1. I want the heading to say Submitted. When I change Expr1 to Submitted I get an error that says "Circular reference caused by alias 'Submitted' in query definitions's SELECT list". If I put quotes around Submitted, it works, but it shows the quotes in the heading. Is there a way to show Submitted in the heading without the quotes.

  4. #4
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: List View oin Form (2007)

    Use
    <code>
    Format([NameOfTable].[Submitted],";x") AS Submitted
    </code>
    if you're using SQL, or
    <code>
    Submitted: Format([NameOfTable].[Submitted],";x")
    </code>
    in the query design window.

    By specifying the table name, you should be able to get around the circular reference problem.

  5. #5
    2 Star Lounger
    Join Date
    Apr 2002
    Location
    Evansville, Indiana, USA
    Posts
    132
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: List View oin Form (2007)

    Ok I used the second example and that works. Thanks

  6. #6
    2 Star Lounger
    Join Date
    Apr 2002
    Location
    Evansville, Indiana, USA
    Posts
    132
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: List View oin Form (2007)

    When I try to set the row source in VBA like you showed me yesterday, it blows up. Here is the line it blows up on:

    strSQL = "SELECT quPOlistbox.[PO#], quPOlistbox.[Generated PO], quPOlistbox.[Customer Name], Format([tblPO].[Submitted],";X") AS Submitted, Format([tblPO].[Rejected],";X") AS Rejected, Format([tblPO].[Approved Boss],";X") AS [Principal Approved], Format([tblPO].[Approved Pay],";X") AS [OK to Pay], Format([tblPO].[Paid],";X") AS Paid, quPOlistbox.[I/E] FROM quPOlistbox WHERE (((quPOlistbox.[I/E])='E')) ORDER BY quPOlistbox.[Customer Name], quPOlistbox.[PO#];"

    The compiler stops at the semicolon right before the X in the submitted format.

    Have I made a mistake?
    Thanks,

  7. #7
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: List View oin Form (2007)

    If you include a quoted string such as <code>";X"</code> inside another quoted string, VBA becomes confused - where does the quoted string end? That is why I used single quotes around the strings in the WHERE part: <code>'I'</code> and <code>'E'</code>. It is OK to use single quotes around strings within an SQL statement instead of double quotes. So you can use <code>';X'</code> in the Format function.

    See Chr() function and quotes within strings (all) for more detailed info.

  8. #8
    2 Star Lounger
    Join Date
    Apr 2002
    Location
    Evansville, Indiana, USA
    Posts
    132
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: List View oin Form (2007)

    I replaced the "text" with 'text' and that works. Thanks for your help.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •