Query based recordset

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


"Brian" wrote in message
...
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.

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


"tina" wrote in message
...
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" wrote in message
...
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.

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.


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

--
Marsh
MVP [MS Access]

On Fri, 07 May 2004 23:08:30 -0500, Marshall Barton
wrote:

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

Or else spell it out:
MySet.Fields("Booking ID").Value = 1

--
John Nurick [Microsoft Access MVP]

Please respond in the newgroup and not by email.

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" wrote in message
...
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.


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

--
Marsh
MVP [MS Access]

tina wrote:

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.


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

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" wrote in message
...
tina wrote:

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.


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

Hi, I am novice when it comes to Access programming and have had very little experience of using Recordsets. I wanted to open a recordset based on a query which has criteria based on a field on a form. I have tried the code you suggested here and the code runs through without any errors but produces an empty recordset when it should contain records. The parameter value is passed in the for loop but when it drops out to the 'Set rs' line prm loses its value?

The query checks to see if any outstanding Messages exist relating to an Enquiry, if Messages are outstanding, it opens the Messages to action before it lets the user close the enquiry. It takes the [CALLID] value off the current form as a parameter and where [Completed] = False.

Any help on this would be greatly appreciated, I could do it by using a recordset based on a hidden form but this would be messy and I would like to learn more about how to use recordsets in VBA. Please see the Code below....

Many thanks in advance
Sue

Private Sub Resolved_AfterUpdate()

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

If Resolved = True Then
Set db = CurrentDb()
Set qdf = db.QueryDefs("qryMsgs")
For Each prm In qdf.Parameters
prm.Value = Eval(prm.Name)
Next

Set rs = qdf.OpenRecordset(dbOpenDynaset)

If Not rs.BOF And Not rs.EOF Then
Me.Modal = False
DoCmd.OpenForm "Outstanding Messages"
Else
Msg = "No Outstanding Messages exist for this Call Enquiry"
MsgBox Msg

End If

End If

End Sub

Sorry... please ignore this. It does work... :-)
Cheers
Sue

----- Sue wrote: -----

Hi, I am novice when it comes to Access programming and have had very little experience of using Recordsets. I wanted to open a recordset based on a query which has criteria based on a field on a form. I have tried the code you suggested here and the code runs through without any errors but produces an empty recordset when it should contain records. The parameter value is passed in the for loop but when it drops out to the 'Set rs' line prm loses its value?

The query checks to see if any outstanding Messages exist relating to an Enquiry, if Messages are outstanding, it opens the Messages to action before it lets the user close the enquiry. It takes the [CALLID] value off the current form as a parameter and where [Completed] = False.

Any help on this would be greatly appreciated, I could do it by using a recordset based on a hidden form but this would be messy and I would like to learn more about how to use recordsets in VBA. Please see the Code below....

Many thanks in advance
Sue

Private Sub Resolved_AfterUpdate()

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

If Resolved = True Then
Set db = CurrentDb()
Set qdf = db.QueryDefs("qryMsgs")
For Each prm In qdf.Parameters
prm.Value = Eval(prm.Name)
Next

Set rs = qdf.OpenRecordset(dbOpenDynaset)

If Not rs.BOF And Not rs.EOF Then
Me.Modal = False
DoCmd.OpenForm "Outstanding Messages"
Else
Msg = "No Outstanding Messages exist for this Call Enquiry"
MsgBox Msg

End If

End If

End Sub