SQL statement is give me error 3061 Too few parameters. Expected 1

[bama4954]

The SQL statement works in the query grid but not in the vba. I want a search box to pop up and I will enter the EquipID into the search box then it will load the correct records into a list box for that EquipID. See below for the SQL Statement.

strSQL = "SELECT tblTag.TagNumber, tblTag.TagID, tblEquipment.Equipment, tblEquipment.EquipID " & vbCrLf & _
"FROM tblEquipment INNER JOIN tblTag ON tblEquipment.EquipID = tblTag.EquipID " & vbCrLf & _
"WHERE (((tblEquipment.EquipID) Like ['Filter Report: Type EquipID]));"

 

[bama4954]

Sorry, here is the entire code: First part is the loading of the list box. The second a mod of the building of the three tags.

Option Compare Database
Option Explicit
Public rstTag As Recordset
Public rstToPrint As Recordset
Private Sub cboEquipNumber_Click()
Dim strSQL As String
strSQL = "SELECT tblTag.TagNumber, tblTag.TagID, tblEquipment.Equipment, tblEquipment.EquipID " _
& "FROM tblEquipment INNER JOIN tblTag ON tblEquipment.EquipID = tblTag.EquipID " _
& "WHERE tblEquipment.EquipID = " & Me.cboEquipNumber & ""

Set rstTag = CurrentDb.OpenRecordset(strSQL)

Do Until rstTag.EOF
Me.lstTag.AddItem (Format(rstTag!TagNumber, "0000") & ";" & rstTag!TagID)
rstTag.MoveNext
Loop

rstTag.Close
Set rstTag = Nothing
End Sub
Private Sub cmdAddToList_Click()
Dim varList As Variant
Dim strRemoveList As String
Dim varToRemove As Variant
Dim x As Integer

For Each varList In lstToPrint.ItemsSelected

lstTag.AddItem (lstToPrint.Column(0, varList) & ";" & lstToPrint.Column(1, varList))

Next

For Each varList In lstToPrint.ItemsSelected

strRemoveList = strRemoveList & "," & varList

Next

varToRemove = Split(Mid(strRemoveList, 2), ",")

For x = UBound(varToRemove) To 0 Step -1

lstToPrint.RemoveItem (CInt(varToRemove(x)))

Next x
End Sub
Private Sub cmdAddToPrint_Click()

Dim varList As Variant
Dim strRemoveList As String
Dim varToRemove As Variant
Dim x As Integer

For Each varList In lstTag.ItemsSelected

lstToPrint.AddItem (lstTag.Column(0, varList) & ";" & lstTag.Column(1, varList))

Next

For Each varList In lstTag.ItemsSelected

strRemoveList = strRemoveList & "," & varList

Next

varToRemove = Split(Mid(strRemoveList, 2), ",")

For x = UBound(varToRemove) To 0 Step -1

lstTag.RemoveItem (CInt(varToRemove(x)))

Next x



End Sub
Public Function BuildInList() As String
Dim x As Integer
Dim strIN As String
For x = 0 To Me.lstToPrint.ListCount - 1

If x = 0 Then
strIN = lstToPrint.Column(1, x)
Else
strIN = strIN & ", " & lstToPrint.Column(1, x)
End If

Next x
strIN = "(" & strIN & ")"

BuildInList = strIN

End Function
Private Sub cmdLoadReport_Click()
Dim strSQL As String
strSQL = "SELECT * FROM qryTagReport WHERE TagID IN " & BuildInList


'Debug.Print strSQL
If Me.lstToPrint.ListCount = 0 Then
Exit Sub
Else
'Debug.Print "cmdLoadReport: " & strSQL
basTagReport.BuildTagReport_table strSQL

Select Case Me.frmReportType.Value

Case 1

DoCmd.OpenReport "rptSmartBlind", acViewPreview

Case 2
DoCmd.OpenReport "rptConfinedSpaceTag", acViewPreview

