Results 1 to 5 of 5
  1. #1
    Join Date
    Dec 2007
    Posts
    25

    Unanswered: Changing Row Source with Button on Listbox

    This is what I have...

    Code:
    Private Sub SortByLastName_Click()
    
    Me.Contacts.RowSource = "SELECT [AddressBook].[ID] AS ID, [AddressBook].[Category] AS Category, [AddressBook].[CompanyName] AS [Company Name], [AddressBook].[LastName] AS [Last Name], [AddressBook].[FirstName] AS [First Name], [AddressBook].[JobTitle] AS Title, [AddressBook].[Department] AS Department, [AddressBook].[ContactMethod] AS [Contact Method] FROM AddressBook WHERE ((([AddressBook].[LastName]) Like [Forms]![frmChooseContact]![SortBy] & " * ")) ORDER BY [AddressBook].[LastName];"
    Me.Contacts.Requery
    Me.Contacts.SetFocus
    
    End Sub
    If I put the Select statement in the row source manually it works fine, but having it re-sort via code as done above it doesn't work. I have tried both with and without quotes.

    I could use two list boxes and whichever way its sorted by is which will be visable, but I figured I could just change the RowSource with code.

    Thanks,

    RJ

  2. #2
    Join Date
    Nov 2007
    Location
    Adelaide, South Australia
    Posts
    4,049
    You have quotes inside your quotes. Change your double quotes in the WHERE clause to single quotes.

    Code:
    Private Sub SortByLastName_Click()
    
    Me.Contacts.RowSource = "SELECT [AddressBook].[ID] AS ID, [AddressBook].[Category] AS Category, [AddressBook].[CompanyName] AS [Company Name], [AddressBook].[LastName] AS [Last Name], [AddressBook].[FirstName] AS [First Name], [AddressBook].[JobTitle] AS Title, [AddressBook].[Department] AS Department, [AddressBook].[ContactMethod] AS [Contact Method] FROM AddressBook WHERE ((([AddressBook].[LastName]) Like [Forms]![frmChooseContact]![SortBy] & ' * ')) ORDER BY [AddressBook].[LastName];"
    Me.Contacts.Requery
    Me.Contacts.SetFocus
    
    End Sub
    The SQL looks a lot more complicated than it needs to be too, for example I would just use SELECT ID, Category etc rather than SELECT [AddressBook].[ID] AS ID, [AddressBook].[Category] AS Category etc... they are both equivalent since all of your fields are coming from [AddressBook].

    Cheers

    ST
    Owner and Manager of
    CypherBYTE, Microsoft Access Development Specialists.
    Microsoft Access MCP.
    And all around nice guy!


    "Heck it's something understood by accountants ... so it can't be 'that' difficult..." -- Healdem
    "...teach a man to code and he'll be frustrated for life! " -- georgev

  3. #3
    Join Date
    Dec 2007
    Posts
    25
    Quote Originally Posted by StarTrekker
    The SQL looks a lot more complicated than it needs to be too, for example I would just use SELECT ID, Category etc rather than SELECT [AddressBook].[ID] AS ID, [AddressBook].[Category] AS Category etc... they are both equivalent since all of your fields are coming from [AddressBook].

    Cheers

    ST
    Thanks, that worked. Have quick question about the SQL. I did make it shorter by removing all the AS WHATEVER That is the same as a the field name, but still required for [CompanyName] AS [Company Name] just so it appears right, but I'm not able to reference them without specifically stating the [AddressBook] table. I am assuming that is because the ListBox is UnBound. Would that be correct or possibly a syntax in the way its written.

    Current looks like this...

    Code:
    Me.Contacts.RowSource = "SELECT [AddressBook].[ID], [AddressBook].[Category], [AddressBook].[CompanyName] AS [Company Name], [AddressBook].[LastName] AS [Last Name], [AddressBook].[FirstName] AS [First Name], [AddressBook].[JobTitle] AS Title, [AddressBook].[Department], [AddressBook].[ContactMethod] AS [Contact Method] FROM AddressBook WHERE ((([AddressBook].[CompanyName]) Like [Forms]![frmChooseContact]![SortBy] & '*')) ORDER BY [AddressBook].[CompanyName];"
    Thanks,

    RJ

  4. #4
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Provided Answers: 10
    Code:
    Like '" & [Forms]![frmChooseContact]![SortBy] & "*' ORDER BY
    Note the use of single and double quotes.
    George
    Home | Blog

  5. #5
    Join Date
    Nov 2007
    Location
    Adelaide, South Australia
    Posts
    4,049
    Have quick question about the SQL. I did make it shorter by removing all the AS WHATEVER That is the same as a the field name, but still required for [CompanyName] AS [Company Name] just so it appears right, but I'm not able to reference them without specifically stating the [AddressBook] table. I am assuming that is because the ListBox is UnBound. Would that be correct or possibly a syntax in the way its written.
    It shouldn't be because it is unbound. I'm not sure why... without having the database to look at anyway.

    Glad to hear it works though, that's the important thing
    Owner and Manager of
    CypherBYTE, Microsoft Access Development Specialists.
    Microsoft Access MCP.
    And all around nice guy!


    "Heck it's something understood by accountants ... so it can't be 'that' difficult..." -- Healdem
    "...teach a man to code and he'll be frustrated for life! " -- georgev

Posting Permissions

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