too few paramaters

[samonwalkabout]

Having great deal of trouble with this one getting error 2 few paramater expecting 2. From reading other posts i think i need to declare the paramaters in the statement somewhere but ever combination give me some diffrent error. Can anyone help?

Function UpdateAmounts(intRawMaterialID As Integer)

Dim rst As Recordset
Dim dt As Date
Dim Lngamnt As Long
Dim db As DAO.Database
Dim strSQL As String
Dim rsttblstock2 As Recordset


Set db = CurrentDb

strSQL = "SELECT StockQry.RawMaterialID, StockQry.WeekID, StockQry.EndAmount,StockQry.StartAmount, StockQry.OrderID, StockQry.Amount, tblForecast2.ForecastID, tblForecast2.Amount,tblUsage2.UsageID, tblUsage2.Amount, tblAdjustment2.AdjustmentID, tblAdjustment2.Amount, [StartAmount]+[tblForecast2].[Amount]+[tblOrder2].[Amount]+[tblAdjustment2].[Amount]-[tblUsage2].[Amount] AS Ender " _
& " FROM tblUsage2 INNER JOIN (tblOrder2 INNER JOIN (tblForecast2 INNER JOIN (tblAdjustment2 INNER JOIN (tblRawMaterial INNER JOIN StockQry ON tblRawMaterial.RawMaterialID = StockQry.RawMaterialID) ON tblAdjustment2.AdjustmentID = StockQry.AdjustmentsID) ON tblForecast2.ForecastID = StockQry.ForecastID) ON tblOrder2.OrderID = StockQry.OrderID) ON tblUsage2.UsageID = StockQry.UsageID" _
& " WHERE (((tblStock2.RawMaterialID)= " & intRawMaterialID & " ) AND ((tblStock2.WeekID) Between [forms]![frmMainMenu]![weekID]-4 And [forms]![frmmainmenu]![weekid]+100)) " _
& " ORDER BY [StockQry].[WeekID]"


Set rst = db.OpenRecordset(strSQL, dbOpenDynaset)

Lngamnt = rst!StartAmount
With rst
.MoveFirst
Do Until .EOF
.Edit
!StartAmount = Lngamnt
!EndAmount = !Ender
.Update
Lngamnt = !EndAmount
.MoveNext
Loop
End With
End Function


Thanks

 

[GumbyD]

I see this one lots of times. You are missing some data that the query needs to run. If you put a breakpoint on the code after you set your strSQL and then in the debug window type ? strSQL it will give the SQL it is trying to run. At this point stop the code exection and copy the SQL into the SQL pane of a new query. When you try to execute the query you should see an enter parameter box - this is the data that Access can't find. At that point you just need to either adjust the query or populate the missing data.

Good Luck!

GumbyD

 

[dcx693]

I think you've a problem with your syntax at the end of the strSQL here:
" ) AND ((tblStock2.WeekID) Between [forms]![frmMainMenu]![weekID]-4 And [forms]![frmmainmenu]![weekid]+100)) " _

The form references need to be outside of the quotation marks like:
" ) AND ((tblStock2.WeekID) Between " & [forms]![frmMainMenu]![weekID]-4 & " And " & [forms]![frmmainmenu]![weekid]+100))

 

[samonwalkabout]

Gumby

Im guessing you mean ? strsql in the immediate window???

I can't seem to get anything to happen it doesnt show the SQL or an error it does nothing???


dcx693

Good point on the syntax think i have it fixed now

thanks to both of you.

Im kinda new to the whole debug thing never had code stuff this hard before Any idea why the break point ? strsql gives nothing

 

[samonwalkabout]

Actually in getting a "type mismatch" which i guess is a syntax error.

Using

& " WHERE ((([tblStock2].[RawMaterialID])= " & intRawMaterialID & " ) And (([tblStock2].[WeekID]) Between & [forms]![frmMainMenu]![weekID]-4 & " And " & [forms]![frmmainmenu]![weekid]+100)) & " _

 

[GumbyD]

As long as you had already set the variable to the value and have not stopped the code exection it should show you what that variables value is in the immediate window. I may not have mentioned that you need to press the return key after you type in the

? strSQL

You can use it next time since the code is up and running now!!

GumbyD

 

[GumbyD]

If [WeekID] is not a numeric datatype than that may be causing your problem. It looks like you are evaluating it as a number so if it is text then you would get a datatype mismatch error.

GumbyD

 

[dcx693]

If [WeekID] is not a numeric datatype than that may be causing your problem. It looks like you are evaluating it as a number so if it is text then you would get a datatype mismatch error.

GumbyD

Well, I would hope he's not trying to subtract and add numbers from strings - but you never know!

You're missing a quotation mark:
& " WHERE ((([tblStock2].[RawMaterialID])= " & intRawMaterialID & " ) And (([tblStock2].[WeekID]) Between " & [forms]![frmMainMenu]![weekID]-4 & " And " & [forms]![frmmainmenu]![weekid]+100)) & " _

 

[samonwalkabout]

Yep it was that pesky " a few extra ) and a Stock instead of a stock2. Runs perfectly now!

Thanks very much for your help, Gumbyd and dcx693.