I need help in this, thanks million

[ihere]

I have this db and for automtic sending emil based on query to send asingle record to specific contact email
but I have a problem with SELECT with a message said ( too few parameters . expected1) i do not know how to solve that

Private Sub Command6_Click()
'Dim MYDB As Database, RST11 As Recordset, rstemp As Recordset, mto As String, empstring As String, sqlstring As String, memp As string, med As string
Dim MYDB As dao.Database, RST11 As Recordset, rstemp As Recordset, mto As String, empstring As String, sqlstring As String, mco As String, memp As String, med As String
Dim Qdf As QueryDef
Dim prm As Parameter
Set MYDB = CurrentDb
empstring = "SELECT distinct payroll,id FROM allownceQuery where id = id"
Set rstemp = MYDB.OpenRecordset(empstring, dbOpenDynaset)
rstemp.MoveFirst
While Not rstemp.EOF
memp = rstemp("payroll")
med = rstemp(id)
Set Qdf = MYDB.QueryDefs("allownceQuery")
sqlstring = "SELECT allownce1.payroll,personal.al-name,personal.field,allownce1.allawnce, allownce1.amount,"
sqlstring = sqlstring & " allownce2.date, allownce2.id,balance.balance FROM (((personal inner join allownce1 on personal.payroll = allownce1.payroll),"
sqlstring = sqlstring & " inner join allownce2 on allownce1.id = allownce2.id)"
sqlstring = sqlstring & " inner join balance on allownce1.payroll = balance.payroll)"
sqlstring = sqlstring & " WHERE personal.email like '*Khalda*' and personal.payroll = " & memp & " and allownce1.id = '" & med & "'"
sqlstring = sqlstring & " ORDER BY personal.payroll"
Qdf.SQL = sqlstring
Qdf.Close
'For Each prm In Qdf.Parameters
'prm.Value = Eval(prm.NAME)
'Next prm
Set RST11 = Qdf.OpenRecordset
RST11.MoveFirst
'While Not RST11.EOF
'combo_emp.RowSource = "SELECT EMPID,EMPNAME FROM employee WHERE COMPANY = '" & RST11("COMPANY") & "' AND EMPID = " & RST11("EMPID")
mto = RST11("email")
RST11.Close
DoCmd.SendObject acSendQuery, "allownceQuery", acFormatXLS, mto, , , " allowances added to payroll", "attached is your allowances payment", False
rstemp.MoveNext
Wend
MsgBox "EMAIL PROCESS COMPLETE", vbInformation
MYDB.Close
End Sub

 

[WayneRyan]

ihere,

Try --> personal.[al-name]

When it sees the "-" it thinks that you are trying to subract name.

Also:

"SELECT distinct payroll,id FROM allownceQuery where id = id"

id = id WILL ALWAYS BE TRUE.

Maybe you meant: "SELECT distinct payroll,id FROM allownceQuery where id = " & Me.id

Wayne

 

[ihere]

I changed it completely and now i have problem " data type mismatch in criteria expression " at
Set rest = mydb.OpenRecordset(temp, dbOpenDynaset)


code
Private Sub Command2_Click()
Dim mydb As DAO.Database
Dim rest As Recordset
Dim temp As String
Dim mpayroll As String
Dim mpage As String
Dim sqlstring As String
Dim qdf As QueryDef
Dim reset As Recordset
Dim mmail As String


Set mydb = CurrentDb
temp = " SELECT DISTINCT payroll, page FROM pay WHERE page = '" & scode & "'" & " ORDER BY payroll"
Set rest = mydb.OpenRecordset(temp, dbOpenDynaset)
rest.MoveFirst
While Not rest.EOF

mpayroll = rest("payroll")
mpage = rest("page")
sqlstring = " SELECT payed2.payroll, payed2.type, payed1.page, payed2.amount, personal.namee, personl.email" & _
" FROM payed2 INNER JOIN payed1 ON payed2.page = payed1.page, INNER JOIN personal ON payed2.payroll = personal.payroll" & _
" WHERE payed2.payroll = '" & mpayroll & "' And payed2.Page = " & mpage & "" & _
" ORDER BY payed2.payroll "

qdf.SQL = sqlstring
qdf.Close

Set reset = qdf.OpenRecordset
reset.MoveFirst
mmail = ("email")
reset.Close
DoCmd.SendObject acSendQuery, "pay", acFormatXLS, mmail, , , "here balance", " your balance here", False
rest.MoveNext
Wend
MsgBox "EMAIL PROCESS COMPLETE", vbInformation

mydb.Close

End Sub

 

[pbaldy]

If the data type of page is numeric you don't want single quotes around the value.