Hi, Im trying to make a db for my small bussiness.
I have a main form(frm1) with unbounts objects and a subform(qry1) with the result. When i dclick on the filtered records it shows the details of the record in a new form (frm2) via its subform(qr2(qry1+some other tables).
As im trying to have the ability showing the details of the whole group of filtered records and not just the selected(on focus) the solution that iam able to thought, is making a temp table from the qry1 which icould use as an element of my qry's2 structure, and here comes the problem:
The query's1 clause "where" string, is builded from a function. When im trying to run an append query with the function as "where", access gives me type mismatch errors.
code:


Code:
CurrentDb.Execute "INSERT INTO tblTmpSrch ( CAT1, SUPNAME, STYLE, DES, ID_GD, SX, REF, SEASON, ID_SX, ID_SP, ID_SSN, [real], RPRICE ) " & vbCrLf & _
"SELECT CAT1.CAT1, SUPPLIER.SUPNAME, GOODS.STYLE, GOODS.DES, GOODS.ID_GD, SX.SX, GOODS.REF, SEASON.SEASON, SX.ID_SX,SUPPLIER.ID_SP, SEASON.ID_SSN, [TQTY]-[SUMSOLD] AS [real], GOODS.RPRICE " & vbCrLf & _
"FROM CAT1 INNER JOIN ((SUPPLIER INNER JOIN ((CAT3 INNER JOIN (SX INNER JOIN CAT4 ON SX.ID_SX = CAT4.ID_SX) " & vbCrLf & _
"ON CAT3.ID_CT3 = CAT4.ID_CT3) INNER JOIN (SEASON INNER JOIN (GOODS INNER JOIN qrySumSalesItems " & vbCrLf & _
"ON GOODS.ID_GD = qrySumSalesItems.ID_GD) ON SEASON.ID_SSN = GOODS.ID_SSN) ON CAT4.ID_CT4 = GOODS.ID_CT4) " & vbCrLf & _
"ON SUPPLIER.ID_SP = GOODS.ID_SP) INNER JOIN CAT2 ON (CAT2.ID_CT2 = CAT4.ID_CT2) AND (SEX.ID_SX = CAT2.ID_SX)) " & vbCrLf & _
"ON CAT1.ID_CT1 = CAT3.ID_CT1 & Wherefilter ;"
I figured out that if the txt fields from my wherefilter are with double quotes the action query is working perfectly but now the where filter function is not working
wherefilter function code:
Code:
Public Function WhereFilter() As Variant
    Dim varWhere As Variant
    Dim varCat1 As Variant
    Dim varItem As Variant
    Dim intIndex As Integer

    

    varWhere = Null  ' Main filter
    varCat1 = Null  ' Subfilter used for GeneralCategory (CAT1)
    
     
    ' Check for LIKE Style
    If Forms!frmSearch.txtStyle > "" Then
        varWhere = varWhere & "GOODS.STYLE LIKE """ & Forms!frmSearch.txtStyle & "*"" AND "
    End If
    
       
    ' Check for SupplierID
    If Forms!frmSearch.cmbSupplier > 0 Then
        varWhere = varWhere & "SUPPLIER.ID_SP = " & Forms!frmSearch.cmbSupplier & " AND "
    End If

   ' Check for SeasonID 
    If Forms!frmSearch.cmbSeason > 0 Then
        varWhere = varWhere & "SEASON.ID_SSN = " & Forms!frmSearch.cmbSeason & " AND "
    End If
    
    ' Check for SexID
    If Forms!frmSearch.cmbSx > 0 Then
        varWhere = varWhere & "SX.ID_SX = " & Forms!frmSearch.cmbSx & " AND "
    End If
    
    ' Check for GenCategory Items in multiselect list
    For Each varItem In Forms!frmSearch.lstCat1.ItemsSelected
       varCat1 = varCat1 & "[CAT1].[CAT1] = """ & Forms!frmSearch.lstCat1.ItemData(varItem) & """ Or "
        
    Next
    
    ' Test to see if we have subfilter for GenCategory Items...
    If IsNull(varCat1) Then
     
    Else
I'm stuck for the last 24 h, everything im trying it just doesn't work
HELP!