Query based recordset

  • Thread starter Thread starter Brian
  • Start date Start date
B

Brian

I'm trying to create a recordset based upon a query
called QueryAv that is a SELECT so that I can then update
the records. What are the declarations and staements I
need to do this?

Dim MyDB As Database, MySet As Recordset
Set MyDb = CurrentDb
Set MySet = MyDb.OpenRecordset("QueryAv")
MySet.Edit
MySet.[Booking ID] = 1
Myset.Update

etc.

This doesn't work with an error too few parameters.
 
if the underlying query has criteria parameters, such as a reference to a
form, or a box that pops up for the user to enter a value, then you can't
use the query object directly as the source of the recordset. you have to
resolve the parameters first. here's how:

Dim strSQL as String

strSQL = "paste the query's select statement here"

make sure you put the parameters *outside* the select statement, example:

strSQL = "SELECT NumberFieldName1, TextFieldName2 " _
& "FROM TableName WHERE NumberFieldName1 = " _
& Me!NumberControlName & " AND TextFieldName2 = '" _
& Me!TextControlName & "'"

notice how the text value is surrounded by single quotes ' , and the number
value is not. btw, it's a good idea to disambiguate your references, as

Dim MySet As DAO.Recordset

hth
 
sorry, didn't quite finish my train of thought: open the recordset using
strSQL instead of "QueryAv".


tina said:
if the underlying query has criteria parameters, such as a reference to a
form, or a box that pops up for the user to enter a value, then you can't
use the query object directly as the source of the recordset. you have to
resolve the parameters first. here's how:

Dim strSQL as String

strSQL = "paste the query's select statement here"

make sure you put the parameters *outside* the select statement, example:

strSQL = "SELECT NumberFieldName1, TextFieldName2 " _
& "FROM TableName WHERE NumberFieldName1 = " _
& Me!NumberControlName & " AND TextFieldName2 = '" _
& Me!TextControlName & "'"

notice how the text value is surrounded by single quotes ' , and the number
value is not. btw, it's a good idea to disambiguate your references, as

Dim MySet As DAO.Recordset

hth


Brian said:
I'm trying to create a recordset based upon a query
called QueryAv that is a SELECT so that I can then update
the records. What are the declarations and staements I
need to do this?

Dim MyDB As Database, MySet As Recordset
Set MyDb = CurrentDb
Set MySet = MyDb.OpenRecordset("QueryAv")
MySet.Edit
MySet.[Booking ID] = 1
Myset.Update

etc.

This doesn't work with an error too few parameters.
Click to expand...
Click to expand...
 
Brian said:
I'm trying to create a recordset based upon a query
called QueryAv that is a SELECT so that I can then update
the records. What are the declarations and staements I
need to do this?

Dim MyDB As Database, MySet As Recordset
Set MyDb = CurrentDb
Set MySet = MyDb.OpenRecordset("QueryAv")
MySet.Edit
MySet.[Booking ID] = 1
Myset.Update

etc.

This doesn't work with an error too few parameters.
Click to expand...


First, you must use the ! to reference a field in a
recordset:
MySet![Booking ID] = 1


The error message is because you're running the query
directly, as oposed to asking Access to do it for you (from
the db window, query design or a form/report record source).

In the VBA environment, you have to resolve the parameters
before you open the recordset. One way is to construct the
query's SQL as tina demonstrated.

Another way is to assign the desired value to each
parameter. If the parameters are refences to controls on a
form, you can do it like this:

Dim db As DAO.Database
Dim qdf As DAO.QyeryDef
Dim prm As DAO.Parameter
Dim rs As DAO.Recordset

Set db = CurrentDb()
Set qdf = db.QueryDefs("nameofquery")
For Each prm In qdf.Parameters
prm.Value = Eval(prm.Name)
Next

Set rs = qdf.OpenRecordset(dbOpenDynaset)
. . .

rs.Close : Set rs = Nothing
Set qdf = Nothing
Set db= Nothing
 
hi Marshall. i've seen the alternate solution to resolving query parameters
that you wrote below, in other posts, but have never used it. should i be
using that solution instead of the SQL string solution? or are there certain
situations where it is a better solution? or can i use either one with no
significant impact on performance either way? i'm always trying to expand my
knowledge of VBA, so your comments will be appreciated.
thx, tina :)


Marshall Barton said:
Brian said:
I'm trying to create a recordset based upon a query
called QueryAv that is a SELECT so that I can then update
the records. What are the declarations and staements I
need to do this?

Dim MyDB As Database, MySet As Recordset
Set MyDb = CurrentDb
Set MySet = MyDb.OpenRecordset("QueryAv")
MySet.Edit
MySet.[Booking ID] = 1
Myset.Update

etc.

This doesn't work with an error too few parameters.
Click to expand...


First, you must use the ! to reference a field in a
recordset:
MySet![Booking ID] = 1


The error message is because you're running the query
directly, as oposed to asking Access to do it for you (from
the db window, query design or a form/report record source).

In the VBA environment, you have to resolve the parameters
before you open the recordset. One way is to construct the
query's SQL as tina demonstrated.

Another way is to assign the desired value to each
parameter. If the parameters are refences to controls on a
form, you can do it like this:

