VBA query help...

[Swift_74d]

Ok, I’m currently trying to ultimately solve a query problem on a sort able form. The displayed data is based off three tables...<?xml:namespace prefix = o ns = "urn:schemas-microsoft-comfficeffice" /><o></o>
<o></o>
Materials<o></o>
M_ID / primary key (one to many with Build.B_Mat)<o></o>
M_Name / name of the material (filtered by referring to check boxes on the form)<o></o>
M_Disc / description of the material<o></o>
M_Stock / amount in stock<o></o>
<o></o>
Build<o></o>
B_ID / primary key<o></o>
B_Product / foreign key (one to many with Product.P_ID)<o></o>
B_Mat / foreign key (many to one with Materials.M_ID)<o></o>
B_Amt / amount of material needed for this particular product<o></o>
<o></o>
Product<o></o>
P_ID / primary key (many to one with Build.B_Product<o></o>
P_Name / product name<o></o>
*many other descriptive fields*<o></o>
<o></o>
<o></o>
For now this works, but I’ve been asked to eventually include the ability to change the comparative operator in the materials condition from OR to AND via dropdown or some other control. The only way I can see to do this is by vba and I’m at a loss.<o></o>
<o></o>
I can get as far as creating a temp table in vba based on Materials.M_ID filtered by the check boxes on the form but I’m not sure how to continue from there...<o></o>

 

[CharlesChuckieCharles]

each control showing data should have its rowsource = Temptable.Fieldname

US not being able to see what you have designed

means that you have to ask the right question?

the rowsource property is on almost all controls

 

[Swift_74d]

OK, let me rephrase the question...

I had a query (seen in the sql) and a continuous form with header.
In its header there were several check boxes and a command button.
The forms control source was the query and the command button would requery.
This allowed the user to select the check boxes needed and click the button to refresh the data in the detail section.

What do i need to do to recreate this in VBA?

 

[xenou]

You were using a SELECT INTO to create your "sort" table. This has to destroy (delete) the existing table to create the new one. But the very form you are running the code from is probably the object that is using the existing "sort" table.

You could instead use a two-step delete/append so that the table does not need to be re-created.

 

[Swift_74d]

A lot of times with researching how and what to do, knowing the appropriate search terms help. I want to thank everyone that helped but it would seem the missing piece to my puzzle was me.RecordSource<?xml:namespace prefix = o ns = "urn:schemas-microsoft-comfficeffice" /><o></o>
<o></o>
I came up with the below code that simulates the sql version of the query perfectly....for now.

Sub mySorter()
 
    Dim strWhere As String
    Dim a As Long
    Dim templen As Long
    Dim sSQL As String
 
    strWhere = ""
 
    For a = 1 To 7
        If Me.Controls("matchk" & a).Value = True Then
            strWhere = strWhere & "Materials.M_Name = '" & Me.Controls("matchk" & a & "cpt").Caption & "' Or "
        End If
    Next a
 
    templen = Len(strWhere) - 4
 
    If Len(strWhere) <= 0 Then
        strtemp = "Materials.M_Name is null"
    Else
        strWhere = Left$(strWhere, templen)
    End If
 
    sSQL = "SELECT Product.P_ID, Product.P_Name, Product.P_Disc, Product.P_LBs, Product.P_Catagory " & _
            "FROM Product " & _
            "RIGHT JOIN (Build RIGHT JOIN Materials ON Build.B_Mat = Materials.M_ID) ON Product.P_ID = Build.B_Item " & _
            "WHERE " & strWhere & _
            "GROUP BY Product.P_ID, Product.P_Name, Product.P_Disc, Product.P_LBs, Product.P_Catagory;"
    Me.RecordSource = sSQL
End Sub