1. No, it doesn't matter. It's just a question of preference. I go for
2. Yes. Again, to me, double click is deliberate, single can be accidental
3. I take it you mean position the calendar form on the screen? Yes, you
can. The method is:
and are in twips (1/1440 of an inch). The method has two more optional
[, 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 procedure:
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
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.
:
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
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
:
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.
:
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
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.
