recordset

[Peter Bailey]

I have a querystring built up in vba and I want to open a recordset based on
the sql and pass the date value to a textbox or label for use elsewhere on
the form.

The recordset isnt working as it gives too few parameter error:
Dim db As DAO.Database
Set db = CurrentDb
Dim rsTmp As DAO.Recordset
Set rsTmp = rsTmp.OpenRecordset(strSelect) *****strSelect is the sql
string*****
rsTmp.MoveFirst

Me.TxtYear.Value = rsTmp.Fields(1)

Nothing seems to work even looking at examples from a book.

I must apologise in advance for my stupidity.

Regards
Peter

[Allen Browne]

There is something in the SQL string that Access does not recognise. It may
be:
- a field name that has been spelled incorrectly,
- a name that contains a space or other character and you omitted the square
brackets,
- a reference to a text box on a form (which works in a query, but does not
work in this context).

Just in case you are not aware, the Fields collection is zero-based, so
Fields(1) would be the 2nd field named in the SELECT clause.

Could you just use DLookup() instead of a whole recordset here?
See:
Getting a Value from a table
at:
http://members.iinet.net.au/~allenbrowne/casu-07.html
or if you want an example of doing this programmatically, see:
http://members.iinet.net.au/~allenbrowne/ser-42.html

--
Allen Browne - Microsoft MVP. Perth, Western Australia.
Tips for Access users - http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.

"Peter Bailey" <pe*********@andaluz.fsbusiness.co.uk> wrote in message
news:ch**********@news8.svr.pol.co.uk...

I have a querystring built up in vba and I want to open a recordset based
on
the sql and pass the date value to a textbox or label for use elsewhere on
the form.

The recordset isnt working as it gives too few parameter error:
Dim db As DAO.Database
Set db = CurrentDb
Dim rsTmp As DAO.Recordset
Set rsTmp = rsTmp.OpenRecordset(strSelect) *****strSelect is the sql
string*****
rsTmp.MoveFirst

Me.TxtYear.Value = rsTmp.Fields(1)

Nothing seems to work even looking at examples from a book.

I must apologise in advance for my stupidity.

Regards
Peter

[Peter Bailey]

I tried your vba method as well but that failed it must be a reference that
works in a query but not in vba.

Could you point me in the direction of creating the query dynamically and
that will solve the problem I think as I can make the query part of the
control.

regards
Peter
"Allen Browne" <Al*********@SeeSig.Invalid> wrote in message
news:41***********************@per-qv1-newsreader-01.iinet.net.au...

There is something in the SQL string that Access does not recognise. It may be:
- a field name that has been spelled incorrectly,
- a name that contains a space or other character and you omitted the square brackets,
- a reference to a text box on a form (which works in a query, but does not work in this context).

Just in case you are not aware, the Fields collection is zero-based, so
Fields(1) would be the 2nd field named in the SELECT clause.

Could you just use DLookup() instead of a whole recordset here?
See:
Getting a Value from a table
at:
http://members.iinet.net.au/~allenbrowne/casu-07.html
or if you want an example of doing this programmatically, see:
http://members.iinet.net.au/~allenbrowne/ser-42.html

--
Allen Browne - Microsoft MVP. Perth, Western Australia.
Tips for Access users - http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.

"Peter Bailey" <pe*********@andaluz.fsbusiness.co.uk> wrote in message
news:ch**********@news8.svr.pol.co.uk...

I have a querystring built up in vba and I want to open a recordset based
on
the sql and pass the date value to a textbox or label for use elsewhere on the form.

The recordset isnt working as it gives too few parameter error:
Dim db As DAO.Database
Set db = CurrentDb
Dim rsTmp As DAO.Recordset
Set rsTmp = rsTmp.OpenRecordset(strSelect) *****strSelect is the sql
string*****
rsTmp.MoveFirst

Me.TxtYear.Value = rsTmp.Fields(1)

Nothing seems to work even looking at examples from a book.

I must apologise in advance for my stupidity.

Regards
Peter

[Allen Browne]

You can dynamically assign the SQL property of a QueryDef, e.g.:
dbEngine(0)(0).QuerDefs("MyQuery").SQL = "SELECT ...

However, I'm not sure that would be the best approach for your needs.

