parameter value to be determined in code

[ss6857]

I'm trying to create a query. The only part that is giving me trouble is the Parameter value. Even though the parameter value is defined (I think) the "enter parameter value" still pops up. I don't want that box to pop up and I want the query to show the results for "objname." Any suggestions?

Private Sub Command93_Click()

Dim objname As String
Dim db As DAO.Database
Dim MyQry As DAO.QueryDef
Dim rs As Recordset

Me.Combo7.SetFocus
objname = Me.Combo7.Text
If Me.Check90 = True Then
Set db = CurrentDb
Set MyQry = db.CreateQueryDef("TempQry", "SELECT dbo_qry_MasterPriceTable.Name, dbo_qry_MasterPriceTable.Category, " & _
"dbo_qry_MasterPriceTable.Product, dbo_qry_MasterPriceTable.ProductDesc As Description, dbo_qry_MasterPriceTable.Weight, " & _
"dbo_qry_MasterPriceTable.Price, dbo_qry_MasterPriceTable.UnitPrice, dbo_qry_MasterPriceTable.Level_Desc As [Price Level] " & _
"FROM dbo_qry_MasterPriceTable " & _
"Where dbo_qry_MasterPriceTable.name = objname " & _
"ORDER BY dbo_qry_MasterPriceTable.Num;")
'Set rs = MyQry.OpenRecordset()
DoCmd.OpenQuery "TempQry", acViewNormal, acEdit
MyQry.Parameters(0) = objname
End If
End Sub

 

[PNGBill]

Is this a text box control on a form ?

objname = Me.Combo7.Text

If so, why the .Text ? Do you need to set focus ?

add:
MsgBox objname

in your code after the above and you should see the value of objname.

 

[ss6857]

Its a combo box in a form. The combo displays the names and then it finds the name chosen in the MasterPriceTable and creates a query just using the names chosen (that's what it's suppose to do.) and it is defined.. when I added the message box it came up with the right name, it just doesn't want be seen as a parameter value, I guess. It runs through the code fine, like no errors come up or anything.. it just still makes me "enter a parameter value" when I thought I already did it in my code.

 

[PNGBill]

Try this

"Where dbo_qry_MasterPriceTable.name =" & objname & " " & _

 

[ss6857]

Now what it is doing is that the "Enter Parameter Value" box still displays, the objname is showing up as the label or title, I guess? I'm not sure what you would call it, it's the text that is above the box that you are suppose to type in the paramenter value. It's still requiring me to type in the box.

 

[PNGBill]

Does this work

"Where dbo_qry_MasterPriceTable.name =" & objname & _

 

[ss6857]

no... because now it reads it as "Where dbo_qry_MasterPriceTable.name =ogjnameORDER BY dbo_qry.......

 

[PNGBill]

Try this

"Where dbo_qry_MasterPriceTable.name = " & Chr(34) &  objname & Chr(34) & _

 

[ss6857]

Sorry for not responding but I have been off of work until now. But it worked! Thank you so much!

 

[PNGBill]

Glad to help. I am not sure why Chr(34) works where " " won't but it has something to do with the data being a string - I think.
Others may have an explanantion but so long as it works

 

[spikepl]

#10 OP did as many others: put a long SQL-statement together and hoped it will work.

1. Put the SQL together in a string.
2. debug.print TheSQLString
3. Inspect it
4. When it works, use that string where it's needed.

As to the Char(34) - if you debug.print the string in each attempt, then you'll see why that worked in the last case, and not the previous suggestions, recalling the SQL syntax rules for values of type string vs eg. those of type number