A Microsoft Office (Excel, Word) forum. OfficeFrustration

If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below.

Go Back   Home » OfficeFrustration forum » Microsoft Access » Using Forms
Site Map Home Register Authors List Search Today's Posts Mark Forums Read  

Dates in a listbox connected to a form...



 
 
Thread Tools Display Modes
  #1  
Old November 16th, 2004, 06:04 PM
RusCat
external usenet poster
 
Posts: n/a
Default Dates in a listbox connected to a form...

Good morning,

Here is something I'm tryng to do, but can't seem to get a handle on. I am
hoping for some direction.

I have the following things listed in a listbox: today, this week, this
month, last week, last month, custom

I want to be able to click on THISWEEK and have all the records for this
week to show up on my form.

It is a projects table where our employees log in the hours they have spent
on a project. I need them to be able to see the data by the choices above.

ALSO: how would I create a CUSTOM set of data? Click on CUSTOM, some sort
of boxes appear where I could type in the 2 dates and everything between
those dates would show up on the form.

I've tried queries, but I'm just not getting it. I understand the logic
behind it, but just not how to get it done. I'm a novice Access user, so
please be gentle in your help and explainations !!

Thank you so much.
LisaB.
  #2  
Old November 17th, 2004, 12:04 PM
Nikos Yannacopoulos
external usenet poster
 
Posts: n/a
Default

Lisa,

To begin with, I would add two textboxes on the form (called, say, txtDateFm
and txtDateTo, formatted as date - same format as the date field in the
table), and set their Visible property to No in form design. These will be
used for holding the from and to dates, populated by the listbox (while
still invisible) on every other choice, and made visible to be filled
manually when custom is selected.

The query for the records would reference these two text boxes in its
criterion on the date field, like:

= Forms![Form Name]![txtDateFm] and = Forms![Form Name]![txtDateTo]


I would use the double click event of the listbox (assumed name List0) to
calculate the dates and requery the subform (assumed name: Subform1) with
the records. Of course, when custom is selected, the user has to enter the
dates and then somehow requery the subform. For this I would use a command
button (called Command2 in my sample code, caption something like Refresh or
Get Data, also hidden in the form design) which is toggled between visible
and invisible together with the textboxes.

Here's the code behind the Command2 button:

Private Sub Command2_Click()
Me.Subform1.Requery
End Sub

And, finally, here's the code behind the listbox's double click event:

Private Sub List0_DblClick(Cancel As Integer)
Select Case Me.List0
Case "Today"
Me.txtDateFm = Date
Me.txtDateTo = Date
Me.Subform1.Requery
Case "This Week"
dt = Date
If Weekday(dt, 3) = 7 Then dt = dt + 7
Me.txtDateFm = dt - Weekday(dt, 3)
Me.txtDateTo = dt - Weekday(dt, 3) + 6
Me.Subform1.Requery
Case "This Month"
Select Case Month(Date)
Case 12
y1 = Year(Date)
m1 = Month(Date)
y2 = y1 + 1
m2 = 1
Case Else
y1 = Year(Date)
m1 = Month(Date)
y2 = y1
m2 = m1 + 1
End Select
Me.txtDateFm = DateSerial(y1, m1, 1)
Me.txtDateTo = DateSerial(y2, m2, 1) - 1
Me.Subform1.Requery
Case "Last Week"
dt = Date
If Weekday(dt, 3) = 7 Then dt = dt + 7
Me.txtDateFm = dt - Weekday(dt, 3) - 7
Me.txtDateTo = dt - Weekday(dt, 3) - 1
Me.Subform1.Requery
Case "Last Month"
Select Case Month(Date)
Case 1
y1 = Year(Date) - 1
m1 = 12
y2 = y1 + 1
m2 = 1
Case Else
y1 = Year(Date)
m1 = Month(Date) - 1
y2 = y1
m2 = m1 + 1
End Select
Me.txtDateFm = DateSerial(y1, m1, 1)
Me.txtDateTo = DateSerial(y2, m2, 1) - 1
Me.Subform1.Requery
Case "Custom"
Me.txtDateFm = Null
Me.txtDateTo = Null
Me.txtDateFm.Visible = True
Me.txtDateTo.Visible = True
Me.Command2.Visible = True
GoTo skip_hide
End Select

Me.txtDateFm.Visible = False
Me.txtDateTo.Visible = False
Me.Command2.Visible = False

skip_hide:
End Sub

Don't forget to change the object names to your real names!

HTH,
Nikos

"RusCat" wrote in message
...
Good morning,

Here is something I'm tryng to do, but can't seem to get a handle on. I

am
hoping for some direction.

I have the following things listed in a listbox: today, this week, this
month, last week, last month, custom

I want to be able to click on THISWEEK and have all the records for this
week to show up on my form.

It is a projects table where our employees log in the hours they have

spent
on a project. I need them to be able to see the data by the choices

above.

ALSO: how would I create a CUSTOM set of data? Click on CUSTOM, some

sort
of boxes appear where I could type in the 2 dates and everything between
those dates would show up on the form.

I've tried queries, but I'm just not getting it. I understand the logic
behind it, but just not how to get it done. I'm a novice Access user, so
please be gentle in your help and explainations !!

Thank you so much.
LisaB.



  #3  
Old November 18th, 2004, 12:29 AM
RusCat
external usenet poster
 
Posts: n/a
Default

Nikos,

I am completely flabbergasted at the detail of your reply. Thank you so
much for taking the time and effort to help me. I appreciate it very much.

To be honest, I don't understand all of it, but I'm going to begin trying to
dissect it this evening and see where I get.

I hope you will continue to be available for additional questions, which I
will undoubtedly have !!

Thank you again.
:-)
LisaB.

"Nikos Yannacopoulos" wrote:

Lisa,

To begin with, I would add two textboxes on the form (called, say, txtDateFm
and txtDateTo, formatted as date - same format as the date field in the
table), and set their Visible property to No in form design. These will be
used for holding the from and to dates, populated by the listbox (while
still invisible) on every other choice, and made visible to be filled
manually when custom is selected.

The query for the records would reference these two text boxes in its
criterion on the date field, like:

= Forms![Form Name]![txtDateFm] and = Forms![Form Name]![txtDateTo]


I would use the double click event of the listbox (assumed name List0) to
calculate the dates and requery the subform (assumed name: Subform1) with
the records. Of course, when custom is selected, the user has to enter the
dates and then somehow requery the subform. For this I would use a command
button (called Command2 in my sample code, caption something like Refresh or
Get Data, also hidden in the form design) which is toggled between visible
and invisible together with the textboxes.

Here's the code behind the Command2 button:

Private Sub Command2_Click()
Me.Subform1.Requery
End Sub

And, finally, here's the code behind the listbox's double click event:

Private Sub List0_DblClick(Cancel As Integer)
Select Case Me.List0
Case "Today"
Me.txtDateFm = Date
Me.txtDateTo = Date
Me.Subform1.Requery
Case "This Week"
dt = Date
If Weekday(dt, 3) = 7 Then dt = dt + 7
Me.txtDateFm = dt - Weekday(dt, 3)
Me.txtDateTo = dt - Weekday(dt, 3) + 6
Me.Subform1.Requery
Case "This Month"
Select Case Month(Date)
Case 12
y1 = Year(Date)
m1 = Month(Date)
y2 = y1 + 1
m2 = 1
Case Else
y1 = Year(Date)
m1 = Month(Date)
y2 = y1
m2 = m1 + 1
End Select
Me.txtDateFm = DateSerial(y1, m1, 1)
Me.txtDateTo = DateSerial(y2, m2, 1) - 1
Me.Subform1.Requery
Case "Last Week"
dt = Date
If Weekday(dt, 3) = 7 Then dt = dt + 7
Me.txtDateFm = dt - Weekday(dt, 3) - 7
Me.txtDateTo = dt - Weekday(dt, 3) - 1
Me.Subform1.Requery
Case "Last Month"
Select Case Month(Date)
Case 1
y1 = Year(Date) - 1
m1 = 12
y2 = y1 + 1
m2 = 1
Case Else
y1 = Year(Date)
m1 = Month(Date) - 1
y2 = y1
m2 = m1 + 1
End Select
Me.txtDateFm = DateSerial(y1, m1, 1)
Me.txtDateTo = DateSerial(y2, m2, 1) - 1
Me.Subform1.Requery
Case "Custom"
Me.txtDateFm = Null
Me.txtDateTo = Null
Me.txtDateFm.Visible = True
Me.txtDateTo.Visible = True
Me.Command2.Visible = True
GoTo skip_hide
End Select

Me.txtDateFm.Visible = False
Me.txtDateTo.Visible = False
Me.Command2.Visible = False

skip_hide:
End Sub

Don't forget to change the object names to your real names!

HTH,
Nikos

"RusCat" wrote in message
...
Good morning,

Here is something I'm tryng to do, but can't seem to get a handle on. I

am
hoping for some direction.

I have the following things listed in a listbox: today, this week, this
month, last week, last month, custom

I want to be able to click on THISWEEK and have all the records for this
week to show up on my form.

It is a projects table where our employees log in the hours they have

spent
on a project. I need them to be able to see the data by the choices

above.

ALSO: how would I create a CUSTOM set of data? Click on CUSTOM, some

sort
of boxes appear where I could type in the 2 dates and everything between
those dates would show up on the form.

I've tried queries, but I'm just not getting it. I understand the logic
behind it, but just not how to get it done. I'm a novice Access user, so
please be gentle in your help and explainations !!

Thank you so much.
LisaB.




  #4  
Old November 19th, 2004, 01:49 AM
RusCat
external usenet poster
 
Posts: n/a
Default

Good evening Nikos,

I just want you to know that my jaw is laying on the floor !! Your help and
code did EXACTLY what I was wanting to do. Thank you thank you thank you.

I didn't understand most of it, but I spent part of yesterday and most of
today figuring it out and it works GREAT !!! It was the query that gave me
the most trouble. But then again, I usually have the most trouble with
queries.

I appreciate you and the many others that frequent this board and help those
of us who are lost. I live in rural Maine with no opportunity to take a
class in Access, so this board has been a wonderful learning experience.

I'm going to see if I can incorporate a drop down calendar on the Custom
date from and to. I've researched it here and know that it can be done.

Any additional help you can offer will be most appreciated with regards to
the calendar.

Thank you again for sharing your skills and knowledge. It really means
something to me.
:-)
LisaB

"RusCat" wrote:

Nikos,

I am completely flabbergasted at the detail of your reply. Thank you so
much for taking the time and effort to help me. I appreciate it very much.

To be honest, I don't understand all of it, but I'm going to begin trying to
dissect it this evening and see where I get.

I hope you will continue to be available for additional questions, which I
will undoubtedly have !!

Thank you again.
:-)
LisaB.

"Nikos Yannacopoulos" wrote:

Lisa,

To begin with, I would add two textboxes on the form (called, say, txtDateFm
and txtDateTo, formatted as date - same format as the date field in the
table), and set their Visible property to No in form design. These will be
used for holding the from and to dates, populated by the listbox (while
still invisible) on every other choice, and made visible to be filled
manually when custom is selected.

The query for the records would reference these two text boxes in its
criterion on the date field, like:

= Forms![Form Name]![txtDateFm] and = Forms![Form Name]![txtDateTo]


