Runtime Error, pls help

[styleest]

I'm an intermediate vb programmer, i need help . I wrote a code that'll fetch a particular employee id (EM_ID) and print the report but its saying this whenever i compile and run.

Runtime Error 2147217904 [80040e10] [ODBC Microsoft Access Driver] Too few parameters

The code is below

rs.Open "SELECT tblPayroll.EM_ID, tblPayroll.EM_Name, tblPayroll.Monthly_Rate, tblPayroll.dDate, tblPayroll.Bonus, tblPayroll.OvertimePay, tblPayroll.AwardTicket, tblPayroll.FineTicket, tblPayroll.pension, tblPayroll.w/Tax, tblPayroll.OtherDeductions, tblPayroll.OtherEarnings, tblPayroll.Loans, tblPayroll.TotalDeductions, tblPayroll.Medical, tblPayroll.Housing, tblPayroll.Transport, tblPayroll.Furniture, tblPayroll.Feeding, tblPayroll.Miscellaneous, tblPayroll.NetPay, tblPayroll.RemainingBalance, tblPayroll.TotalPaid, tblPayroll.LoanCollected, tblPayroll.MonthlyDeduction " & _
"FROM tblPayroll WHERE EM_ID =" & Me.lvwInfo.SelectedItem.Text & " GROUP BY tblPayroll.EM_ID, tblPayroll.EM_Name, tblPayroll.Monthly_Rate, tblPayroll.dDate, tblPayroll.Bonus, tblPayroll.OvertimePay, tblPayroll.AwardTicket, tblPayroll.FineTicket, tblPayroll.pension, tblPayroll.w/Tax, tblPayroll.OtherDeductions, tblPayroll.OtherEarnings, tblPayroll.Loans, tblPayroll.TotalDeductions, tblPayroll.Medical, tblPayroll.Housing, tblPayroll.Transport, tblPayroll.Furniture, tblPayroll.Feeding, tblPayroll.Miscellaneous, tblPayroll.NetPay, tblPayroll.RemainingBalance, tblPayroll.TotalPaid, tblPayroll.LoanCollected, tblPayroll.MonthlyDeduction having Month(dDate)='" & Month(Me.dt1.Value) & "' And Day(dDate)='" & Day(Me.dt1.Value) & "' And Year(dDate)='" & Year(Me.dt1.Value) & "' ORDER BY tblPayroll.EM_ID;", cn, adOpenKeyset, adLockPessimistic

[AndreRet]

The error message "Too few parameters" simply means you are not passing the appropriate parameters with your SQL statement OR the tablename / fieldname used doesn't exist in the database you were trying to connect.

Change your sql statement at this part - "FROM tblPayroll WHERE EM_ID =" & Me.lvwInfo.SelectedItem.Text & " GROUP BY to

"FROM tblPayroll WHERE EM_ID =" & "'" & Me.lvwInfo.SelectedItem.Text & "' GROUP BY

Have a look at your field structure EM_ID. Is it set up to receive only integers? If text is added to the field that can only accept numbers, same error will show.

Finally, the problem might be with your report. Check the column headers as well.

[styleest]

Thanks but it did not solve the problem. If i put in this code, it prints all the report
rs.Open "Select * from tblPayroll where Month(dDate)='" & Month(Me.dtPick.Value) & "' And Year(dDate)='" & Year(Me.dtPick.Value) & "'ORDER BY tblPayroll.EM_ID;", cn, adOpenKeyset, adLockPessimistic

But, when i want it to print a particular report, i put in this code and its the one not working.

