Results 1 to 6 of 6
  1. #1
    4 Star Lounger
    Join Date
    Oct 2003
    Location
    Guelph, Ontario, Canada
    Posts
    428
    Thanks
    0
    Thanked 0 Times in 0 Posts

    change combo box rowsource on the fly (2000 latest)

    I have a form which I want to switch the record source on the fly. The main purpose for the form is to show Active records, but I also want to be able to switch and show Inactive records.

    The switching of the record source works without difficulty, by using two Option buttons.

    However, on the form is a combo box, and I want to switch the combo box rowsource on the fly so that records shown in it switch with the option buttons.

    Below is the code behind the two option buttons...
    Private Sub OptionShowActive_Click()
    Me.RecordSource = "SELECT tblTrinity.UniqueID, tblTrinity.LastName, tblTrinity.FirstName, tblTrinity.FirstName2, tblTrinity.LastName2, tblTrinity.HouseNbr, tblTrinity.Street, tblTrinity.City, tblTrinity.Province, tblTrinity.HomePhone, tblTrinity.Code, tblTrinity.BusPhone, tblTrinity.Person1Status, tblTrinity.Person2Status, tblTrinity.Person1DateOfBirth, tblTrinity.Person1Baptism, tblTrinity.Person1Confirmation, tblTrinity.Person2DateOfBirth, tblTrinity.Person2Baptism, tblTrinity.Person2Confirmation, tblTrinity.Remarks, tblTrinity.Person1Removed, tblTrinity.Person2Removed, tblTrinity.BothRemoved, tblTrinity.RemovedDate, tblTrinity.RemovedHow, tblTrinity.DateAdded " _
    & "FROM tblTrinity " _
    & "WHERE (((tblTrinity.RemovedDate) Is Null));"

    Me.Combo71.RowSource = "SELECT tblTrinity.UniqueID, tblTrinity.LastName, tblTrinity.FirstName, tblTrinity.RemovedDate " _
    & "FROM tblTrinity " _
    & "WHERE (((tblTrinity.RemovedDate) Is Null)); " _
    & "ORDER BY tblTrinity.LastName, tblTrinity.FirstName;"
    Combo71 = UniqueID
    End Sub

    Private Sub OptionShowRemoved_Click()
    Me.RecordSource = "SELECT tblTrinity.UniqueID, tblTrinity.LastName, tblTrinity.FirstName, tblTrinity.FirstName2, tblTrinity.LastName2, tblTrinity.HouseNbr, tblTrinity.Street, tblTrinity.City, tblTrinity.Province, tblTrinity.HomePhone, tblTrinity.Code, tblTrinity.BusPhone, tblTrinity.Person1Status, tblTrinity.Person2Status, tblTrinity.Person1DateOfBirth, tblTrinity.Person1Baptism, tblTrinity.Person1Confirmation, tblTrinity.Person2DateOfBirth, tblTrinity.Person2Baptism, tblTrinity.Person2Confirmation, tblTrinity.Remarks, tblTrinity.Person1Removed, tblTrinity.Person2Removed, tblTrinity.BothRemoved, tblTrinity.RemovedDate, tblTrinity.RemovedHow, tblTrinity.DateAdded " _
    & "FROM tblTrinity " _
    & "WHERE (((tblTrinity.RemovedDate) Is Not Null));"

    Me.Combo71.RowSource = "SELECT tblTrinity.UniqueID, tblTrinity.LastName, tblTrinity.FirstName, tblTrinity.RemovedDate " _
    & "FROM tblTrinity " _
    & "WHERE (((tblTrinity.RemovedDate) Is Not Null)); " _
    & "ORDER BY tblTrinity.LastName, tblTrinity.FirstName;"
    Combo71 = UniqueID
    End Sub

    No records show in either view. What additionally do I have to do?

    Thanks.

    Tom

  2. #2
    4 Star Lounger
    Join Date
    Oct 2003
    Location
    Guelph, Ontario, Canada
    Posts
    428
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: change combo box rowsource on the fly (2000 latest)

    Hans
    Putting the Requery commands, as you suggested, doesn't do it.

    All the SQLs work fine in a new query, returning exactly the records they were intended to return.

    As I indicated in my first post, the record sources for the form switch back and forth perfectly. It's just the combo box that doesn't work.

    Methinks I'm looking for a needle in a haystack.

    Tom

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

    Re: change combo box rowsource on the fly (2000 latest)

    After changing the record source of the form, put

    Me.Requery

    and after changing the row source of the combo box, put

    Me.Combo71.Requery

    If that doesn't help, create a new query in design view, switch to SQL view and copy the SQL for the record source of the form into the window, then switch to datasheet view to see if any records are returned; do the same for the row source of the combo box.

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

    Re: change combo box rowsource on the fly (2000 latest)

    Could you post a stripped down copy of the database? See <post#=401925>post 401925</post#> for instructions.

  5. #5
    4 Star Lounger
    Join Date
    Oct 2003
    Location
    Guelph, Ontario, Canada
    Posts
    428
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: change combo box rowsource on the fly (2000 latest)

    Hans
    I found the needle!

    In each of the combo box SQL formulations, I had a semicolon ( ; ) at the end of the WHERE clause and before the ORDER BY clause line.

    Don't know why I didn't find that before, but I didn't, and Access didn't flag it.

    Thanks for your offer to post a stripped down copy, but I won't need it now. Whew!

    Tom

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

    Re: change combo box rowsource on the fly (2000 latest)

    <img src=/S/blush.gif border=0 alt=blush width=15 height=15> I should have seen that.

    But issues like that are precisely why I suggested pasting the record source/row source into a query in SQL view; if you try to switch to datasheet or design view, Access raises an error message "Characters found after end of SQL statement."

Posting Permissions

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