You cannot bind the query directly to the control, but you can use:
=DLookup("SomeField", "SomeQuery")

--
Allen Browne - Microsoft MVP. Perth, Western Australia.
Tips for Access users - http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.

"Peter Bailey" <pe*********@andaluz.fsbusiness.co.uk> wrote in message
news:ch**********@news7.svr.pol.co.uk...

I tried your vba method as well but that failed it must be a reference that
works in a query but not in vba.

Could you point me in the direction of creating the query dynamically and
that will solve the problem I think as I can make the query part of the
control.

regards
Peter
"Allen Browne" <Al*********@SeeSig.Invalid> wrote in message
news:41***********************@per-qv1-newsreader-01.iinet.net.au...

There is something in the SQL string that Access does not recognise. It

may

be:
- a field name that has been spelled incorrectly,
- a name that contains a space or other character and you omitted the

square

brackets,
- a reference to a text box on a form (which works in a query, but does

not

work in this context).

Just in case you are not aware, the Fields collection is zero-based, so
Fields(1) would be the 2nd field named in the SELECT clause.

Could you just use DLookup() instead of a whole recordset here?
See:
Getting a Value from a table
at:
http://members.iinet.net.au/~allenbrowne/casu-07.html
or if you want an example of doing this programmatically, see:
http://members.iinet.net.au/~allenbrowne/ser-42.html

--
Allen Browne - Microsoft MVP. Perth, Western Australia.
Tips for Access users - http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.

"Peter Bailey" <pe*********@andaluz.fsbusiness.co.uk> wrote in message
news:ch**********@news8.svr.pol.co.uk...

>I have a querystring built up in vba and I want to open a recordset
>based
>on
> the sql and pass the date value to a textbox or label for use elsewhere on > the form.
>
> The recordset isnt working as it gives too few parameter error:
> Dim db As DAO.Database
> Set db = CurrentDb
> Dim rsTmp As DAO.Recordset
> Set rsTmp = rsTmp.OpenRecordset(strSelect) *****strSelect is the sql
> string*****
> rsTmp.MoveFirst
>
> Me.TxtYear.Value = rsTmp.Fields(1)
>
> Nothing seems to work even looking at examples from a book.
>
> I must apologise in advance for my stupidity.
>
> Regards
> Peter

[Piano Forte]

"Peter Bailey" <pe*********@andaluz.fsbusiness.co.uk> wrote in message
news:ch**********@news8.svr.pol.co.uk...

I have a querystring built up in vba and I want to open a recordset based on the sql and pass the date value to a textbox or label for use elsewhere on
the form.

The recordset isnt working as it gives too few parameter error:
Dim db As DAO.Database
Set db = CurrentDb
Dim rsTmp As DAO.Recordset
Set rsTmp = rsTmp.OpenRecordset(strSelect) *****strSelect is the sql
string*****
rsTmp.MoveFirst

Me.TxtYear.Value = rsTmp.Fields(1)

Nothing seems to work even looking at examples from a book.

I must apologise in advance for my stupidity.

Regards
Peter

You've got several problems. First, your SQL string is messed up. It is
missing one or more parameters or the parameters are not written properly.
Post the string for strSelect.

Second, you need to test the resultant recordset rsTmp to make sure it has a
record before you perform a Move method.

Third, you need to assign the value (date) of the field you want in rsTmp to
your textbox on the form:

With rstTmp
Me.TxtYear = ![MyDate]
.Close
End With

Set rsTmp = Nothing

[Peter Bailey]

Your advice has all paid off and the record set works and returns the date
that I required to the form text box., The sql string before reffered to a
form control which of course is why it failed.

I have stored a value in a string called datestore which almost works.

I am having trouble getting the quotes arond it for the sql string ie
DateStore should return "2004" the query at the moment show 2004.

partial string example:
=2003) AND ((QryGetDosmodulestartdatesbyyear.[MODULE Name])="M9SA"));

the vba to build up the string (part of it):

=" & DateStore & ")

how do I get the quotes around DateStore I tried many things but it didnt
return the right thing.

Regards

Peter
"Piano Forte" <no******@notvalid.com> wrote in message
news:NI********************@vnet-inc.com...