rs.Open "SELECT tblPayroll.EM_ID, tblPayroll.EM_Name, tblPayroll.Monthly_Rate, tblPayroll.dDate, tblPayroll.Bonus, tblPayroll.OvertimePay, tblPayroll.AwardTicket, tblPayroll.FineTicket, tblPayroll.pension, tblPayroll.w/Tax, tblPayroll.OtherDeductions, tblPayroll.OtherEarnings, tblPayroll.Loans, tblPayroll.TotalDeductions, tblPayroll.Medical, tblPayroll.Housing, tblPayroll.Transport, tblPayroll.Furniture, tblPayroll.Feeding, tblPayroll.Miscellaneous, tblPayroll.NetPay, tblPayroll.RemainingBalance, tblPayroll.TotalPaid, tblPayroll.LoanCollected, tblPayroll.MonthlyDeduction " & _
"FROM tblPayroll WHERE EM_ID =" & "" & Me.lvwInfo.SelectedItem.Text & " GROUP BY tblPayroll.EM_ID, tblPayroll.EM_Name, tblPayroll.Monthly_Rate, tblPayroll.dDate, tblPayroll.Bonus, tblPayroll.OvertimePay, tblPayroll.AwardTicket, tblPayroll.FineTicket, tblPayroll.pension, tblPayroll.w/Tax, tblPayroll.OtherDeductions, tblPayroll.OtherEarnings, tblPayroll.Loans, tblPayroll.TotalDeductions, tblPayroll.Medical, tblPayroll.Housing, tblPayroll.Transport, tblPayroll.Furniture, tblPayroll.Feeding, tblPayroll.Miscellaneous, tblPayroll.NetPay, tblPayroll.RemainingBalance, tblPayroll.TotalPaid, tblPayroll.LoanCollected, tblPayroll.MonthlyDeduction having Month(dDate)='" & Month(Me.dt1.Value) & "' And Day(dDate)='" & Day(Me.dt1.Value) & "' And Year(dDate)='" & Year(Me.dt1.Value) & "' ORDER BY tblPayroll.EM_ID;", cn, adOpenKeyset, adLockPessimistic

pls i need your help

[AndreRet]

You have an incorrect statement still -

"FROM tblPayroll WHERE EM_ID =" & "" & Me.lvwInfo.SelectedItem.Text & " GROUP BY

Have a look at the ' in between brackets -

"FROM tblPayroll WHERE EM_ID =" & "'" & Me.lvwInfo.SelectedItem.Text & "' GROUP BY

[styleest]

Thanks for your help, i've used it still, its not working. Please i dont know if you can help me with another code that will fetch a particular person's information from a list of many information or can i attach my project so u'll see it and help me rectify the problem?
THanks for your help. Its a nice forum

[styleest]

pls i need your email to forward a copy of the project to you.

[AndreRet]

Copy and post the code exactly as it is below. Test it and let me know if it worked for you.

Dim MyDate As Date = Me.dt1.Value
Dim MyString As String = Me.lvwInfo.SelectedItem.Text

rs.Open "SELECT tblPayroll.EM_ID, tblPayroll.EM_Name, tblPayroll.Monthly_Rate, tblPayroll.dDate, tblPayroll.Bonus, tblPayroll.OvertimePay, tblPayroll.AwardTicket, tblPayroll.FineTicket, tblPayroll.pension, tblPayroll.w/Tax, tblPayroll.OtherDeductions, tblPayroll.OtherEarnings, tblPayroll.Loans, tblPayroll.TotalDeductions, tblPayroll.Medical, tblPayroll.Housing, tblPayroll.Transport, tblPayroll.Furniture, tblPayroll.Feeding, tblPayroll.Miscellaneous, tblPayroll.NetPay, tblPayroll.RemainingBalance, tblPayroll.TotalPaid, tblPayroll.LoanCollected, tblPayroll.MonthlyDeduction " & _
"FROM tblPayroll WHERE EM_ID =" & "'" & MyString & "' GROUP BY tblPayroll.EM_ID, tblPayroll.EM_Name, tblPayroll.Monthly_Rate, tblPayroll.dDate, tblPayroll.Bonus, tblPayroll.OvertimePay, tblPayroll.AwardTicket, tblPayroll.FineTicket, tblPayroll.pension, tblPayroll.w/Tax, tblPayroll.OtherDeductions, tblPayroll.OtherEarnings, tblPayroll.Loans, tblPayroll.TotalDeductions, tblPayroll.Medical, tblPayroll.Housing, tblPayroll.Transport, tblPayroll.Furniture, tblPayroll.Feeding, tblPayroll.Miscellaneous, tblPayroll.NetPay, tblPayroll.RemainingBalance, tblPayroll.TotalPaid, tblPayroll.LoanCollected, tblPayroll.MonthlyDeduction HAVING Month(dDate)='" & MyDate & "' ORDER BY tblPayroll.EM_ID;", cn, adOpenStatic, adLockOptimistic

[styleest]

Thanks but it still din't work, can u pls send me ur email to send the full project as a zip file so u'll see it well

[AndreRet]

See your private messages...

[styleest]

