too few paramters. expected 3 (1 Viewer)

bretch

Registered User.
Local time
Today, 22:30
Joined
May 19, 2003
Messages
17
Can somebody please help me with this:
highlighted during error: Set Rsdb = DB.OpenRecordset("qry_bscactivity", dbOpenDynaset)

heres the code:

Private Sub Command0_Click()

Dim DB As Database
Dim Rsdb As Recordset
Dim i As Integer, j As Integer
Dim RsSql As Variant
Dim CurrentValue As Variant
Dim CurrentField As Variant
Dim Workbook As Object
Dim xlApp As Object
Dim Sheet As Object

Set DB = DBEngine.Workspaces(0).Databases(0)

Set Rsdb = DB.OpenRecordset("qry_bscactivity", dbOpenDynaset)
Set xlApp = CreateObject("Excel.Application")
xlApp.Visible = True
xlApp.Workbooks.Add
Set Sheet = xlApp.ActiveWorkbook.Sheets(1)
j = 1

' Loop through the Microsoft Access field names and create
' the Microsoft Excel labels.
For i = 0 To Rsdb.Fields.Count - 1
CurrentValue = Rsdb.Fields(i).Name
Sheet.Cells(j, i + 1).Value = CurrentValue
Next i

j = 2

' Loop through the Microsoft Access records and copy the records
' to the Microsoft Excel spreadsheet.
Do Until Rsdb.EOF
For i = 0 To Rsdb.Fields.Count - 1
CurrentField = Rsdb(i)
Sheet.Cells(j, i + 1).Value = CurrentField
Next i
Rsdb.MoveNext
j = j + 1
Loop

' Print the Microsoft Excel spreadsheet.
'Sheet.PrintOut

' Close workbook without saving.
'xlApp.ActiveWorkbook.Saved = True
Set Sheet = Nothing
'xlApp.Quit
Set xlApp = Nothing

End Sub


here's the query referred to:

SELECT [tbl_bscactivity].[network_element] AS [Network Element], [tbl_bscactivity].[implementation_date] AS [Implementation Date], [tbl_bscactivity].activity AS Activity, [tbl_bscactivity].[mar_reference] AS [MAR Reference], [tbl_bscactivity].[service_affecting] AS Effect, [tbl_bscactivity].[site_name] AS [Site Name], [tbl_bscactivity].[start_time] AS [Start Time], [tbl_bscactivity].[end_time] AS [End Time], [tbl_bscactivity].remarks AS Remarks, [tbl_bscactivity].[contact_person] AS [Contact Person], [tbl_bscactivity].[bsc_engr] AS [BSC Engr], *
FROM tbl_bscactivity
WHERE ((([tbl_bscactivity].[network_element])=IIf([forms]![frm_reports]![frmnetworkelement].[value] Like "BSS Iligan",[network_element],[forms]![frm_reports]![frmnetworkelement].[value])) AND (([tbl_bscactivity].[implementation_date]) Between [forms]![frm_reports]![beginningdate] And [forms]![frm_reports]![enddate]))
ORDER BY [tbl_bscactivity].[network_element] DESC;
 

Pat Hartman

Super Moderator
Staff member
Local time
Today, 10:30
Joined
Feb 19, 2002
Messages
42,984
When you run parameter queries from code, you need to expressly supply the parameters. Here is a sample that does that:

Code:
Dim WgtDB As DAO.Database
Dim QD1 As DAO.QueryDef
Dim RST1 As DAO.Recordset
Set WgtDB = CurrentDb
Set QD1 = WgtDB.QueryDefs!QFindFormulaDesc
QD1.Parameters![Formula] = gFormula
QD1.Parameters![Plant] = gPlant
Set RST1 = QD1.OpenRecordset
If RST1.EOF Then
-------recordset is empty 
Else
-------recordset has data 
End If
QD1.Close
 

bretch

Registered User.
Local time
Today, 22:30
Joined
May 19, 2003
Messages
17
:confused:
i'm having a hard time with what you are suggesting. I am still learning(newbie) VBA, i havent have any idea about create/using Querydefs. Can you please share with me a sample database that i can study. Also i dont know how to reference values from a form. please help me....
 

Pat Hartman

Super Moderator
Staff member
Local time
Today, 10:30
Joined
Feb 19, 2002
Messages
42,984
Replace:
Dim DB As Database
Dim Rsdb As Recordset
Set Rsdb = DB.OpenRecordset("qry_bscactivity", dbOpenDynaset)