"Peter Bailey" <pe*********@andaluz.fsbusiness.co.uk> wrote in message
news:ch**********@news8.svr.pol.co.uk...

I have a querystring built up in vba and I want to open a recordset based

on

the sql and pass the date value to a textbox or label for use elsewhere

on the form.

The recordset isnt working as it gives too few parameter error:
Dim db As DAO.Database
Set db = CurrentDb
Dim rsTmp As DAO.Recordset
Set rsTmp = rsTmp.OpenRecordset(strSelect) *****strSelect is the sql
string*****
rsTmp.MoveFirst

Me.TxtYear.Value = rsTmp.Fields(1)

Nothing seems to work even looking at examples from a book.

I must apologise in advance for my stupidity.

Regards
Peter

You've got several problems. First, your SQL string is messed up. It is
missing one or more parameters or the parameters are not written properly.
Post the string for strSelect.

Second, you need to test the resultant recordset rsTmp to make sure it has

a record before you perform a Move method.

Third, you need to assign the value (date) of the field you want in rsTmp to your textbox on the form:

With rstTmp
Me.TxtYear = ![MyDate]
.Close
End With

Set rsTmp = Nothing

[Pieter Linden]

"Peter Bailey" <pe*********@andaluz.fsbusiness.co.uk> wrote in message news:<ch**********@news5.svr.pol.co.uk>...

Your advice has all paid off and the record set works and returns the date
that I required to the form text box., The sql string before reffered to a
form control which of course is why it failed.

I have stored a value in a string called datestore which almost works.

I am having trouble getting the quotes arond it for the sql string ie
DateStore should return "2004" the query at the moment show 2004.

partial string example:
=2003) AND ((QryGetDosmodulestartdatesbyyear.[MODULE Name])="M9SA"));

the vba to build up the string (part of it):

=" & DateStore & ")

how do I get the quotes around DateStore I tried many things but it didnt
return the right thing.

Regards

Peter

since it's a string, do this:

'for clarity
Const cQUOTE As String = "'"

= cQUOTE & DateStore & cQUOTE

[Piano Forte]

You lost me now.

Usually, a date data type is enclosed by the hatch-number-pound symbols
(##):

#1/1/2004#

So my guess is you want something like:

"= #" & DateStore & "#"


"Peter Bailey" <pe*********@andaluz.fsbusiness.co.uk> wrote in message
news:ch**********@news5.svr.pol.co.uk...

Your advice has all paid off and the record set works and returns the date
that I required to the form text box., The sql string before reffered to a
form control which of course is why it failed.

I have stored a value in a string called datestore which almost works.

I am having trouble getting the quotes arond it for the sql string ie
DateStore should return "2004" the query at the moment show 2004.

partial string example:
=2003) AND ((QryGetDosmodulestartdatesbyyear.[MODULE Name])="M9SA"));

the vba to build up the string (part of it):

=" & DateStore & ")

how do I get the quotes around DateStore I tried many things but it didnt
return the right thing.

Regards

Peter
"Piano Forte" <no******@notvalid.com> wrote in message
news:NI********************@vnet-inc.com...

"Peter Bailey" <pe*********@andaluz.fsbusiness.co.uk> wrote in message
news:ch**********@news8.svr.pol.co.uk...

I have a querystring built up in vba and I want to open a recordset based

on

the sql and pass the date value to a textbox or label for use

elsewhere

on the form.

The recordset isnt working as it gives too few parameter error:
Dim db As DAO.Database
Set db = CurrentDb
Dim rsTmp As DAO.Recordset
Set rsTmp = rsTmp.OpenRecordset(strSelect) *****strSelect is the sql
string*****
rsTmp.MoveFirst

Me.TxtYear.Value = rsTmp.Fields(1)

Nothing seems to work even looking at examples from a book.

I must apologise in advance for my stupidity.

Regards
Peter

You've got several problems. First, your SQL string is messed up. It

is missing one or more parameters or the parameters are not written properly. Post the string for strSelect.

Second, you need to test the resultant recordset rsTmp to make sure it

has a

record before you perform a Move method.

Third, you need to assign the value (date) of the field you want in

rsTmp to

your textbox on the form:

With rstTmp
Me.TxtYear = ![MyDate]
.Close
End With

Set rsTmp = Nothing