I would use the double click event of the listbox (assumed name List0) to
calculate the dates and requery the subform (assumed name: Subform1) with
the records. Of course, when custom is selected, the user has to enter the
dates and then somehow requery the subform. For this I would use a command
button (called Command2 in my sample code, caption something like Refresh or
Get Data, also hidden in the form design) which is toggled between visible
and invisible together with the textboxes.

Here's the code behind the Command2 button:

Private Sub Command2_Click()
Me.Subform1.Requery
End Sub

And, finally, here's the code behind the listbox's double click event:

Private Sub List0_DblClick(Cancel As Integer)
Select Case Me.List0
Case "Today"
Me.txtDateFm = Date
Me.txtDateTo = Date
Me.Subform1.Requery
Case "This Week"
dt = Date
If Weekday(dt, 3) = 7 Then dt = dt + 7
Me.txtDateFm = dt - Weekday(dt, 3)
Me.txtDateTo = dt - Weekday(dt, 3) + 6
Me.Subform1.Requery
Case "This Month"
Select Case Month(Date)
Case 12
y1 = Year(Date)
m1 = Month(Date)
y2 = y1 + 1
m2 = 1
Case Else
y1 = Year(Date)
m1 = Month(Date)
y2 = y1
m2 = m1 + 1
End Select
Me.txtDateFm = DateSerial(y1, m1, 1)
Me.txtDateTo = DateSerial(y2, m2, 1) - 1
Me.Subform1.Requery
Case "Last Week"
dt = Date
If Weekday(dt, 3) = 7 Then dt = dt + 7
Me.txtDateFm = dt - Weekday(dt, 3) - 7
Me.txtDateTo = dt - Weekday(dt, 3) - 1
Me.Subform1.Requery
Case "Last Month"
Select Case Month(Date)
Case 1
y1 = Year(Date) - 1
m1 = 12
y2 = y1 + 1
m2 = 1
Case Else
y1 = Year(Date)
m1 = Month(Date) - 1
y2 = y1
m2 = m1 + 1
End Select
Me.txtDateFm = DateSerial(y1, m1, 1)
Me.txtDateTo = DateSerial(y2, m2, 1) - 1
Me.Subform1.Requery
Case "Custom"
Me.txtDateFm = Null
Me.txtDateTo = Null
Me.txtDateFm.Visible = True
Me.txtDateTo.Visible = True
Me.Command2.Visible = True
GoTo skip_hide
End Select

Me.txtDateFm.Visible = False
Me.txtDateTo.Visible = False
Me.Command2.Visible = False

skip_hide:
End Sub

Don't forget to change the object names to your real names!

HTH,
Nikos

"RusCat" wrote in message
...
Good morning,

Here is something I'm tryng to do, but can't seem to get a handle on. I

am
hoping for some direction.

I have the following things listed in a listbox: today, this week, this
month, last week, last month, custom

I want to be able to click on THISWEEK and have all the records for this
week to show up on my form.

It is a projects table where our employees log in the hours they have

spent
on a project. I need them to be able to see the data by the choices

above.

ALSO: how would I create a CUSTOM set of data? Click on CUSTOM, some

sort
of boxes appear where I could type in the 2 dates and everything between
those dates would show up on the form.

I've tried queries, but I'm just not getting it. I understand the logic
behind it, but just not how to get it done. I'm a novice Access user, so
please be gentle in your help and explainations !!

Thank you so much.
LisaB.




  #5  
Old November 19th, 2004, 08:46 AM
Nikos Yannacopoulos
external usenet poster
 
Posts: n/a
Default

Lisa,

Glad I could help. Not a big believer in classes, I'm self-taught in most
everything I do with a computer (and when I got my first PC back in '87 the
internet was practically unheard of!). Anyway...

On the drop-down calendar: there are two approaches:
1. You can use the Date and Time Picker active X control instead of
textboxes. You can set their value in code just like the textboxes, but you
can't set them to Null for Custom; they have to have a value, so you would
set them to, say, current date instead, as a basis for the user to start
from. Also, because the DTPicker has a time part as well, even though you
don't see it, you would have to use an Int() function on the control
references in the query criteria, so as to get "clean"dates, without the
hour part (date/time in Access is actually a number, 0 being Dec.31, 1899,
the integer part being the date, and the decimal part being the time).

2. You can leave the textboxes as they are and use their double-click event
to pop-up a separate form with a Calendar active X control on it, to select
a date and return it to the textbox on the main form, while the date can
still be typed in if desired. Also, the calendar control has no time part,
so no bneed to worry about that, and you can still Null the textboxes for
Custom. Incidentally, I answered a post on thi this very subject yesterday,
I have the "how-to" ready! Here it goes:

The calendar control is on a separate form, opened by the double-click (or
click?) event of the text boxes. I'll assume the following names in my
example, and you'll have to change to the real ones:

Form with calendar: frmCalendar
Calendar control: ActiveXCtl1

The code behind the double-click event of the text boxes must be something
like:

Private Sub txtDateFm_DblClick(Cancel As Integer)
ctrl = Me.Name & "*" & Me.ActiveControl.Name
DoCmd.OpenForm "frmCalendar", , , stLinkCriteria, , , ctrl
Forms!frmCalendar!ActiveXCtl1 = Date
End Sub

(same for txtDateTo)

This way, you are passing the form and control names (separated by a *) as
an opening argument to frmCalendar.

The code behind the click event of the calendar should be something like:

Private Sub ActiveXCtl1_Click()
sep = InStr(1, OpenArgs, "*")
frm = Left(OpenArgs, sep - 1)
ctl = Right(OpenArgs, Len(OpenArgs) - sep)
Forms(frm).Controls(ctl) = Me.ActiveXCtl1

End Sub

This will work with different forms as well!

If you go for option 2, play around with the properties on Format tab for
the form to get rid of record selector, navigation buttons, control box etc
wich are meaningless in this case. In either case, to put an ActiveX control
on a form, go Insert ActiveX control.

Good luck!
Nikos


"RusCat" wrote in message
...
Good evening Nikos,

I just want you to know that my jaw is laying on the floor !! Your help

and
code did EXACTLY what I was wanting to do. Thank you thank you thank you.

I didn't understand most of it, but I spent part of yesterday and most of
today figuring it out and it works GREAT !!! It was the query that gave

me
the most trouble. But then again, I usually have the most trouble with
queries.

I appreciate you and the many others that frequent this board and help

those
of us who are lost. I live in rural Maine with no opportunity to take a
class in Access, so this board has been a wonderful learning experience.

I'm going to see if I can incorporate a drop down calendar on the Custom
date from and to. I've researched it here and know that it can be done.

Any additional help you can offer will be most appreciated with regards to
the calendar.

Thank you again for sharing your skills and knowledge. It really means
something to me.
:-)
LisaB

"RusCat" wrote:

Nikos,

I am completely flabbergasted at the detail of your reply. Thank you so
much for taking the time and effort to help me. I appreciate it very

much.

To be honest, I don't understand all of it, but I'm going to begin

trying to
dissect it this evening and see where I get.

I hope you will continue to be available for additional questions, which

I
will undoubtedly have !!

Thank you again.
:-)
LisaB.

"Nikos Yannacopoulos" wrote:

Lisa,

To begin with, I would add two textboxes on the form (called, say,

txtDateFm
and txtDateTo, formatted as date - same format as the date field in

the
table), and set their Visible property to No in form design. These

will be
used for holding the from and to dates, populated by the listbox

(while
still invisible) on every other choice, and made visible to be filled
manually when custom is selected.

The query for the records would reference these two text boxes in its
criterion on the date field, like:

= Forms![Form Name]![txtDateFm] and = Forms![Form Name]![txtDateTo]

I would use the double click event of the listbox (assumed name List0)

to
calculate the dates and requery the subform (assumed name: Subform1)

with
the records. Of course, when custom is selected, the user has to enter

the
dates and then somehow requery the subform. For this I would use a

command
button (called Command2 in my sample code, caption something like

Refresh or
Get Data, also hidden in the form design) which is toggled between

visible
and invisible together with the textboxes.

Here's the code behind the Command2 button:

Private Sub Command2_Click()
Me.Subform1.Requery
End Sub

And, finally, here's the code behind the listbox's double click event:

Private Sub List0_DblClick(Cancel As Integer)
Select Case Me.List0
Case "Today"
Me.txtDateFm = Date
Me.txtDateTo = Date
Me.Subform1.Requery
Case "This Week"
dt = Date
If Weekday(dt, 3) = 7 Then dt = dt + 7
Me.txtDateFm = dt - Weekday(dt, 3)
Me.txtDateTo = dt - Weekday(dt, 3) + 6
Me.Subform1.Requery
Case "This Month"
Select Case Month(Date)
Case 12
y1 = Year(Date)
m1 = Month(Date)
y2 = y1 + 1
m2 = 1
Case Else
y1 = Year(Date)
m1 = Month(Date)
y2 = y1
m2 = m1 + 1
End Select
Me.txtDateFm = DateSerial(y1, m1, 1)
Me.txtDateTo = DateSerial(y2, m2, 1) - 1
Me.Subform1.Requery
Case "Last Week"
dt = Date
If Weekday(dt, 3) = 7 Then dt = dt + 7
Me.txtDateFm = dt - Weekday(dt, 3) - 7
Me.txtDateTo = dt - Weekday(dt, 3) - 1
Me.Subform1.Requery
Case "Last Month"
Select Case Month(Date)
Case 1
y1 = Year(Date) - 1
m1 = 12
y2 = y1 + 1
m2 = 1
Case Else
y1 = Year(Date)
m1 = Month(Date) - 1
y2 = y1
m2 = m1 + 1
End Select
Me.txtDateFm = DateSerial(y1, m1, 1)
Me.txtDateTo = DateSerial(y2, m2, 1) - 1
Me.Subform1.Requery
Case "Custom"
Me.txtDateFm = Null
Me.txtDateTo = Null
Me.txtDateFm.Visible = True
Me.txtDateTo.Visible = True
Me.Command2.Visible = True
GoTo skip_hide
End Select

Me.txtDateFm.Visible = False
Me.txtDateTo.Visible = False
Me.Command2.Visible = False

skip_hide:
End Sub

Don't forget to change the object names to your real names!

HTH,
Nikos

"RusCat" wrote in message
...
Good morning,

Here is something I'm tryng to do, but can't seem to get a handle

on. I
am
hoping for some direction.

I have the following things listed in a listbox: today, this week,

this
month, last week, last month, custom

I want to be able to click on THISWEEK and have all the records for

this
week to show up on my form.

It is a projects table where our employees log in the hours they

have
spent
on a project. I need them to be able to see the data by the choices
above.

ALSO: how would I create a CUSTOM set of data? Click on CUSTOM,

some
sort
of boxes appear where I could type in the 2 dates and everything

between
those dates would show up on the form.

I've tried queries, but I'm just not getting it. I understand the

logic
behind it, but just not how to get it done. I'm a novice Access

user, so
please be gentle in your help and explainations !!

Thank you so much.
LisaB.





  #6  
Old November 20th, 2004, 02:25 AM
RusCat
external usenet poster
 
Posts: n/a
Default

Wow Nikos !

I used to be self taught, years and years ago. I used to program in dbase
and in Clipper. Before the windows environment was around. I did some great
stuff and really enjoyed the 'puzzle' aspect of programming. Working to
figure out how to make something work. I just taught myself by trial and
error. THEN.... I had kids and my brain turned to mush!! Things have
changed ALOT in the last 10 or 12 years. I'm enjoying the challenge again
now that my youngest is in school all day.

