I am trying to create a recordset in within a form and when I click on the button I get the error "Too few parameters. Expected 1". The recordset is created from a saved query which runs OK by itself. I have included the code and also the SQL from the query. Can some one look at it for me and tell me what I am doing wron. I am new to this. Thanks.
On Error GoTo Err_Save_Click
Dim db As DAO.Database
Dim rs1 As DAO.Recordset
Dim rs2 As DAO.Recordset
Dim mySql As String
' stock = [Description]
Set db = CurrentDb
Set rs1 = db.OpenRecordset("SalesMaturity", dbOpenDynaset)
stock = [Qty]
If rs1.RecordCount <> 0 Then
Do While [Qty] <> 0
[seed] = rs1![Seedling Id]
If rs1![Qty in Stock] >= [Qty] Then
mySql = "INSERT INTO [Reduction Transaction] ([Seedling Id], [Reduction Type], [Reduction Qty], [Reduction Date]) VALUES ([seed], [Reduction], [Qty], [Date])"
[Qty] = 0
[new qty] = rs1![Qty in Stock]
mySql = "INSERT INTO [Reduction Transaction] ([Seedling Id], [Reduction Type], [Reduction Date], [Reduction Qty]) VALUES ([seed], [Reduction], [Date], [new qty])"
[Qty] = [Qty] - rs1![Qty in Stock]
Set rs1 = Nothing
SQL in the saved query (SalesMaturity):
SELECT Plants.[Seedling ID], Plants.Seedling, [Plant Category].[Plant Code], [Seedling Category].Description, Plants.[Date Planted], Plants.[Qty in Stock], [Date planted]+([Maturity Period (Wks)]*7) AS [Maturity Date]
FROM [Plant Category] INNER JOIN ([Seedling Category] INNER JOIN Plants ON [Seedling Category].[Seedling ID]=Plants.Seedling) ON [Plant Category].[Plant ID]=Plants.[Plant Type]
WHERE (((Plants.Seedling)=Forms.[Seedling Sales]!Description) And (([Plant Category].[Plant Code])="S") And ((Plants.[Qty in Stock])>0) And (([Date planted]+([Maturity Period (Wks)]*7))<=Now()))
ORDER BY [Date planted]+([Maturity Period (Wks)]*7);
usually i get that same error when i've typed something in my sql statement incorrectly, like tblEmployee vs tblEmployees. try copying your sql, pasting it into sql view in your query designer, and then flip to design view and see if it labels any of your fields as Expr1: etc.
The INSERT INTO statements works fine as those were tested with a simple recordset() SELECT statement. It's the "SalesMaturity" query that I think is the problem. That's the one at the bottom of the post.
I have found a workaround. I removed the criteria "[Forms[.[Seedling Sales]![Description]" from the "SalesMaturity" query and then use this query to feed another query "Sales". "Sales" will now have individual fields with no calculations. I then used the SELECT statement in my code to create the recordset() filtering the records from "Sales" with the WHERE " & [Forms].[Seedling Sales]![Description]". This works. I know the this is not the most efficient way so if anyone can still decifer the problem I'd appreciate it. Thanks for all you responses.
The easiest way to test SQL statements (in my opinion) is to put a break at the RunSQL line then run the code. When the code breaks, in the Immediate Window type ?mySql, you should get the constructed SQL statement. Then copy that SQL statement, design a new query in SQL view, paste the SQL statement and see if you can run it.
When you run it you will see the problem. Even switching from SQL view to Design view will usually show you the problem.