Dim db As DAO.Database
Dim qdf As DAO.QyeryDef
Dim prm As DAO.Parameter
Dim rs As DAO.Recordset

Set db = CurrentDb()
Set qdf = db.QueryDefs("nameofquery")
For Each prm In qdf.Parameters
prm.Value = Eval(prm.Name)
Next

Set rs = qdf.OpenRecordset(dbOpenDynaset)
. . .

rs.Close : Set rs = Nothing
Set qdf = Nothing
Set db= Nothing
Click to expand...
 
tina said:
hi Marshall. i've seen the alternate solution to resolving query parameters
that you wrote below, in other posts, but have never used it. should i be
using that solution instead of the SQL string solution? or are there certain
situations where it is a better solution? or can i use either one with no
significant impact on performance either way? i'm always trying to expand my
knowledge of VBA, so your comments will be appreciated.
Click to expand...


There is some overhead in using an SQL string because the
SQL statement must have a query plan built on the fly. But
AFAIC, its more a matter of which way is easier to
understand in the code. For simple queries, I just go ahead
and build the SQL. If the query is complicated, I'll use
the Parameters approach rather than include a dozen lines of
quoted quotes in concatenated strings of SQL.

Others will have their own opinion, but I'd say it's your
call.
--
Marsh
MVP [MS Access]


Brian said:
I'm trying to create a recordset based upon a query
called QueryAv that is a SELECT so that I can then update
the records. What are the declarations and staements I
need to do this?

Dim MyDB As Database, MySet As Recordset
Set MyDb = CurrentDb
Set MySet = MyDb.OpenRecordset("QueryAv")
MySet.Edit
MySet.[Booking ID] = 1
Myset.Update

etc.

This doesn't work with an error too few parameters.
Click to expand...

"Marshall Barton" wrote
First, you must use the ! to reference a field in a
recordset:
MySet![Booking ID] = 1


The error message is because you're running the query
directly, as oposed to asking Access to do it for you (from
the db window, query design or a form/report record source).

In the VBA environment, you have to resolve the parameters
before you open the recordset. One way is to construct the
query's SQL as tina demonstrated.

Another way is to assign the desired value to each
parameter. If the parameters are refences to controls on a
form, you can do it like this:

Dim db As DAO.Database
Dim qdf As DAO.QyeryDef
Dim prm As DAO.Parameter
Dim rs As DAO.Recordset

Set db = CurrentDb()
Set qdf = db.QueryDefs("nameofquery")
For Each prm In qdf.Parameters
prm.Value = Eval(prm.Name)
Next

Set rs = qdf.OpenRecordset(dbOpenDynaset)
. . .

rs.Close : Set rs = Nothing
Set qdf = Nothing
Set db= Nothing
Click to expand...
Click to expand...
 
ah, good point. i've done some of those loooong SQL strings - gets to be
quite a drag. i'll have to try the other method; chances are you'll run
across a post from me one of these days saying "help!". <g>
thanks for the info! :)


Marshall Barton said:
tina said:
hi Marshall. i've seen the alternate solution to resolving query parameters
that you wrote below, in other posts, but have never used it. should i be
using that solution instead of the SQL string solution? or are there certain
situations where it is a better solution? or can i use either one with no
significant impact on performance either way? i'm always trying to expand my
knowledge of VBA, so your comments will be appreciated.
Click to expand...


There is some overhead in using an SQL string because the
SQL statement must have a query plan built on the fly. But
AFAIC, its more a matter of which way is easier to
understand in the code. For simple queries, I just go ahead
and build the SQL. If the query is complicated, I'll use
the Parameters approach rather than include a dozen lines of
quoted quotes in concatenated strings of SQL.

Others will have their own opinion, but I'd say it's your
call.
--
Marsh
MVP [MS Access]


Brian wrote:
I'm trying to create a recordset based upon a query
called QueryAv that is a SELECT so that I can then update
the records. What are the declarations and staements I
need to do this?

Dim MyDB As Database, MySet As Recordset
Set MyDb = CurrentDb
Set MySet = MyDb.OpenRecordset("QueryAv")
MySet.Edit
MySet.[Booking ID] = 1
Myset.Update

etc.

This doesn't work with an error too few parameters.

"Marshall Barton" wrote
First, you must use the ! to reference a field in a
recordset:
MySet![Booking ID] = 1


The error message is because you're running the query
directly, as oposed to asking Access to do it for you (from
the db window, query design or a form/report record source).

In the VBA environment, you have to resolve the parameters
before you open the recordset. One way is to construct the
query's SQL as tina demonstrated.

Another way is to assign the desired value to each
parameter. If the parameters are refences to controls on a
form, you can do it like this:

Dim db As DAO.Database
Dim qdf As DAO.QyeryDef
Dim prm As DAO.Parameter
Dim rs As DAO.Recordset

Set db = CurrentDb()
Set qdf = db.QueryDefs("nameofquery")
For Each prm In qdf.Parameters
prm.Value = Eval(prm.Name)
Next

Set rs = qdf.OpenRecordset(dbOpenDynaset)
. . .

rs.Close : Set rs = Nothing
Set qdf = Nothing
Set db= Nothing
Click to expand...
Click to expand...
Click to expand...
 
Back
Top