P
Pete B.
Ladies and Gentleman,
I am attempting to execute an append query with paramaters. In the criteria
section of my query I have a parameter called strSQL. In the Query Parameters
Box I used the data type "Text". I placed [strSQL] in the criteria box.
All of my parameters where I use the datatypeenum "integer" work well. I
continuously get an error when I try to set a parameter using a string. I am
using the datatypeenum adChar and I have already tried using all of the
datatypeenums that can be used with Text. They all produce an error. I also
have the size declared as Long and am using a variable for the string length.
I have tried setting the size to 4 times the length of the string and I still
get an error.
I would appreciate any feedback I can get. I have pasted the code below.
Regards,
Pete
Private Sub Report_Open(Cancel As Integer)
On Error GoTo Err_Report_Open
'Upon opening, this form executes UpdateTable which updates tblSECTIONLIST.
tblSectionList determines the data
'order for rstAgendaData which is the record source for this report.
Dim cmdReportPrint As New ADODB.Command, prmReportPrint As New
ADODB.Parameter, cnn As New ADODB.Connection, _
prmGroupValue As New ADODB.Parameter, frm As Form, intGroupValue As Integer,
intDate As Integer, _
strSQL As String, intNumColumns As Integer, cat As New ADOX.Catalog, col As
ADOX.Column, lngSeed As Long, _
ChangeSeed As Boolean, intI As Integer
Set frm = Forms!frmReportPrint
Let intDate = frm.cboMeetingDate
Let intGroupValue = frm.grpPrintReport.Value
Call UpdateTable
DoCmd.SetWarnings False
Set cnn = CurrentProject.Connection
cnn.Execute "qdfClearReportTable"
DoCmd.SetWarnings True
Let lngSeed = 1
cat.ActiveConnection = cnn
Set col = cat.Tables("tblReportRecSource").Columns("PrimaryKey")
col.Properties("Seed") = lngSeed
cat.Tables("tblReportRecSource").Columns.Refresh
If col.Properties("Seed") = lngSeed Then
ChangeSeed = True
Else: ChangeSeed = False
End If
cnn.Close
Set col = Nothing
Set cat = Nothing
Set cnn = Nothing
Let strSQL = "(((qdfReportRecSource.SA_AlertNumber) Is Null)) OR "
intNumColumns = frm![lstSelectedFlds].ListCount
For intI = 0 To intNumColumns
If frm![lstSelectedFlds].Column(0, intI) > "" Then
If intI = 0 Then
Let strSQL = strSQL & "(((qdfReportRecSource.SA_AlertNumber)=" &
Chr(34) & frm![lstSelectedFlds].Column(0, intI) & Chr(34) & "))"
Else
Let strSQL = strSQL & " OR
(((qdfReportRecSource.SA_AlertNumber)=" & Chr(34) &
frm![lstSelectedFlds].Column(0, intI) & Chr(34) & "))"
End If
End If
Next intI
Let strSQL = strSQL & Chr(59)
Set cmdReportPrint.ActiveConnection = CurrentProject.Connection
cmdReportPrint.CommandType = adCmdText
cmdReportPrint.CommandText = strSQL
Set prmReportPrint = cmdReportPrint.CreateParameter("intDate", adInteger,
adParamInput, , intDate)
cmdReportPrint.Parameters.Append prmReportPrint
Set prmGroupValue = cmdReportPrint.CreateParameter("intGroupValue",
adInteger, adParamInput, , intGroupValue)
cmdReportPrint.Parameters.Append prmGroupValue
Set prmGroupValue = cmdReportPrint.CreateParameter("intSelection",
adInteger, adParamInput, , intMainSelection)
cmdReportPrint.Parameters.Append prmGroupValue
Set prmGroupValue = cmdReportPrint.CreateParameter("strSQL", adChar,
adParamInput, lngPrmSize, strSQL)
cmdReportPrint.Parameters.Append prmGroupValue
cmdReportPrint.Execute
Set prmReportPrint = Nothing
Set prmGroupValue = Nothing
Set cmdReportPrint = Nothing
Let strSQL = ""
Exit_Report_Open:
Exit Sub
Err_Report_Open:
msgbox "Error Number " & Err.Number & " " & Err.Description
Resume Exit_Report_Open
End Sub
I am attempting to execute an append query with paramaters. In the criteria
section of my query I have a parameter called strSQL. In the Query Parameters
Box I used the data type "Text". I placed [strSQL] in the criteria box.
All of my parameters where I use the datatypeenum "integer" work well. I
continuously get an error when I try to set a parameter using a string. I am
using the datatypeenum adChar and I have already tried using all of the
datatypeenums that can be used with Text. They all produce an error. I also
have the size declared as Long and am using a variable for the string length.
I have tried setting the size to 4 times the length of the string and I still
get an error.
I would appreciate any feedback I can get. I have pasted the code below.
Regards,
Pete
Private Sub Report_Open(Cancel As Integer)
On Error GoTo Err_Report_Open
'Upon opening, this form executes UpdateTable which updates tblSECTIONLIST.
tblSectionList determines the data
'order for rstAgendaData which is the record source for this report.
Dim cmdReportPrint As New ADODB.Command, prmReportPrint As New
ADODB.Parameter, cnn As New ADODB.Connection, _
prmGroupValue As New ADODB.Parameter, frm As Form, intGroupValue As Integer,
intDate As Integer, _
strSQL As String, intNumColumns As Integer, cat As New ADOX.Catalog, col As
ADOX.Column, lngSeed As Long, _
ChangeSeed As Boolean, intI As Integer
Set frm = Forms!frmReportPrint
Let intDate = frm.cboMeetingDate
Let intGroupValue = frm.grpPrintReport.Value
Call UpdateTable
DoCmd.SetWarnings False
Set cnn = CurrentProject.Connection
cnn.Execute "qdfClearReportTable"
DoCmd.SetWarnings True
Let lngSeed = 1
cat.ActiveConnection = cnn
Set col = cat.Tables("tblReportRecSource").Columns("PrimaryKey")
col.Properties("Seed") = lngSeed
cat.Tables("tblReportRecSource").Columns.Refresh
If col.Properties("Seed") = lngSeed Then
ChangeSeed = True
Else: ChangeSeed = False
End If
cnn.Close
Set col = Nothing
Set cat = Nothing
Set cnn = Nothing
Let strSQL = "(((qdfReportRecSource.SA_AlertNumber) Is Null)) OR "
intNumColumns = frm![lstSelectedFlds].ListCount
For intI = 0 To intNumColumns
If frm![lstSelectedFlds].Column(0, intI) > "" Then
If intI = 0 Then
Let strSQL = strSQL & "(((qdfReportRecSource.SA_AlertNumber)=" &
Chr(34) & frm![lstSelectedFlds].Column(0, intI) & Chr(34) & "))"
Else
Let strSQL = strSQL & " OR
(((qdfReportRecSource.SA_AlertNumber)=" & Chr(34) &
frm![lstSelectedFlds].Column(0, intI) & Chr(34) & "))"
End If
End If
Next intI
Let strSQL = strSQL & Chr(59)
Set cmdReportPrint.ActiveConnection = CurrentProject.Connection
cmdReportPrint.CommandType = adCmdText
cmdReportPrint.CommandText = strSQL
Set prmReportPrint = cmdReportPrint.CreateParameter("intDate", adInteger,
adParamInput, , intDate)
cmdReportPrint.Parameters.Append prmReportPrint
Set prmGroupValue = cmdReportPrint.CreateParameter("intGroupValue",
adInteger, adParamInput, , intGroupValue)
cmdReportPrint.Parameters.Append prmGroupValue
Set prmGroupValue = cmdReportPrint.CreateParameter("intSelection",
adInteger, adParamInput, , intMainSelection)
cmdReportPrint.Parameters.Append prmGroupValue
Set prmGroupValue = cmdReportPrint.CreateParameter("strSQL", adChar,
adParamInput, lngPrmSize, strSQL)
cmdReportPrint.Parameters.Append prmGroupValue
cmdReportPrint.Execute
Set prmReportPrint = Nothing
Set prmGroupValue = Nothing
Set cmdReportPrint = Nothing
Let strSQL = ""
Exit_Report_Open:
Exit Sub
Err_Report_Open:
msgbox "Error Number " & Err.Number & " " & Err.Description
Resume Exit_Report_Open
End Sub