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 7 2543
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
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
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
"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
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
"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
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
This thread has been closed and replies have been disabled. Please start a new discussion. Similar topics
by: gary artim |
last post by:
Hi All,
I have a problem using DBIx::RecordSet. I get correct results but continue
to get these messages on stderr. I looked at Compat.pm and it seems to be
pointing out a problem with my call...
|
by: Tom |
last post by:
I want to open a recordset object on an .asp page. When I open the
recordset I would like to use a stored procedure that expects a parameter to
be passed for the stored procedure. I will then use...
|
by: Adam Short |
last post by:
I am trying to write a routine that will connect a .NET server with a
classic ASP server.
I know the following code doesn't work! The data is being returned as a
dataset, however ASP does not...
|
by: CFW |
last post by:
I thought this was going to be easy but I'm missing something . . . I
need to open an ADODB recordset using the recordset source for a list
box on my for. When my form opens, the list box ADODB...
|
by: lenny |
last post by:
Hi,
I've been trying to use a Sub or Function in VBA to connect to a
database, make a query and return the recordset that results from the
query. The connection to the database and the query...
|
by: kjvt |
last post by:
Based on a prior posting, I've written a function to convert a
recordset to a dataview. The first call to the function for a given
recordset works perfectly, but the second call always returns a...
|
by: ADezii |
last post by:
Most Access Users realize that Recordsets, being virtual representations of a Query, Table, or SQL Statement, exist only in our PC's memory. They, and the data they contain, literally exist at one...
|
by: ADezii |
last post by:
When you create an ADO Recordset, you should have some idea as to what functionality the Recordset does/does not provide. Some critical questions may, and should, be:
Can I add New Records to the...
|
by: Oko |
last post by:
I'm currently developing an MS Access Data Project (.adp) in MS Access
2002.
One of the reports within the DB uses data that is Dynamic and cannot
be stored on the SQL Server. To resolve this, I...
|
by: wallconor |
last post by:
Hi,
I am having a problem using Dreamweaver CS3 standard recordset paging behavior. It doesn’t seem to work when I pass parameter values from a FORM on my search page, to the recordset on my...
|
by: taylorcarr |
last post by:
A Canon printer is a smart device known for being advanced, efficient, and reliable. It is designed for home, office, and hybrid workspace use and can also be used for a variety of purposes. However,...
|
by: aa123db |
last post by:
Variable and constants
Use var or let for variables and const fror constants.
Var foo ='bar';
Let foo ='bar';const baz ='bar';
Functions
function $name$ ($parameters$) {
}
...
|
by: ryjfgjl |
last post by:
If we have dozens or hundreds of excel to import into the database, if we use the excel import function provided by database editors such as navicat, it will be extremely tedious and time-consuming...
|
by: ryjfgjl |
last post by:
In our work, we often receive Excel tables with data in the same format. If we want to analyze these data, it can be difficult to analyze them because the data is spread across multiple Excel files...
|
by: emmanuelkatto |
last post by:
Hi All, I am Emmanuel katto from Uganda. I want to ask what challenges you've faced while migrating a website to cloud.
Please let me know.
Thanks!
Emmanuel
|
by: Sonnysonu |
last post by:
This is the data of csv file
1 2 3
1 2 3
1 2 3
1 2 3
2 3
2 3
3
the lengths should be different i have to store the data by column-wise with in the specific length.
suppose the i have to...
|
by: Hystou |
last post by:
There are some requirements for setting up RAID:
1. The motherboard and BIOS support RAID configuration.
2. The motherboard has 2 or more available SATA protocol SSD/HDD slots (including MSATA, M.2...
|
by: Hystou |
last post by:
Most computers default to English, but sometimes we require a different language, especially when relocating. Forgot to request a specific language before your computer shipped? No problem! You can...
|
by: jinu1996 |
last post by:
In today's digital age, having a compelling online presence is paramount for businesses aiming to thrive in a competitive landscape. At the heart of this digital strategy lies an intricately woven...
| |