Case 3
DoCmd.OpenReport "rptLOTOTag", acViewPreview

End Select

End If

End Sub

'Second Part:

Option Compare Database
Option Explicit
Private Type TagReport

TagNumber As Long
Location As String
JobNumber As String
Equipment As String
DrawingRef As String
SizeRating As String
Type As String
FirstPosition As String
SecondPosition As String
Service As String
SpreadSide As String
AirJob As String

End Type

Public Sub BuildTagReport_table(strQuery As String)
Dim TAG1 As TagReport
Dim TAG2 As TagReport
Dim TAG3 As TagReport
Dim rst As Recordset
Dim intColumn As Integer
Dim x As Integer
Dim intRecord As Long
Dim strSQL As String

On Error GoTo ErrHandler

strSQL = "DELETE * from tblTagReport"

' Erase the Temporary table
DoCmd.SetWarnings False
DoCmd.RunSQL strSQL
DoCmd.SetWarnings True
intColumn = 1

' Change DataSource to the Name of the Query which will hold the filtered Records

Set rst = CurrentDb.OpenRecordset(strQuery)
rst.MoveLast
x = rst.RecordCount
rst.MoveFirst

For intRecord = 1 To x

Select Case intColumn
Case 1
TAG1.TagNumber = Nz(rst!TagNumber, " ")
TAG1.Location = Nz(rst!Location, " ")
TAG1.Equipment = Nz(rst!Equipment, " ")
TAG1.DrawingRef = Nz(rst!DrawingRef, " ")
TAG1.JobNumber = Nz(rst!JobNumber, " ")
TAG1.SizeRating = Nz(rst!SizeRating, " ")
TAG1.Type = Nz(rst!Type, " ")
TAG1.Service = Nz(rst!Service, " ")
TAG1.SpreadSide = Nz(rst!SpreadSide, " ")
TAG1.FirstPosition = Nz(rst!FirstPosition, " ")
TAG1.SecondPosition = Nz(rst!SecondPosition, " ")
TAG1.AirJob = Nz(rst!AirJob, " ")
intColumn = 2
rst.MoveNext
Case 2

TAG2.TagNumber = Nz(rst!TagNumber, " ")
TAG2.Location = Nz(rst!Location, " ")
TAG2.Equipment = Nz(rst!Equipment, " ")
TAG2.DrawingRef = Nz(rst!DrawingRef, " ")
TAG2.JobNumber = Nz(rst!JobNumber, " ")
TAG2.SizeRating = Nz(rst!SizeRating, " ")
TAG2.Type = Nz(rst!Type, " ")
TAG2.Service = Nz(rst!Service, " ")
TAG2.SpreadSide = Nz(rst!SpreadSide, " ")
TAG2.FirstPosition = Nz(rst!FirstPosition, " ")
TAG2.SecondPosition = Nz(rst!SecondPosition, " ")
TAG2.AirJob = Nz(rst!AirJob, " ")
intColumn = 3
rst.MoveNext
Case 3

TAG3.TagNumber = Nz(rst!TagNumber, " ")
TAG3.Location = Nz(rst!Location, " ")
TAG3.Equipment = Nz(rst!Equipment, " ")
TAG3.DrawingRef = Nz(rst!DrawingRef, " ")
TAG3.JobNumber = Nz(rst!JobNumber, " ")
TAG3.SizeRating = Nz(rst!SizeRating, " ")
TAG3.Type = Nz(rst!Type, " ")
TAG3.Service = Nz(rst!Service, " ")
TAG3.SpreadSide = Nz(rst!SpreadSide, " ")
TAG3.FirstPosition = Nz(rst!FirstPosition, " ")
TAG3.SecondPosition = Nz(rst!SecondPosition, " ")
TAG3.AirJob = Nz(rst!AirJob, " ")
intColumn = 1
BuildReport TAG1, TAG2, TAG3
rst.MoveNext

