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]

you should post your code AND your SQL (even if it's a rowsource on a control)

 

[Swift_74d]

Query's sql is...

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_Product
WHERE (((Materials.M_Name)=IIf([Forms]![Sorter]![Matchk1],"Wood Peg") Or (Materials.M_Name)=IIf([Forms]![Sorter]![Matchk2],"Nail") Or (Materials.M_Name)=IIf([Forms]![Sorter]![Matchk3],"Metal Sheeting") Or (Materials.M_Name)=IIf([Forms]![Sorter]![Matchk4],"Wood Plank") Or (Materials.M_Name)=IIf([Forms]![Sorter]![Matchk5],"Screw") Or (Materials.M_Name)=IIf([Forms]![Sorter]![Matchk6],"Paint")))
GROUP BY Product.P_ID, Product.P_Name, Product.P_Disc, Product.P_LBs, Product.P_Catagory;

VBA so far is kinda patchy as i'm learning by trial and error, but here's what i got....

Sub Sort()
    Dim strSQL As String
    Dim strWhere As String
    Dim a As Long
    Dim templen As Long
 
    strWhere = ""
 
    For a = 1 To 6
        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
        strWhere = "Materials.M_Name is null"
    Else
        strWhere = Left$(strWhere, templen)
    End If
    strSQL = "SELECT Materials.M_Name, Materials.M_ID " & _
             "INTO tblTemp " & _
             "FROM Materials " & _
             "WHERE " & strWhere & ";"
    DoCmd.SetWarnings False
    DoCmd.RunSQL strSQL
    DoCmd.SetWarnings True
End Sub

 

[CharlesChuckieCharles]

ON your form

Create an Option Group with two options "Use OR", "Use And"

These by default return a value from the Option Group could be called FRAME1 etc
of 1 and 2 respectively

Then

   Dim strCondition As String
   
   If Me.Frame3.Value = 1 Then 'or
      strCondition = "OR"
   Else
      strCondition = "And"
   DoEvents
 
strWhere = ""

Then alter your where clause builder by

& Me.Controls("matchk" & a & "cpt").Caption & "' " & strCondition  & " "
        End If

 

[Swift_74d]

Thanks for the input, but concatenating variables is not where my problem is. Where i'm getting stuck is replicating the query (the sql) in vba. I have gotten as far as making a temp table in vba using the Materials Table filtered by the check boxes on the form.

How do i then filter the Build Table with by the results of the Temp Table? (seeing that will allow me to figure out how to then do it then to the Product table)
What would the form reference to display the appropriate records?

 

[Swift_74d]

Alright,
I am now as far as having VBA create a sorted table for the detail section of the form to be based off of, however i get a..

Run-time error '3211':
The database engine could not lock table 'Sort' because it is already in use by another person or process.

The code (however ugly) i came up with is below.
How do i get the form to base itself off of the table created by below code?

Sub Sort_Click()
    Dim strSQL As String
    Dim strWhere As String
    Dim a As Long
    Dim templen As Long
 
    strWhere = ""
 
    For a = 1 To 6
        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
        strWhere = "Materials.M_Name is null"
    Else
        strWhere = Left$(strWhere, templen)
    End If
 
    strSQL = "SELECT Materials.M_Name, Materials.M_ID " & _
             "INTO tblTemp " & _
             "FROM Materials " & _
             "WHERE " & strWhere & ";"
    DoCmd.SetWarnings False
    DoCmd.RunSQL strSQL
    DoCmd.SetWarnings True
    strSQL = "SELECT Build.B_Item, Build.B_Mat " & _
             "Into tblTemp2 " & _
             "From tblTemp INNER JOIN Craft ON tblTemp.M_ID = Build.B_Mat;"
    DoCmd.SetWarnings False
    DoCmd.RunSQL strSQL
    DoCmd.SetWarnings True
 
    strSQL = "SELECT Product.* " & _
             "Into Sort " & _
             "FROM tblTemp2 INNER JOIN Items ON tblTemp2.B_Item = Product.P_ID " & _
             "GROUP BY Product.P_ID;"
    DoCmd.SetWarnings False
    DoCmd.RunSQL strSQL
    DoCmd.SetWarnings True
End Sub

 

[CharlesChuckieCharles]

SORT is a reserved word change it to MySortedTable

you may also have it open in another window which is a no no with create table


BUT

so the problem is
"AND"

you want to list products which use a number of materials but they must use ALL of those materials to be selected.

So the temp table should include the buildID and the corresponding product id

like

strSQL = "SELECT Build.B_Product, Materials.M_Name, Materials.M_ID COUNT(Materials.M_ID ) as countofMaterials " & _
"INTO tblTemp " & _
"FROM Build, Materials " & _
"WHERE " & strWhere & " and Materials.M_ID =Build.B_Mat GROUP BY Build.B_Product, Materials.M_Name, Materials.M_ID ;"


this creates you tblTemp

if you have selected OR's then you only care that the count is > 0
if you have selected AND's then you care that the count is = the number of products selected in the controls


Select products field, From products
From products, TbleTemp
where Product.P_ID = tbletemp.B_Product and tbleTemp.countofMaterials > 0 (or)

where Product.P_ID = tbletemp.B_Product and tbleTemp.countofMaterials = 6 (AND)

There'll be some cleaning up but These two steps would work, or use a subquery for an all-in-one solution!

 

[Swift_74d]

No Charles, the AND/OR is not the problem i need to solve. I know how to do that, and will plug it in after i solve the issue of how to recreate the effect of the sql query in VBA. The only reason i mentioned the AND/OR was to explain why i was using VBA and not the built in query builder.

Before this whole VBA conversion attempt i had a form based on a query. That query had critera based on choices made on the forms header. Now that i need to be able to dynamicly change the comparitive operators i need to recreate it in vba. I can get a far as having VBA create a filtered table, but if i have the form use that table as the control sorce i get the mentioned error.

How do i take the table that VBA has stored and have the form show the records it contains?
(also realized that i'm trying to have the vba filtering be performed via command button on the form)

 

[CharlesChuckieCharles]

"How do i take the table that VBA has stored and have the form show the records it contains?"

if your showing a datasheet form

put the tablename in the rowsource property of the form

 

[Swift_74d]

And if i'm trying for continuous?

*Edit* now that i try datasheet it wont work on several levels....it gets rid of the command button and check boxs that perform the filter in the first place.