Query must have at least one destination field- running pass through query (1 Viewer)

P

p595659

Registered User.
Local time
Today, 02:25
Joined
Jul 5, 2006
Messages
30
Hi Guys, I'm trying to make a pass through query which connects to a postgresql server. However, When I try running it by clicking on a button, I get:

"Run-time error '3306':
Query must have at least one destination field

I have tried debugging it and all the values in the parameters are being recieved from the forms. and the first parameter is being highlighted. In the occasion I did try running the query, all the fields were null (expected) apart from PAdviser_ID, which was 18.

I've attached my code, if anyone wants to have a look.

Code: 
Dim MyDb As DAO.Database, MyQry As QueryDef, MyRS As DAO.Recordset
   Set MyDb = CurrentDb()
   Set MyQry = MyDb.CreateQueryDef("")
   
   MyQry.Parameters("PAdviser_ID") = [Forms]![frm_MainMenu]![CurrentReport]
   MyQry.Parameters("PProvider_ID") = [Forms]![frm_ReportSelect]![Provider_ID]
   MyQry.Parameters("PIntroducer_ID") = [Forms]![frm_ReportSelect]![Introducer_ID]
   MyQry.Parameters("PPlanGroup_ID") = [Forms]![frm_ReportSelect]![PlanGroup_ID]
   MyQry.Parameters("PPlanType_ID") = [Forms]![frm_ReportSelect]![PlanType_ID]
   MyQry.Parameters("PDateSpecific_Start") = [Forms]![frm_ReportSelect]![DateSpecific_Start]
   MyQry.Parameters("PDateSpecific_End") = [Forms]![frm_ReportSelect]![DateSpecific_End]
   MyQry.Parameters("PDate_Start") = [Forms]![frm_ReportSelect]![Child26]![DateList_Start]
   MyQry.Parameters("PDate_End") = [Forms]![frm_ReportSelect]![Child24]![DateList_End]
   
   MyQry.Connect = "ODBC;DRIVER={PostgreSQL};DATABASE=testing;SERVER=10.0.0.2;PORT=5432;Uid=xxxxxxxxxx;Pwd=xxxxxxxx;"
   
   MyQry.ReturnsRecords = True
   
   If Forms![frm_MainMenu].[CurrentReport] = 18 Then
    MyQry.SQL = "select * from reports as (& PAdviser_ID & ','& PProvider_ID &','& PIntroducer_ID & ',' & PPlanGroup_ID & ',' & PPlanType & ',' & PDateSpecific_Start & ',' & PDateSpecific_End & ',' & PDate_Start & ',' & PDate_End) & ' as  employee_first_name varchar,employee_last_name varchar,date_issued date,client_first_name varchar,client_middle_names varchar,client_surname varchar,tblplantypes.plantype_group varchar,plangroups.plangroups_group varchar,tblproviders.provider_company varchar,policy_number varchar,sum_assured numeric,benefit varchar, premium numeric,brokerage numeric, comments text);'"
   ElseIf Forms![frm_MainMenu].[CurrentReport] = 13 Then
    MyQry.SQL = "select * from reports([Forms]![frm_MainMenu]![CurrentReport],[Forms]![frm_ReportSelect]![Adviser_ID],[Forms]![frm_ReportSelect]![Provider_ID],[Forms]![frm_ReportSelect]![Introducer_ID],[Forms]![frm_ReportSelect]![PlanGroup_ID],[Forms]![frm_ReportSelect]![PlanType_ID],[Forms]![frm_ReportSelect]![DateSpecific_Start],[Forms]![frm_ReportSelect]![DateSpecific_End],[Forms]![frm_ReportSelect]![Child24],[Forms]![frm_ReportSelect]![Child26])"
   ElseIf Forms![frm_MainMenu].[CurrentReport] = 23 Then
    MyQry.SQL = "select * from reports([Forms]![frm_MainMenu]![CurrentReport],[Forms]![frm_ReportSelect]![Adviser_ID],[Forms]![frm_ReportSelect]![Provider_ID],[Forms]![frm_ReportSelect]![Introducer_ID],[Forms]![frm_ReportSelect]![PlanGroup_ID],[Forms]![frm_ReportSelect]![PlanType_ID],[Forms]![frm_ReportSelect]![DateSpecific_Start],[Forms]![frm_ReportSelect]![DateSpecific_End],[Forms]![frm_ReportSelect]![Child24],[Forms]![frm_ReportSelect]![Child26])"
   ElseIf Forms![frm_MainMenu].[CurrentReport] = 25 Then
    MyQry.SQL = "select * from reports([Forms]![frm_MainMenu]![CurrentReport],[Forms]![frm_ReportSelect]![Adviser_ID],[Forms]![frm_ReportSelect]![Provider_ID],[Forms]![frm_ReportSelect]![Introducer_ID],[Forms]![frm_ReportSelect]![PlanGroup_ID],[Forms]![frm_ReportSelect]![PlanType_ID],[Forms]![frm_ReportSelect]![DateSpecific_Start],[Forms]![frm_ReportSelect]![DateSpecific_End],[Forms]![frm_ReportSelect]![Child24],[Forms]![frm_ReportSelect]![Child26])"
   End If
   
   
   
   MyQry.Execute (MyQry.SQL)
   Set MyRS = MyQry.OpenRecordset()
   MyRS.MoveFirst

   Debug.Print MyRS!attribute_id, MyRS!attribute_name, _
      MyRS!attribute_value

   MyQry.Close
   MyRS.Close
   MyDb.Close