End Select

Next intRecord
Dim tagBlank As TagReport
Select Case x Mod 3
Case 0
' BuildReport TAG1, TAG2, TAG3
Case 1
BuildReport TAG1, tagBlank, tagBlank
Case 2
BuildReport TAG1, TAG2, tagBlank

End Select


Set rst = Nothing
ExitHere:
Exit Sub
ErrHandler:

GoTo ExitHere

End Sub

Public Sub BuildReport(TAG1 As TagReport, TAG2 As TagReport, TAG3 As TagReport)
Dim rst As Recordset
Set rst = CurrentDb.OpenRecordset("tblTagReport")

rst.AddNew
rst!TagNumber1 = TAG1.TagNumber
rst!Location1 = TAG1.Location
rst!JobNumber1 = TAG1.JobNumber
rst!Equipment1 = TAG1.Equipment
rst!DrawingRef1 = TAG1.DrawingRef
rst!SizeRating1 = TAG1.SizeRating
rst!Type1 = TAG1.Type
rst!FirstPosition1 = TAG1.FirstPosition
rst!SecondPosition1 = TAG1.SecondPosition
rst!SpreadSide1 = TAG1.SpreadSide
rst!Service1 = TAG1.Service
rst!AirJob1 = TAG1.AirJob

rst!TagNumber2 = TAG2.TagNumber
rst!Location2 = TAG2.Location
rst!JobNumber2 = TAG2.JobNumber
rst!Equipment2 = TAG2.Equipment
rst!DrawingRef2 = TAG2.DrawingRef
rst!SizeRating2 = TAG2.SizeRating
rst!Type2 = TAG2.Type
rst!FirstPosition2 = TAG2.FirstPosition
rst!SecondPosition2 = TAG2.SecondPosition
rst!SpreadSide2 = TAG2.SpreadSide
rst!Service2 = TAG2.Service
rst!AirJob2 = TAG2.AirJob

rst!TagNumber3 = TAG3.TagNumber
rst!Location3 = TAG3.Location
rst!JobNumber3 = TAG3.JobNumber
rst!Equipment3 = TAG3.Equipment
rst!DrawingRef3 = TAG3.DrawingRef
rst!SizeRating3 = TAG3.SizeRating
rst!Type3 = TAG3.Type
rst!FirstPosition3 = TAG3.FirstPosition
rst!SecondPosition3 = TAG3.SecondPosition
rst!SpreadSide3 = TAG3.SpreadSide
rst!Service3 = TAG3.Service
rst!AirJob3 = TAG3.AirJob
rst.Update

Set rst = Nothing
End Sub

 

[xenou]

That might be too much information now.

It loooks like the problem is in how you are using strSQL, in the sub cboEquipNumber_Click()

What is the value of me.cboEquipNumber when you are running this code?

For example,

Edit this sub:

Private Sub cboEquipNumber_Click()

Dim strSQL As String
strSQL = "SELECT tblTag.TagNumber, tblTag.TagID, tblEquipment.Equipment, tblEquipment.EquipID " _
& "FROM tblEquipment INNER JOIN tblTag ON tblEquipment.EquipID = tblTag.EquipID " _
& "WHERE tblEquipment.EquipID = " & Me.cboEquipNumber & ""

Set rstTag = CurrentDb.OpenRecordset(strSQL)

Do Until rstTag.EOF
    Me.lstTag.AddItem (Format(rstTag!TagNumber, "0000") & ";" & rstTag!TagID)
    rstTag.MoveNext
Loop

rstTag.Close
Set rstTag = Nothing

End Sub

Change it to this:

Private Sub cboEquipNumber_Click()

MsgBox Me.cboEquipNumber

End Sub

• What value does the message box display?
• Is it the correct value for an EquipID?
• What is the datatype of EquipID in the underlying table (tblEquipment) - is it numeric or string/text?

Next also you can change it to this:

