OpenRecordset() can't take parameter inside query?

[jackie]

Dear Gurus:
I have an event procedure as follow:

Private Sub cmdEmail_Unmanaged_Click()
Dim dbs As Database, ic As Recordset
Dim strToWhom As String
Dim strMsgBody As String
Dim strQry As String

Set dbs = CurrentDb
strQry = "SELECT distinct EMAIL_ADDRESS FROM
TRIUMPH_FXF_SALESCONTEST_ENTRY where
EMPL_NBR=[forms]![frmlEmployeeNbr]![emp_no]"
Set ic = dbs.OpenRecordset(strQry, dbOpenForwardOnly)
While Not ic.EOF
strToWhom = ic![EMAIL_ADDRESS]
strMsgBody = "Please find attached file for the
information you recently_ entered for the FedEx Freight Sales Contest. This
entry failed the contest’s validation process for the following reason: ENTRY
IS NOT A MANAGED ACCOUNT. "

' Send query in excel format.
DoCmd.SendObject acSendQuery, "Validate_Acct_NonManaged_Send",
acSpreadsheetTypeExcel9, _
strToWhom, , , "Invalid Entry for Freight Sales Contest ", _
strMsgBody, False
ic.MoveNext
Wend

ic.Close
Set ic = Nothing
End Sub

The code stops at the openRecordset() and says "too few parameters, expected
1". What is this error? How can I get it work?

Thanks,

JC

[Ofer]

Are you sure that the
[forms]![frmlEmployeeNbr]![emp_no] returns a value?

While the code stop, open the immidiate window and type
?forms![frmlEmployeeNbr]![emp_no]
And check which value it returns
--
If I answered your question, please mark it as an answer. That way, it will
stay saved for a longer time, so other can benifit from it.

Good luck

[jackie]

Yes, [forms]![frmlEmployeeNbr]![emp_no] does return value in the immediate
window, the same value in the form

I also tried another way to pass parameter, such as convert sql into query
and use criteria [enter the empl_no] in the EMP_NO field, still the same
error.

desperately need help. project due this week,

Very apprecated

[Ofer]

Try this if the EMPL_NBR field type is number

strQry = "SELECT distinct EMAIL_ADDRESS FROM
TRIUMPH_FXF_SALESCONTEST_ENTRY where

EMPL_NBR= " & forms![frmlEmployeeNbr]![emp_no]

Try this if the EMPL_NBR field type is text

strQry = "SELECT distinct EMAIL_ADDRESS FROM
TRIUMPH_FXF_SALESCONTEST_ENTRY where

EMPL_NBR= '" & forms![frmlEmployeeNbr]![emp_no] & "'"

[jackie]

Thanks a lot, it works!!!

[Meitatsu]

I have the same problem that instead of SQL, I am trying to use a query with
a criterion refering to a combo box in a form. like the following statement.
Ofer's solution does work in case of SQL, does it mean that no way to use a
query in such case?
Any ideas would be much appreciated!

Private Sub Command3_Click()
On Error GoTo Err_Command3_Click
Dim DBS As DAO.Database
Dim rstA1, rstA2 As DAO.Recordset

Dim stDocName, strQueryName, strQueryName1, strQRY As String
Dim strB1 As String
Dim stLinkCriteria As String
Dim blnA1 As Boolean
Dim bytB1 As Byte
Dim lngA1 As Long

strQueryName = "20211CreateAsmblySchedul"
strQueryName1 = "20213AsmblySchedul"
Set DBS = CurrentDb()

'Error message: too few parameters, expected 1
Set rstA1 = DBS.OpenRecordset(strQueryName1)
lngA1 = rstA1.RecordCount
If lngA1 = 0 Then blnA1 = True Else blnA1 = False
rstA1.Close

If blnA1 = False Then
GoTo Open_Form
Else
strB1 = "No schedule data complying with your query, would you like
to create one?"
bytB1 = msgbox(strB1, vbYesNo)
End If
Select Case bytB1
Case 6
DoCmd.Requery (strQueryName)
GoTo Open_Form
Case Else
GoTo Exit_Command3_Click
End Select

Open_Form:
stDocName = "0075_Edit_Schedul"
DoCmd.OpenForm stDocName, , , stLinkCriteria

Exit_Command3_Click:
Exit Sub

Err_Command3_Click:
msgbox Err.Description
Resume Exit_Command3_Click

End Sub
--
Meitatsu
Taiwan

[Dirk Goldgar]

In news:7A9B0B6B-D1D8-45F8...@microsoft.com,

Meitatsu <Meit...@discussions.microsoft.com> wrote:
> I have the same problem that instead of SQL, I am trying to use a
> query with a criterion refering to a combo box in a form. like the
> following statement. Ofer's solution does work in case of SQL, does
> it mean that no way to use a query in such case?
> Any ideas would be much appreciated!
>
> Private Sub Command3_Click()
> On Error GoTo Err_Command3_Click
> Dim DBS As DAO.Database
> Dim rstA1, rstA2 As DAO.Recordset

**NOTE** the above line defines rstA2 as a DAO.Recordset, but defines
rstA1 only as a Variant. You should write this:

Dim rstA1 As DAO.Recordset, rstA2 As DAO.Recordset

> Dim stDocName, strQueryName, strQueryName1, strQRY As String

Similarly, the above line declares only strQRY as a String.

> Dim strB1 As String
> Dim stLinkCriteria As String
> Dim blnA1 As Boolean
> Dim bytB1 As Byte
> Dim lngA1 As Long
>
> strQueryName = "20211CreateAsmblySchedul"
> strQueryName1 = "20213AsmblySchedul"
> Set DBS = CurrentDb()
>
> 'Error message: too few parameters, expected 1
> Set rstA1 = DBS.OpenRecordset(strQueryName1)

Using a stored query, you have to explicitly resolve the parameters
before you open a recordset from it. You can do this by manipulating
the QueryDef object, like this:

Dim DBS As DAO.Database

Dim rstA1 As DAO.Recordset
Dim rstA2 As DAO.Recordset

Dim qdf As DAO.QueryDef
Dim prm As DAO.Parameter

Dim stDocName As String
Dim strQueryName As String
Dim strQueryName1 As String
Dim strQRY As String

Dim strB1 As String
Dim stLinkCriteria As String
Dim blnA1 As Boolean
Dim bytB1 As Byte
Dim lngA1 As Long

strQueryName = "20211CreateAsmblySchedul"
strQueryName1 = "20213AsmblySchedul"
Set DBS = CurrentDb

Set qdf = DBS.QueryDefs(strQueryName1)
With qdf

' Resolve parameters.
For Each prm In .Parameters
prm.Value = Eval(prm.Name)
Next prm

' Open recordset.
Set rstA1 = .OpenRecordset

End With

--
Dirk Goldgar, MS Access MVP
www.datagnostics.com

(please reply to the newsgroup)