Subform isnt updating

[Guest]

Forgive me if I am not explaining things right here... im sure those of you
who can help will understand what I am refering to.

I posted a questions yesterday, and discovered my own answer....

I have a form that collects lesson registrations.
The user enters client info, date & time, and lesson details

There are 2 subforms on that form... both linked to queries.
The first subform returns Limits that have been set for the date that the
user selects on the main form. The query linked to the subform has a criteria
line that looks like this under the "date" feild:
[forms].[lessonbooker].[date]

My second subform is supposed to return the actual # of lessons that have
been booked on the DATE and TIME that the user has inputed.
This would allow the user to see if the booking he/she is making is actually
crossing one of the limits that has been set.
I have a second query for that subform.... it searches my entire lesson
database and returns the date, time, client, & type of lesson
I have entered 2 crieteia for that query.
Under the Date feild I have
[forms].[lessonbooker].[date]
under the time feild I have
[forms].[lessonbooker].[time]

HERES MY PROBLEM....
When I am using the main form, my first subform that shows limits, updates
emediatley... the second I enter a date, all the limits pop up for that
day... works GREAT

when I enter a time... the second subform stays blank... even though both
criteria it requires have been entered. It seems to not work at all.

To check it, I open the query, and it asks me for the parameteres.... So I
enter a Date & Time that I know will return results, and sure enough... up
pop the expected results!

I beleive I have the date & time formatted the same in all locations... & I
type the date & time in the exact same way in the query as it would appear in
the form... however my results for the query with 2 criteria only show up in
query view.... they will not update or show in the subform?!?!?

How many people have I confused???

What am I doning wrong? My only thought is that using two lookup style
criteria does not work in subforms.... but then again, the results show up
fine when I use the query on its own............. DAH!

 

[Douglas J Steele]

How are you storing the information? In other words, what do your tables
look like?

In order to be able to determine what lessons are scheduled for a particular
point in time, you need to know when each lesson starts, and when it ends. I
would recommend very strongly that you not store the date and time as
separate fields: it's MUCH easier to work with combined date/time fields
(and when you need only the date, or only the time, you can use the
DateValue and TimeValue functions on the combined value). Lessons that are
scheduled for a particular point in time will start before (or at) the point
of time of interest and end after that point of time. That means you need to
compare what was input on the form with the two different timestamps
associated with each lesson, using <= and >

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)

Forgive me if I am not explaining things right here... im sure those of you
who can help will understand what I am refering to.

I posted a questions yesterday, and discovered my own answer....

I have a form that collects lesson registrations.
The user enters client info, date & time, and lesson details

There are 2 subforms on that form... both linked to queries.
The first subform returns Limits that have been set for the date that the
user selects on the main form. The query linked to the subform has a criteria
line that looks like this under the "date" feild:
[forms].[lessonbooker].[date]

My second subform is supposed to return the actual # of lessons that have
been booked on the DATE and TIME that the user has inputed.
This would allow the user to see if the booking he/she is making is actually
crossing one of the limits that has been set.
I have a second query for that subform.... it searches my entire lesson
database and returns the date, time, client, & type of lesson
I have entered 2 crieteia for that query.
Under the Date feild I have
[forms].[lessonbooker].[date]
under the time feild I have
[forms].[lessonbooker].[time]

HERES MY PROBLEM....
When I am using the main form, my first subform that shows limits, updates
emediatley... the second I enter a date, all the limits pop up for that
day... works GREAT

when I enter a time... the second subform stays blank... even though both
criteria it requires have been entered. It seems to not work at all.

To check it, I open the query, and it asks me for the parameteres.... So I
enter a Date & Time that I know will return results, and sure enough... up
pop the expected results!

I beleive I have the date & time formatted the same in all locations... & I
type the date & time in the exact same way in the query as it would appear in
the form... however my results for the query with 2 criteria only show up in
query view.... they will not update or show in the subform?!?!?

How many people have I confused???

What am I doning wrong? My only thought is that using two lookup style
criteria does not work in subforms.... but then again, the results show up
fine when I use the query on its own............. DAH!