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

  • Thread starter Thread starter DawnTreader
  • Start date Start date
D

DawnTreader

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.
 
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 said:
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.
Click to expand...
 
Hello again

i have changed the sql code to this:

sqlProducts = "SELECT qryPTQFPTL.PART_ID, qryPTQFPTL.QTY_PER,
qryPTQFPTL.ProductID FROM qryPTQFPTL " & _
"WHERE (((qryPTQFPTL.PART_ID) = " &
[Forms]![frmMassProductPartAddingTool]![txtWhatPartHidden] & "))"

still not working.

a little more information. this qryPTQFPTL is built on a stack of other
queries. one of the lower queries requires criteria that comes from the same
text box that this sql is trying to use. is that a problem?

would it be better to append lower level query records to a table and then
use that table to store, temporarily, the records used in the next levels up
to this sql?

i have done that before and it worked really good for that instance. the
only reason i am not doing that here is that it takes time to work that out
and i dont want to spend any more time on this.

any and all appreciated.

DawnTreader said:
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.
Click to expand...
 
Hello All

i figured a solution, but i would still like to know if it was syntax errors
or if the form criteria in a lower level query was the problem.

i think the latter because i basically did what i was thinking of doing and
built a query to put the information in a table and then use the sql in my
code to query the table.

any and all!

DawnTreader said:
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.
Click to expand...
 
Back
Top