Results 1 to 8 of 8
  1. #1
    Join Date
    Apr 2008
    Posts
    189

    Question Unanswered: How to change the .RecordSource of a Form without having to open it

    Hello,
    I want to change the .RecordSource of a Form without having to open it.

    Code:
    Forms(strDocName).RecordSource = "SELECT * FROM FOO"
    I want to do something like what I can do with a Query where I can change the SQL without having to open the Query in DesignView.
    Something like:

    Code:
    Dim strSql As String
    strSql = "SELECT ...
    CurrentDb.QueryDefs("qryData").SQL = strSql
    Any ideas how to change the .RecordSource of a Form?

    Thanks!

  2. #2
    Join Date
    Nov 2007
    Location
    Adelaide, South Australia
    Posts
    4,049
    You have to open it afaik. But you can do that with code.
    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
    Apr 2008
    Posts
    189
    Oh, too bad.

    I know that I can open the Form via some code... but takes time and also the the user sees some "flashes" of the Forms being opened and closed.

    Thanks for your help.

  4. #4
    Join Date
    Nov 2007
    Location
    Adelaide, South Australia
    Posts
    4,049
    Perhaps look for an alternative solution. I have never had to do such a thing while the database is "in the field". Why do you believe you need to modify the record source like this?

    Why not just set it when the form is opened?
    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

  5. #5
    Join Date
    Apr 2008
    Posts
    189
    The problem is this.

    I have a Form that its .RecordSource is a Query of another Query.
    I need to change the dates and some other filters depending on what the User has chosen.

    Maybe I can edit the .RecordSource adding something like
    Code:
    & "Form('Bla').Controls(1).Value" &
    I'll see.

    Thanks for your help!!!

  6. #6
    Join Date
    Nov 2007
    Location
    Adelaide, South Australia
    Posts
    4,049
    Well the typical way of handling that is to have the underlying queries take into account what the user has chosen. Then the form's design remains static, only the data should be dynamic.

    For example, you might have a CustomerID combo box on a dlgSelectCustomer form. You select a customer from it, hit the OK button and you then see a form/report with, say, transactions from that customer only. That form has a query that is something like:

    SELECT * FROM tblTransaction WHERE CustomerID = forms!dlgSelectCustomer!CustomerID

    I see no need yet to have to modify the design of a form during user operation.
    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

  7. #7
    Join Date
    Apr 2008
    Posts
    189
    forms!dlgSelectCustomer!CustomerID
    Yes, good option.
    Thank you for the great advice!

  8. #8
    Join Date
    Nov 2007
    Location
    Adelaide, South Australia
    Posts
    4,049
    You're most welcome
    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
  •