If you want any more information, or to ask any questions then feel free to ask.

Cheers,
Ben
 
Last edited:
S

steevie_t

Registered User.
Local time
Today, 01:25
Joined
Feb 9, 2006
Messages
21
You say the first parameter is being highlighted when you debug... Perhaps setting the SQL string first would resolve this.

I'd do Connection, SQL, ReturnsRecords then Parameters
 
P

p595659

Registered User.
Local time
Today, 02:25
Joined
Jul 5, 2006
Messages
30
Hi Steevie,
thanks for your reply.

I've changed the order of the code as you have suggusted, and now I'm getting a different error:

Run-time error '3265'
Item not found in this collection

I've attached my stored procedure also in case its anything in there.

Here's my updated code:

Code: 
Dim MyDb As DAO.Database, MyQry As QueryDef, MyRS As DAO.Recordset
   Set MyDb = CurrentDb()
   Set MyQry = MyDb.CreateQueryDef("")
   
    MyQry.Connect = "ODBC;DRIVER={PostgreSQL};DATABASE=testing;SERVER=10.0.0.2;PORT=5432;Uid=xxxxxxxxxxxxx;Pwd=xxxxxxx;"
        
   MyQry.ReturnsRecords = True
   
     If Forms![frm_MainMenu].[CurrentReport] = 18 Then
    MyQry.SQL = "select * from reports as (& PAdviser_ID & ','& PProvider_ID &','& PIntroducer_ID & ',' & PPlanGroup_ID & ',' & PPlanType & ',' & PDateSpecific_Start & ',' & PDateSpecific_End & ',' & PDate_Start & ',' & PDate_End) & ' as  employee_first_name varchar,employee_last_name varchar,date_issued date,client_first_name varchar,client_middle_names varchar,client_surname varchar,tblplantypes.plantype_group varchar,plangroups.plangroups_group varchar,tblproviders.provider_company varchar,policy_number varchar,sum_assured numeric,benefit varchar, premium numeric,brokerage numeric, comments text);'"
   ElseIf Forms![frm_MainMenu].[CurrentReport] = 13 Then
    MyQry.SQL = "select * from reports([Forms]![frm_MainMenu]![CurrentReport],[Forms]![frm_ReportSelect]![Adviser_ID],[Forms]![frm_ReportSelect]![Provider_ID],[Forms]![frm_ReportSelect]![Introducer_ID],[Forms]![frm_ReportSelect]![PlanGroup_ID],[Forms]![frm_ReportSelect]![PlanType_ID],[Forms]![frm_ReportSelect]![DateSpecific_Start],[Forms]![frm_ReportSelect]![DateSpecific_End],[Forms]![frm_ReportSelect]![Child24],[Forms]![frm_ReportSelect]![Child26])"
   ElseIf Forms![frm_MainMenu].[CurrentReport] = 23 Then
    MyQry.SQL = "select * from reports([Forms]![frm_MainMenu]![CurrentReport],[Forms]![frm_ReportSelect]![Adviser_ID],[Forms]![frm_ReportSelect]![Provider_ID],[Forms]![frm_ReportSelect]![Introducer_ID],[Forms]![frm_ReportSelect]![PlanGroup_ID],[Forms]![frm_ReportSelect]![PlanType_ID],[Forms]![frm_ReportSelect]![DateSpecific_Start],[Forms]![frm_ReportSelect]![DateSpecific_End],[Forms]![frm_ReportSelect]![Child24],[Forms]![frm_ReportSelect]![Child26])"
   ElseIf Forms![frm_MainMenu].[CurrentReport] = 25 Then
    MyQry.SQL = "select * from reports([Forms]![frm_MainMenu]![CurrentReport],[Forms]![frm_ReportSelect]![Adviser_ID],[Forms]![frm_ReportSelect]![Provider_ID],[Forms]![frm_ReportSelect]![Introducer_ID],[Forms]![frm_ReportSelect]![PlanGroup_ID],[Forms]![frm_ReportSelect]![PlanType_ID],[Forms]![frm_ReportSelect]![DateSpecific_Start],[Forms]![frm_ReportSelect]![DateSpecific_End],[Forms]![frm_ReportSelect]![Child24],[Forms]![frm_ReportSelect]![Child26])"
   End If
   
   MyQry.Parameters("PAdviser_ID") = [Forms]![frm_MainMenu]![CurrentReport]
   MyQry.Parameters("PProvider_ID") = [Forms]![frm_ReportSelect]![Provider_ID]
   MyQry.Parameters("PIntroducer_ID") = [Forms]![frm_ReportSelect]![Introducer_ID]
   MyQry.Parameters("PPlanGroup_ID") = [Forms]![frm_ReportSelect]![PlanGroup_ID]
   MyQry.Parameters("PPlanType_ID") = [Forms]![frm_ReportSelect]![PlanType_ID]
   MyQry.Parameters("PDateSpecific_Start") = [Forms]![frm_ReportSelect]![DateSpecific_Start]
   MyQry.Parameters("PDateSpecific_End") = [Forms]![frm_ReportSelect]![DateSpecific_End]
   MyQry.Parameters("PDate_Start") = [Forms]![frm_ReportSelect]![Child26]![DateList_Start]
   MyQry.Parameters("PDate_End") = [Forms]![frm_ReportSelect]![Child24]![DateList_End]
     
   MyQry.Execute (MyQry.SQL)
   Set MyRS = MyQry.OpenRecordset()
   MyRS.MoveFirst

   Debug.Print MyRS!attribute_id, MyRS!attribute_name, _
      MyRS!attribute_value

   MyQry.Close
   MyRS.Close
   MyDb.Close