This is helping me SO much. Not only is it solving a problem but it is
teaching me SO much. Your directions are clear and easy to follow.

What you explained is working well. I finally got everything in the right
place and it is doing what I wanted it to do !! Mostly.

I have some questions about the actual operations:

1. Does it matter whether the code is behind the Click or DoubleClick? One
click seems to work best for me, but if that is not the correct way to set
things up, then I'll stay with DClick.

2. Can the event for the Listbox also be just Click?

3. I did go with option 2 and played around with the format stuff - very
cool. Can I tell it somehow exactly where I want the calendar to appear?

4. When the calendar does appear, I click on a date (which appears in the
textbox), but the calendar does not disappear. How do I make it do that?

5. Can I put the exact same code in the OnEnter event for the Listbox?
That way you can select the item by clicking or entering?

6. I've done something wrong with the textboxes.... Click the first box,
calendar appears, click date, date goes to first textbox. Click second box,
calendar appears, click date, date ALSO goes to the first textbox. I checked
the code and I have the right names. Where might I be going wrong?

Well, 6 problems/questions is enough for right now. thanks again for the
help.
:-)
LisaB.
"Nikos Yannacopoulos" wrote:

Lisa,

Glad I could help. Not a big believer in classes, I'm self-taught in most
everything I do with a computer (and when I got my first PC back in '87 the
internet was practically unheard of!). Anyway...

On the drop-down calendar: there are two approaches:
1. You can use the Date and Time Picker active X control instead of
textboxes. You can set their value in code just like the textboxes, but you
can't set them to Null for Custom; they have to have a value, so you would
set them to, say, current date instead, as a basis for the user to start
from. Also, because the DTPicker has a time part as well, even though you
don't see it, you would have to use an Int() function on the control
references in the query criteria, so as to get "clean"dates, without the
hour part (date/time in Access is actually a number, 0 being Dec.31, 1899,
the integer part being the date, and the decimal part being the time).

2. You can leave the textboxes as they are and use their double-click event
to pop-up a separate form with a Calendar active X control on it, to select
a date and return it to the textbox on the main form, while the date can
still be typed in if desired. Also, the calendar control has no time part,
so no bneed to worry about that, and you can still Null the textboxes for
Custom. Incidentally, I answered a post on thi this very subject yesterday,
I have the "how-to" ready! Here it goes:

The calendar control is on a separate form, opened by the double-click (or
click?) event of the text boxes. I'll assume the following names in my
example, and you'll have to change to the real ones:

Form with calendar: frmCalendar
Calendar control: ActiveXCtl1

The code behind the double-click event of the text boxes must be something
like:

Private Sub txtDateFm_DblClick(Cancel As Integer)
ctrl = Me.Name & "*" & Me.ActiveControl.Name
DoCmd.OpenForm "frmCalendar", , , stLinkCriteria, , , ctrl
Forms!frmCalendar!ActiveXCtl1 = Date
End Sub

(same for txtDateTo)

This way, you are passing the form and control names (separated by a *) as
an opening argument to frmCalendar.

The code behind the click event of the calendar should be something like:

Private Sub ActiveXCtl1_Click()
sep = InStr(1, OpenArgs, "*")
frm = Left(OpenArgs, sep - 1)
ctl = Right(OpenArgs, Len(OpenArgs) - sep)
Forms(frm).Controls(ctl) = Me.ActiveXCtl1

End Sub

This will work with different forms as well!

If you go for option 2, play around with the properties on Format tab for
the form to get rid of record selector, navigation buttons, control box etc
wich are meaningless in this case. In either case, to put an ActiveX control
on a form, go Insert ActiveX control.

Good luck!
Nikos


"RusCat" wrote in message
...
Good evening Nikos,

I just want you to know that my jaw is laying on the floor !! Your help

and
code did EXACTLY what I was wanting to do. Thank you thank you thank you.

I didn't understand most of it, but I spent part of yesterday and most of
today figuring it out and it works GREAT !!! It was the query that gave

me
the most trouble. But then again, I usually have the most trouble with
queries.

I appreciate you and the many others that frequent this board and help

those
of us who are lost. I live in rural Maine with no opportunity to take a
class in Access, so this board has been a wonderful learning experience.

I'm going to see if I can incorporate a drop down calendar on the Custom
date from and to. I've researched it here and know that it can be done.

Any additional help you can offer will be most appreciated with regards to
the calendar.

Thank you again for sharing your skills and knowledge. It really means
something to me.
:-)
LisaB

"RusCat" wrote:

Nikos,

I am completely flabbergasted at the detail of your reply. Thank you so
much for taking the time and effort to help me. I appreciate it very

much.

To be honest, I don't understand all of it, but I'm going to begin

trying to
dissect it this evening and see where I get.

I hope you will continue to be available for additional questions, which

I
will undoubtedly have !!

Thank you again.
:-)
LisaB.

"Nikos Yannacopoulos" wrote:

Lisa,

To begin with, I would add two textboxes on the form (called, say,

txtDateFm
and txtDateTo, formatted as date - same format as the date field in

the
table), and set their Visible property to No in form design. These

will be
used for holding the from and to dates, populated by the listbox

(while
still invisible) on every other choice, and made visible to be filled
manually when custom is selected.

The query for the records would reference these two text boxes in its
criterion on the date field, like:

= Forms![Form Name]![txtDateFm] and = Forms![Form Name]![txtDateTo]

I would use the double click event of the listbox (assumed name List0)

to
calculate the dates and requery the subform (assumed name: Subform1)

with
the records. Of course, when custom is selected, the user has to enter

the
dates and then somehow requery the subform. For this I would use a

command
button (called Command2 in my sample code, caption something like

Refresh or
Get Data, also hidden in the form design) which is toggled between

visible
and invisible together with the textboxes.

Here's the code behind the Command2 button:

Private Sub Command2_Click()
Me.Subform1.Requery
End Sub

And, finally, here's the code behind the listbox's double click event:

Private Sub List0_DblClick(Cancel As Integer)
Select Case Me.List0
Case "Today"
Me.txtDateFm = Date
Me.txtDateTo = Date
Me.Subform1.Requery
Case "This Week"
dt = Date
If Weekday(dt, 3) = 7 Then dt = dt + 7
Me.txtDateFm = dt - Weekday(dt, 3)
Me.txtDateTo = dt - Weekday(dt, 3) + 6
Me.Subform1.Requery
Case "This Month"
Select Case Month(Date)
Case 12
y1 = Year(Date)
m1 = Month(Date)
y2 = y1 + 1
m2 = 1
Case Else
y1 = Year(Date)
m1 = Month(Date)
y2 = y1
m2 = m1 + 1
End Select
Me.txtDateFm = DateSerial(y1, m1, 1)
Me.txtDateTo = DateSerial(y2, m2, 1) - 1
Me.Subform1.Requery
Case "Last Week"
dt = Date
If Weekday(dt, 3) = 7 Then dt = dt + 7
Me.txtDateFm = dt - Weekday(dt, 3) - 7
Me.txtDateTo = dt - Weekday(dt, 3) - 1
Me.Subform1.Requery
Case "Last Month"
Select Case Month(Date)
Case 1
y1 = Year(Date) - 1
m1 = 12
y2 = y1 + 1
m2 = 1
Case Else
y1 = Year(Date)
m1 = Month(Date) - 1
y2 = y1
m2 = m1 + 1
End Select
Me.txtDateFm = DateSerial(y1, m1, 1)
Me.txtDateTo = DateSerial(y2, m2, 1) - 1
Me.Subform1.Requery
Case "Custom"
Me.txtDateFm = Null
Me.txtDateTo = Null
Me.txtDateFm.Visible = True
Me.txtDateTo.Visible = True
Me.Command2.Visible = True
GoTo skip_hide
End Select

Me.txtDateFm.Visible = False
Me.txtDateTo.Visible = False
Me.Command2.Visible = False

skip_hide:
End Sub

Don't forget to change the object names to your real names!

HTH,
Nikos

"RusCat" wrote in message
...
Good morning,

Here is something I'm tryng to do, but can't seem to get a handle

on. I
am
hoping for some direction.

I have the following things listed in a listbox: today, this week,

this
month, last week, last month, custom

I want to be able to click on THISWEEK and have all the records for

this
week to show up on my form.

It is a projects table where our employees log in the hours they

have
spent
on a project. I need them to be able to see the data by the choices
above.

ALSO: how would I create a CUSTOM set of data? Click on CUSTOM,

some
sort
of boxes appear where I could type in the 2 dates and everything

between
those dates would show up on the form.

I've tried queries, but I'm just not getting it. I understand the

logic
behind it, but just not how to get it done. I'm a novice Access

user, so
please be gentle in your help and explainations !!

Thank you so much.
LisaB.






  #7  
Old November 20th, 2004, 11:10 AM
Nikos Yannacopoulos
external usenet poster
 
Posts: n/a
Default

Lisa,

1. No, it doesn't matter. It's just a question of preference. I go for
double because double is deliberate, single can be accidental or intended
just to set focus.

2. Yes. Again, to me, double click is deliberate, single can be accidental
(aimed at the row above or below).

3. I take it you mean position the calendar form on the screen? Yes, you
can. The method is:
DoCmd.MoveSize 1440, 1440
The numbers correspond to the vertical and horizontal distance of the upper
lefthand corner from the upper lefthand corner of the application window,
and are in twips (1/1440 of an inch). The method has two more optional
arguments:
DoCmd.MoveSize [right][, down][, width][, height]
that set the form's size.
If you want the form always placed at the same position, you can put this
command in the calendar form's On Open event. If, on the other hand, you
want it placed depending on the calling txtbox, you can put something like:
Forms("frmCalendar").SetFocus
DoCmd.MoveSize 1440, 1440
after the DoCmd.OpenForm in the txtbox's event code. I use the SetFocus to
make sure the calendar form is the active one when the movesize is
executed, because by default it acts on the active window.

4. Add an extra line of code at the end of the procedu
Private Sub ActiveXCtl1_Click()
sep = InStr(1, OpenArgs, "*")
frm = Left(OpenArgs, sep - 1)
ctl = Right(OpenArgs, Len(OpenArgs) - sep)
Forms(frm).Controls(ctl) = Me.ActiveXCtl1
DoCmd.Close
End Sub

5. Yes, you can, but you will no longer be able to just enter and type in a
date. Again, a user interface decision.

6. I can think of: one possible reason: the form was already open from the
first textbox, because the calendar's click event didn't have the extra line
to close it, just hidden behind the main form when you manually set focus
back to the main one. If that's the case, when you double-click (or click)
the second textbox the calendar form reappears but it was already open, so
the OpenArgs are not passed again, thus still pointing to the From txtbox.
Had the calling textbox name been hardcoded in there I would suspect that,
but the way it is (retrieved by reference: Me.ActiveControl.Name) I don't
see how that could be, unless there's code preceding the snippet I gave you
that moves focus back to the From txtbox before the calendar form is opened;
very unlikely!

HTH,
Nikos


"RusCat" wrote in message
...
Wow Nikos !

I used to be self taught, years and years ago. I used to program in dbase
and in Clipper. Before the windows environment was around. I did some

great
stuff and really enjoyed the 'puzzle' aspect of programming. Working to
figure out how to make something work. I just taught myself by trial and
error. THEN.... I had kids and my brain turned to mush!! Things have
changed ALOT in the last 10 or 12 years. I'm enjoying the challenge again
now that my youngest is in school all day.