I just read thru the forum rules and discovered , we can't receive or send mails for program solving purpose. I wont bug you again, i think i'll try to find the solution somewhere else cause its not working and if you have another code that can make it work, i'll appreciate. Thanks for your effort

[AndreRet]

It's a pleasure. Thank you for reading our posting rules AND abiding by it. I have just gave you some points for that.

Back to your problem, if you say it does not work, what error are you getting and on what line of your code gets highlighted. Lets try and narrow this down and get this to work for you.:)

[styleest]

ok, i've seen my private message, pls check urs too

[AndreRet]

I'll let you know...

[styleest]

i guess my network is really bad, still trying to send it.

[AndreRet]

Paste the code for only the form you are using. I should be able to read from that and find your solution. Also post the table names and fields in the database that is applicable.

[styleest]

Just got this your comment. i'm pasting the codesince i cant attach the whole project

[styleest]

THis is the code to print the selected persons payroll info.

Private Sub cmdPrintSel_Click()
Dim MyDate As Date
Date = Me.dt1.Value
Dim MyString As String
MyString = Me.lvwInfo.SelectedItem.Text

dt1.Value = Me.lvwInfo.ListItems(Me.lvwInfo.SelectedItem.Index).SubItems(3)
If rs.State = adStateOpen Then rs.Close

rs.Open "SELECT tblPayroll.EM_ID, tblPayroll.EM_Name, tblPayroll.Monthly_Rate, tblPayroll.dDate, tblPayroll.Bonus, tblPayroll.OvertimePay, tblPayroll.AwardTicket, tblPayroll.FineTicket, tblPayroll.pension, tblPayroll.w/Tax, tblPayroll.OtherDeductions, tblPayroll.OtherEarnings, tblPayroll.Loans, tblPayroll.TotalDeductions, tblPayroll.Medical, tblPayroll.Housing, tblPayroll.Transport, tblPayroll.Furniture, tblPayroll.Feeding, tblPayroll.Miscellaneous, tblPayroll.NetPay, tblPayroll.RemainingBalance, tblPayroll.TotalPaid, tblPayroll.LoanCollected, tblPayroll.MonthlyDeduction " & _
"FROM tblPayroll WHERE EM_ID =" & "'" & MyString & "' GROUP BY tblPayroll.EM_ID, tblPayroll.EM_Name, tblPayroll.Monthly_Rate, tblPayroll.dDate, tblPayroll.Bonus, tblPayroll.OvertimePay, tblPayroll.AwardTicket, tblPayroll.FineTicket, tblPayroll.pension, tblPayroll.w/Tax, tblPayroll.OtherDeductions, tblPayroll.OtherEarnings, tblPayroll.Loans, tblPayroll.TotalDeductions, tblPayroll.Medical, tblPayroll.Housing, tblPayroll.Transport, tblPayroll.Furniture, tblPayroll.Feeding, tblPayroll.Miscellaneous, tblPayroll.NetPay, tblPayroll.RemainingBalance, tblPayroll.TotalPaid, tblPayroll.LoanCollected, tblPayroll.MonthlyDeduction HAVING Month(dDate)='" & MyDate & "' ORDER BY tblPayroll.EM_ID;", cn, adOpenStatic, adLockOptimistic

Set drtSel.DataSource = rs
drtSel.PrintReport
drtSel.Show 1
Exit Sub
err:
MsgBox err.Description, vbCritical, "Error"
Set rs = Nothing
End Sub

THe table name is,tblPayroll. With fields,

EM_ID,EM_Name,Monthly_Rate,dDate,Bonus,OvertimePay,AwardTicket,FineTicket,pension,w/Tax,OtherDeductions,OtherEarnings,Loans,TotalDeductions,Medical,Housing,Transport,Furniture,Feeding,Miscellaneous,NetPay,RemainingBalance,TotalPaid,LoanCollected,MonthlyDeduction.

Thanks very much. Will wait for your reply. Meanwhile, i'm still trying to attach the whole project

[AndreRet]

What information is returned with the following code?

dt1.Value = Me.lvwInfo.ListItems(Me.lvwInfo.SelectedItem.Index).SubItems(3)

Is it a proper date that gets returned?

[styleest]

yes its a date that get returned and i used a list view to display all my info inorder to select it, click on it and it will print. I've suceeded in attaching 97% of the whole project pls hold on so that you can get the whole view.

Thanks

[styleest]

Pls keep trying to work on that info. The full project has refused to download. i'm still working on it .

Thanks