By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
424,479 Members | 2,260 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 424,479 IT Pros & Developers. It's quick & easy.

Openrecordset with criteria problem

P: n/a
I have a report that looks up the earliest start time in a query
[assignments]. Right now it opens up the query and grabs the earliest
start date like it should. This is the working code:

Set rs = db.OpenRecordset("SELECT Min([TimeStart]) AS MinOfStartTime "
_
& " FROM Assignments", dbOpenSnapshot)

The problem is that this query is showing start and finish times from
different events. Is there a way to have it grab the earliest start
date from the query where [eventname] in the query is the same as
[eventname] on the form [EventScheduler]. I tried this code:

Set rs = db.OpenRecordset("SELECT Min([TimeStart]) AS MinOfStartTime "
_
& " FROM Assignments WHERE
EventName=forms![EventScheduler][EventName]", dbOpenSnapshot)

This code returns the error: "Too few parameters. Expected 1."
Any ideas
SY

Jul 21 '06 #1
Share this Question
Share on Google+
18 Replies


P: n/a
Set the filter in the Open event, not the recordsource.

DoCmd.OpenReport... one of the arguments is the filter "EventName=..."

Jul 21 '06 #2

P: n/a
Shawn Yates wrote:
Set rs = db.OpenRecordset("SELECT Min([TimeStart]) AS MinOfStartTime "
_
& " FROM Assignments WHERE
EventName=forms![EventScheduler][EventName]", dbOpenSnapshot)
Maybe put a ! between [EventScheduler] and [EventName]?

James A. Fortune
CD********@FortuneJames.com

Jul 21 '06 #3

P: n/a
I'm not quite sure if we are thinking the same thing. You may be
totally correct but how would that fit into my current code. Let me
show you more of the code and see what you think.
__________________________________________________ ________
Private Sub Report_Open(Cancel As Integer)
Dim db As Database
Dim rs As Recordset
Set db = CurrentDb
Set rs = db.OpenRecordset("SELECT Min([TimeStart]) AS MinOfStartTime
" _
& " FROM Assignments", dbOpenSnapshot)
If rs.RecordCount 0 Then
mtimeEarliest = rs!MinOfStartTime
End If
Set rs = db.OpenRecordset("SELECT
Max(IIf(IsDate([TimeFinish]),CDate([TimeFinish]),Null)) " _
& "AS MaxOfEndTime FROM Assignments", dbOpenSnapshot)
If rs.RecordCount 0 Then
mtimeLatest = rs!MaxOfEndTime
End If

mintTimeDiff = DateDiff("n", mtimeEarliest, mtimeLatest)

Me.txtMinStartTime.Caption = Format(mtimeEarliest, "hh:nn AMPM")
Me.txtMaxEndTime.Caption = Format(mtimeLatest, "hh:nn AMPM")
Set rs = Nothing
Set db = Nothing
End Sub
___________________________________________

This a larger part of what is going on. Now all I want to do is enter
some criteria when it selects the max of time finish and the min of
time finish. Hopefully this helped my first post make more sense. If it
would help I can post all of the code for my report.
Thanks
SY

Jul 21 '06 #4

P: n/a

CDMAPos...@FortuneJames.com wrote:
Shawn Yates wrote:
Set rs = db.OpenRecordset("SELECT Min([TimeStart]) AS MinOfStartTime "
_
& " FROM Assignments WHERE
EventName=forms![EventScheduler][EventName]", dbOpenSnapshot)

Maybe put a ! between [EventScheduler] and [EventName]?

James A. Fortune
Thanks James, I actually had ! inbetween these in my trial code I just
didn't post it correctly. I also tried just a "." but that didn't do
the trick either.
Thanks again
SY

Jul 21 '06 #5

P: n/a
Shawn Yates wrote:
CDMAPos...@FortuneJames.com wrote:
Shawn Yates wrote:
Set rs = db.OpenRecordset("SELECT Min([TimeStart]) AS MinOfStartTime "
_
& " FROM Assignments WHERE
EventName=forms![EventScheduler][EventName]", dbOpenSnapshot)
Maybe put a ! between [EventScheduler] and [EventName]?

James A. Fortune

Thanks James, I actually had ! inbetween these in my trial code I just
didn't post it correctly. I also tried just a "." but that didn't do
the trick either.
Thanks again
SY
O.K. Does it work when you replace form![Event... Name] with a literal
EventName in single quotes?

James A. Fortune
CD********@FortuneJames.com

Jul 21 '06 #6

P: n/a
"Shawn Yates" <sy****@cc.usu.eduwrote in
news:11**********************@b28g2000cwb.googlegr oups.com:
& " FROM Assignments WHERE
EventName=forms![EventScheduler][EventName]", dbOpenSnapshot)
Replace the WHERE with:

" WHERE EventName=" & Chr(34) & _
Forms!EventScheduler.Form!EventName & Chr(34)

You cannot reliably pass form references through to a recordset, so
resolve the value of the form control *before* constructing the SQL
string.

--
David W. Fenton http://www.dfenton.com/
usenet at dfenton dot com http://www.dfenton.com/DFA/
Jul 21 '06 #7

P: n/a
So could I do something like:
Private Sub Report_Open(Cancel As Integer)
Dim db As Database
Dim rs As Recordset
Dim fld As Field

Set db = CurrentDb
Set fld = Forms![EventScheduler]![EventName]
Set rs = db.OpenRecordset("SELECT Min([TimeStart]) AS MinOfStartTime
" _
& " FROM Assignments WHERE [EventName]=fld ", dbOpenSnapshot)

As you can see I am fairly new at code but I want to learn more. I
tried this but I think I did something wrong with (Dim fld as field).
I guess what I was trying to do was set the field name from the form
using the variable fld then open the recordset with WHERE
[EventName]=fld. Am I on the right track or am I completely lost.
Thanks for your patience and help.
SY

Jul 21 '06 #8

P: n/a
Hi Shawn,

I think that your problem is that EventName is a String. In that case
it must be quoted in the Select-statement.
I use a small function As_text to convert to quoted strings. In that
case I do not have to bother on all that quotes to type.

Function As_text(cur_text AS String) AS String
As_text = "'" & cur_text & "'"
End Function

As_text adds single quotes, whereas the SELECT-string is build up with
double quotes.

You can use the next line

Set rs = CurrentDb.OpenRecordset _
("SELECT Min(TimeStart) AS MinOfStartTime FROM
Assignments" _
& " WHERE EventName = " &
As_text(Forms!EventScheduler!EventName),dbOpenSnap shot)

BTW the square brackets [ and ] are only necessary if the name contains
characters like space or quote etc.
HBInc.

Shawn Yates wrote:
So could I do something like:
Private Sub Report_Open(Cancel As Integer)
Dim db As Database
Dim rs As Recordset
Dim fld As Field

Set db = CurrentDb
Set fld = Forms![EventScheduler]![EventName]
Set rs = db.OpenRecordset("SELECT Min([TimeStart]) AS MinOfStartTime
" _
& " FROM Assignments WHERE [EventName]=fld ", dbOpenSnapshot)

As you can see I am fairly new at code but I want to learn more. I
tried this but I think I did something wrong with (Dim fld as field).
I guess what I was trying to do was set the field name from the form
using the variable fld then open the recordset with WHERE
[EventName]=fld. Am I on the right track or am I completely lost.
Thanks for your patience and help.
SY
Jul 21 '06 #9

P: n/a
As_text adds single quotes, whereas the SELECT-string is build up with
double quotes.

You can use the next line

Set rs = CurrentDb.OpenRecordset _
("SELECT Min(TimeStart) AS MinOfStartTime FROM
Assignments" _
& " WHERE EventName = " &
As_text(Forms!EventScheduler!EventName),dbOpenSnap shot)
Thanks for the info. I put this into my code but it said Sub or
Function not defined. When I pressed debug it had the As_text
highlighted in grey.
thanks
SY

Jul 21 '06 #10

P: n/a
"Shawn Yates" <sy****@cc.usu.eduwrote in
news:11**********************@b28g2000cwb.googlegr oups.com:
So could I do something like:
Private Sub Report_Open(Cancel As Integer)
Dim db As Database
Dim rs As Recordset
Dim fld As Field

Set db = CurrentDb
Set fld = Forms![EventScheduler]![EventName]
Set rs = db.OpenRecordset("SELECT Min([TimeStart]) AS
MinOfStartTime
" _
& " FROM Assignments WHERE [EventName]=fld ", dbOpenSnapshot)
Um, why are you using a recordset at all? Just assign the
recordsource of the subform, with no WHERE clause, and then set the
link fields on the subform object.
As you can see I am fairly new at code but I want to learn
more. . . .
So far as I can tell, there is absolutely no need for code to do
what you are attempting.
. . . I
tried this but I think I did something wrong with (Dim fld as
field). I guess what I was trying to do was set the field name
from the form using the variable fld then open the recordset with
WHERE [EventName]=fld. Am I on the right track or am I completely
lost.
This should be something resolvable by point and click methods. Do
you know how to create simple bound forms with bound subforms with
child records? If not, then figure that out, and chances are your
problems will be gone.

A lot of people who are experienced with programming in other
environments reach too soon for code when they start working with
Access, instead of familiarizing themselves with the capabilities of
the UI in generating applications without any code at all. I don't
know if that applies to you, but it's very common.

For what it's worth, I've never even once assigned a recordset
object to a form.

--
David W. Fenton http://www.dfenton.com/
usenet at dfenton dot com http://www.dfenton.com/DFA/
Jul 21 '06 #11

P: n/a
Hi Shawn,

Probably you added the As_text function on the form. In that case the
function is not known at opening the form.
Instead add the function to a Module. If you don't have any Module,
make a new one, call it f.i. General and add the code. Be sure the
function has NOT the predicate PRIVATE.
Now you can use it in any instance.

Success, HBInc.

hbinc wrote:
Hi Shawn,

I think that your problem is that EventName is a String. In that case
it must be quoted in the Select-statement.
I use a small function As_text to convert to quoted strings. In that
case I do not have to bother on all that quotes to type.

Function As_text(cur_text AS String) AS String
As_text = "'" & cur_text & "'"
End Function

As_text adds single quotes, whereas the SELECT-string is build up with
double quotes.

You can use the next line

Set rs = CurrentDb.OpenRecordset _
("SELECT Min(TimeStart) AS MinOfStartTime FROM
Assignments" _
& " WHERE EventName = " &
As_text(Forms!EventScheduler!EventName),dbOpenSnap shot)

BTW the square brackets [ and ] are only necessary if the name contains
characters like space or quote etc.
HBInc.

Shawn Yates wrote:
So could I do something like:
Private Sub Report_Open(Cancel As Integer)
Dim db As Database
Dim rs As Recordset
Dim fld As Field

Set db = CurrentDb
Set fld = Forms![EventScheduler]![EventName]
Set rs = db.OpenRecordset("SELECT Min([TimeStart]) AS MinOfStartTime
" _
& " FROM Assignments WHERE [EventName]=fld ", dbOpenSnapshot)

As you can see I am fairly new at code but I want to learn more. I
tried this but I think I did something wrong with (Dim fld as field).
I guess what I was trying to do was set the field name from the form
using the variable fld then open the recordset with WHERE
[EventName]=fld. Am I on the right track or am I completely lost.
Thanks for your patience and help.
SY
Jul 21 '06 #12

P: n/a
Hi David,

It's a way of working.
In all my applications I have no single Querydef definied, and all is
done in code.
Modification of any application is no problem anymore, and done in
notime.

HBInc.

David W. Fenton wrote:
"Shawn Yates" <sy****@cc.usu.eduwrote in
news:11**********************@b28g2000cwb.googlegr oups.com:
So could I do something like:
Private Sub Report_Open(Cancel As Integer)
Dim db As Database
Dim rs As Recordset
Dim fld As Field

Set db = CurrentDb
Set fld = Forms![EventScheduler]![EventName]
Set rs = db.OpenRecordset("SELECT Min([TimeStart]) AS
MinOfStartTime
" _
& " FROM Assignments WHERE [EventName]=fld ", dbOpenSnapshot)

Um, why are you using a recordset at all? Just assign the
recordsource of the subform, with no WHERE clause, and then set the
link fields on the subform object.
As you can see I am fairly new at code but I want to learn
more. . . .

So far as I can tell, there is absolutely no need for code to do
what you are attempting.
. . . I
tried this but I think I did something wrong with (Dim fld as
field). I guess what I was trying to do was set the field name
from the form using the variable fld then open the recordset with
WHERE [EventName]=fld. Am I on the right track or am I completely
lost.

This should be something resolvable by point and click methods. Do
you know how to create simple bound forms with bound subforms with
child records? If not, then figure that out, and chances are your
problems will be gone.

A lot of people who are experienced with programming in other
environments reach too soon for code when they start working with
Access, instead of familiarizing themselves with the capabilities of
the UI in generating applications without any code at all. I don't
know if that applies to you, but it's very common.

For what it's worth, I've never even once assigned a recordset
object to a form.

--
David W. Fenton http://www.dfenton.com/
usenet at dfenton dot com http://www.dfenton.com/DFA/
Jul 21 '06 #13

P: n/a
"hbinc" <j.********@hccnet.nlwrote in
news:11*********************@p79g2000cwp.googlegro ups.com:
David W. Fenton wrote:
>"Shawn Yates" <sy****@cc.usu.eduwrote in
news:11**********************@b28g2000cwb.googleg roups.com:
So could I do something like:
Private Sub Report_Open(Cancel As Integer)
Dim db As Database
Dim rs As Recordset
Dim fld As Field

Set db = CurrentDb
Set fld = Forms![EventScheduler]![EventName]
Set rs = db.OpenRecordset("SELECT Min([TimeStart]) AS
MinOfStartTime
" _
& " FROM Assignments WHERE [EventName]=fld ", dbOpenSnapshot)

Um, why are you using a recordset at all? Just assign the
recordsource of the subform, with no WHERE clause, and then set
the link fields on the subform object.
As you can see I am fairly new at code but I want to learn
more. . . .

So far as I can tell, there is absolutely no need for code to do
what you are attempting.
. . . I
tried this but I think I did something wrong with (Dim fld as
field). I guess what I was trying to do was set the field name
from the form using the variable fld then open the recordset
with WHERE [EventName]=fld. Am I on the right track or am I
completely lost.

This should be something resolvable by point and click methods.
Do you know how to create simple bound forms with bound subforms
with child records? If not, then figure that out, and chances are
your problems will be gone.

A lot of people who are experienced with programming in other
environments reach too soon for code when they start working with
Access, instead of familiarizing themselves with the capabilities
of the UI in generating applications without any code at all. I
don't know if that applies to you, but it's very common.

For what it's worth, I've never even once assigned a recordset
object to a form.

It's a way of working.
In all my applications I have no single Querydef definied, and all
is done in code.
Huh? There's no problem with using a Querydef in multiple
recordsources of a form, changing the WHERE clause when you assign
the recordsource. Of course, I hardly ever save recordsources as
queries, except for recordsources that would be used by more than
one form.
Modification of any application is no problem anymore, and done in
notime.
Huh? Why do you need a recordset? All you need is to assign the
appropriate recordsource in code. Unless you actually want to edit
the same recordset in multiple forms simultaneously, I just can't
see the point (and that's a problematic scenario itself). The only
other reason I can think of would be if you're editing a
disconnected recordset, which is something you *can't* do with a
regular recordsource (unless you use temp tables).

--
David W. Fenton http://www.dfenton.com/
usenet at dfenton dot com http://www.dfenton.com/DFA/
Jul 22 '06 #14

P: n/a
I am using this code for a report. It is pulling the record source from
a query that involves dozens of events. I need the report to show the
earliest start times and latest finish times for the event that the
form EventScheduler is currently on. The section of code I am currently
stuck on is looking in the query for the right info but it is including
all events. I need it to look in the query for only the event I am
currently viewing on the form. The start and finish times I then
recieve are used in other calculations and such in my code so it would
really help if I could handle all of this part in code. I apologize for
my lack of knowledge but I don't quite understand what you think I
should do here.
>Hi Shawn,
Probably you added the As_text function on the form. In that case the
function is not known at opening the form.
Instead add the function to a Module. If you don't have any Module,
make a new one, call it f.i. General and add the code. Be sure the
function has NOT the predicate PRIVATE.
Now you can use it in any instance.
Success, HBInc.
I put the code you gave me into the On Open event for my REPORT. I am
still learning code, but I understand what your getting at with the
module idea. What would the module look like and how would I
incorperate it into my exsisting On Open code. Or would I have to
completely modify my whole report. I again apologize for my lack of
knowledge in this area but I really appreciate all the help I can get.
Thanks
SY

Jul 22 '06 #15

P: n/a
Hi Shawn,

Go to the database window, en select "Modules".
Click on "New" to make a new Module.

You then see:

Option Compare Database
Option Explicit

Next you type ( or paste) the code for then Function As_text:
Function As_text(cur_text AS String) AS String
...
...
End Function
Close the module, and save it. You can call it General or
String_routines or whatever name you think is more appropriate.
That all. This function is now available on any Form, Report, Module,
....

Success, HBInc.

Shawn Yates wrote:
I am using this code for a report. It is pulling the record source from
a query that involves dozens of events. I need the report to show the
earliest start times and latest finish times for the event that the
form EventScheduler is currently on. The section of code I am currently
stuck on is looking in the query for the right info but it is including
all events. I need it to look in the query for only the event I am
currently viewing on the form. The start and finish times I then
recieve are used in other calculations and such in my code so it would
really help if I could handle all of this part in code. I apologize for
my lack of knowledge but I don't quite understand what you think I
should do here.
Hi Shawn,
Probably you added the As_text function on the form. In that case the
function is not known at opening the form.
Instead add the function to a Module. If you don't have any Module,
make a new one, call it f.i. General and add the code. Be sure the
function has NOT the predicate PRIVATE.
Now you can use it in any instance.
Success, HBInc.

I put the code you gave me into the On Open event for my REPORT. I am
still learning code, but I understand what your getting at with the
module idea. What would the module look like and how would I
incorperate it into my exsisting On Open code. Or would I have to
completely modify my whole report. I again apologize for my lack of
knowledge in this area but I really appreciate all the help I can get.
Thanks
SY
Jul 22 '06 #16

P: n/a
Alright
I've got the module up an working now. THANK YOU. Now I am trying to
get this line of code working with the module:

Set rs = CurrentDb.OpenRecordset _
("SELECT Min(TimeStart) AS MinOfStartTime FROM
Assignments" _
& " WHERE EventName= " & _
As_text(Forms!EventScheduler!EventName), dbOpenSnapshot)

This is the code you gave me earlier. Now the As_text is working but it
returned an error: Run-time Error '3075': Syntax error (missing
operator) in query expression 'EventName='.
I also put my cursor over (Forms!EventScheduler!EventName) and it said
Forms!EventScheduler!EventName="Dancin Dynamics". Could the problem be
that the name of this event has a space in it?
SY

Jul 22 '06 #17

P: n/a
Disregard my last post I realized my stupid mistake. I forgot to put
(As_text = "'" & Cur_Text & "'") in my module. You are a genius and I
thank you for your expertice and patience with me.
Shawn Yates

Jul 22 '06 #18

P: n/a
"Shawn Yates" <sy****@cc.usu.eduwrote in
news:11**********************@p79g2000cwp.googlegr oups.com:
I am using this code for a report. . . .
Since you didn't include all your coce, it wasn't clear to me what
you were using the Recordset for.

My original advice stands:

Don't include the control reference in the SQL you use to open your
recordset. Instead, resolve it to the value of the control and put
the literal control value in the SQL you use to open the recordset.
This will get rid of the original error you reported, which was TOO
FEW PARAMETERS, because DAO can't resolve the meaning of
Forms!EventScheduler.Form!EventName

Given that you need to look up this information for each event, I
think you don't want it in the form's OnOpen, unless the report is
always opened with a single event in the recordset. You'd want it in
the OnFormat of the detail, I think.

It all depends on how many records there are in your report, and how
many records apply to a single "event".

--
David W. Fenton http://www.dfenton.com/
usenet at dfenton dot com http://www.dfenton.com/DFA/
Jul 23 '06 #19

This discussion thread is closed

Replies have been disabled for this discussion.