This is helping me SO much. Not only is it solving a problem but it is
teaching me SO much. Your directions are clear and easy to follow.

What you explained is working well. I finally got everything in the right
place and it is doing what I wanted it to do !! Mostly.

I have some questions about the actual operations:

1. Does it matter whether the code is behind the Click or DoubleClick?

One
click seems to work best for me, but if that is not the correct way to set
things up, then I'll stay with DClick.

2. Can the event for the Listbox also be just Click?

3. I did go with option 2 and played around with the format stuff - very
cool. Can I tell it somehow exactly where I want the calendar to appear?

4. When the calendar does appear, I click on a date (which appears in the
textbox), but the calendar does not disappear. How do I make it do that?

5. Can I put the exact same code in the OnEnter event for the Listbox?
That way you can select the item by clicking or entering?

6. I've done something wrong with the textboxes.... Click the first box,
calendar appears, click date, date goes to first textbox. Click second

box,
calendar appears, click date, date ALSO goes to the first textbox. I

checked
the code and I have the right names. Where might I be going wrong?

Well, 6 problems/questions is enough for right now. thanks again for the
help.
:-)
LisaB.
"Nikos Yannacopoulos" wrote:

Lisa,

Glad I could help. Not a big believer in classes, I'm self-taught in

most
everything I do with a computer (and when I got my first PC back in '87

the
internet was practically unheard of!). Anyway...

On the drop-down calendar: there are two approaches:
1. You can use the Date and Time Picker active X control instead of
textboxes. You can set their value in code just like the textboxes, but

you
can't set them to Null for Custom; they have to have a value, so you

would
set them to, say, current date instead, as a basis for the user to start
from. Also, because the DTPicker has a time part as well, even though

you
don't see it, you would have to use an Int() function on the control
references in the query criteria, so as to get "clean"dates, without the
hour part (date/time in Access is actually a number, 0 being Dec.31,

1899,
the integer part being the date, and the decimal part being the time).

2. You can leave the textboxes as they are and use their double-click

event
to pop-up a separate form with a Calendar active X control on it, to

select
a date and return it to the textbox on the main form, while the date can
still be typed in if desired. Also, the calendar control has no time

part,
so no bneed to worry about that, and you can still Null the textboxes

for
Custom. Incidentally, I answered a post on thi this very subject

yesterday,
I have the "how-to" ready! Here it goes:

The calendar control is on a separate form, opened by the double-click

(or
click?) event of the text boxes. I'll assume the following names in my
example, and you'll have to change to the real ones:

Form with calendar: frmCalendar
Calendar control: ActiveXCtl1

The code behind the double-click event of the text boxes must be

something
like:

Private Sub txtDateFm_DblClick(Cancel As Integer)
ctrl = Me.Name & "*" & Me.ActiveControl.Name
DoCmd.OpenForm "frmCalendar", , , stLinkCriteria, , , ctrl
Forms!frmCalendar!ActiveXCtl1 = Date
End Sub

(same for txtDateTo)

This way, you are passing the form and control names (separated by a *)

as
an opening argument to frmCalendar.

The code behind the click event of the calendar should be something

like:

Private Sub ActiveXCtl1_Click()
sep = InStr(1, OpenArgs, "*")
frm = Left(OpenArgs, sep - 1)
ctl = Right(OpenArgs, Len(OpenArgs) - sep)
Forms(frm).Controls(ctl) = Me.ActiveXCtl1

End Sub

This will work with different forms as well!

If you go for option 2, play around with the properties on Format tab

for
the form to get rid of record selector, navigation buttons, control box

etc
wich are meaningless in this case. In either case, to put an ActiveX

control
on a form, go Insert ActiveX control.

Good luck!
Nikos


"RusCat" wrote in message
...
Good evening Nikos,

I just want you to know that my jaw is laying on the floor !! Your

help
and
code did EXACTLY what I was wanting to do. Thank you thank you thank

you.

I didn't understand most of it, but I spent part of yesterday and most

of
today figuring it out and it works GREAT !!! It was the query that

gave
me
the most trouble. But then again, I usually have the most trouble

with
queries.

I appreciate you and the many others that frequent this board and help

those
of us who are lost. I live in rural Maine with no opportunity to take

a
class in Access, so this board has been a wonderful learning

experience.

I'm going to see if I can incorporate a drop down calendar on the

Custom
date from and to. I've researched it here and know that it can be

done.

Any additional help you can offer will be most appreciated with

regards to
the calendar.

Thank you again for sharing your skills and knowledge. It really

means
something to me.
:-)
LisaB

"RusCat" wrote:

Nikos,

I am completely flabbergasted at the detail of your reply. Thank

you so
much for taking the time and effort to help me. I appreciate it

very
much.

To be honest, I don't understand all of it, but I'm going to begin

trying to
dissect it this evening and see where I get.

I hope you will continue to be available for additional questions,

which
I
will undoubtedly have !!

Thank you again.
:-)
LisaB.

"Nikos Yannacopoulos" wrote:

Lisa,

To begin with, I would add two textboxes on the form (called, say,

txtDateFm
and txtDateTo, formatted as date - same format as the date field

in
the
table), and set their Visible property to No in form design. These

will be
used for holding the from and to dates, populated by the listbox

(while
still invisible) on every other choice, and made visible to be

filled
manually when custom is selected.

The query for the records would reference these two text boxes in

its
criterion on the date field, like:

= Forms![Form Name]![txtDateFm] and = Forms![Form

Name]![txtDateTo]

I would use the double click event of the listbox (assumed name

List0)
to
calculate the dates and requery the subform (assumed name:

Subform1)
with
the records. Of course, when custom is selected, the user has to

enter
the
dates and then somehow requery the subform. For this I would use a

command
button (called Command2 in my sample code, caption something like

Refresh or
Get Data, also hidden in the form design) which is toggled between

visible
and invisible together with the textboxes.

Here's the code behind the Command2 button:

Private Sub Command2_Click()
Me.Subform1.Requery
End Sub

And, finally, here's the code behind the listbox's double click

event:

Private Sub List0_DblClick(Cancel As Integer)
Select Case Me.List0
Case "Today"
Me.txtDateFm = Date
Me.txtDateTo = Date
Me.Subform1.Requery
Case "This Week"
dt = Date
If Weekday(dt, 3) = 7 Then dt = dt + 7
Me.txtDateFm = dt - Weekday(dt, 3)
Me.txtDateTo = dt - Weekday(dt, 3) + 6
Me.Subform1.Requery
Case "This Month"
Select Case Month(Date)
Case 12
y1 = Year(Date)
m1 = Month(Date)
y2 = y1 + 1
m2 = 1
Case Else
y1 = Year(Date)
m1 = Month(Date)
y2 = y1
m2 = m1 + 1
End Select
Me.txtDateFm = DateSerial(y1, m1, 1)
Me.txtDateTo = DateSerial(y2, m2, 1) - 1
Me.Subform1.Requery
Case "Last Week"
dt = Date
If Weekday(dt, 3) = 7 Then dt = dt + 7
Me.txtDateFm = dt - Weekday(dt, 3) - 7
Me.txtDateTo = dt - Weekday(dt, 3) - 1
Me.Subform1.Requery
Case "Last Month"
Select Case Month(Date)
Case 1
y1 = Year(Date) - 1
m1 = 12
y2 = y1 + 1
m2 = 1
Case Else
y1 = Year(Date)
m1 = Month(Date) - 1
y2 = y1
m2 = m1 + 1
End Select
Me.txtDateFm = DateSerial(y1, m1, 1)
Me.txtDateTo = DateSerial(y2, m2, 1) - 1
Me.Subform1.Requery
Case "Custom"
Me.txtDateFm = Null
Me.txtDateTo = Null
Me.txtDateFm.Visible = True
Me.txtDateTo.Visible = True
Me.Command2.Visible = True
GoTo skip_hide
End Select

Me.txtDateFm.Visible = False
Me.txtDateTo.Visible = False
Me.Command2.Visible = False

skip_hide:
End Sub

Don't forget to change the object names to your real names!

HTH,
Nikos

"RusCat" wrote in message
...
Good morning,

Here is something I'm tryng to do, but can't seem to get a

handle
on. I
am
hoping for some direction.

I have the following things listed in a listbox: today, this

week,
this
month, last week, last month, custom

I want to be able to click on THISWEEK and have all the records

for
this
week to show up on my form.

It is a projects table where our employees log in the hours they

have
spent
on a project. I need them to be able to see the data by the

choices
above.

ALSO: how would I create a CUSTOM set of data? Click on

CUSTOM,
some
sort
of boxes appear where I could type in the 2 dates and everything

between
those dates would show up on the form.

I've tried queries, but I'm just not getting it. I understand

the
logic
behind it, but just not how to get it done. I'm a novice Access

user, so
please be gentle in your help and explainations !!

Thank you so much.
LisaB.








  #8  
Old November 21st, 2004, 05:27 AM
RusCat
external usenet poster
 
Posts: n/a
Default

Good evening Nikos,

I hope you are having a nice weekend. The weather here in Maine was great
today, but they are forecasting snow for tomorrow! I do love winter.

Your answers to my questions were, as always, very helpful. I'm really
beginning to get the hang of what stuff means and even the whys.

With regards to question #3 and your answer. I thought I understood what
you wrote, but I can't get anything to work.

I did mean the position of the calendar, but within the form, not the
screen. You said "if you want it placed depending on the calling txtbox, you
can put something like :
Forms("frmCalendar").Set forcus
DoCmd.moveSize 1440, 1440
After the DoCmd.OpenForm in teh txtbox's event code."

Can you clarify for me exactly where to put it.

I would like the calendar to be "attached" or "anchored" to the txtbox. If
I move the form around the calendar stays stationary. Does that make sense?
Is that possible?

Also, something related back to the listbox: How can I make the "list" drop
down below the box when I move my curser there. Now the list just scrolls in
the box I made, but I see other lists that drop down and then the user can
click on their selection.

Thanks again for the great direction. What I have learned from you with
this form is going to help me with alot of other projects. I really
appreciate the time you take to post on the board.

:-)
LisaB.


"Nikos Yannacopoulos" wrote:

Lisa,

1. No, it doesn't matter. It's just a question of preference. I go for
double because double is deliberate, single can be accidental or intended
just to set focus.

2. Yes. Again, to me, double click is deliberate, single can be accidental
(aimed at the row above or below).

3. I take it you mean position the calendar form on the screen? Yes, you
can. The method is:
DoCmd.MoveSize 1440, 1440
The numbers correspond to the vertical and horizontal distance of the upper
lefthand corner from the upper lefthand corner of the application window,
and are in twips (1/1440 of an inch). The method has two more optional
arguments:
DoCmd.MoveSize [right][, down][, width][, height]
that set the form's size.
If you want the form always placed at the same position, you can put this
command in the calendar form's On Open event. If, on the other hand, you
want it placed depending on the calling txtbox, you can put something like:
Forms("frmCalendar").SetFocus
DoCmd.MoveSize 1440, 1440
after the DoCmd.OpenForm in the txtbox's event code. I use the SetFocus to
make sure the calendar form is the active one when the movesize is
executed, because by default it acts on the active window.

4. Add an extra line of code at the end of the procedu
Private Sub ActiveXCtl1_Click()
sep = InStr(1, OpenArgs, "*")
frm = Left(OpenArgs, sep - 1)
ctl = Right(OpenArgs, Len(OpenArgs) - sep)
Forms(frm).Controls(ctl) = Me.ActiveXCtl1
DoCmd.Close
End Sub

