RE: Error "no value given for one or more required parameters" HELP

Tech-Archive recommends: Repair Windows Errors & Optimize Windows Performance

Hello Again

is my SQL to long? i think i might need to cut back the length of the query
names.

i tried putting the sql string into 2 parts by:

sqlProducts = "SELECT qryWhereUsedinWhichProduct.WORKORDER_TYPE,
qryWhereUsedinWhichProduct.WORKORDER_BASE_ID, " & _
"qryWhereUsedinWhichProduct.PART_ID,
qryWhereUsedinWhichProduct.QTY_PER, qryWhereUsedinWhichProduct.SerialNumber,
" & _
"qryWhereUsedinWhichProduct.ProductID,
qryProductsThatDoNotQualifyForPartsToLink.ManualPNIDPTL, " & _
"qryProductsThatDoNotQualifyForPartsToLink.VisualPNIDPTL,
qryWhereUsedinWhichProduct.CBBANumber, " & _
"qryWhereUsedinWhichProduct.ProductTypeID,
qryWhereUsedinWhichProduct.Type "

sqlProducts = sqlProducts & "FROM qryWhereUsedinWhichProduct LEFT JOIN
qryProductsThatDoNotQualifyForPartsToLink ON " & _
"qryWhereUsedinWhichProduct.ProductID =
qryProductsThatDoNotQualifyForPartsToLink.WUProdID " & _
"WHERE (((qryWhereUsedinWhichProduct.PART_ID) = """ &
[Forms]![frmMassProductPartAddingTool]![txtWhatPartHidden] & _
""") And
((qryProductsThatDoNotQualifyForPartsToLink.ManualPNIDPTL) Is Null) And " & _

"((qryProductsThatDoNotQualifyForPartsToLink.VisualPNIDPTL) Is Null)) "

but i still get the same problem. unless anyone has some other ideas i think
i am going to have to cut back on the query name lenght.

"DawnTreader" wrote:

Hello All

why is this not working:

Private Sub AddPartsToProductsManually()

Dim cn As ADODB.Connection

Dim rstProduct As ADODB.Recordset
Dim rstPartsToAdd As ADODB.Recordset

Dim sqlProducts As String
sqlProducts = ""
Dim sqlPartsToAdd As String
Dim sqlInsertPartsToProducts As String

Set cn = CurrentProject.Connection

sqlProducts = "SELECT qryWhereUsedinWhichProduct.WORKORDER_TYPE,
qryWhereUsedinWhichProduct.WORKORDER_BASE_ID, " & _
"qryWhereUsedinWhichProduct.PART_ID,
qryWhereUsedinWhichProduct.QTY_PER, qryWhereUsedinWhichProduct.SerialNumber,
" & _
"qryWhereUsedinWhichProduct.ProductID,
qryProductsThatDoNotQualifyForPartsToLink.ManualPNIDPTL, " & _
"qryProductsThatDoNotQualifyForPartsToLink.VisualPNIDPTL,
qryWhereUsedinWhichProduct.CBBANumber, " & _
"qryWhereUsedinWhichProduct.ProductTypeID,
qryWhereUsedinWhichProduct.Type " & _
"FROM qryWhereUsedinWhichProduct LEFT JOIN
qryProductsThatDoNotQualifyForPartsToLink ON " & _
"qryWhereUsedinWhichProduct.ProductID =
qryProductsThatDoNotQualifyForPartsToLink.WUProdID " & _
"WHERE (((qryWhereUsedinWhichProduct.PART_ID) = " &
[Forms]![frmMassProductPartAddingTool]![txtWhatPartHidden] & _
") And
((qryProductsThatDoNotQualifyForPartsToLink.ManualPNIDPTL) Is Null) And " & _

"((qryProductsThatDoNotQualifyForPartsToLink.VisualPNIDPTL) Is Null)) " '& _
'"ORDER BY qryWhereUsedinWhichProduct.WORKORDER_BASE_ID,
qryWhereUsedinWhichProduct.SerialNumber"

sqlPartsToAdd = "SELECT tblPartsToLink.PartToLinkID,
tblPartsToLink.PartToLinkIMWPN, dbo_PART.DESCRIPTION,
tblPartsToLink.SectionNameID FROM tblPartsToLink LEFT JOIN dbo_PART ON
tblPartsToLink.PartToLinkIMWPN = dbo_PART.ID"

MsgBox sqlProducts
' MsgBox sqlPartsToAdd

Set rstProduct = New ADODB.Recordset
With rstProduct
Set .ActiveConnection = cn
.Source = sqlProducts
.LockType = adLockReadOnly
.CursorType = adOpenForwardOnly
.Open
End With

Set rstPartsToAdd = New ADODB.Recordset
With rstPartsToAdd
Set .ActiveConnection = cn
.Source = sqlPartsToAdd
.LockType = adLockReadOnly
.CursorType = adOpenForwardOnly
.Open
End With

Do While Not rstProduct.EOF
rstPartsToAdd.MoveFirst
Do While Not rstPartsToAdd.EOF
sqlInsertPartsToProducts = "INSERT INTO tblProductPartList
(ProductID, IMWPartNumberID, QTY, SectionNameID) VALUES (" &
rstProduct.Fields("ProductID") & ", " &
rstPartsToAdd.Fields("PartToLinkIMWPN") & ", " & rstProduct.Fields("QTY_PER")
& ", " & rstPartsToAdd.Fields("SectionNameID") & ")"
' MsgBox sqlInsertPartsToProducts
DoCmd.RunSQL sqlInsertPartsToProducts
rstPartsToAdd.MoveNext
Loop
' MsgBox rstProduct.Fields("ProductID")
rstProduct.MoveNext
Me.sfrmqryWhereUsedinWhichProduct.Requery
Loop

'kill everything
rstProduct.Close
rstPartsToAdd.Close
cn.Close
Set cn = Nothing
Set rstProduct = Nothing
Set rstPartsToAdd = Nothing

End Sub

it chokes on the section:

With rstProduct
Set .ActiveConnection = cn
.Source = sqlProducts
.LockType = adLockReadOnly
.CursorType = adOpenForwardOnly
.Open
End With

specifically on the line .open. i know it has to do with my sql string, but
everything i have tried hasn't helped. most of all the field
[Forms]![frmMassProductPartAddingTool]![txtWhatPartHidden] is a text field.
the value is put into the text box through a combo box. the user chooses a
part and the part number gets put into the box.

i have tried it with single quotes:

"WHERE (((qryWhereUsedinWhichProduct.PART_ID) = '" &
[Forms]![frmMassProductPartAddingTool]![txtWhatPartHidden] & "')"

double quotes:

"WHERE (((qryWhereUsedinWhichProduct.PART_ID) = """ &
[Forms]![frmMassProductPartAddingTool]![txtWhatPartHidden] & """)"

no quotes:

"WHERE (((qryWhereUsedinWhichProduct.PART_ID) = " &
[Forms]![frmMassProductPartAddingTool]![txtWhatPartHidden] & ")"

i have even created a query that is using the sql and called that query
instead.

the thing i am working on was "working" but needed a few tweaks for speed
and accuracy and now it doesnt work at all because of this problem.

as always, any and all help is appreciated.
.

Relevant Pages

  • Re: As a generale rule - Query or VBA?
    ... But I end up with loads of queries with long names like: ... Stored queries have a precompiled query plan. ... For a code jockey the SQL string is tempting. ...
    (comp.databases.ms-access)
  • Re: Can I Pass Query Parameters Through SQL Statements?
    ... One way to do it (assumes that CompanyID is a numeric format and not text ... Dim strSQL As String ... Suppose I have a parameterized query ... >> You can construct the SQL String (with parameters replaced ...
    (microsoft.public.access.modulesdaovba)
  • Re: creating counter query
    ... some query: select * from table ... AFAIK, TOP doesn't take expressions, so short of creating a SQL string ... RowNumber TableID theField ...
    (comp.databases.ms-access)
  • Re: Variable Recordset Fields
    ... Just remember that a sql string is just a string, ... string by using Debug.Print strSql and copy it from the debug window. ... run the query. ...
    (comp.databases.ms-access)
  • Re: Problems with Delete Command
    ... The SQL could get fairly messy if you need to construct it in code, ... ContactID, and WebComID, and create your on-the-fly SQL on that saved query, ... to find the list of ContactIDs from the junction table, ... This is a style/readability thing: if you are going to use string ...
    (microsoft.public.access.tablesdbdesign)