VB, SELECT queries, Inner Joins, and trouble!

[Junkee Brewster]

Hi,

I have a command button on my form that (usually!) allows me to switch between current and archived records. I'll show you the basic code (first part).

If cmdShowArchived.Caption = "Show Archived" Then
        Me.RecordSource = "SELECT QryTools.* FROM QryTools WHERE    (((QryTools.Archived)=Yes));"
        DoCmd.Requery
        DoCmd.GoToRecord , , acFirst
        cmdShowArchived.Caption = "Show Current"

Problem: I cannot get the "SELECT" properties right for my new Tools form. Main form: QryToolGroups.ToolGoup, Subform: QryTools, linked to the main form by [ToolGoup]. The Archived Box is in the Subform. Normally it's easy because the main form holds the Archived box (like the code above) but not this time.

Obviously I can't just select the Subform query in the VB, because when I click the cmdbutton I get the paramater box asking where the QryTools.ToolGroup is. I'm doing something like this (rather badly):

"SELECT QryToolGroups.* FROM QryToolGroups INNER JOIN QryTools ON QryToolGroups.ToolGroup = QryTools.ToolGroup WHERE (((QryTools.Archived)=Yes));"

I know it's totally wrong! The way I'm setting up the code (above and several frustrated variations) I get:
1) Shows the Main form data correctly, but Subform data isn't visible
2) Blank section where the form should be
3) Both Main and Sub data works, but the Mainform no longer displays the singular values for each tool. e.g If I have 3 drills in the subform, then the main form will also display three listings for drills instead of just the one value. In other words I'm making some kind of nonsensical query where QryTools is creating duplicate values in my main form table.

I guess I should've cut long story short: I need to make a SELECT query that reflects my Form setup, and not the one I'm making which is junk. CHEERS!!

 

[Junkee Brewster]

*Slaps Forehead* Thanks very muchly Pat! You know the whole can't see forest from trees thing? lol