5. Yes, you can, but you will no longer be able to just enter and type in a
date. Again, a user interface decision.

6. I can think of: one possible reason: the form was already open from the
first textbox, because the calendar's click event didn't have the extra line
to close it, just hidden behind the main form when you manually set focus
back to the main one. If that's the case, when you double-click (or click)
the second textbox the calendar form reappears but it was already open, so
the OpenArgs are not passed again, thus still pointing to the From txtbox.
Had the calling textbox name been hardcoded in there I would suspect that,
but the way it is (retrieved by reference: Me.ActiveControl.Name) I don't
see how that could be, unless there's code preceding the snippet I gave you
that moves focus back to the From txtbox before the calendar form is opened;
very unlikely!

HTH,
Nikos


"RusCat" wrote in message
...
Wow Nikos !

I used to be self taught, years and years ago. I used to program in dbase
and in Clipper. Before the windows environment was around. I did some

great
stuff and really enjoyed the 'puzzle' aspect of programming. Working to
figure out how to make something work. I just taught myself by trial and
error. THEN.... I had kids and my brain turned to mush!! Things have
changed ALOT in the last 10 or 12 years. I'm enjoying the challenge again
now that my youngest is in school all day.

This is helping me SO much. Not only is it solving a problem but it is
teaching me SO much. Your directions are clear and easy to follow.

What you explained is working well. I finally got everything in the right
place and it is doing what I wanted it to do !! Mostly.

I have some questions about the actual operations:

1. Does it matter whether the code is behind the Click or DoubleClick?

One
click seems to work best for me, but if that is not the correct way to set
things up, then I'll stay with DClick.

2. Can the event for the Listbox also be just Click?

3. I did go with option 2 and played around with the format stuff - very
cool. Can I tell it somehow exactly where I want the calendar to appear?

4. When the calendar does appear, I click on a date (which appears in the
textbox), but the calendar does not disappear. How do I make it do that?

5. Can I put the exact same code in the OnEnter event for the Listbox?
That way you can select the item by clicking or entering?

6. I've done something wrong with the textboxes.... Click the first box,
calendar appears, click date, date goes to first textbox. Click second

box,
calendar appears, click date, date ALSO goes to the first textbox. I

checked
the code and I have the right names. Where might I be going wrong?

Well, 6 problems/questions is enough for right now. thanks again for the
help.
:-)
LisaB.
"Nikos Yannacopoulos" wrote:

Lisa,

Glad I could help. Not a big believer in classes, I'm self-taught in

most
everything I do with a computer (and when I got my first PC back in '87

the
internet was practically unheard of!). Anyway...

On the drop-down calendar: there are two approaches:
1. You can use the Date and Time Picker active X control instead of
textboxes. You can set their value in code just like the textboxes, but

you
can't set them to Null for Custom; they have to have a value, so you

would
set them to, say, current date instead, as a basis for the user to start
from. Also, because the DTPicker has a time part as well, even though

you
don't see it, you would have to use an Int() function on the control
references in the query criteria, so as to get "clean"dates, without the
hour part (date/time in Access is actually a number, 0 being Dec.31,

1899,
the integer part being the date, and the decimal part being the time).

2. You can leave the textboxes as they are and use their double-click

event
to pop-up a separate form with a Calendar active X control on it, to

select
a date and return it to the textbox on the main form, while the date can
still be typed in if desired. Also, the calendar control has no time

part,
so no bneed to worry about that, and you can still Null the textboxes

for
Custom. Incidentally, I answered a post on thi this very subject

yesterday,
I have the "how-to" ready! Here it goes:

The calendar control is on a separate form, opened by the double-click

(or
click?) event of the text boxes. I'll assume the following names in my
example, and you'll have to change to the real ones:

Form with calendar: frmCalendar
Calendar control: ActiveXCtl1

The code behind the double-click event of the text boxes must be

something
like:

Private Sub txtDateFm_DblClick(Cancel As Integer)
ctrl = Me.Name & "*" & Me.ActiveControl.Name
DoCmd.OpenForm "frmCalendar", , , stLinkCriteria, , , ctrl
Forms!frmCalendar!ActiveXCtl1 = Date
End Sub

(same for txtDateTo)

This way, you are passing the form and control names (separated by a *)

as
an opening argument to frmCalendar.

The code behind the click event of the calendar should be something

like:

Private Sub ActiveXCtl1_Click()
sep = InStr(1, OpenArgs, "*")
frm = Left(OpenArgs, sep - 1)
ctl = Right(OpenArgs, Len(OpenArgs) - sep)
Forms(frm).Controls(ctl) = Me.ActiveXCtl1

End Sub

This will work with different forms as well!

If you go for option 2, play around with the properties on Format tab

for
the form to get rid of record selector, navigation buttons, control box

etc
wich are meaningless in this case. In either case, to put an ActiveX

control
on a form, go Insert ActiveX control.

Good luck!
Nikos


"RusCat" wrote in message
...
Good evening Nikos,

I just want you to know that my jaw is laying on the floor !! Your

help
and
code did EXACTLY what I was wanting to do. Thank you thank you thank

you.

I didn't understand most of it, but I spent part of yesterday and most

of
today figuring it out and it works GREAT !!! It was the query that

gave
me
the most trouble. But then again, I usually have the most trouble

with
queries.

I appreciate you and the many others that frequent this board and help
those
of us who are lost. I live in rural Maine with no opportunity to take

a
class in Access, so this board has been a wonderful learning

experience.

I'm going to see if I can incorporate a drop down calendar on the

Custom
date from and to. I've researched it here and know that it can be

done.

Any additional help you can offer will be most appreciated with

regards to
the calendar.

Thank you again for sharing your skills and knowledge. It really

means
something to me.
:-)
LisaB

"RusCat" wrote:

Nikos,

I am completely flabbergasted at the detail of your reply. Thank

you so
much for taking the time and effort to help me. I appreciate it

very
much.

To be honest, I don't understand all of it, but I'm going to begin
trying to
dissect it this evening and see where I get.

I hope you will continue to be available for additional questions,

which
I
will undoubtedly have !!

Thank you again.
:-)
LisaB.

"Nikos Yannacopoulos" wrote:

Lisa,

To begin with, I would add two textboxes on the form (called, say,
txtDateFm
and txtDateTo, formatted as date - same format as the date field

in
the
table), and set their Visible property to No in form design. These
will be
used for holding the from and to dates, populated by the listbox
(while
still invisible) on every other choice, and made visible to be

filled
manually when custom is selected.

The query for the records would reference these two text boxes in

its
criterion on the date field, like:

= Forms![Form Name]![txtDateFm] and = Forms![Form

Name]![txtDateTo]

I would use the double click event of the listbox (assumed name

List0)
to
calculate the dates and requery the subform (assumed name:

Subform1)
with
the records. Of course, when custom is selected, the user has to

enter
the
dates and then somehow requery the subform. For this I would use a
command
button (called Command2 in my sample code, caption something like
Refresh or
Get Data, also hidden in the form design) which is toggled between
visible
and invisible together with the textboxes.

Here's the code behind the Command2 button:

Private Sub Command2_Click()
Me.Subform1.Requery
End Sub

And, finally, here's the code behind the listbox's double click

event:

Private Sub List0_DblClick(Cancel As Integer)
Select Case Me.List0
Case "Today"
Me.txtDateFm = Date
Me.txtDateTo = Date
Me.Subform1.Requery
Case "This Week"
dt = Date
If Weekday(dt, 3) = 7 Then dt = dt + 7
Me.txtDateFm = dt - Weekday(dt, 3)
Me.txtDateTo = dt - Weekday(dt, 3) + 6
Me.Subform1.Requery
Case "This Month"
Select Case Month(Date)
Case 12
y1 = Year(Date)
m1 = Month(Date)
y2 = y1 + 1
m2 = 1
Case Else
y1 = Year(Date)
m1 = Month(Date)
y2 = y1
m2 = m1 + 1
End Select
Me.txtDateFm = DateSerial(y1, m1, 1)
Me.txtDateTo = DateSerial(y2, m2, 1) - 1
Me.Subform1.Requery
Case "Last Week"
dt = Date
If Weekday(dt, 3) = 7 Then dt = dt + 7
Me.txtDateFm = dt - Weekday(dt, 3) - 7
Me.txtDateTo = dt - Weekday(dt, 3) - 1
Me.Subform1.Requery
Case "Last Month"
Select Case Month(Date)
Case 1
y1 = Year(Date) - 1
m1 = 12
y2 = y1 + 1
m2 = 1
Case Else
y1 = Year(Date)
m1 = Month(Date) - 1
y2 = y1
m2 = m1 + 1
End Select
Me.txtDateFm = DateSerial(y1, m1, 1)
Me.txtDateTo = DateSerial(y2, m2, 1) - 1
Me.Subform1.Requery
Case "Custom"
Me.txtDateFm = Null
Me.txtDateTo = Null
Me.txtDateFm.Visible = True
Me.txtDateTo.Visible = True
Me.Command2.Visible = True
GoTo skip_hide
End Select

Me.txtDateFm.Visible = False
Me.txtDateTo.Visible = False
Me.Command2.Visible = False

skip_hide:
End Sub

Don't forget to change the object names to your real names!

HTH,
Nikos

"RusCat" wrote in message
...
Good morning,

Here is something I'm tryng to do, but can't seem to get a

handle
on. I
am
hoping for some direction.

I have the following things listed in a listbox: today, this

week,
this
month, last week, last month, custom

I want to be able to click on THISWEEK and have all the records

for
this
week to show up on my form.

It is a projects table where our employees log in the hours they
have
spent
on a project. I need them to be able to see the data by the

choices
above.

ALSO: how would I create a CUSTOM set of data? Click on

CUSTOM,
some
sort
of boxes appear where I could type in the 2 dates and everything
between
those dates would show up on the form.

I've tried queries, but I'm just not getting it. I understand

the
logic
behind it, but just not how to get it done. I'm a novice Access
user, so
please be gentle in your help and explainations !!

Thank you so much.
LisaB.









  #9  
Old November 22nd, 2004, 09:54 AM
Nikos Yannacopoulos
external usenet poster
 
Posts: n/a
Default

Lisa,

The easy part first: to get the drop-down behaviour, you need to use a combo
box instead of a listbox. Open the form in design view and right-click on
the listbox, select Change To Combobox, and that's all there is to it! Its
name will remain unchanged, possibly connotating to a list, but it will work
just fine all the same, if you don't want to to bother to change it... and
so will the code behind the event. In a combo's case, of course, the single
click makes a lot more sense that the double.

Now, on the calendar position... what I had in mind was a separate form,
frmCalendar, with only tghe calenmdar control on it, which(the form) would
open on top of the main one, and I thought the idea was to move the whole
form with the calendar so it anchors to the txtbox. Your description is like
you have put just a calendar control on the same form? I'll try to address
both:

1. Calendar on separate form:
The idea is that frmCalendar is hardly any bigger than the control itself,
and its sole purpose is to host the latter; consequently, it is moved all
together in order to position the calendar. In this case, the form is moved
right after it is opened, from the click event of the txtbox on the main
form:

Private Sub txtDateFm_Click()
ctrl = Me.Name & "*" & Me.ActiveControl.Name
DoCmd.OpenForm "frmCalendar", , , stLinkCriteria, , , ctrl
Forms!frmCalendar!ActiveXCtl1 = Date
Forms("frmCalendar").SetFocus
DoCmd.MoveSize 1440, 1440 'or whatever
End Sub

