Refresh a form with new date selected

[Guest]

Hello all....

I would like to have a date box on my form where the user can pick a new
date and using the On Exit event, have the textboxes on the form refresh with
data matching the selected date. My textboxes are using a DLookup function
and I have tried adding the txtDate field as a criteria, but this does not
work.

Any ideas would be appreciated.

 

[Jeff Boyce]

Paul

How are you using the txtDate field as a criterion (please post the SQL)?

How are you telling the textboxes (or the full form) to refresh OnExit
(please post the SQL)?

 

[Guest]

The following piece of code is tied to the OK button of my popup calendar:
----
If gtxtCalTarget = Me.txtDate Then
'do nothing
Else
gtxtCalTarget = Me.txtDate
End If
gtxtCalTarget.SetFocus

Dim stDocName As String
Dim stLinkCriteria As String
stDocName = "frmSchedule"

stLinkCriteria = "[Dates]=" & "#" & Format$(Me![txtDate], "mm/dd/yyyy")
& "#"
DoCmd.OpenForm stDocName, , , stLinkCriteria

DoCmd.Close acForm, Me.Name, acSaveNo
---

What I would like to do is have this on one form instead of two.

Is this enough info??

Cheers

 

[Jeff Boyce]

Sorry, Paul, but I'm not there -- I can't see your popup calendar, and I
don't understand it has to do with your original post re: refreshing text
boxes...

Jeff Boyce
<Access MVP>

The following piece of code is tied to the OK button of my popup calendar:
----
If gtxtCalTarget = Me.txtDate Then
'do nothing
Else
gtxtCalTarget = Me.txtDate
End If
gtxtCalTarget.SetFocus

Dim stDocName As String
Dim stLinkCriteria As String
stDocName = "frmSchedule"

stLinkCriteria = "[Dates]=" & "#" & Format$(Me![txtDate], "mm/dd/yyyy")
& "#"
DoCmd.OpenForm stDocName, , , stLinkCriteria

DoCmd.Close acForm, Me.Name, acSaveNo
---

What I would like to do is have this on one form instead of two.

Is this enough info??

Cheers

Paul

How are you using the txtDate field as a criterion (please post the SQL)?

How are you telling the textboxes (or the full form) to refresh OnExit
(please post the SQL)?

--
Good luck

Jeff Boyce
<Access MVP>

refresh
with

 

[Guest]

Sorry Jeff,

The popup calendar is on a form called 'frmSelectDate'.

When I click the OK button on the calendar, the form 'frmSchedule' opens to
the selected date, effectively 'refreshing' the data. I should probably close
the open form first, then re-open with the new date, but I'll fix that later.

On the form 'frmSchedule', there are a number of textboxes, 'txt1030Slot',
using DLookup to pull the values of First and Last Name based on value of the
textbox 'txt1030Time'. The form is bound to the table 'tblSchedule' and
filtered to match the 'txtSelectDate' value from the popup calendar.

I have this working using seperate forms, but my boss wants it in one form.

So, ultimately, I would like this on one form so that selecting a new date
from the popup calendar re-popluates the form with the data row matching the
date selected.

The control source for the txt1030Slot is:

=IIf([txt1030Time]>=2, DLookup("[LastName] & ' , ' & [FirstName]", _
"tblPatients","[PatientID]=" & [txt1030Time]), "")


I hope this paints a better picture for you....

cheers

Sorry, Paul, but I'm not there -- I can't see your popup calendar, and I
don't understand it has to do with your original post re: refreshing text
boxes...

Jeff Boyce
<Access MVP>

The following piece of code is tied to the OK button of my popup calendar:
----
If gtxtCalTarget = Me.txtDate Then
'do nothing
Else
gtxtCalTarget = Me.txtDate
End If
gtxtCalTarget.SetFocus

Dim stDocName As String
Dim stLinkCriteria As String
stDocName = "frmSchedule"

stLinkCriteria = "[Dates]=" & "#" & Format$(Me![txtDate], "mm/dd/yyyy")
& "#"
DoCmd.OpenForm stDocName, , , stLinkCriteria

DoCmd.Close acForm, Me.Name, acSaveNo
---

What I would like to do is have this on one form instead of two.

Is this enough info??

Cheers

Paul

How are you using the txtDate field as a criterion (please post the SQL)?

How are you telling the textboxes (or the full form) to refresh OnExit
(please post the SQL)?

--
Good luck

Jeff Boyce
<Access MVP>

Hello all....

I would like to have a date box on my form where the user can pick a new
date and using the On Exit event, have the textboxes on the form refresh
with
data matching the selected date. My textboxes are using a DLookup function
and I have tried adding the txtDate field as a criteria, but this does not
work.

Any ideas would be appreciated.

 

[Jeff Boyce]

Sorry, I must be caffeine-deprived, but I'm still not seeing it.

If you are still working on this, and willing to expand a bit further, it
might help to see a brief description of the table structure underlying your
forms/queries.

And if you step away from the "how" of what you are currently doing, can you
describe the "what" and "why". In other words, you've come up with one way
to try to accomplish something. I'm asking about what the something is, and
what the underlying business need is (the "why"). It may be that there are
alternative approaches to accomplish the same end-result.

 

[Guest]

Thanks Jeff...

I am having difficulty is trying to unwrap my head around what I was trying
to accomplish, but here goes...

In a nut shell, I want a single form to act as a Time Table so that
selecting a date from the calendar shows the appointments for that day.