With:
Dim DB As DAO.Database
Dim Rsdb As DAO.Recordset
Dim QD1 As DAO.QueryDef
Set QD1 = DB.QueryDefs![qry_bscactivity]
QD1.Parameters![forms]![frm_reports]![frmnetworkelement] = [forms]![frm_reports]![frmnetworkelement]
QD1.Parameters![forms]![frm_reports]![frmnetworkelement] =[forms]![frm_reports]![frmnetworkelement]
QD1.Parameters![forms]![frm_reports]![beginningdate] = [forms]![frm_reports]![beginningdate]
QD1.Paramters![forms]![frm_reports]![enddate] = [forms]![frm_reports]![enddate]
QD1.Parameters![Plant] = gPlant
Set Rsdb = QD1.OpenRecordset


Someone may be able to convert this to ADO for you.

WATCH THE WORD WRAP. I'm having trouble getting it right.
 

bretch

Registered User.
Local time
Today, 22:30
Joined
May 19, 2003
Messages
17
hello pat,

I had tried your suggested script below but it prompted a run time error: OBJECT VARIABLE OR WITH BLOCK VARIABLE NOT SET.
the error points to the line: Set QD1 = DB.QueryDefs![qry_bscactivity]
 

Tim K.

Registered User.
Local time
Today, 14:30
Joined
Aug 1, 2002
Messages
242
There are 2 ways to deal with your problem.

1. Make the query a real parametered query. Check this How to make a parametered query out.

Then you can use what Pat has suggested lately.

2. Modify the SQL of the qry_bscactivity on-fly by replacing the parameters with the current values on the frm_reports form.

Code:
Private Sub Command0_Click() 

Dim DB As Database 
Dim Rsdb As Recordset 
Dim i As Integer, j As Integer 
Dim RsSql As Variant 
Dim CurrentValue As Variant 
Dim CurrentField As Variant 
Dim Workbook As Object 
Dim xlApp As Object 
Dim Sheet As Object 

Dim strSQL As String
Dim qdf As Object

Set DB = CurrentDb 

Set qdf = DB.QueryDefs("qry_bscactivity")

strSQL = "SELECT [tbl_bscactivity].[network_element] AS [Network Element], " _
   & "[tbl_bscactivity].[implementation_date] AS [Implementation Date], " _
   & "[tbl_bscactivity].activity AS Activity, [tbl_bscactivity].[mar_reference] AS [MAR Reference], " _
   & "[tbl_bscactivity].[service_affecting] AS Effect, [tbl_bscactivity].[site_name] " _ 
   & "AS [Site Name], [tbl_bscactivity].[start_time] AS [Start Time], " _
   & "[tbl_bscactivity].[end_time] AS [End Time], [tbl_bscactivity].remarks " _ 
   & "AS Remarks, [tbl_bscactivity].[contact_person] AS [Contact Person], " _
   & "[tbl_bscactivity].[bsc_engr] AS [BSC Engr], * " _
   & "FROM tbl_bscactivity " _
   & "WHERE ((([tbl_bscactivity]. [network_element])="

If [forms]![frm_reports]![frmnetworkelement].[value] Like "BSS Iligan" Then
   strSQL = strSQL & "[network_element]"
Else
   strSQL = strSQL & [forms]![frm_reports]![frmnetworkelement].[value]
End If

strSQL = strSQL & " AND [tbl_bscactivity].[implementation_date]  Between #" _
   & [forms]![frm_reports]![beginningdate] "# And #" _
   & [forms]![frm_reports]![enddate] & "# " _
   & "ORDER BY [tbl_bscactivity].[network_element] DESC;"
qdf.SQL = strSQL

Set Rsdb = DB.OpenRecordset("qry_bscactivity", dbOpenDynaset) 
Set xlApp = CreateObject("Excel.Application") 
xlApp.Visible = True 
xlApp.Workbooks.Add 
Set Sheet = xlApp.ActiveWorkbook.Sheets(1) 
j = 1 

' Loop through the Microsoft Access field names and create 
' the Microsoft Excel labels. 
For i = 0 To Rsdb.Fields.Count - 1 
    CurrentValue = Rsdb.Fields(i).Name 
    Sheet.Cells(j, i + 1).Value = CurrentValue 
Next i 

j = 2 

' Loop through the Microsoft Access records and copy the records 
' to the Microsoft Excel spreadsheet. 
Do Until Rsdb.EOF 
   For i = 0 To Rsdb.Fields.Count - 1 
      CurrentField = Rsdb(i) 
      Sheet.Cells(j, i + 1).Value = CurrentField 
   Next i 
   Rsdb.MoveNext 
   j = j + 1 