Likewise for the other textbox.

2. Calendar on the same form:
This is a different approach. The idae is that you put the control on the
same form (it doesn't matter where), and set its Visible property to No.
Then when you need it you position it as required and make it appear,
possibly on top of other controls, hiding them. When its job is done, you
just hide it again.
This would result in slight changes to the code, since there is no separate
form involved now:

Option Compare Database
Public caller As String

Private Sub ActiveXCtl1_Click()
Me.Controls(caller) = Me.ActiveXCtl1
Me.Controls(caller).SetFocus
Me.ActiveXCtl1.Visible = False
End Sub

Private Sub txtDateFm_Click()
caller = Me.ActiveControl.Name
Me.ActiveXCtl1 = Date
Me.ActiveXCtl1.Top = Me.txtDateFm.Top + Me.txtDateFm.Height + 15
Me.ActiveXCtl1.Left = Me.txtDateFm.Left
Me.ActiveXCtl1.Visible = True
End Sub

Private Sub txtDateTo_Click()
caller = Me.ActiveControl.Name
Me.ActiveXCtl1 = Date
Me.ActiveXCtl1.Top = Me.txtDateTo.Top + Me.txtDateTo.Height + 15
Me.ActiveXCtl1.Left = Me.txtDateTo.Left
Me.ActiveXCtl1.Visible = True
End Sub

Note that I am using a Public variable to store the name of the control
calling the calendar, so it knows which control to return its selected date
to.
The code behind the listbox/combo click event and the command button remains
unchanged.

The second approach allows you to easily anchor the clendar to the calling
control, regardless of the form's current position. The first appoach works
well if the main form is maximised, so the clling txtboxes' position is
fixed, but can't follow them around otherwise - I haven't been able to
figure out how to "read" a form's position in the Access app window. The
advantage of the former approach, on the other hand, is one calendar form
can support any number of forms using it.

Hope this is clear now. Good luck,
Nikos

"RusCat" wrote in message
...
Good evening Nikos,

I hope you are having a nice weekend. The weather here in Maine was great
today, but they are forecasting snow for tomorrow! I do love winter.

Your answers to my questions were, as always, very helpful. I'm really
beginning to get the hang of what stuff means and even the whys.

With regards to question #3 and your answer. I thought I understood what
you wrote, but I can't get anything to work.

I did mean the position of the calendar, but within the form, not the
screen. You said "if you want it placed depending on the calling txtbox,

you
can put something like :
Forms("frmCalendar").Set forcus
DoCmd.moveSize 1440, 1440
After the DoCmd.OpenForm in teh txtbox's event code."

Can you clarify for me exactly where to put it.

I would like the calendar to be "attached" or "anchored" to the txtbox.

If
I move the form around the calendar stays stationary. Does that make

sense?
Is that possible?

Also, something related back to the listbox: How can I make the "list"

drop
down below the box when I move my curser there. Now the list just scrolls

in
the box I made, but I see other lists that drop down and then the user can
click on their selection.

Thanks again for the great direction. What I have learned from you with
this form is going to help me with alot of other projects. I really
appreciate the time you take to post on the board.

:-)
LisaB.


"Nikos Yannacopoulos" wrote:

Lisa,

1. No, it doesn't matter. It's just a question of preference. I go for
double because double is deliberate, single can be accidental or

intended
just to set focus.

2. Yes. Again, to me, double click is deliberate, single can be

accidental
(aimed at the row above or below).

3. I take it you mean position the calendar form on the screen? Yes, you
can. The method is:
DoCmd.MoveSize 1440, 1440
The numbers correspond to the vertical and horizontal distance of the

upper
lefthand corner from the upper lefthand corner of the application

window,
and are in twips (1/1440 of an inch). The method has two more optional
arguments:
DoCmd.MoveSize [right][, down][, width][, height]
that set the form's size.
If you want the form always placed at the same position, you can put

this
command in the calendar form's On Open event. If, on the other hand, you
want it placed depending on the calling txtbox, you can put something

like:
Forms("frmCalendar").SetFocus
DoCmd.MoveSize 1440, 1440
after the DoCmd.OpenForm in the txtbox's event code. I use the SetFocus

to
make sure the calendar form is the active one when the movesize is
executed, because by default it acts on the active window.

4. Add an extra line of code at the end of the procedu
Private Sub ActiveXCtl1_Click()
sep = InStr(1, OpenArgs, "*")
frm = Left(OpenArgs, sep - 1)
ctl = Right(OpenArgs, Len(OpenArgs) - sep)
Forms(frm).Controls(ctl) = Me.ActiveXCtl1
DoCmd.Close
End Sub

5. Yes, you can, but you will no longer be able to just enter and type

in a
date. Again, a user interface decision.

6. I can think of: one possible reason: the form was already open from

the
first textbox, because the calendar's click event didn't have the extra

line
to close it, just hidden behind the main form when you manually set

focus
back to the main one. If that's the case, when you double-click (or

click)
the second textbox the calendar form reappears but it was already open,

so
the OpenArgs are not passed again, thus still pointing to the From

txtbox.
Had the calling textbox name been hardcoded in there I would suspect

that,
but the way it is (retrieved by reference: Me.ActiveControl.Name) I

don't
see how that could be, unless there's code preceding the snippet I gave

you
that moves focus back to the From txtbox before the calendar form is

opened;
very unlikely!

HTH,
Nikos


"RusCat" wrote in message
...
Wow Nikos !

I used to be self taught, years and years ago. I used to program in

dbase
and in Clipper. Before the windows environment was around. I did

some
great
stuff and really enjoyed the 'puzzle' aspect of programming. Working

to
figure out how to make something work. I just taught myself by trial

and
error. THEN.... I had kids and my brain turned to mush!! Things have
changed ALOT in the last 10 or 12 years. I'm enjoying the challenge

again
now that my youngest is in school all day.

This is helping me SO much. Not only is it solving a problem but it

is
teaching me SO much. Your directions are clear and easy to follow.

What you explained is working well. I finally got everything in the

right
place and it is doing what I wanted it to do !! Mostly.

I have some questions about the actual operations:

1. Does it matter whether the code is behind the Click or

DoubleClick?
One
click seems to work best for me, but if that is not the correct way to

set
things up, then I'll stay with DClick.

2. Can the event for the Listbox also be just Click?

3. I did go with option 2 and played around with the format stuff -

very
cool. Can I tell it somehow exactly where I want the calendar to

appear?

4. When the calendar does appear, I click on a date (which appears in

the
textbox), but the calendar does not disappear. How do I make it do

that?

5. Can I put the exact same code in the OnEnter event for the

Listbox?
That way you can select the item by clicking or entering?

6. I've done something wrong with the textboxes.... Click the first

box,
calendar appears, click date, date goes to first textbox. Click

second
box,
calendar appears, click date, date ALSO goes to the first textbox. I

checked
the code and I have the right names. Where might I be going wrong?

Well, 6 problems/questions is enough for right now. thanks again for

the
help.
:-)
LisaB.
"Nikos Yannacopoulos" wrote:

Lisa,

Glad I could help. Not a big believer in classes, I'm self-taught in

most
everything I do with a computer (and when I got my first PC back in

'87
the
internet was practically unheard of!). Anyway...

On the drop-down calendar: there are two approaches:
1. You can use the Date and Time Picker active X control instead of
textboxes. You can set their value in code just like the textboxes,

but
you
can't set them to Null for Custom; they have to have a value, so you

would
set them to, say, current date instead, as a basis for the user to

start
from. Also, because the DTPicker has a time part as well, even

though
you
don't see it, you would have to use an Int() function on the control
references in the query criteria, so as to get "clean"dates, without

the
hour part (date/time in Access is actually a number, 0 being Dec.31,

1899,
the integer part being the date, and the decimal part being the

time).

2. You can leave the textboxes as they are and use their

double-click
event
to pop-up a separate form with a Calendar active X control on it, to

select
a date and return it to the textbox on the main form, while the date

can
still be typed in if desired. Also, the calendar control has no time

part,
so no bneed to worry about that, and you can still Null the

textboxes
for
Custom. Incidentally, I answered a post on thi this very subject

yesterday,
I have the "how-to" ready! Here it goes:

The calendar control is on a separate form, opened by the

double-click
(or
click?) event of the text boxes. I'll assume the following names in

my
example, and you'll have to change to the real ones:

Form with calendar: frmCalendar
Calendar control: ActiveXCtl1

The code behind the double-click event of the text boxes must be

something
like:

Private Sub txtDateFm_DblClick(Cancel As Integer)
ctrl = Me.Name & "*" & Me.ActiveControl.Name
DoCmd.OpenForm "frmCalendar", , , stLinkCriteria, , , ctrl
Forms!frmCalendar!ActiveXCtl1 = Date
End Sub

(same for txtDateTo)

This way, you are passing the form and control names (separated by a

*)
as
an opening argument to frmCalendar.

The code behind the click event of the calendar should be something

like:

Private Sub ActiveXCtl1_Click()
sep = InStr(1, OpenArgs, "*")
frm = Left(OpenArgs, sep - 1)
ctl = Right(OpenArgs, Len(OpenArgs) - sep)
Forms(frm).Controls(ctl) = Me.ActiveXCtl1

End Sub

This will work with different forms as well!

If you go for option 2, play around with the properties on Format

tab
for
the form to get rid of record selector, navigation buttons, control

box
etc
wich are meaningless in this case. In either case, to put an ActiveX

control
on a form, go Insert ActiveX control.

Good luck!
Nikos


"RusCat" wrote in message
...
Good evening Nikos,

I just want you to know that my jaw is laying on the floor !!

Your
help
and
code did EXACTLY what I was wanting to do. Thank you thank you

thank
you.

I didn't understand most of it, but I spent part of yesterday and

most
of
today figuring it out and it works GREAT !!! It was the query

that
gave
me
the most trouble. But then again, I usually have the most trouble

with
queries.

I appreciate you and the many others that frequent this board and

help
those
of us who are lost. I live in rural Maine with no opportunity to

take
a
class in Access, so this board has been a wonderful learning

experience.

I'm going to see if I can incorporate a drop down calendar on the

Custom
date from and to. I've researched it here and know that it can be

done.

Any additional help you can offer will be most appreciated with

regards to
the calendar.

Thank you again for sharing your skills and knowledge. It really

means
something to me.
:-)
LisaB

"RusCat" wrote:

Nikos,

I am completely flabbergasted at the detail of your reply.

Thank
you so
much for taking the time and effort to help me. I appreciate it

very
much.

To be honest, I don't understand all of it, but I'm going to

begin
trying to
dissect it this evening and see where I get.

I hope you will continue to be available for additional

questions,
which
I
will undoubtedly have !!

Thank you again.
:-)
LisaB.

"Nikos Yannacopoulos" wrote:

Lisa,

To begin with, I would add two textboxes on the form (called,

say,
txtDateFm
and txtDateTo, formatted as date - same format as the date

field
in
the
table), and set their Visible property to No in form design.

These
will be
used for holding the from and to dates, populated by the

listbox
(while
still invisible) on every other choice, and made visible to be

filled
manually when custom is selected.

The query for the records would reference these two text boxes

in
its
criterion on the date field, like:

= Forms![Form Name]![txtDateFm] and = Forms![Form

Name]![txtDateTo]

I would use the double click event of the listbox (assumed

name
List0)
to
calculate the dates and requery the subform (assumed name:

Subform1)
with
the records. Of course, when custom is selected, the user has