Any ideas? In debug mode, the first parameter is being highlighted and all the values from the forms are getting recieved in the code.

Cheers,
Ben
 

Attachments

  • reports_sp.txt
    7.5 KB · Views: 127
F

FireStrike

Registered User.
Local time
Yesterday, 20:25
Joined
Jul 14, 2006
Messages
69
on ething that may make it alittle easier to find the error is to copy your sql statement to a string like strSQL and then run your command like

MyQry.SQL = strSQL

Then you while in run time you can put strSQL into the immediate window to see what the actual SQL statment is running. That should tell you where the error is
 
P

p595659

Registered User.
Local time
Today, 02:25
Joined
Jul 5, 2006
Messages
30
FireStrike said:
on ething that may make it alittle easier to find the error is to copy your sql statement to a string like strSQL and then run your command like

MyQry.SQL = strSQL

Then you while in run time you can put strSQL into the immediate window to see what the actual SQL statment is running. That should tell you where the error is
Click to expand...

Hi FireStrike,
that method wouldn't work, the SQL is taking 9 parameters from the form and it stops before the variables are swapped in. Just to summerize so far:

Code: 
   MyQry.Parameters("PAdviser_ID") = [Forms]![frm_MainMenu]![CurrentReport]

MyQry.Parameters("PAdviser_ID") - this part is saying item not found in this collection