Private Sub cboEquipNumber_Click()

Dim strSQL As String
strSQL = "SELECT tblTag.TagNumber, tblTag.TagID, tblEquipment.Equipment, tblEquipment.EquipID " _
& "FROM tblEquipment INNER JOIN tblTag ON tblEquipment.EquipID = tblTag.EquipID " _
& "WHERE tblEquipment.EquipID = " & Me.cboEquipNumber & ""

MsgBox strSQL

End Sub

• Is that good sql?
• Can it be run as is?
• Can you put it into a new query as raw sql and actually run it with correct results?

Try to keep in mind that your goal is to produce a raw SQL string that is correct and can be run as is. So all you need to really do it keep tweaking this until strSQL is correct and runnable as is. As far as quotes or no quotes the rule is simple - text goes in quotes, numbers do not.

 

[bama4954]

Answers to your questions:

What value does the message box display?​

(The value is a list of Equipment. It shows the equipID when running code)
What value does the message box display? (1 which is the equipID for that piece of equipment)
Is it the correct value for an EquipID? (Yes)

What is the datatype of EquipID in the underlying table (tblEquipment) - is it numeric or string/text? (Auto Number "Primary Key "Long Integer)​

_{^{<strike>
</strike>}}

• Is that good sql? (Yes)
• Can it be run as is? (Yes)
• Can you put it into a new query as raw sql and actually run it with correct results?(Yes)

When I run it from the raw query it works perfect. But when I run it from the combo box it populates the list box with the correct information but does not populate the report, it is blank. Can not understand what is happening.

The SQL populates a list box. Then I choose records out of the list box to run a report, but the report does not populate with the records. Everything you have done works fine, just not getting to the report.

_{^{<strike>
</strike>}}​

 

[xenou]

When I run it from the raw query it works perfect. But when I run it from the combo box it populates the list box with the correct information but does not populate the report, it is blank. Can not understand what is happening.

The sql appears to be working correctly. I cannot understand what is happening either. Why would sql from a combobox populate a report? Its job is to populate the combobox.

 

[bama4954]

I finally figured it out. Thanks to everyone that helped me through this. I just had to walk away from it for a bit it just popped into my head what I was doing wrong. It is working great now but I could not have done it without every ones help. Thanks again!!

 

[xenou]

I just had to walk away from it for a bit it just popped into my head what I was doing wrong.

Yes this is another good debugging tip for all of us.

 

[bama4954]

So now I am wanting to change the combo box to search for JobNumber instead of EquipID. Here is the new sql statement. I am getting the same error: Missing Parameter expected 1. What am I doing wrong?

[FONT=Verdana,Arial,Tahoma,Calibri,Geneva,sans-serif] strSQL = "SELECT tblTag.TagNumber, tblTag.TagID " & vbCrLf & _
"FROM tblTag " & vbCrLf & _
"WHERE tblTag.JobNumber = & [Forms]![frmTagReport]![cboJobID] & ;"[/FONT]

 

[xenou]

What is the jobID you are testing on?

 

[bama4954]

JobID is a number. 384456

 

[xenou]

Okay well the basic rule for testing is:

1) does the raw sql work.
2) does the string I have created match the raw sql I wanted to create?

In this case, the raw sql is:

SELECT tblTag.TagNumber, tblTag.TagID 
FROM tblTag 
WHERE tblTag.JobNumber = 384456;

and the string you have created is:

SELECT tblTag.TagNumber, tblTag.TagID 
FROM tblTag 
WHERE tblTag.JobNumber = & [Forms]![frmTagReport]![cboJobID] & ;

As you can see, they don't match.

What you meant (probably) was:

strSQL = "SELECT tblTag.TagNumber, tblTag.TagID " & vbCrLf & _
"FROM tblTag " & vbCrLf & _
"WHERE tblTag.JobNumber = " & [Forms]![frmTagReport]![cboJobID] & ";"