Passing output Fields from a Query to controsl on Form

[singhpriyanka]

I have a query qrySimCatSolverType as follows-
SELECT tblSimCategory.SimCategoryID, tblProductSim.Product, tblProductSim.Application, tblProductSim.SolverTypes, tblProductInfo.Productdescription, tblOwner.OwnerFirstName, tblOwner.OwnerLastName, tblOwner.OwnerLoc, tblOwner.DomainID
FROM tblOwner INNER JOIN (tblProductInfo LEFT JOIN (tblSimCategory RIGHT JOIN tblProductSim ON tblSimCategory.SimCategoryID = tblProductSim.SimCategory) ON tblProductInfo.Productname = tblProductSim.Product) ON tblOwner.OwnerID = tblProductInfo.OwnerID
WHERE (((tblSimCategory.SimCategoryID) Like "*" & [Forms]![frmAdminShowRecords]![TextSimCatSolver] & "*")) OR (((tblProductSim.SolverTypes) Like "*" & [Forms]![frmAdminShowRecords]![TextSimCatSolver] & "*"));

The results of could be multiple records in the different fields or just one record. Depends upon the input in the Textbox

This is basically taking the input from a textbox called "TextSimCatSolver" and displaying results about SimCategoryID, SolverTypes, Product, Application, Productdescription, OwnerFirstName, OwnerLastName, OwnerLoc, DomainID from joined tables as indiacted above. The query runs perfectly fine on its own, but when I try to write it in a string statemnet to open the recordset to access the fileds and assign these to different controls like Combob box , textbox and combobox on my form, I get errors as "Run Time Error 13, Type Mismatch". This what i have so far..

Dim db As dao.Database
Dim rs As dao.Recordset
Dim st As String
st = "SELECT tblSimCategory.SimCategoryID, tblProductSim.Product, tblProductSim.Application, tblProductSim.SolverTypes, tblProductInfo.Productdescription, tblOwner.OwnerFirstName, tblOwner.OwnerLastName, tblOwner.OwnerLoc, tblOwner.DomainID FROM tblOwner INNER JOIN (tblProductInfo LEFT JOIN (tblSimCategory RIGHT JOIN tblProductSim ON tblSimCategory.SimCategoryID = tblProductSim.SimCategory) ON tblProductInfo.Productname = tblProductSim.Product) ON tblOwner.OwnerID = tblProductInfo.OwnerID WHERE ((((tblSimCategory.SimCategoryID) Like " * " & [Forms]![frmAdminShowRecords]![TextSimCatSolver] & " * ")) OR (((tblProductSim.SolverTypes) Like " * " & [Forms]![frmAdminShowRecords]![TextSimCatSolver] & " * ")))"
Debug.Print st
Set rs = CurrentDb.OpenRecordset(st)
Me.ListSimCategory = ra!SimCategoryID
Me.ListSolverTypes = ra!SolverTypes
Me.ComboProduct = ra!Product
Me.ComboApplication = ra!Application
Set rs = Nothing

Set db = Nothing

Pleas help me!
Thanks,
Priyanka

[June7]

Why don't you use query as source for form or report then apply filter criteria to form or report?

If the recordset results in multiple records, then code will take values only from first record.

Replace the inner quote marks used as text delimiters with apostrophes. Only the one outer pair of quote marks should remain when constructing SQL in VBA.

[singhpriyanka]

Thanks June7. I changed the delimiters to apostrophes. Didn't help. Now I get "Too few Parameters" error.

st = "SELECT tblSimCategory.SimCategoryID, tblProductSim.Product, tblProductSim.Application, tblProductSim.SolverTypes, tblProductInfo.Productdescription, tblOwner.OwnerFirstName, tblOwner.OwnerLastName, tblOwner.OwnerLoc, tblOwner.DomainID FROM tblOwner INNER JOIN (tblProductInfo LEFT JOIN (tblSimCategory RIGHT JOIN tblProductSim ON tblSimCategory.SimCategoryID = tblProductSim.SimCategory) ON tblProductInfo.Productname = tblProductSim.Product) ON tblOwner.OwnerID = tblProductInfo.OwnerID WHERE ((((tblSimCategory.SimCategoryID) Like ' * ' & [Forms]![frmAdminShowRecords]![TextSimCatSolver] & ' * ')) OR (((tblProductSim.SolverTypes) Like ' * ' & [Forms]![frmAdminShowRecords]![TextSimCatSolver] & ' * ')))"

[June7]

Sorry, my mistake. You are concatenating variables (references to controls). Will need to use closing and opening quote marks for each literal string part. Suggest removing spaces from around asterisk.

... WHERE ((((tblSimCategory.SimCategoryID) Like '*'" & [Forms]![frmAdminShowRecords]![TextSimCatSolver] & "'*')) OR (((tblProductSim.SolverTypes) Like '*'" & [Forms]![frmAdminShowRecords]![TextSimCatSolver] & "'*')))"

[singhpriyanka]

Sorry, my mistake. You are concatenating variables (references to controls). Will need to use closing and opening quote marks for each literal string part. Suggest removing spaces from around asterisk.

... WHERE ((((tblSimCategory.SimCategoryID) Like '*'" & [Forms]![frmAdminShowRecords]![TextSimCatSolver] & "'*')) OR (((tblProductSim.SolverTypes) Like '*'" & [Forms]![frmAdminShowRecords]![TextSimCatSolver] & "'*')))"

Hello, Sorry If I came out wrong in my explanation of the query. The query is not concatenating as you mentioned but comparing the text value in the textbox to throw results

For example:
If user types Dynamic* in the textbox then it should display all the selected fields from the joined tables ad denoted in the query. So the query that works fine is defined as follows, I ahve tested it but when I assign it to a string in VBA, I get errors.

SELECT tblSimCategory.SimCategoryID, tblProductSim.Product, tblProductSim.Application, tblProductSim.SolverTypes, tblProductInfo.Productdescription, tblOwner.OwnerFirstName, tblOwner.OwnerLastName, tblOwner.OwnerLoc, tblOwner.DomainID
FROM tblOwner INNER JOIN (tblProductInfo LEFT JOIN (tblSimCategory RIGHT JOIN tblProductSim ON tblSimCategory.SimCategoryID = tblProductSim.SimCategory) ON tblProductInfo.Productname = tblProductSim.Product) ON tblOwner.OwnerID = tblProductInfo.OwnerID
WHERE (((tblSimCategory.SimCategoryID) Like "*" & [Forms]![frmAdminShowRecords]![TextSimCatSolver] & "*")) OR (((tblProductSim.SolverTypes) Like "*" & [Forms]![frmAdminShowRecords]![TextSimCatSolver] & "*"));

I tried your solution in the previous post, but I get errors that the opeartor is missing. Please advise

[Gicu]

Save the SQL statement that works as a query (i.e. qrySimCategory) then use the following code to get your recordset:

Dim db as DAO.database
Dim qdf as DAO.QueryDef
Dim rst as DAO.Recordset
Dim prm as Parameter

Set db=CurrentDb
Set qdf=db.QueryDefs("qrySimCategory")

For Each prm in qdf.parameters
    prm.value=Eval(prm.name)
Next prm

Set rst=qdf.openrecordset
'do whatever else with it

Cheers,
Vlad

[June7]

Your last example does not use apostrophe delimiters as shown in my suggestion.

And yes, the code is concatenating literal text strings with variables.