[Forms]![frm_MainMenu]![CurrentReport] - this is getting the data from the field, which has a value of 18.

It's the same for all of the other parameters. Any ideas?

Cheers,
Ben
 
allan57

allan57

Allan
Local time
Today, 01:25
Joined
Nov 29, 2004
Messages
336
I not familiar with postgresql server but below is a couple of examples of how I use pass through queries with parameters to MS SQL Server. Hope it helps you in some way.

Option Compare Database
Option Explicit

Private Sub Form_Load()

TestSqlPassThrough_SELECT
TestSqlPassThrough_UPDATE

End Sub

Public Function TestSqlPassThrough_SELECT()

Dim dbs As DAO.Database
Dim qd As QueryDef
Dim rstGetDataGetData As Recordset
Dim strSQL As String

On Error Resume Next

'DELETE EXISTING QUERY SO THAT IT CAN BE REBUILT USING UPDATED PARAMETERS
DoCmd.DeleteObject acQuery, "1_Pass_Through_Queries_with_Parameters_SELECT"

'For the example I have hard coded the criteria but you can use text boxs etc
strSQL = "SELECT AS400_BPCS_IIM.IPROD, AS400_BPCS_IIM.IDESC, AS400_BPCS_IIM.ICLAS " & _
"FROM AS400_BPCS_IIM " & _
"WHERE AS400_BPCS_IIM.ICLAS='BF';"

Set dbs = CurrentDb()
Set qd = dbs.CreateQueryDef("1_Pass_Through_Queries_with_Parameters_SELECT", strSQL)

qd.SQL = strSQL
qd.Connect = "ODBC;DSN=sensdata;UID=TAYLORAL;DATABASE=SensData;Network=DBMSSOCN;Trusted_Connection=Yes;Regional=Yes"
qd.ODBCTimeout = 0
qd.ReturnsRecords = True

Set rstGetData = qd.OpenRecordset()

rstGetData.MoveLast
rstGetData.MoveFirstGetData

MsgBox rstGetData.RecordCount

dbs.Close
qd.Close
rstGetDataGetData.Close

Set dbs = Nothing
Set qd = Nothing
Set rstGetDataGetData = Nothing

End Function


Public Function TestSqlPassThrough_UPDATE()

Dim dbs As DAO.Database
Dim qd As QueryDef
Dim strSQL As String

On Error Resume Next

'DELETE EXISTING QUERY SO THAT IT CAN BE REBUILT USING UPDATED PARAMETERS
DoCmd.DeleteObject acQuery, "1_Pass_Through_Queries_with_Parameters_UPDATE"

'For the example I have hard coded the criteria but you can use text boxs etc
strSQL = "UPDATE AS400_BPCS_IIM SET AS400_BPCS_IIM.ICLAS = '6A' WHERE AS400_BPCS_IIM.ICLAS='ZZ';"

Set dbs = CurrentDb()
Set qd = dbs.CreateQueryDef("1_Pass_Through_Queries_with_Parameters_UPDATE", strSQL)

qd.SQL = strSQL
qd.Connect = "ODBC;DSN=sensdata;UID=TAYLORAL;DATABASE=SensData;Network=DBMSSOCN;Trusted_Connection=Yes;Regional=Yes"
qd.ODBCTimeout = 0
qd.ReturnsRecords = True

dbs.Close
qd.Close
rstGetDataGetData.Close

Set dbs = Nothing
Set qd = Nothing
Set rstGetDataGetData = Nothing

End Function
 
You must log in or register to reply here.

Users who are viewing this thread

Total: 1 (members: 0, guests: 1)
Top Bottom