Re: [MS_AccessPros] Using SQL in the Row Source

 

Thank you Duane.  I will check out that link when I get some time.  Last time you recommended a tool it was very beneficial to me.

I have a somewhat related question; again basic.

In this application, I have several configuration tables that have a hierarchy similar to:

tbl1 (tblSites with PK SiteID and also a SiteName field)

    tbl2

        tbl3

          tbl4  and on to tbl 7 (which is my tblSamples).

My tbl7 is my table of samples and all the upper level tables lead to that table. 

I have found myself creating many queries in various forms to do things with the samples table.  When I do this, I end up including all seven of these tables just to get to the top level tblSites table, to use SiteID in the top level table as a filter. 

Instead of doing this each time, should I create a saved query with all these tables and include in the query results only the two fields, tblSites.SiteID and tblSamples.SampleID?  Then use that query to build other queries from?

---In MS_Access_Professionals@yahoogroups.com, <duanehookom@...> wrote :

David,

I'm not sure if I have seen an answer to your post. The only time you should need to use code to update the SQL property of your Row Source is when it dynamically needs to change like cascading combo/list boxes.  These special case typically have "trigger" events like the After Update event of one combo box should limit the selections in another combo box.

To set the initial Row Source I would do this in the On Open event of the form.

One tool I use is http://www.dpriver.com/pp/sqlformat.htm

It will take something like:

SELECT Model
FROM CarModels
WHERE ModelMake = "Ford"

and convert it to VB:

varname1 = ""
varname1 = varname1 & "SELECT Model " & vbCrLf
varname1 = varname1 & "FROM CarModels " & vbCrLf
varname1 = varname1 & "WHERE ModelMake = ""Ford"""

Regards,

Duane

---

From: MS_Access_Professionals@yahoogroups.com <MS_Access_Professionals@yahoogroups.com> on behalf of david.pratt@... [MS_Access_Professionals] <MS_Access_Professionals@yahoogroups.com>
Sent: Monday, August 7, 2017 6:41 PM
To: MS_Access_Professionals@yahoogroups.com
Subject: [MS_AccessPros] Using SQL in the Row Source

 

this is likely a simpleton question, but I need to ask since I am not certain.

If I have an unbound form with a parent and child list boxes,  a form style I find myself using a lot.  For my education I want to write SQL code for the row souces of the list boxes.  I have always used the row source of the list box properties and used the query builder to build the queries that serve as the row source.

I'd like to practice writing the SQL code using the strSQL = strSQL & "..." process to build the list boxes.  Where do I put the SQL code?  Do I build the SQL statements in the Form_Open event? and then just assign the SQL to the list box control?

me.lstOne.RowSource = strSQL1

me.lstTwo.RowSource = strSQL2

__._,_.___

---

Posted by: david.pratt@outlook.com

---

Reply via web post

•

Reply to sender

•

Reply to group

•

Start a New Topic

•

Messages in this topic (3)

---

Have you tried the highest rated email app?

With 4.5 stars in iTunes, the Yahoo Mail app is the highest rated email app on the market. What are you waiting for? Now you can access all your inboxes (Gmail, Outlook, AOL and more) in one place. Never delete an email again with 1000GB of free cloud storage.

---

Visit Your Group

• Privacy • Unsubscribe • Terms of Use

.

__,_._,___