to
enter
the
dates and then somehow requery the subform. For this I would

use a
command
button (called Command2 in my sample code, caption something

like
Refresh or
Get Data, also hidden in the form design) which is toggled

between
visible
and invisible together with the textboxes.

Here's the code behind the Command2 button:

Private Sub Command2_Click()
Me.Subform1.Requery
End Sub

And, finally, here's the code behind the listbox's double

click
event:

Private Sub List0_DblClick(Cancel As Integer)
Select Case Me.List0
Case "Today"
Me.txtDateFm = Date
Me.txtDateTo = Date
Me.Subform1.Requery
Case "This Week"
dt = Date
If Weekday(dt, 3) = 7 Then dt = dt + 7
Me.txtDateFm = dt - Weekday(dt, 3)
Me.txtDateTo = dt - Weekday(dt, 3) + 6
Me.Subform1.Requery
Case "This Month"
Select Case Month(Date)
Case 12
y1 = Year(Date)
m1 = Month(Date)
y2 = y1 + 1
m2 = 1
Case Else
y1 = Year(Date)
m1 = Month(Date)
y2 = y1
m2 = m1 + 1
End Select
Me.txtDateFm = DateSerial(y1, m1, 1)
Me.txtDateTo = DateSerial(y2, m2, 1) - 1
Me.Subform1.Requery
Case "Last Week"
dt = Date
If Weekday(dt, 3) = 7 Then dt = dt + 7
Me.txtDateFm = dt - Weekday(dt, 3) - 7
Me.txtDateTo = dt - Weekday(dt, 3) - 1
Me.Subform1.Requery
Case "Last Month"
Select Case Month(Date)
Case 1
y1 = Year(Date) - 1
m1 = 12
y2 = y1 + 1
m2 = 1
Case Else
y1 = Year(Date)
m1 = Month(Date) - 1
y2 = y1
m2 = m1 + 1
End Select
Me.txtDateFm = DateSerial(y1, m1, 1)
Me.txtDateTo = DateSerial(y2, m2, 1) - 1
Me.Subform1.Requery
Case "Custom"
Me.txtDateFm = Null
Me.txtDateTo = Null
Me.txtDateFm.Visible = True
Me.txtDateTo.Visible = True
Me.Command2.Visible = True
GoTo skip_hide
End Select

Me.txtDateFm.Visible = False
Me.txtDateTo.Visible = False
Me.Command2.Visible = False

skip_hide:
End Sub

Don't forget to change the object names to your real names!

HTH,
Nikos

"RusCat" wrote in message
...
Good morning,

Here is something I'm tryng to do, but can't seem to get a

handle
on. I
am
hoping for some direction.

I have the following things listed in a listbox: today,

this
week,
this
month, last week, last month, custom

I want to be able to click on THISWEEK and have all the

records
for
this
week to show up on my form.

It is a projects table where our employees log in the hours

they
have
spent
on a project. I need them to be able to see the data by the

choices
above.

ALSO: how would I create a CUSTOM set of data? Click on

CUSTOM,
some
sort
of boxes appear where I could type in the 2 dates and

everything
between
those dates would show up on the form.

I've tried queries, but I'm just not getting it. I

understand
the
logic
behind it, but just not how to get it done. I'm a novice

Access
user, so
please be gentle in your help and explainations !!

Thank you so much.
LisaB.











  #10  
Old November 23rd, 2004, 02:49 AM
RusCat
external usenet poster
 
Posts: n/a
Default

Good Evening Nikos !

Well, after I asked you about the listbox dropping down, I did actually
figure it out by myself. It was so simple, I felt a bit stupid for asking
you.

Now, that pesky Calendar position.... I DID make the calendar on a separate
form called frmCalendar. (to be honest, I called everything exactly what you
did, so I wouldn't get confused!) :-)

Besides you, I'm using a book called "Running MS Access 2000" by John
Viescas. It seems to be a good book and comes with a companion disk of
samples. One of the samples has some sort of order form where the calendar
opens up directly under the txtbox for the To/From dates. I can only say
that it seems to be "anchored" there. It doesn't seem to move. That was
what I was trying to get my frmCalendar to do.

In your most recent post you say that the second approach allows me to
easily 'anchor' the calendar to the calling control.

1. Is the calling control the txtbox?
2. IF, I was going to try to do your second approach, where would I put
this code?
3. Will it mess up what's shown on the form, if it is suddenly visible when
I get to the txtbox?

I'm happy with the separate form (frmCalendar), but not happy with being
unable to anchor it to the txtbox. I like your suggestion about maximizing
the form and then nobody could move it around and the calendar would 'look'
anchored.

OK, I showed all that I have done to the boss and he was quite impressed, so
please pat yourself on the back! I have 2 more questions and then I think it
will be done.

A. the listbox and the txtboxes for custom dates brings up some data with I
am displaying on the frmMaine with a subform. My boss would like to be able
to click on a record on the subform and have just that record 'moved" up to
the top of the form into a data entry screen (which I already have and am
using successfully) Does that make sense? On the subform there might be 8
or 9 records listed. He wants to 'click' on a specific record and 'move' it
up to the data entry screen where he can "see' it better. I tried to explain
that he can 'edit' it in the subform , but he wants to be able to move it.
Can this be done, at my understanding level?

B. Another thing that I've done is after you get those 8 or 9 records
(depending on the dates you entered) I have totaled the hours that where
worked. This works fine except when there are no records in the data set.
then I get that error thing... #error. I would like to be able to have that
just show 0.00 if there are no records that meet the criteria. I tried to
find someplace to set a default to 0, but couldnt' find anything. I also
tried to set the "input mask" to something! This worked fairly well with the
Password function. If there are no records then the total box shows "*****"!
I laugh about this. There seems to be some way to create a custom input
mask, but I couldn't figure that out either !!

I feel like I am keeping you busy with my questions, but I want you to know
how much I have appreciated all your help. I can hardly wait to start a new
project and learn some new stuff from you !

Thanks again.
:-)
LisaB.

"Nikos Yannacopoulos" wrote:

Lisa,

The easy part first: to get the drop-down behaviour, you need to use a combo
box instead of a listbox. Open the form in design view and right-click on
the listbox, select Change To Combobox, and that's all there is to it! Its
name will remain unchanged, possibly connotating to a list, but it will work
just fine all the same, if you don't want to to bother to change it... and
so will the code behind the event. In a combo's case, of course, the single
click makes a lot more sense that the double.

Now, on the calendar position... what I had in mind was a separate form,
frmCalendar, with only tghe calenmdar control on it, which(the form) would
open on top of the main one, and I thought the idea was to move the whole
form with the calendar so it anchors to the txtbox. Your description is like
you have put just a calendar control on the same form? I'll try to address
both:

1. Calendar on separate form:
The idea is that frmCalendar is hardly any bigger than the control itself,
and its sole purpose is to host the latter; consequently, it is moved all
together in order to position the calendar. In this case, the form is moved
right after it is opened, from the click event of the txtbox on the main
form:

Private Sub txtDateFm_Click()
ctrl = Me.Name & "*" & Me.ActiveControl.Name
DoCmd.OpenForm "frmCalendar", , , stLinkCriteria, , , ctrl
Forms!frmCalendar!ActiveXCtl1 = Date
Forms("frmCalendar").SetFocus
DoCmd.MoveSize 1440, 1440 'or whatever
End Sub

Likewise for the other textbox.

2. Calendar on the same form:
This is a different approach. The idae is that you put the control on the
same form (it doesn't matter where), and set its Visible property to No.
Then when you need it you position it as required and make it appear,
possibly on top of other controls, hiding them. When its job is done, you
just hide it again.
This would result in slight changes to the code, since there is no separate
form involved now:

Option Compare Database
Public caller As String

Private Sub ActiveXCtl1_Click()
Me.Controls(caller) = Me.ActiveXCtl1
Me.Controls(caller).SetFocus
Me.ActiveXCtl1.Visible = False
End Sub

Private Sub txtDateFm_Click()
caller = Me.ActiveControl.Name
Me.ActiveXCtl1 = Date
Me.ActiveXCtl1.Top = Me.txtDateFm.Top + Me.txtDateFm.Height + 15
Me.ActiveXCtl1.Left = Me.txtDateFm.Left
Me.ActiveXCtl1.Visible = True
End Sub

Private Sub txtDateTo_Click()
caller = Me.ActiveControl.Name
Me.ActiveXCtl1 = Date
Me.ActiveXCtl1.Top = Me.txtDateTo.Top + Me.txtDateTo.Height + 15
Me.ActiveXCtl1.Left = Me.txtDateTo.Left
Me.ActiveXCtl1.Visible = True
End Sub

Note that I am using a Public variable to store the name of the control
calling the calendar, so it knows which control to return its selected date
to.
The code behind the listbox/combo click event and the command button remains
unchanged.

The second approach allows you to easily anchor the clendar to the calling
control, regardless of the form's current position. The first appoach works
well if the main form is maximised, so the clling txtboxes' position is
fixed, but can't follow them around otherwise - I haven't been able to
figure out how to "read" a form's position in the Access app window. The
advantage of the former approach, on the other hand, is one calendar form
can support any number of forms using it.

Hope this is clear now. Good luck,
Nikos

"RusCat" wrote in message
...
Good evening Nikos,

I hope you are having a nice weekend. The weather here in Maine was great
today, but they are forecasting snow for tomorrow! I do love winter.

Your answers to my questions were, as always, very helpful. I'm really
beginning to get the hang of what stuff means and even the whys.

With regards to question #3 and your answer. I thought I understood what
you wrote, but I can't get anything to work.

I did mean the position of the calendar, but within the form, not the
screen. You said "if you want it placed depending on the calling txtbox,

you
can put something like :
Forms("frmCalendar").Set forcus
DoCmd.moveSize 1440, 1440
After the DoCmd.OpenForm in teh txtbox's event code."

Can you clarify for me exactly where to put it.

I would like the calendar to be "attached" or "anchored" to the txtbox.

If
I move the form around the calendar stays stationary. Does that make

sense?
Is that possible?

Also, something related back to the listbox: How can I make the "list"

drop
down below the box when I move my curser there. Now the list just scrolls

in
the box I made, but I see other lists that drop down and then the user can
click on their selection.

Thanks again for the great direction. What I have learned from you with
this form is going to help me with alot of other projects. I really
appreciate the time you take to post on the board.

:-)
LisaB.


"Nikos Yannacopoulos" wrote:

Lisa,

1. No, it doesn't matter. It's just a question of preference. I go for
double because double is deliberate, single can be accidental or

intended
just to set focus.

2. Yes. Again, to me, double click is deliberate, single can be

accidental
(aimed at the row above or below).

3. I take it you mean position the calendar form on the screen? Yes, you
can. The method is:
DoCmd.MoveSize 1440, 1440
The numbers correspond to the vertical and horizontal distance of the

upper
lefthand corner from the upper lefthand corner of the application

window,
and are in twips (1/1440 of an inch). The method has two more optional
arguments:
DoCmd.MoveSize [right][, down][, width][, height]
that set the form's size.
If you want the form always placed at the same position, you can put

this
command in the calendar form's On Open event. If, on the other hand, you
want it placed depending on the calling txtbox, you can put something

like:
Forms("frmCalendar").SetFocus
DoCmd.MoveSize 1440, 1440
after the DoCmd.OpenForm in the txtbox's event code. I use the SetFocus

