query doesn't work 1

[ailyn]

I need to show only the records that match the criteria:

Dim stDocName As String
Dim stVal As String
Dim stWhere As String
Dim stFilter As String

stDocName = "ProformaSearch"
stVal = "='" & Me.SellerIdctrl.Value & "'"
stWhere = "[SellerId] " & stVal
stFilter = "SELECT ProformaId " & _
"FROM Proformas " & _
"WHERE OrderId = (SELECT OrderId " & _
"FROM Orders " & _
"WHERE " & stWhere & "));"
If IsNull(stFilter) Then
MsgBox "Sorry, no proformas available for this seller", vbInformation
Else
DoCmd.OpenReport stDocName, acViewPreview, , , , stFilter
End If

this is my code. I tried the stFilter on OpenArgs and on the Filter position. they both gave me the same results. they display all records instead of only those for the Seller I selected (he comes from SellerIdctrl). I also cannot use the stFilter in the 'Where' condition for opening the report. Any idea on why ti doesn't work? I want it to display only the records for one seller at a time.

 

[PHV]

Assumptions: the underlaying query of the ProformaSearch report is named qryProformat and contains a field named SellerID.
If Nz(DCount("*", qryProformat, stWhere), 0) < 1 Then
MsgBox "Sorry, no proformas available for this seller", vbInformation
Else
DoCmd.OpenReport stDocName, acViewPreview, , stWhere)
End If

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

 

[ailyn]

Sorry but there is not such a query because all I have is the control in the form where I get the sellerId (the form is for the sellers and there is a button for opening all their corresponding proformas) The stWhere is of no use here for opening the report because the table proformas doesn't have that field included.
My apologies for not espcifying this earlier:

tblProformas:
ProformaId
ProformaDate
OrderId
TOTAL

tblOrders
OrderId
OrderDate
SellerId
CustomerId
VAT
Discount
FreightCharges

tblSelllers
SellerId
company
name
tel...

From the formSellers, that feeds on tblSellers, we have a subform that feeds on tblOrders joined by SellerId, obviously. There is the button for the proformas whose code I included here. I extracts the results of the hidden value SellerIdctrl (that contains Orders.SellerId) and is supposed to open a report with all the proformas made by that seller only. This report feeds on tblProformas.
the query I meant didn't work where the ones already included in the code.
I have the same problem when I try to filter similar complicated queries. The filters end up showing all the records, i.e. they don't work properly I want only the selected records. any suggestions on this?

 

[ailyn]

Ok, the Dlookup and an external query were the key! Now I finally made it work. I made this query:

SELECT Proformas.ProformaId
FROM Orders INNER JOIN Proformas ON Orders.OrderId = Proformas.OrderId
WHERE (((Orders.SellerId)=[Forms]![Sellers].[SellerSumSubform]![SellerIdctrl]));

& changed my code to this:

Dim stDocName As String
stDocName = "ProformaSearch"
If Nz(DCount("*", "SellerSubP"), 0) < 1 Then
MsgBox "Sorry, no proformas available for this seller", vbInformation
Else
DoCmd.OpenReport stDocName, acViewPreview, "SellerSubP"
End If

Thanks a lot PHV. The problem was that I was trying the query from the form and I was not checking properly for the results.