Changing record source in VBA

Topic

New Reply

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!

Reply | Quote

Replies

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.

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

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

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:

  strSQL = "SELECT * FROM tblWhatever "
  strSQL = strSQL & "WHERE tblWhatever.AnyField=" & varCriteria

Does that help?

Reply | Quote

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.

Reply | Quote

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?

Reply | Quote

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!

Reply | Quote

Try aliasing the table names in the SQL. Oddly enough, aliasing can make the query execute faster (no, don’t 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.

Reply | Quote

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

Reply | Quote