Passing parameter to back end database

[Dom Torrez]

I've been trying to find a way to have Access 2003 pass a parameter to a back
end database function. I'm running Access as my front end with postgres as
my back end. I created a function in postgres which needs an ID passed in as
a parameter. Once the field is passed in as the parameter the function
returns a set of data which I need to use for populating the fields in my
form (read only). I can create a Pass-through query in Access with the ID
parameter "hard coded" into the select statement, and it works just fine. My
problem is that I need this to be a static parameter. I want Access to pass
this ID parameter into the pass through query dependant on which set of data
it is in. For example:
select * from dbfunction(forms![form1]![subform1]![subform2]![id]);

Another option I'm looking at is using VB. Something like:

Private Sub Form_Open(Cancel As Integer)
'pass in current idlocale as parameter to query against function.
Dim dbs As Database
Dim rsQuery As DAO.Recordset
Dim strSQL As String
Dim rsid As Integer

Set dbs = CurrentDb()
Set rsid = Me![idRecon]
strSQL = "SELECT idlocale, idrecon, imageid, activity, imagecategory, path,
whentaken, imagecaption FROM getReconImages(rsid)"
Set rsQuery = dbs.OpenRecordset(strSQL, dbOpenSnapshot)
Set
Forms![LocalesDataEntry]![ReconnaissanceDataEntrySF]![ReconImagesSSF]![ImageCategory] = rsQuery![ImageCategory]
End Sub

Does anybody have a good way to accomplish this?
Please help!
-Dom Torrez

 

[Alex Dybenko]

Hi,
you can just modify SQL of a pass-through query:

Currentdb.querydefs("MyQuery").SQL="select * from dbfunction(" &
forms![form1]![subform1]![subform2]![id] & ");"

and then execute query, or use it in code, whatever

--
Best regards,
___________
Alex Dybenko (MVP)
http://alexdyb.blogspot.com
http://www.PointLtd.com

 

[Dom Torrez]

