Error in the code, need some insight! 2

[haerion]

Hi all, I'm trying to get a sql to work in code, but there is a mistake somewhere in the code, can't find what i need to change for it to work, here is the code:

strsql = "SELECT [PO Header].Vendor, [PO Detail].Part, [PO Detail].Transfert1, [PO Detail].TrsfStore, [PO Detail].Order1, [PO Detail].Comment, [PO Detail].LSTCOST, [DFR Detail].ORDER " & _
         "FROM ([PO Header] INNER JOIN [Date du Traitement de PO] ON [PO Header].Date = [Date du Traitement de PO].DateTraitement) INNER JOIN ([PO Detail] LEFT JOIN [DFR Detail] ON [PO Detail].STPart = [DFR Detail].STITEM) ON [PO Header].AccessID = [PO Detail].AccessID " & _
         "WHERE [PO Detail].[Store#]= '" & [Forms]![vendororder]![ChkStore] & "'" & _
         "AND " & IsSelectedVar("VendorOrder", "txtVendor#", [Vendor]) = -1

The problem is at the IsSelectedVar that is in a module and filter all the selected item on the form VendorOrder and the list box txtvendor#.

Thanks for your help!

 

[Remou]

I think you should post the code for IsSelectedVar.

 

[haerion]

Here

Function IsSelectedVar( _
        strFormName As String, _
        strListBoxName As String, _
        varValue As Variant) _
            As Boolean
    'strFormName is the name of the form
    'strListBoxName is the name of the listbox
    'varValue is the field to check against the listbox
    Dim lbo As ListBox
    Dim item As Variant
    If IsNumeric(varValue) Then
        varValue = Trim(Str(varValue))
    End If
    Set lbo = Forms(strFormName)(strListBoxName)
    For Each item In lbo.ItemsSelected
        If lbo.ItemData(item) = varValue Then
            IsSelectedVar = True
            Exit Function
        End If
    Next
End Function

 

[lespaul]

additionally, you're not passing the value of the txtVendor# box to the process, you're passing "txtVendor#" that actual text, not a number at all. Maybe you're looking for:

IsSelectedVar("VendorOrder", [Forms]![VendorOrder]![txtVendor#], [Vendor])

Leslie

Anything worth doing is a lot more difficult than it's worth - Unknown Induhvidual

Essential reading for database developers:
The Fundamentals of Relational Database Design
Understanding SQL Joins

 

[Remou]

I tried the sql, and found that you need to include the function in the quotes:

"AND IsSelectedVar("VendorOrder", "txtVendor#", [Vendor]) = -1"

 

[Remou]

... and make sure that the function is in a public module.

 

[haerion]

Will need to made some modification in this one:

"AND IsSelectedVar("VendorOrder", "txtVendor#", [Vendor]) = -1"

The " for VendorOrder and txtVendor finish the sentence and make this an error, how can I go around that?

 

[PHV]

AND IsSelectedVar('VendorOrder','txtVendor#',[Vendor])=True"


Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[haerion]

Hum... as soon as I select something i got an error of incompatibility and it select this line in the code:

Set rst = db.OpenRecordset(strsql, dbOpenDynaset)

Thanks for all your help, greatly appreciated!

 

[PHV]

You can't use an UDF in a Recordset.
You may consider a QueryDef.

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[haerion]

Sorry, but could you tell me what is a UDF?

 

[PHV]

User Defined Function

 

[haerion]

hum... dont mind, User define fonction

 

[haerion]

Right now, I already got a query named "Email Result to Vendor" but since I cannot get form reference to work in a recordset, I tried the sql one, but since you saying that UDF doesn't work in recordset, I don't know what to try.

 

[PHV]

Have a look at the Parameters collection and the Eval function

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[PHV]

Another way is to build a list of the selected items and use the IN operator in your WHERE clause.

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[haerion]

I used the IN operator idea and it works great, thanks a lot PHV, Remou and Lespaul, have a great day, finally my code look like this:

Private Sub Commande124_Click()
On Error GoTo Err_cmd124_Click

Dim SentTo As String
Dim body As String

   Dim db As DAO.Database, rst As DAO.Recordset, Build As String
   Dim strsql As String
   strsql = "SELECT Fournisseurs.[Supplier#] " & _
            "FROM [Email Result to vendor Selected item], Fournisseurs " & _
            "WHERE (((Fournisseurs.[Supplier#]) In ([Email Result to vendor Selected item].[Supplier#])))"
   
            '"SELECT [PO Header].Vendor, [PO Detail].Part, [PO Detail].Transfert1, [PO Detail].TrsfStore, [PO Detail].Order1, [PO Detail].Comment, [PO Detail].LSTCOST, [DFR Detail].ORDER " &
            '"FROM ([PO Header] INNER JOIN [Date du Traitement de PO] ON [PO Header].Date = [Date du Traitement de PO].DateTraitement) INNER JOIN ([PO Detail] LEFT JOIN [DFR Detail] ON [PO Detail].STPart = [DFR Detail].STITEM) ON [PO Header].AccessID = [PO Detail].AccessID " &
            '"WHERE [PO Detail].[Store#]= '" & [Forms]![vendororder]![ChkStore] & "'"
            '"AND " & Eval("IsSelectedVar('VendorOrder', 'txtVendor#', [Vendor])") = -1


   Set db = CurrentDb
   Set rst = db.OpenRecordset(strsql, dbOpenDynaset)
   
   If Not rst.BOF Then
      Do
         If Build <> "" Then
            Build = Build & "-" & CStr(rst![Supplier#])
         Else
            Build = "Commande " & CStr(rst![Supplier#])
         End If
         
         rst.MoveNext
      Loop Until rst.EOF
   End If
   
   Set rst = Nothing
   Set db = Nothing

body = "Voir la commande ci-jointe." & Chr(13) & _
"SVP confirmer les prix ainsi que la date de livraison par fax ou courriel!" & Chr(13) & Chr(13) & Chr(13) & _
"Merci et bonne journée!" & Chr(13) & Chr(13) & Chr(13) & Chr(13) & _
"Si vous ne pouvez pas ouvrir ce fichier, vous pouvez télécharger le logiciel de microsoft en suivant ce lien:" & Chr(13) & _
"[URL unfurl="true"]http://support.microsoft.com/kb/q175274/"[/URL] & Chr(13)


DoCmd.SendObject acSendReport, "Email Qty to vendor", acFormatXLS, , , , Build


Err_cmd124_Click:
Exit Sub

End Sub