Loop 

' Print the Microsoft Excel spreadsheet. 
'Sheet.PrintOut 

' Close workbook without saving. 
'xlApp.ActiveWorkbook.Saved = True 
Set Sheet = Nothing 
'xlApp.Quit 
Set xlApp = Nothing 

End Sub
 

bretch

Registered User.
Local time
Today, 22:30
Joined
May 19, 2003
Messages
17
:) thanks Tim,

The code below from the one you supplied prompts a "syntax error":

& [forms]![frm_reports]![beginningdate] "# And #" _
& [forms]![frm_reports]![enddate] & "# " _
& "ORDER BY [tbl_bscactivity].[network_element] DESC;"

plsss help me again
million thanks
 

Tim K.

Registered User.
Local time
Today, 14:30
Joined
Aug 1, 2002
Messages
242
Sorry for that. Now try this.

& [forms]![frm_reports]![beginningdate] & "# And #" _
& [forms]![frm_reports]![enddate] & "# " _
& "ORDER BY [tbl_bscactivity].[network_element] DESC;"
 

bretch

Registered User.
Local time
Today, 22:30
Joined
May 19, 2003
Messages
17
Hi Timk,

I am still getting the "too few parameters. expected 1" error at this line....

Set Rsdb = DB.OpenRecordset("qry_bscactivity", dbOpenDynaset)

attached is the edited code. I removed the AS expressions on the query so as not to complicate/confuse me....

thanks
 

Attachments

  • commandclick2.txt
    2.3 KB · Views: 741

Tim K.

Registered User.
Local time
Today, 14:30
Joined
Aug 1, 2002
Messages
242
I guess it has to do with this line:

Code:
...
If [forms]![frm_reports]![frmnetworkelement].[value] Like "BSS Iligan" Then
   strSQL = strSQL & "[network_element]"
Else
...

What is the [network_element]? Is it a field name?

Check the qry_bscactivity query and view it manually and see if it returns what you want.
 

bretch

Registered User.
Local time
Today, 22:30
Joined
May 19, 2003
Messages
17
yes the [network_element] is a field name..

It is like if you chose "BSS Iligan" in the control(forms!frm_reports!frmnetworkelement) the query would output all the records between the "beginningdate" and the "enddate"

Else it the query would return the records with the specific value of the control(forms!frm_reports!frmnetworkelement) between the "beginningdate" and the "enddate"...


I have checked the query manually and found out that though
"WHERE [tbl_bscactivity].[network_element]=EILIGAN1 AND [tbl_bscactivity].[implementation_date] Between #5/1/2003# And #5/15/2003 #
ORDER BY [tbl_bscactivity].[network_element] DESC;" the query will require the value of EILIGAN1....

:confused:
 

Tim K.

Registered User.
Local time
Today, 14:30
Joined
Aug 1, 2002
Messages
242
Then change this part from:

Code:
...
   & "FROM tbl_bscactivity " _
   & "WHERE ((([tbl_bscactivity]. [network_element])="

If [forms]![frm_reports]![frmnetworkelement].[value] Like "BSS Iligan" Then
   strSQL = strSQL & "[network_element]"
Else
   strSQL = strSQL & [forms]![frm_reports]![frmnetworkelement].[value]
End If

strSQL = strSQL & " AND [tbl_bscactivity].[implementation_date]  Between #" _
   & [forms]![frm_reports]![beginningdate] "# And #" _
   & [forms]![frm_reports]![enddate] & "# " _
   & "ORDER BY [tbl_bscactivity].[network_element] DESC;"

...

to

Code:
...
   & "FROM tbl_bscactivity "

   & "WHERE ((([tbl_bscactivity]. [network_element])="

If [forms]![frm_reports]![frmnetworkelement].[value] <> "BSS Iligan" Then
   strSQL = strSQL & "WHERE ((([tbl_bscactivity].[network_element])=" & [forms]![frm_reports]![frmnetworkelement].[value] & " AND "
Else
   strSQL = "Where"
End If

strSQL = strSQL & " [tbl_bscactivity].[implementation_date]  Between #" _
   & [forms]![frm_reports]![beginningdate] "# And #" _
   & [forms]![frm_reports]![enddate] & "# " _
   & "ORDER BY [tbl_bscactivity].[network_element] DESC;"
...
 

bretch

Registered User.
Local time
Today, 22:30
Joined
May 19, 2003
Messages
17
Hi Timk,

Thank you very much of your help, finally it got working. I have modified some of the code you gave... here is the modified version of the case statement...