So, when the user selects a date from the popup calendar, the form refreshes
(if that's the right term) with the data row matching the date selected.

The 'tblSchedule' table looks like this...

DateID | Dates | 0830 | 0930| 1000 | 1030 | etc... | 1600 |

Auto# 17-Feb-05 1 1 5 7 1


The numbers in the time fields above are the PatientID's. These fields are
hidden on the time table form (frmSchedule). The textboxes [txt0830Slot]
looks up the LastName and FirstName based on the value of the corresponding
hidden textbox [txt0830Time] and if = 1 then it is left blank.

I guess I could make the frmSchedule a subform of the frmSelectDate where
the popup calendar is. I didn't realize that this would so difficult to solve.

Sorry, I don't know how to explain it any other way, so if this doesn't help
you understand what I am trying to do, then Thank You anyway.

cheers

Sorry, I must be caffeine-deprived, but I'm still not seeing it.

If you are still working on this, and willing to expand a bit further, it
might help to see a brief description of the table structure underlying your
forms/queries.

And if you step away from the "how" of what you are currently doing, can you
describe the "what" and "why". In other words, you've come up with one way
to try to accomplish something. I'm asking about what the something is, and
what the underlying business need is (the "why"). It may be that there are
alternative approaches to accomplish the same end-result.

--
Good luck

Jeff Boyce
<Access MVP>

Sorry Jeff,

The popup calendar is on a form called 'frmSelectDate'.

When I click the OK button on the calendar, the form 'frmSchedule' opens to
the selected date, effectively 'refreshing' the data. I should probably close
the open form first, then re-open with the new date, but I'll fix that later.

On the form 'frmSchedule', there are a number of textboxes, 'txt1030Slot',
using DLookup to pull the values of First and Last Name based on value of the
textbox 'txt1030Time'. The form is bound to the table 'tblSchedule' and
filtered to match the 'txtSelectDate' value from the popup calendar.

I have this working using seperate forms, but my boss wants it in one form.

So, ultimately, I would like this on one form so that selecting a new date
from the popup calendar re-popluates the form with the data row matching the
date selected.

The control source for the txt1030Slot is:

=IIf([txt1030Time]>=2, DLookup("[LastName] & ' , ' & [FirstName]", _
"tblPatients","[PatientID]=" & [txt1030Time]), "")


I hope this paints a better picture for you....

cheers

 

[Jeff Boyce]

Paul

So, you have a table that has "timeslot" fields? This is a very appropriate
design, but only if you are using a spreadsheet. As one of the MVPs is fond
of pointing out, Access is a relational database, ... use it relationally!

If I am understanding, you have the follow facts related (closely) to each
other:

a Patient (PatientID)
a Date and Time (e.g. February 26, 2005 at 9:30 am)
a duration (e.g., 30 minutes, 60 minutes, ...)

The table design you describe would have to be altered everytime the
duration of appointment was altered, leading to severe maintenance issues.
The table design above may handle any combination that you throw at it,
including deciding to start "evening hours".

From a "forms" standpoint, you may want to re-post, or try searching
Google.com or at the mvps.org/access website to see if you can get the
display to look like you are describing.

I would ask, however, how important it is to the end user to be able to see
all the appointments of the day, rather than identifying only the full
and/or empty ones?

Good luck

Jeff Boyce
<Access MVP>

Thanks Jeff...

I am having difficulty is trying to unwrap my head around what I was trying
to accomplish, but here goes...

In a nut shell, I want a single form to act as a Time Table so that
selecting a date from the calendar shows the appointments for that day.

So, when the user selects a date from the popup calendar, the form refreshes
(if that's the right term) with the data row matching the date selected.

The 'tblSchedule' table looks like this...

DateID | Dates | 0830 | 0930| 1000 | 1030 | etc... | 1600 |

Auto# 17-Feb-05 1 1 5 7 1


The numbers in the time fields above are the PatientID's. These fields are
hidden on the time table form (frmSchedule). The textboxes [txt0830Slot]
looks up the LastName and FirstName based on the value of the corresponding
hidden textbox [txt0830Time] and if = 1 then it is left blank.

I guess I could make the frmSchedule a subform of the frmSelectDate where
the popup calendar is. I didn't realize that this would so difficult to solve.

Sorry, I don't know how to explain it any other way, so if this doesn't help
you understand what I am trying to do, then Thank You anyway.

cheers

Sorry, I must be caffeine-deprived, but I'm still not seeing it.

If you are still working on this, and willing to expand a bit further, it
might help to see a brief description of the table structure underlying your
forms/queries.

And if you step away from the "how" of what you are currently doing, can you
describe the "what" and "why". In other words, you've come up with one way
to try to accomplish something. I'm asking about what the something is, and
what the underlying business need is (the "why"). It may be that there are
alternative approaches to accomplish the same end-result.

--
Good luck

Jeff Boyce
<Access MVP>

Sorry Jeff,

The popup calendar is on a form called 'frmSelectDate'.

When I click the OK button on the calendar, the form 'frmSchedule'

opens
to

the selected date, effectively 'refreshing' the data. I should

probably
close

the open form first, then re-open with the new date, but I'll fix that later.

On the form 'frmSchedule', there are a number of textboxes, 'txt1030Slot',
using DLookup to pull the values of First and Last Name based on value

of
the

textbox 'txt1030Time'. The form is bound to the table 'tblSchedule' and
filtered to match the 'txtSelectDate' value from the popup calendar.

I have this working using seperate forms, but my boss wants it in one form.

So, ultimately, I would like this on one form so that selecting a new date
from the popup calendar re-popluates the form with the data row

matching
the

date selected.

The control source for the txt1030Slot is:

=IIf([txt1030Time]>=2, DLookup("[LastName] & ' , ' & [FirstName]", _
"tblPatients","[PatientID]=" & [txt1030Time]), "")


I hope this paints a better picture for you....

cheers