to
make sure the calendar form is the active one when the movesize is
executed, because by default it acts on the active window.

4. Add an extra line of code at the end of the procedu
Private Sub ActiveXCtl1_Click()
sep = InStr(1, OpenArgs, "*")
frm = Left(OpenArgs, sep - 1)
ctl = Right(OpenArgs, Len(OpenArgs) - sep)
Forms(frm).Controls(ctl) = Me.ActiveXCtl1
DoCmd.Close
End Sub

5. Yes, you can, but you will no longer be able to just enter and type

in a
date. Again, a user interface decision.

6. I can think of: one possible reason: the form was already open from

the
first textbox, because the calendar's click event didn't have the extra

line
to close it, just hidden behind the main form when you manually set

focus
back to the main one. If that's the case, when you double-click (or

click)
the second textbox the calendar form reappears but it was already open,

so
the OpenArgs are not passed again, thus still pointing to the From

txtbox.
Had the calling textbox name been hardcoded in there I would suspect

that,
but the way it is (retrieved by reference: Me.ActiveControl.Name) I

don't
see how that could be, unless there's code preceding the snippet I gave

you
that moves focus back to the From txtbox before the calendar form is

opened;
very unlikely!

HTH,
Nikos


"RusCat" wrote in message
...
Wow Nikos !

I used to be self taught, years and years ago. I used to program in

dbase
and in Clipper. Before the windows environment was around. I did

some
great
stuff and really enjoyed the 'puzzle' aspect of programming. Working

to
figure out how to make something work. I just taught myself by trial

and
error. THEN.... I had kids and my brain turned to mush!! Things have
changed ALOT in the last 10 or 12 years. I'm enjoying the challenge

again
now that my youngest is in school all day.

This is helping me SO much. Not only is it solving a problem but it

is
teaching me SO much. Your directions are clear and easy to follow.

What you explained is working well. I finally got everything in the

right
place and it is doing what I wanted it to do !! Mostly.

I have some questions about the actual operations:

1. Does it matter whether the code is behind the Click or

DoubleClick?
One
click seems to work best for me, but if that is not the correct way to

set
things up, then I'll stay with DClick.

2. Can the event for the Listbox also be just Click?

3. I did go with option 2 and played around with the format stuff -

very
cool. Can I tell it somehow exactly where I want the calendar to

appear?

4. When the calendar does appear, I click on a date (which appears in

the
textbox), but the calendar does not disappear. How do I make it do

that?

5. Can I put the exact same code in the OnEnter event for the

Listbox?
That way you can select the item by clicking or entering?

6. I've done something wrong with the textboxes.... Click the first

box,
calendar appears, click date, date goes to first textbox. Click

second
box,
calendar appears, click date, date ALSO goes to the first textbox. I
checked
the code and I have the right names. Where might I be going wrong?

Well, 6 problems/questions is enough for right now. thanks again for

the
help.
:-)
LisaB.
"Nikos Yannacopoulos" wrote:

Lisa,

Glad I could help. Not a big believer in classes, I'm self-taught in
most
everything I do with a computer (and when I got my first PC back in

'87
the
internet was practically unheard of!). Anyway...

On the drop-down calendar: there are two approaches:
1. You can use the Date and Time Picker active X control instead of
textboxes. You can set their value in code just like the textboxes,

but
you
can't set them to Null for Custom; they have to have a value, so you
would
set them to, say, current date instead, as a basis for the user to

start
from. Also, because the DTPicker has a time part as well, even

though
you
don't see it, you would have to use an Int() function on the control
references in the query criteria, so as to get "clean"dates, without

the
hour part (date/time in Access is actually a number, 0 being Dec.31,
1899,
the integer part being the date, and the decimal part being the

time).

2. You can leave the textboxes as they are and use their

double-click
event
to pop-up a separate form with a Calendar active X control on it, to
select
a date and return it to the textbox on the main form, while the date

can
still be typed in if desired. Also, the calendar control has no time
part,
so no bneed to worry about that, and you can still Null the

textboxes
for
Custom. Incidentally, I answered a post on thi this very subject
yesterday,
I have the "how-to" ready! Here it goes:

The calendar control is on a separate form, opened by the

double-click
(or
click?) event of the text boxes. I'll assume the following names in

my
example, and you'll have to change to the real ones:

Form with calendar: frmCalendar
Calendar control: ActiveXCtl1

The code behind the double-click event of the text boxes must be
something
like:

Private Sub txtDateFm_DblClick(Cancel As Integer)
ctrl = Me.Name & "*" & Me.ActiveControl.Name
DoCmd.OpenForm "frmCalendar", , , stLinkCriteria, , , ctrl
Forms!frmCalendar!ActiveXCtl1 = Date
End Sub

(same for txtDateTo)

This way, you are passing the form and control names (separated by a

*)
as
an opening argument to frmCalendar.

The code behind the click event of the calendar should be something
like:

Private Sub ActiveXCtl1_Click()
sep = InStr(1, OpenArgs, "*")
frm = Left(OpenArgs, sep - 1)
ctl = Right(OpenArgs, Len(OpenArgs) - sep)
Forms(frm).Controls(ctl) = Me.ActiveXCtl1

End Sub

This will work with different forms as well!

If you go for option 2, play around with the properties on Format

tab
for
the form to get rid of record selector, navigation buttons, control

box
etc
wich are meaningless in this case. In either case, to put an ActiveX
control
on a form, go Insert ActiveX control.

Good luck!
Nikos


"RusCat" wrote in message
...
Good evening Nikos,

I just want you to know that my jaw is laying on the floor !!

Your
help
and
code did EXACTLY what I was wanting to do. Thank you thank you

thank
you.

I didn't understand most of it, but I spent part of yesterday and

most
of
today figuring it out and it works GREAT !!! It was the query

that
gave
me
the most trouble. But then again, I usually have the most trouble
with
queries.

I appreciate you and the many others that frequent this board and

help
those
of us who are lost. I live in rural Maine with no opportunity to

take
a
class in Access, so this board has been a wonderful learning
experience.

I'm going to see if I can incorporate a drop down calendar on the
Custom
date from and to. I've researched it here and know that it can be
done.

Any additional help you can offer will be most appreciated with
regards to
the calendar.

Thank you again for sharing your skills and knowledge. It really
means
something to me.
:-)
LisaB

"RusCat" wrote:

Nikos,

I am completely flabbergasted at the detail of your reply.

Thank
you so
much for taking the time and effort to help me. I appreciate it
very
much.

To be honest, I don't understand all of it, but I'm going to

begin
trying to
dissect it this evening and see where I get.

I hope you will continue to be available for additional

questions,
which
I
will undoubtedly have !!

Thank you again.
:-)
LisaB.

"Nikos Yannacopoulos" wrote:

Lisa,

To begin with, I would add two textboxes on the form (called,

say,
txtDateFm
and txtDateTo, formatted as date - same format as the date

field
in
the
table), and set their Visible property to No in form design.

These
will be
used for holding the from and to dates, populated by the

listbox
(while
still invisible) on every other choice, and made visible to be
filled
manually when custom is selected.

The query for the records would reference these two text boxes

in
its
criterion on the date field, like:

= Forms![Form Name]![txtDateFm] and = Forms![Form
Name]![txtDateTo]

I would use the double click event of the listbox (assumed

name
List0)
to
calculate the dates and requery the subform (assumed name:
Subform1)
with
the records. Of course, when custom is selected, the user has

to
enter
the
dates and then somehow requery the subform. For this I would

use a
command
button (called Command2 in my sample code, caption something

like
Refresh or
Get Data, also hidden in the form design) which is toggled

between
visible
and invisible together with the textboxes.

Here's the code behind the Command2 button:

Private Sub Command2_Click()
Me.Subform1.Requery
End Sub

And, finally, here's the code behind the listbox's double

click
event:

Private Sub List0_DblClick(Cancel As Integer)
Select Case Me.List0
Case "Today"
Me.txtDateFm = Date
Me.txtDateTo = Date
Me.Subform1.Requery
Case "This Week"
dt = Date
If Weekday(dt, 3) = 7 Then dt = dt + 7
Me.txtDateFm = dt - Weekday(dt, 3)
Me.txtDateTo = dt - Weekday(dt, 3) + 6
Me.Subform1.Requery
Case "This Month"
Select Case Month(Date)
Case 12
y1 = Year(Date)
m1 = Month(Date)
y2 = y1 + 1
m2 = 1
Case Else
y1 = Year(Date)
m1 = Month(Date)
y2 = y1
m2 = m1 + 1
End Select
Me.txtDateFm = DateSerial(y1, m1, 1)
Me.txtDateTo = DateSerial(y2, m2, 1) - 1
Me.Subform1.Requery
Case "Last Week"
dt = Date
If Weekday(dt, 3) = 7 Then dt = dt + 7
Me.txtDateFm = dt - Weekday(dt, 3) - 7
Me.txtDateTo = dt - Weekday(dt, 3) - 1
Me.Subform1.Requery
Case "Last Month"
Select Case Month(Date)
Case 1
y1 = Year(Date) - 1
m1 = 12
y2 = y1 + 1
m2 = 1
Case Else
y1 = Year(Date)
m1 = Month(Date) - 1
y2 = y1
m2 = m1 + 1
End Select
Me.txtDateFm = DateSerial(y1, m1, 1)
Me.txtDateTo = DateSerial(y2, m2, 1) - 1
Me.Subform1.Requery
Case "Custom"
Me.txtDateFm = Null
Me.txtDateTo = Null
Me.txtDateFm.Visible = True
Me.txtDateTo.Visible = True
Me.Command2.Visible = True
GoTo skip_hide
End Select

Me.txtDateFm.Visible = False
Me.txtDateTo.Visible = False
Me.Command2.Visible = False

skip_hide:
End Sub

Don't forget to change the object names to your real names!

HTH,
Nikos

"RusCat" wrote in message
...
Good morning,

Here is something I'm tryng to do, but can't seem to get a
handle
on. I
am
hoping for some direction.

I have the following things listed in a listbox: today,

this
week,
this
month, last week, last month, custom

I want to be able to click on THISWEEK and have all the

records
for
this
week to show up on my form.

It is a projects table where our employees log in the hours

they
have
spent
on a project. I need them to be able to see the data by the
choices
above.

ALSO: how would I create a CUSTOM set of data? Click on
CUSTOM,
some
sort
of boxes appear where I could type in the 2 dates and

everything
between
those dates would show up on the form.

I've tried queries, but I'm just not getting it. I

understand
the
logic
behind it, but just not how to get it done. I'm a novice

Access
user, so
please be gentle in your help and explainations !!

Thank you so much.
LisaB.












 




Thread Tools
Display Modes

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

vB code is On
Smilies are On
[IMG] code is Off
HTML code is Off
Forum Jump

Similar Threads
Thread Thread Starter Forum Replies Last Post
Cursor Positioning Colin Hammond General Discussion 3 November 2nd, 2004 09:42 PM
Can you move highlighted data from one listbox to another listbox on the same form? Col. Forbin General Discussion 2 September 24th, 2004 07:51 PM
Searching Records Melissa Lambino New Users 6 July 20th, 2004 04:51 AM
Passing value to modal child form in MS Access using VBA? Grahammer Using Forms 6 July 4th, 2004 11:53 PM


All times are GMT +1. The time now is 08:30 AM.


Powered by vBulletin® Version 3.6.4
Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 OfficeFrustration.
The comments are property of their posters.