If [Forms]![frm_reports]![frmnetworkelement].[Value] <> "BSS Iligan" Then
strSQL = strSQL & "WHERE ([tbl_bscactivity].[network_element])= '" + [Forms]![frm_reports]![frmnetworkelement].[Value] + "'" & "AND"
Else
strSQL = strSQL & "Where"
End If

strSQL = strSQL & " [tbl_bscactivity].[implementation_date] Between #" _
& [Forms]![frm_reports]![beginningdate] & "# And #" _
& [Forms]![frm_reports]![enddate] & "# " _
& "ORDER BY [tbl_bscactivity].[network_element] DESC;"


the problem before was with the value of the control ([Forms]![frm_reports]![frmnetworkelement].[Value]) that is of string type... it was solved by enclosing the control with
'" + [Forms]![frm_reports]![frmnetworkelement].[Value] + "'"

however the query outputs all the fields in the queried table though selecting only part of the fields. How will this be done to limit the fields according to the fields that are required in the query?...

thanks again...

:D
 

Tim K.

Registered User.
Local time
Today, 14:30
Joined
Aug 1, 2002
Messages
242
however the query outputs all the fields in the queried table though selecting only part of the fields. How will this be done to limit the fields according to the fields that are required in the query?...

Sorry. I don't quite understand what you want here.

You want to show only a certain fields? So what fields do you want to show?

Or you want to show only a certain part of a given field? To do this, you can use Left(), Mid() or Right() to help.

Or to filter the query with a wild card like this, Like "*" & "Doe" & "*"
 

bretch

Registered User.
Local time
Today, 22:30
Joined
May 19, 2003
Messages
17
Hello TimK

The table being querried has the fields arranged in this order...
network_element, implementation_date, activity, mar_reference, service_affecting, site_name, start_time, end_time, notes, remarks, contact_person, bsc_engr, date_received, issued_by...

how would i remove the field "notes" from the query in the code below?
 

Tim K.

Registered User.
Local time
Today, 14:30
Joined
Aug 1, 2002
Messages
242
Just remove the * out from the SQL like this:

Code:
... 
  & "[tbl_bscactivity].[implementation_date] AS [Implementation Date], " _
   & "[tbl_bscactivity].activity AS Activity, [tbl_bscactivity].[mar_reference] AS [MAR Reference], " _
   & "[tbl_bscactivity].[service_affecting] AS Effect, [tbl_bscactivity].[site_name] " _ 
   & "AS [Site Name], [tbl_bscactivity].[start_time] AS [Start Time], " _
   & "[tbl_bscactivity].[end_time] AS [End Time], [tbl_bscactivity].remarks " _ 
   & "AS Remarks, [tbl_bscactivity].[contact_person] AS [Contact Person], " _
   & "[tbl_bscactivity].[bsc_engr] AS [BSC Engr], remarks, contact_person, bsc_engr, date_received " _
   & "FROM tbl_bscactivity " _
   & "WHERE ((([tbl_bscactivity]. [network_element])="
...
[code]
 

bretch

Registered User.
Local time
Today, 22:30
Joined
May 19, 2003
Messages
17
Thank you very much Timk, for your help....:)

I still have a question though regarding saving of files with "save as" method... from the code below, how will the code be if i would have the file be automatically saved as another file name bearing the current date when the file is generated...
example from the file

"BSS DAR template.xls" to "BSSDAR05302003.xls"


Set xlapp = CreateObject("Excel.Application")
xlapp.Visible = True
Set workbook = xlapp.Workbooks.Open("C:\Documents and Settings\aca5398\My Documents\aca docs 2\Projects\Centralized DB\BSS DAR template")
Set sheet = workbook.Worksheets("DAR")
.
.
.
.
xlapp.ActiveWorkbook.SaveAs
Set sheet = Nothing
Set xlapp = Nothing
Set Rsdb = Nothing
 

Tim K.

Registered User.
Local time
Today, 14:30
Joined
Aug 1, 2002
Messages
242
Try this out.

xlapp.ActiveWorkbook.SaveAs Filename:= _
"C:\"BSSDAR" & Format(Date, "mmddyyyy") & ".xls"
 

bretch

Registered User.
Local time
Today, 22:30
Joined
May 19, 2003
Messages
17
:)
Thanks for the idea TimK......
it worked this way...
.
.
.
xlapp.ActiveWorkbook.SaveAs ("C:\" & " BSSDAR" & Format(date, "mmddyyyy") & ".xls")
.
.
.

thanks

:)
 

Users who are viewing this thread

Top Bottom