Hi Alex,
So I tried to put the SQL query directly into my passthrough query:
select * from getReconImages(" &
forms![LocalesDataEntry]![ReconnaissanceDataEntrySF]![ReconImagesSSF]![idrecon] & ");

This generated the following message:

ODBC--call failed.
Error while executing the query;
ERROR: column "&
forms![LocalesDataEntry]![ReconnaissanceDataEntrySF]![Recon" does not exist
(#7)

Hi,
you can just modify SQL of a pass-through query:

Currentdb.querydefs("MyQuery").SQL="select * from dbfunction(" &
forms![form1]![subform1]![subform2]![id] & ");"

and then execute query, or use it in code, whatever

--
Best regards,
___________
Alex Dybenko (MVP)
http://alexdyb.blogspot.com
http://www.PointLtd.com

I've been trying to find a way to have Access 2003 pass a parameter to a
back
end database function. I'm running Access as my front end with postgres
as
my back end. I created a function in postgres which needs an ID passed in
as
a parameter. Once the field is passed in as the parameter the function
returns a set of data which I need to use for populating the fields in my
form (read only). I can create a Pass-through query in Access with the ID
parameter "hard coded" into the select statement, and it works just fine.
My
problem is that I need this to be a static parameter. I want Access to
pass
this ID parameter into the pass through query dependant on which set of
data
it is in. For example:
select * from dbfunction(forms![form1]![subform1]![subform2]![id]);

Another option I'm looking at is using VB. Something like:

Private Sub Form_Open(Cancel As Integer)
'pass in current idlocale as parameter to query against function.
Dim dbs As Database
Dim rsQuery As DAO.Recordset
Dim strSQL As String
Dim rsid As Integer

Set dbs = CurrentDb()
Set rsid = Me![idRecon]
strSQL = "SELECT idlocale, idrecon, imageid, activity, imagecategory,
path,
whentaken, imagecaption FROM getReconImages(rsid)"
Set rsQuery = dbs.OpenRecordset(strSQL, dbOpenSnapshot)
Set
Forms![LocalesDataEntry]![ReconnaissanceDataEntrySF]![ReconImagesSSF]![ImageCategory]
= rsQuery![ImageCategory]
End Sub

Does anybody have a good way to accomplish this?
Please help!
-Dom Torrez

 

[Alex Dybenko]

no, you have to run what I wrote in VBA:

Currentdb.querydefs("MyQuery").SQL="select * from dbfunction(" &
forms![form1]![subform1]![subform2]![id] & ");"

result of this line - your query's MyQuery SQL will be:

select * from dbfunction(1)

in case id is equal 1


--
Best regards,
___________
Alex Dybenko (MVP)
http://alexdyb.blogspot.com
http://www.PointLtd.com

Hi Alex,
So I tried to put the SQL query directly into my passthrough query:
select * from getReconImages(" &
forms![LocalesDataEntry]![ReconnaissanceDataEntrySF]![ReconImagesSSF]![idrecon]
& ");

This generated the following message:

ODBC--call failed.
Error while executing the query;
ERROR: column "&
forms![LocalesDataEntry]![ReconnaissanceDataEntrySF]![Recon" does not
exist
(#7)

Hi,
you can just modify SQL of a pass-through query:

Currentdb.querydefs("MyQuery").SQL="select * from dbfunction(" &
forms![form1]![subform1]![subform2]![id] & ");"

and then execute query, or use it in code, whatever

--
Best regards,
___________
Alex Dybenko (MVP)
http://alexdyb.blogspot.com
http://www.PointLtd.com

I've been trying to find a way to have Access 2003 pass a parameter to
a
back
end database function. I'm running Access as my front end with
postgres
as
my back end. I created a function in postgres which needs an ID passed
in
as
a parameter. Once the field is passed in as the parameter the function
returns a set of data which I need to use for populating the fields in
my
form (read only). I can create a Pass-through query in Access with the
ID
parameter "hard coded" into the select statement, and it works just
fine.
My
problem is that I need this to be a static parameter. I want Access to
pass
this ID parameter into the pass through query dependant on which set of
data
it is in. For example:
select * from dbfunction(forms![form1]![subform1]![subform2]![id]);

Another option I'm looking at is using VB. Something like:

Private Sub Form_Open(Cancel As Integer)
'pass in current idlocale as parameter to query against function.
Dim dbs As Database
Dim rsQuery As DAO.Recordset
Dim strSQL As String
Dim rsid As Integer

Set dbs = CurrentDb()
Set rsid = Me![idRecon]
strSQL = "SELECT idlocale, idrecon, imageid, activity, imagecategory,
path,
whentaken, imagecaption FROM getReconImages(rsid)"
Set rsQuery = dbs.OpenRecordset(strSQL, dbOpenSnapshot)
Set
Forms![LocalesDataEntry]![ReconnaissanceDataEntrySF]![ReconImagesSSF]![ImageCategory]
= rsQuery![ImageCategory]
End Sub

Does anybody have a good way to accomplish this?
Please help!
-Dom Torrez

 

[Dom Torrez]

ok...so i've created this in VBA on my form, I get no errors and I can
compile just fine. Problem is that my form doesn't want to see the values.
a couple of questions....1) what should my forms 'record source' be? and 2)
how do I populate the fields in my form with the data returned from the query?

here's the code i'm using:

Private Sub Form_Open(Cancel As Integer)
'pass in current idlocale as parameter to query against function.
Dim dbs As Database
Dim rsQuery As DAO.Recordset
Dim strSQL As String
CurrentDb.QueryDefs("rsQuery").SQL = "select imagecategory, activity,
whentaken, imagecaption, path from getReconImages(" &
Forms![LocalesDataEntry]![ReconnaissanceDataEntrySF]![ReconImagesSSF]![idRecon] & ");"

Set dbs = CurrentDb()
Set rsQuery = dbs.OpenRecordset(strSQL, dbOpenSnapshot)
Set
Forms![LocalesDataEntry]![ReconnaissanceDataEntrySF]![ReconImagesSSF]![ImageCategory] = rsQuery![ImageCategory]
Set
Forms![LocalesDataEntry]![ReconnaissanceDataEntrySF]![ReconImagesSSF]![Activity] = rsQuery![Activity]
Set
Forms![LocalesDataEntry]![ReconnaissanceDataEntrySF]![ReconImagesSSF]![WhenTaken] = rsQuery![WhenTaken]
Set
Forms![LocalesDataEntry]![ReconnaissanceDataEntrySF]![ReconImagesSSF]![ImageCaption] = rsQuery![ImageCaption]
Set
Forms![LocalesDataEntry]![ReconnaissanceDataEntrySF]![ReconImagesSSF]![Path]
= rsQuery![Path]

End Sub

thanks for all the help.
-Dom

no, you have to run what I wrote in VBA:

Currentdb.querydefs("MyQuery").SQL="select * from dbfunction(" &
forms![form1]![subform1]![subform2]![id] & ");"

result of this line - your query's MyQuery SQL will be:

select * from dbfunction(1)

in case id is equal 1


--
Best regards,
___________
Alex Dybenko (MVP)
http://alexdyb.blogspot.com
http://www.PointLtd.com

Hi Alex,
So I tried to put the SQL query directly into my passthrough query:
select * from getReconImages(" &
forms![LocalesDataEntry]![ReconnaissanceDataEntrySF]![ReconImagesSSF]![idrecon]
& ");

This generated the following message:

ODBC--call failed.
Error while executing the query;
ERROR: column "&
forms![LocalesDataEntry]![ReconnaissanceDataEntrySF]![Recon" does not
exist
(#7)

Hi,
you can just modify SQL of a pass-through query:

Currentdb.querydefs("MyQuery").SQL="select * from dbfunction(" &
forms![form1]![subform1]![subform2]![id] & ");"

and then execute query, or use it in code, whatever

--
Best regards,
___________
Alex Dybenko (MVP)
http://alexdyb.blogspot.com
http://www.PointLtd.com


I've been trying to find a way to have Access 2003 pass a parameter to
a
back
end database function. I'm running Access as my front end with
postgres
as
my back end. I created a function in postgres which needs an ID passed
in
as
a parameter. Once the field is passed in as the parameter the function
returns a set of data which I need to use for populating the fields in
my
form (read only). I can create a Pass-through query in Access with the
ID
parameter "hard coded" into the select statement, and it works just
fine.
My
problem is that I need this to be a static parameter. I want Access to
pass
this ID parameter into the pass through query dependant on which set of
data
it is in. For example:
select * from dbfunction(forms![form1]![subform1]![subform2]![id]);

Another option I'm looking at is using VB. Something like:

Private Sub Form_Open(Cancel As Integer)
'pass in current idlocale as parameter to query against function.
Dim dbs As Database
Dim rsQuery As DAO.Recordset
Dim strSQL As String
Dim rsid As Integer

Set dbs = CurrentDb()
Set rsid = Me![idRecon]
strSQL = "SELECT idlocale, idrecon, imageid, activity, imagecategory,
path,
whentaken, imagecaption FROM getReconImages(rsid)"
Set rsQuery = dbs.OpenRecordset(strSQL, dbOpenSnapshot)
Set
Forms![LocalesDataEntry]![ReconnaissanceDataEntrySF]![ReconImagesSSF]![ImageCategory]
= rsQuery![ImageCategory]
End Sub

Does anybody have a good way to accomplish this?
Please help!
-Dom Torrez

 

[Alex Dybenko]

Hi Don,

1. you can bind your form to this query, so you don't need to set each
field:

Private Sub Form_Open(Cancel As Integer)
'pass in current idlocale as parameter to query against function.
Dim dbs As Database
Dim rsQuery As DAO.Recordset
Dim strSQL As String
CurrentDb.QueryDefs("rsQuery").SQL = "select imagecategory, activity,
whentaken, imagecaption, path from getReconImages(" &
Forms![LocalesDataEntry]![ReconnaissanceDataEntrySF]![ReconImagesSSF]![idRecon]
& ");"

me.recordsource="rsQuery" 'perhaps you need to set a recordsource of
subform...
end sub

2. to manually set fields the code should be like this:

Private Sub Form_Open(Cancel As Integer)
'pass in current idlocale as parameter to query against function.
Dim dbs As Database
Dim rsQuery As DAO.Recordset
Dim strSQL As String
CurrentDb.QueryDefs("rsQuery").SQL = "select imagecategory, activity,
whentaken, imagecaption, path from getReconImages(" &
Forms![LocalesDataEntry]![ReconnaissanceDataEntrySF]![ReconImagesSSF]![idRecon]
& ");"

Set dbs = CurrentDb()
Set rsQuery = dbs.OpenRecordset(strSQL, dbOpenSnapshot)

if not rsQuery.eof then
Forms![LocalesDataEntry]![ReconnaissanceDataEntrySF]![ReconImagesSSF]![ImageCategory]
= rsQuery![ImageCategory]
....
end if


--
Best regards,
___________
Alex Dybenko (MVP)
http://alexdyb.blogspot.com
http://www.PointLtd.com

ok...so i've created this in VBA on my form, I get no errors and I can
compile just fine. Problem is that my form doesn't want to see the
values.
a couple of questions....1) what should my forms 'record source' be? and
2)
how do I populate the fields in my form with the data returned from the
query?

here's the code i'm using:

Private Sub Form_Open(Cancel As Integer)
'pass in current idlocale as parameter to query against function.
Dim dbs As Database
Dim rsQuery As DAO.Recordset
Dim strSQL As String
CurrentDb.QueryDefs("rsQuery").SQL = "select imagecategory, activity,
whentaken, imagecaption, path from getReconImages(" &
Forms![LocalesDataEntry]![ReconnaissanceDataEntrySF]![ReconImagesSSF]![idRecon]
& ");"

Set dbs = CurrentDb()
Set rsQuery = dbs.OpenRecordset(strSQL, dbOpenSnapshot)
Set
Forms![LocalesDataEntry]![ReconnaissanceDataEntrySF]![ReconImagesSSF]![ImageCategory]
= rsQuery![ImageCategory]
Set
Forms![LocalesDataEntry]![ReconnaissanceDataEntrySF]![ReconImagesSSF]![Activity]
= rsQuery![Activity]
Set
Forms![LocalesDataEntry]![ReconnaissanceDataEntrySF]![ReconImagesSSF]![WhenTaken]
= rsQuery![WhenTaken]
Set
Forms![LocalesDataEntry]![ReconnaissanceDataEntrySF]![ReconImagesSSF]![ImageCaption]
= rsQuery![ImageCaption]
Set
Forms![LocalesDataEntry]![ReconnaissanceDataEntrySF]![ReconImagesSSF]![Path]
= rsQuery![Path]

End Sub

thanks for all the help.
-Dom

no, you have to run what I wrote in VBA:

Currentdb.querydefs("MyQuery").SQL="select * from dbfunction(" &
forms![form1]![subform1]![subform2]![id] & ");"

result of this line - your query's MyQuery SQL will be:

select * from dbfunction(1)

in case id is equal 1


--
Best regards,
___________
Alex Dybenko (MVP)
http://alexdyb.blogspot.com
http://www.PointLtd.com

Hi Alex,
So I tried to put the SQL query directly into my passthrough query:
select * from getReconImages(" &
forms![LocalesDataEntry]![ReconnaissanceDataEntrySF]![ReconImagesSSF]![idrecon]
& ");

This generated the following message:

ODBC--call failed.
Error while executing the query;
ERROR: column "&
forms![LocalesDataEntry]![ReconnaissanceDataEntrySF]![Recon" does not
exist
(#7)

:

Hi,
you can just modify SQL of a pass-through query:

Currentdb.querydefs("MyQuery").SQL="select * from dbfunction(" &
forms![form1]![subform1]![subform2]![id] & ");"

and then execute query, or use it in code, whatever

--
Best regards,
___________
Alex Dybenko (MVP)
http://alexdyb.blogspot.com
http://www.PointLtd.com


I've been trying to find a way to have Access 2003 pass a parameter
to
a
back
end database function. I'm running Access as my front end with
postgres
as
my back end. I created a function in postgres which needs an ID
passed
in
as
a parameter. Once the field is passed in as the parameter the
function
returns a set of data which I need to use for populating the fields
in
my
form (read only). I can create a Pass-through query in Access with
the
ID
parameter "hard coded" into the select statement, and it works just
fine.
My
problem is that I need this to be a static parameter. I want Access
to
pass
this ID parameter into the pass through query dependant on which set
of
data
it is in. For example:
select * from dbfunction(forms![form1]![subform1]![subform2]![id]);

Another option I'm looking at is using VB. Something like:

Private Sub Form_Open(Cancel As Integer)
'pass in current idlocale as parameter to query against function.
Dim dbs As Database
Dim rsQuery As DAO.Recordset
Dim strSQL As String
Dim rsid As Integer

Set dbs = CurrentDb()
Set rsid = Me![idRecon]
strSQL = "SELECT idlocale, idrecon, imageid, activity,
imagecategory,
path,
whentaken, imagecaption FROM getReconImages(rsid)"
Set rsQuery = dbs.OpenRecordset(strSQL, dbOpenSnapshot)
Set
Forms![LocalesDataEntry]![ReconnaissanceDataEntrySF]![ReconImagesSSF]![ImageCategory]
= rsQuery![ImageCategory]
End Sub

Does anybody have a good way to accomplish this?
Please help!
-Dom Torrez

 

[Dom Torrez]

Hi Alex,
Thanks for all the help! I finally got my code working and showing in the
form. I'm still having one small issue. Whenever I load my form I get a pop
up message stating "syntax error in from clause". Other than that
everything seems to work just fine, I can see all the data I need to see, I
can find no other problems, just that one pop up on initial load.
Here's the solution I used, with your help:

***********************************************
Private Sub Form_Open(Cancel As Integer)
'pass in current idlocale as parameter to query against function.
Dim dbs As Database
Dim rsQuery As DAO.Recordset
Dim querydf As DAO.QueryDef
Dim sQuery As String
Dim Icat As Variant
Dim IAct As String
Dim IWhen As String
Dim ICaption As String
Dim IPath As String
Dim IreconID As String
Dim IId As String
Dim IIdLoc As String

sQuery = "select idrecon, imageid, idlocale, imagecategory, activity,
whentaken, imagecaption, path from getReconImages(" &
Forms![LocalesDataEntry]![ReconnaissanceDataEntrySF]![idRecon] & ");"
Debug.Print sQuery
Set querydf = CurrentDb.CreateQueryDef("")

querydf.Connect =
"ODBC;DSN=BUFF;DATABASE=pod;SERVER=buff;PORT=5432;SSLMODE=prefer;A6=;A7=100;A8=8192;B0=254;B1=8190;BI=0;C2=dd_;;CX=1bd0389"
querydf.SQL = sQuery
Debug.Print sQuery
Set rsQuery = querydf.OpenRecordset
'Me.RecordSource = "rsQuery"
'Debug.Print rsQuery
Set Me.Recordset = rsQuery

IreconID = rsQuery![idRecon]
Icat = rsQuery![ImageCategory]
IAct = rsQuery![Activity]
IWhen = rsQuery![WhenTaken]
ICaption = rsQuery![ImageCaption]
IPath = rsQuery![Path]
IId = rsQuery![imageid]
IIdLoc = rsQuery![idLocale]
End Sub
*****************************

Again, Thanks for all your help. Do you have any idea about the "syntax
error in from clause" pop up that i'm getting?

--Dom T.

Hi Don,

1. you can bind your form to this query, so you don't need to set each
field:

Private Sub Form_Open(Cancel As Integer)
'pass in current idlocale as parameter to query against function.
Dim dbs As Database
Dim rsQuery As DAO.Recordset
Dim strSQL As String
CurrentDb.QueryDefs("rsQuery").SQL = "select imagecategory, activity,
whentaken, imagecaption, path from getReconImages(" &
Forms![LocalesDataEntry]![ReconnaissanceDataEntrySF]![ReconImagesSSF]![idRecon]
& ");"

me.recordsource="rsQuery" 'perhaps you need to set a recordsource of
subform...
end sub

2. to manually set fields the code should be like this:

Private Sub Form_Open(Cancel As Integer)
'pass in current idlocale as parameter to query against function.
Dim dbs As Database
Dim rsQuery As DAO.Recordset
Dim strSQL As String
CurrentDb.QueryDefs("rsQuery").SQL = "select imagecategory, activity,
whentaken, imagecaption, path from getReconImages(" &
Forms![LocalesDataEntry]![ReconnaissanceDataEntrySF]![ReconImagesSSF]![idRecon]
& ");"

Set dbs = CurrentDb()
Set rsQuery = dbs.OpenRecordset(strSQL, dbOpenSnapshot)

if not rsQuery.eof then
Forms![LocalesDataEntry]![ReconnaissanceDataEntrySF]![ReconImagesSSF]![ImageCategory]
= rsQuery![ImageCategory]
....
end if


--
Best regards,
___________
Alex Dybenko (MVP)
http://alexdyb.blogspot.com
http://www.PointLtd.com

ok...so i've created this in VBA on my form, I get no errors and I can
compile just fine. Problem is that my form doesn't want to see the
values.
a couple of questions....1) what should my forms 'record source' be? and
2)
how do I populate the fields in my form with the data returned from the
query?

here's the code i'm using:

Private Sub Form_Open(Cancel As Integer)
'pass in current idlocale as parameter to query against function.
Dim dbs As Database
Dim rsQuery As DAO.Recordset
Dim strSQL As String
CurrentDb.QueryDefs("rsQuery").SQL = "select imagecategory, activity,
whentaken, imagecaption, path from getReconImages(" &
Forms![LocalesDataEntry]![ReconnaissanceDataEntrySF]![ReconImagesSSF]![idRecon]
& ");"

Set dbs = CurrentDb()
Set rsQuery = dbs.OpenRecordset(strSQL, dbOpenSnapshot)
Set
Forms![LocalesDataEntry]![ReconnaissanceDataEntrySF]![ReconImagesSSF]![ImageCategory]
= rsQuery![ImageCategory]
Set
Forms![LocalesDataEntry]![ReconnaissanceDataEntrySF]![ReconImagesSSF]![Activity]
= rsQuery![Activity]
Set
Forms![LocalesDataEntry]![ReconnaissanceDataEntrySF]![ReconImagesSSF]![WhenTaken]
= rsQuery![WhenTaken]
Set
Forms![LocalesDataEntry]![ReconnaissanceDataEntrySF]![ReconImagesSSF]![ImageCaption]
= rsQuery![ImageCaption]
Set
Forms![LocalesDataEntry]![ReconnaissanceDataEntrySF]![ReconImagesSSF]![Path]
= rsQuery![Path]

End Sub

thanks for all the help.
-Dom

no, you have to run what I wrote in VBA:

Currentdb.querydefs("MyQuery").SQL="select * from dbfunction(" &
forms![form1]![subform1]![subform2]![id] & ");"

result of this line - your query's MyQuery SQL will be:

select * from dbfunction(1)

in case id is equal 1


--
Best regards,
___________
Alex Dybenko (MVP)
http://alexdyb.blogspot.com
http://www.PointLtd.com


Hi Alex,
So I tried to put the SQL query directly into my passthrough query:
select * from getReconImages(" &
forms![LocalesDataEntry]![ReconnaissanceDataEntrySF]![ReconImagesSSF]![idrecon]
& ");

This generated the following message:

ODBC--call failed.
Error while executing the query;
ERROR: column "&
forms![LocalesDataEntry]![ReconnaissanceDataEntrySF]![Recon" does not
exist
(#7)

:

Hi,
you can just modify SQL of a pass-through query:

Currentdb.querydefs("MyQuery").SQL="select * from dbfunction(" &
forms![form1]![subform1]![subform2]![id] & ");"

and then execute query, or use it in code, whatever

--
Best regards,
___________
Alex Dybenko (MVP)
http://alexdyb.blogspot.com
http://www.PointLtd.com


I've been trying to find a way to have Access 2003 pass a parameter
to
a
back
end database function. I'm running Access as my front end with
postgres
as
my back end. I created a function in postgres which needs an ID
passed
in
as
a parameter. Once the field is passed in as the parameter the
function
returns a set of data which I need to use for populating the fields
in
my
form (read only). I can create a Pass-through query in Access with
the
ID
parameter "hard coded" into the select statement, and it works just
fine.
My
problem is that I need this to be a static parameter. I want Access
to
pass
this ID parameter into the pass through query dependant on which set
of
data
it is in. For example:
select * from dbfunction(forms![form1]![subform1]![subform2]![id]);

Another option I'm looking at is using VB. Something like:

Private Sub Form_Open(Cancel As Integer)
'pass in current idlocale as parameter to query against function.
Dim dbs As Database
Dim rsQuery As DAO.Recordset
Dim strSQL As String
Dim rsid As Integer

Set dbs = CurrentDb()
Set rsid = Me![idRecon]
strSQL = "SELECT idlocale, idrecon, imageid, activity,
imagecategory,
path,
whentaken, imagecaption FROM getReconImages(rsid)"
Set rsQuery = dbs.OpenRecordset(strSQL, dbOpenSnapshot)
Set
Forms![LocalesDataEntry]![ReconnaissanceDataEntrySF]![ReconImagesSSF]![ImageCategory]
= rsQuery![ImageCategory]
End Sub

Does anybody have a good way to accomplish this?
Please help!
-Dom Torrez

 

[Alex Dybenko]

Hi Dom,
after:
Debug.Print sQuery

copy a SQL from immediate window and try to run it at postgres server side
(some query analyzer or whatever it called there).

Could be also that some other query cause this error - perhaps in a row
source of combobox/listbox, or subform recordsource. check all these queries

--
Best regards,
___________
Alex Dybenko (MVP)
http://alexdyb.blogspot.com
http://www.PointLtd.com

Hi Alex,
Thanks for all the help! I finally got my code working and showing in the
form. I'm still having one small issue. Whenever I load my form I get a
pop
up message stating "syntax error in from clause". Other than that
everything seems to work just fine, I can see all the data I need to see,
I
can find no other problems, just that one pop up on initial load.
Here's the solution I used, with your help:

***********************************************
Private Sub Form_Open(Cancel As Integer)
'pass in current idlocale as parameter to query against function.
Dim dbs As Database
Dim rsQuery As DAO.Recordset
Dim querydf As DAO.QueryDef
Dim sQuery As String
Dim Icat As Variant
Dim IAct As String
Dim IWhen As String
Dim ICaption As String
Dim IPath As String
Dim IreconID As String
Dim IId As String
Dim IIdLoc As String

sQuery = "select idrecon, imageid, idlocale, imagecategory, activity,
whentaken, imagecaption, path from getReconImages(" &
Forms![LocalesDataEntry]![ReconnaissanceDataEntrySF]![idRecon] & ");"
Debug.Print sQuery
Set querydf = CurrentDb.CreateQueryDef("")

querydf.Connect =
"ODBC;DSN=BUFF;DATABASE=pod;SERVER=buff;PORT=5432;SSLMODE=prefer;A6=;A7=100;A8=8192;B0=254;B1=8190;BI=0;C2=dd_;;CX=1bd0389"
querydf.SQL = sQuery
Debug.Print sQuery
Set rsQuery = querydf.OpenRecordset
'Me.RecordSource = "rsQuery"
'Debug.Print rsQuery
Set Me.Recordset = rsQuery

IreconID = rsQuery![idRecon]
Icat = rsQuery![ImageCategory]
IAct = rsQuery![Activity]
IWhen = rsQuery![WhenTaken]
ICaption = rsQuery![ImageCaption]
IPath = rsQuery![Path]
IId = rsQuery![imageid]
IIdLoc = rsQuery![idLocale]
End Sub
*****************************

Again, Thanks for all your help. Do you have any idea about the "syntax
error in from clause" pop up that i'm getting?

--Dom T.

Hi Don,

1. you can bind your form to this query, so you don't need to set each
field:

Private Sub Form_Open(Cancel As Integer)
'pass in current idlocale as parameter to query against function.
Dim dbs As Database
Dim rsQuery As DAO.Recordset
Dim strSQL As String
CurrentDb.QueryDefs("rsQuery").SQL = "select imagecategory, activity,
whentaken, imagecaption, path from getReconImages(" &
Forms![LocalesDataEntry]![ReconnaissanceDataEntrySF]![ReconImagesSSF]![idRecon]
& ");"

me.recordsource="rsQuery" 'perhaps you need to set a recordsource of
subform...
end sub

2. to manually set fields the code should be like this:

Private Sub Form_Open(Cancel As Integer)
'pass in current idlocale as parameter to query against function.
Dim dbs As Database
Dim rsQuery As DAO.Recordset
Dim strSQL As String
CurrentDb.QueryDefs("rsQuery").SQL = "select imagecategory, activity,
whentaken, imagecaption, path from getReconImages(" &
Forms![LocalesDataEntry]![ReconnaissanceDataEntrySF]![ReconImagesSSF]![idRecon]
& ");"

Set dbs = CurrentDb()
Set rsQuery = dbs.OpenRecordset(strSQL, dbOpenSnapshot)

if not rsQuery.eof then

Forms![LocalesDataEntry]![ReconnaissanceDataEntrySF]![ReconImagesSSF]![ImageCategory]
= rsQuery![ImageCategory]
....
end if


--
Best regards,
___________
Alex Dybenko (MVP)
http://alexdyb.blogspot.com
http://www.PointLtd.com

ok...so i've created this in VBA on my form, I get no errors and I can
compile just fine. Problem is that my form doesn't want to see the
values.
a couple of questions....1) what should my forms 'record source' be?
and
2)
how do I populate the fields in my form with the data returned from the
query?

here's the code i'm using:

Private Sub Form_Open(Cancel As Integer)
'pass in current idlocale as parameter to query against function.
Dim dbs As Database
Dim rsQuery As DAO.Recordset
Dim strSQL As String
CurrentDb.QueryDefs("rsQuery").SQL = "select imagecategory, activity,
whentaken, imagecaption, path from getReconImages(" &
Forms![LocalesDataEntry]![ReconnaissanceDataEntrySF]![ReconImagesSSF]![idRecon]
& ");"

Set dbs = CurrentDb()
Set rsQuery = dbs.OpenRecordset(strSQL, dbOpenSnapshot)
Set
Forms![LocalesDataEntry]![ReconnaissanceDataEntrySF]![ReconImagesSSF]![ImageCategory]
= rsQuery![ImageCategory]
Set
Forms![LocalesDataEntry]![ReconnaissanceDataEntrySF]![ReconImagesSSF]![Activity]
= rsQuery![Activity]
Set
Forms![LocalesDataEntry]![ReconnaissanceDataEntrySF]![ReconImagesSSF]![WhenTaken]
= rsQuery![WhenTaken]
Set
Forms![LocalesDataEntry]![ReconnaissanceDataEntrySF]![ReconImagesSSF]![ImageCaption]
= rsQuery![ImageCaption]
Set
Forms![LocalesDataEntry]![ReconnaissanceDataEntrySF]![ReconImagesSSF]![Path]
= rsQuery![Path]

End Sub

thanks for all the help.
-Dom

:

no, you have to run what I wrote in VBA:

Currentdb.querydefs("MyQuery").SQL="select * from dbfunction(" &
forms![form1]![subform1]![subform2]![id] & ");"

result of this line - your query's MyQuery SQL will be:

select * from dbfunction(1)

in case id is equal 1


--
Best regards,
___________
Alex Dybenko (MVP)
http://alexdyb.blogspot.com
http://www.PointLtd.com


Hi Alex,
So I tried to put the SQL query directly into my passthrough query:
select * from getReconImages(" &
forms![LocalesDataEntry]![ReconnaissanceDataEntrySF]![ReconImagesSSF]![idrecon]
& ");

This generated the following message:

ODBC--call failed.
Error while executing the query;
ERROR: column "&
forms![LocalesDataEntry]![ReconnaissanceDataEntrySF]![Recon" does
not
exist
(#7)

:

Hi,
you can just modify SQL of a pass-through query:

Currentdb.querydefs("MyQuery").SQL="select * from dbfunction(" &
forms![form1]![subform1]![subform2]![id] & ");"

and then execute query, or use it in code, whatever

--
Best regards,
___________
Alex Dybenko (MVP)
http://alexdyb.blogspot.com
http://www.PointLtd.com


message
I've been trying to find a way to have Access 2003 pass a
parameter
to
a
back
end database function. I'm running Access as my front end with
postgres
as
my back end. I created a function in postgres which needs an ID
passed
in
as
a parameter. Once the field is passed in as the parameter the
function
returns a set of data which I need to use for populating the
fields
in
my
form (read only). I can create a Pass-through query in Access
with
the
ID
parameter "hard coded" into the select statement, and it works
just
fine.
My
problem is that I need this to be a static parameter. I want
Access
to
pass
this ID parameter into the pass through query dependant on which
set
of
data
it is in. For example:
select * from
dbfunction(forms![form1]![subform1]![subform2]![id]);

Another option I'm looking at is using VB. Something like:

Private Sub Form_Open(Cancel As Integer)
'pass in current idlocale as parameter to query against function.
Dim dbs As Database
Dim rsQuery As DAO.Recordset
Dim strSQL As String
Dim rsid As Integer

Set dbs = CurrentDb()
Set rsid = Me![idRecon]
strSQL = "SELECT idlocale, idrecon, imageid, activity,
imagecategory,
path,
whentaken, imagecaption FROM getReconImages(rsid)"
Set rsQuery = dbs.OpenRecordset(strSQL, dbOpenSnapshot)
Set
Forms![LocalesDataEntry]![ReconnaissanceDataEntrySF]![ReconImagesSSF]![ImageCategory]
= rsQuery![ImageCategory]
End Sub

Does anybody have a good way to accomplish this?
Please help!
-Dom Torrez

 

[Guest]

You will have invalid sql if you use that code while the control is missing
or null.
Check the control value before trying to set the sql.

(david)

Hi Alex,
Thanks for all the help! I finally got my code working and showing in the
form. I'm still having one small issue. Whenever I load my form I get a pop
up message stating "syntax error in from clause". Other than that
everything seems to work just fine, I can see all the data I need to see, I
can find no other problems, just that one pop up on initial load.
Here's the solution I used, with your help:

***********************************************
Private Sub Form_Open(Cancel As Integer)
'pass in current idlocale as parameter to query against function.
Dim dbs As Database
Dim rsQuery As DAO.Recordset
Dim querydf As DAO.QueryDef
Dim sQuery As String
Dim Icat As Variant
Dim IAct As String
Dim IWhen As String
Dim ICaption As String
Dim IPath As String
Dim IreconID As String
Dim IId As String
Dim IIdLoc As String

sQuery = "select idrecon, imageid, idlocale, imagecategory, activity,
whentaken, imagecaption, path from getReconImages(" &
Forms![LocalesDataEntry]![ReconnaissanceDataEntrySF]![idRecon] & ");"
Debug.Print sQuery
Set querydf = CurrentDb.CreateQueryDef("")

querydf.Connect =
"ODBC;DSN=BUFF;DATABASE=pod;SERVER=buff;PORT=5432;SSLMODE=prefer;A6=;A7=100;
A8=8192;B0=254;B1=8190;BI=0;C2=dd_;;CX=1bd0389"
querydf.SQL = sQuery
Debug.Print sQuery
Set rsQuery = querydf.OpenRecordset
'Me.RecordSource = "rsQuery"
'Debug.Print rsQuery
Set Me.Recordset = rsQuery

IreconID = rsQuery![idRecon]
Icat = rsQuery![ImageCategory]
IAct = rsQuery![Activity]
IWhen = rsQuery![WhenTaken]
ICaption = rsQuery![ImageCaption]
IPath = rsQuery![Path]
IId = rsQuery![imageid]
IIdLoc = rsQuery![idLocale]
End Sub
*****************************

Again, Thanks for all your help. Do you have any idea about the "syntax
error in from clause" pop up that i'm getting?

--Dom T.

Hi Don,

1. you can bind your form to this query, so you don't need to set each
field:

Private Sub Form_Open(Cancel As Integer)
'pass in current idlocale as parameter to query against function.
Dim dbs As Database
Dim rsQuery As DAO.Recordset
Dim strSQL As String
CurrentDb.QueryDefs("rsQuery").SQL = "select imagecategory, activity,
whentaken, imagecaption, path from getReconImages(" &

Forms![LocalesDataEntry]![ReconnaissanceDataEntrySF]![ReconImagesSSF]![idRec
on]

& ");"

me.recordsource="rsQuery" 'perhaps you need to set a recordsource of
subform...
end sub

2. to manually set fields the code should be like this:

Private Sub Form_Open(Cancel As Integer)
'pass in current idlocale as parameter to query against function.
Dim dbs As Database
Dim rsQuery As DAO.Recordset
Dim strSQL As String
CurrentDb.QueryDefs("rsQuery").SQL = "select imagecategory, activity,
whentaken, imagecaption, path from getReconImages(" &

Forms![LocalesDataEntry]![ReconnaissanceDataEntrySF]![ReconImagesSSF]![idRec
on]

& ");"

Set dbs = CurrentDb()
Set rsQuery = dbs.OpenRecordset(strSQL, dbOpenSnapshot)

if not rsQuery.eof then

Forms![LocalesDataEntry]![ReconnaissanceDataEntrySF]![ReconImagesSSF]![Image
Category]

= rsQuery![ImageCategory]
....
end if


--
Best regards,
___________
Alex Dybenko (MVP)
http://alexdyb.blogspot.com
http://www.PointLtd.com

ok...so i've created this in VBA on my form, I get no errors and I can
compile just fine. Problem is that my form doesn't want to see the
values.
a couple of questions....1) what should my forms 'record source' be? and
2)
how do I populate the fields in my form with the data returned from the
query?

here's the code i'm using:

Private Sub Form_Open(Cancel As Integer)
'pass in current idlocale as parameter to query against function.
Dim dbs As Database
Dim rsQuery As DAO.Recordset
Dim strSQL As String
CurrentDb.QueryDefs("rsQuery").SQL = "select imagecategory, activity,
whentaken, imagecaption, path from getReconImages(" &

Forms![LocalesDataEntry]![ReconnaissanceDataEntrySF]![ReconImagesSSF]![idRec
on] Forms![LocalesDataEntry]![ReconnaissanceDataEntrySF]![ReconImagesSSF]![Image
Category]

= rsQuery![ImageCategory]
Set

Forms![LocalesDataEntry]![ReconnaissanceDataEntrySF]![ReconImagesSSF]![Activ
ity]

= rsQuery![Activity]
Set

Forms![LocalesDataEntry]![ReconnaissanceDataEntrySF]![ReconImagesSSF]![WhenT
aken]

= rsQuery![WhenTaken]
Set

Forms![LocalesDataEntry]![ReconnaissanceDataEntrySF]![ReconImagesSSF]![Image
Caption]

= rsQuery![ImageCaption]
Set
Forms![LocalesDataEntry]![ReconnaissanceDataEntrySF]![ReconImagesSSF]![Path]
= rsQuery![Path]

End Sub

thanks for all the help.
-Dom

:

no, you have to run what I wrote in VBA:

Currentdb.querydefs("MyQuery").SQL="select * from dbfunction(" &
forms![form1]![subform1]![subform2]![id] & ");"

result of this line - your query's MyQuery SQL will be:

select * from dbfunction(1)

in case id is equal 1


--
Best regards,
___________
Alex Dybenko (MVP)
http://alexdyb.blogspot.com
http://www.PointLtd.com


Hi Alex,
So I tried to put the SQL query directly into my passthrough query:
select * from getReconImages(" &

forms![LocalesDataEntry]![ReconnaissanceDataEntrySF]![ReconImagesSSF]![idrec
on]

& ");

This generated the following message:

ODBC--call failed.
Error while executing the query;
ERROR: column "&
forms![LocalesDataEntry]![ReconnaissanceDataEntrySF]![Recon" does not
exist
(#7)

:

Hi,
you can just modify SQL of a pass-through query:

Currentdb.querydefs("MyQuery").SQL="select * from dbfunction(" &
forms![form1]![subform1]![subform2]![id] & ");"

and then execute query, or use it in code, whatever

--
Best regards,
___________
Alex Dybenko (MVP)
http://alexdyb.blogspot.com
http://www.PointLtd.com


I've been trying to find a way to have Access 2003 pass a parameter
to
a
back
end database function. I'm running Access as my front end with
postgres
as
my back end. I created a function in postgres which needs an ID
passed
in
as
a parameter. Once the field is passed in as the parameter the
function
returns a set of data which I need to use for populating the fields
in
my
form (read only). I can create a Pass-through query in Access with
the
ID
parameter "hard coded" into the select statement, and it works just
fine.
My
problem is that I need this to be a static parameter. I want Access
to
pass
this ID parameter into the pass through query dependant on which set
of
data
it is in. For example:
select * from dbfunction(forms![form1]![subform1]![subform2]![id]);

Another option I'm looking at is using VB. Something like:

Private Sub Form_Open(Cancel As Integer)
'pass in current idlocale as parameter to query against function.
Dim dbs As Database
Dim rsQuery As DAO.Recordset
Dim strSQL As String
Dim rsid As Integer

Set dbs = CurrentDb()
Set rsid = Me![idRecon]
strSQL = "SELECT idlocale, idrecon, imageid, activity,
imagecategory,
path,
whentaken, imagecaption FROM getReconImages(rsid)"
Set rsQuery = dbs.OpenRecordset(strSQL, dbOpenSnapshot)
Set

Forms![LocalesDataEntry]![ReconnaissanceDataEntrySF]![ReconImagesSSF]![Image
Category]

= rsQuery![ImageCategory]
End Sub

Does anybody have a good way to accomplish this?
Please help!
-Dom Torrez