Results 1 to 7 of 7
  1. #1
    JamesP
    Guest

    Changing record source in VBA

    I wish to create an SQLstr in my VBA code and then set the record set which is created as the form record source. I now I can put the SQL in the record source property, but I wish to do it with in my code.

    Also I am a having trouble keeping my SQL strings down to a usable size. Does anybody have any hints for someone very new to this Access thing.

    Thank you!

  2. #2
    Plutonium Lounger
    Join Date
    Dec 2000
    Location
    Sacramento, California, USA
    Posts
    16,775
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Changing record source in VBA

    If I haven't misunderstood you, you don't want to just paste the SQL string into the form's RecordSource property, you want to create the string in code and assign it, is that right?

    If it is, you could do something like this in the code behind the form and call it from various events like the Open event of the form.

    <pre>Private Sub SetRecordsource(byval varCriteria as Variant)
    Dim strSQL As String

    strSQL = "SELECT * FROM tblWhatever " _
    & "WHERE tblWhatever.AnyField=" & varCriteria
    Me.RecordSource = strSQL
    End Sub</pre>


    I'm not sure what you mean about keeping your strings down to a usable size. If you mean line length, you can either use line a continuation character (space, underscore, return) when you want to break a line and continue it in the next line, or you can use separate assignment statements like this:

    <pre> strSQL = "SELECT * FROM tblWhatever "
    strSQL = strSQL & "WHERE tblWhatever.AnyField=" & varCriteria</pre>


    Does that help?
    Charlotte

  3. #3
    3 Star Lounger
    Join Date
    Jan 2001
    Location
    The Netherlands
    Posts
    216
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Changing record source in VBA

    Hi,

    First setting the RecordSource property of your form. You can do this in code like this:

    forms![frmYourForm].RecordSource = strYourSQL

    To keep your SQL string in code readable you can format your code like this:

    Dim strSQL as string

    strSQL = "SELECT etc.etc. "
    strSQL = strSQL & "FROM etc.etc. "
    strSQL = strSQL & "WHERE etc.etc. ;"

    Just split it up over several lines. Use the & sign to concatinate strings.

  4. #4
    WS Lounge VIP rory's Avatar
    Join Date
    Dec 2000
    Location
    Burwash, East Sussex, United Kingdom
    Posts
    6,280
    Thanks
    3
    Thanked 191 Times in 177 Posts

    Re: Changing record source in VBA

    Hi,
    If you're running this from your form, then you can use the syntax:
    Me.Recordsource = strSQL
    where strSQL is your SQL string.
    As for the second question, what exactly do you mean by unmanageable?
    Regards,
    Rory

    Microsoft MVP - Excel

  5. #5
    JamesP
    Guest

    Re: Changing record source in VBA

    Thanks folks!
    Thanks folks! Your answer was right on for changing the record source.
    I'm afraid I wasn't not clear about the SQL though.
    I have some rather large tables that I am attempting to run an aggregate function and a sort as well. My SQLs strings are always to large. I can't use the * because of the aggregate. I thought there was a way to shorten the string yet include all the fields you wish. Such as a way that you only have mention the table name once in the select part.

    On the side I am interested in doing more with queries such as query within query. Can anyone recommend some good information on the web for this subject.
    Thanks again!

  6. #6
    Plutonium Lounger
    Join Date
    Dec 2000
    Location
    Sacramento, California, USA
    Posts
    16,775
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Changing record source in VBA

    Try aliasing the table names in the SQL. Oddly enough, aliasing can make the query execute faster (no, don't [img]/w3timages/icons/scream.gif[/img] at me, Peter Vogel demonstrated this for me), and it can certainly make the SQL shorter. What you do is alias the table in the FROM clause, so that instead of "FROM MyLongTableName" you use "FROM MyLongTableName As T1" and your query reads something like this:

    SELECT T1.Field1, T1.Field2, T1.Field3, T2,Field5 .... FROM MyLongTableName As T1 INNER JOIN MyOtherLongTableName as T2 ON T1.Field1 = T2.Field1

    I generally avoid T1 and T2 alias and use something like L for Loans and B for LoanBalances, etc. You can always look at the SQL to see what the tables really are if you work in the query grid and forget.
    Charlotte

  7. #7
    JamesP
    Guest

    Re: Changing record source in VBA

    That is it!
    Wow!!! Thanks Charotte!!!

Posting Permissions

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