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

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.
 
D

DawnTreader

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...
 
D

DawnTreader

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...
 
D

DawnTreader

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...
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Form based on recordset limited to one record 1
why does this code give me fits? 2
Append Recordset 2
Referencing a field from a table 5
How do I debug VB error #80004005 1
Datasheet's Recordset 3
No value given for one or more required parameters 2
Binding ADO recordset to form 2

Top