Dates in a listbox connected to a form...

Hi Lisa,

See my comments in between your post.

Regards,
Nikos

"RusCat" wrote in message
...
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.

Good! Feels much better when you dicsover on your own.



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.


OK. So the problem is the positioning, right? I assume you've solved the
which-txtbox-is-updated issue. I checked out John Viescas's site (John is an
Access MVP, well known to the NG users) in case he had the same example
available there. He does have a calendar form example (surprise, it's called
frmCalendar!) which works similar to what I proposed, but - regrettably -
doesn't get anchored to the calling form/textbox, so I couldn't see that. I
was thinking, it might be that John's order form is using the DTPicker
control within the same form instead of a Calendar control on a separate
form; the DTPicker control looks like a combo box (button with down arrow on
the right hand side) when idle, and opens to a small calendar (smaller than
the Calendar control) when the arrow button is clicked. This is always
anchored to itself by definition, being a single entity! Can you check
whether this is what it is?

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?

Yes, that's what I meant. Not a standard term, just my private gismo for the
purpose.


2. IF, I was going to try to do your second approach, where would I put
this code?

All the code procedures go in the main form's module. Rather than pasting it
directly, it is better to go through the properties, selecting the
appropriate event, and pasting the actual code between the automatically
inserted event procedure start and end lines, so you make sure the form
"registers" the procedures. If you just paste the code, sometimes it might
not (at least in my experience).


3. Will it mess up what's shown on the form, if it is suddenly visible
when
I get to the txtbox?


The code will make the calendar appear right below the textbox, over any
control(s) / subform already there (you may have to do a Format Bring to
Front on the calendar control in design view), but won't affect them in
anyway, as soon as it is closed they wil be just like they were before. A
Date and Time Picker control, like I suspect is used in John Viescas's form,
would behave exactly the same.


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.


The Calendar on the main form itself seems a more robust solution after all,
unless you want the separate form to work with multiple "main" forms. You
are in a better position to judge this, though, I haven't even seen your
project!


OK, I showed all that I have done to the boss and he was quite impressed,
so
please pat yourself on the back!

Please ask him to call mine and put in a good word for me! :-)


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?

Yes, it can. Open the subform in design view, and put this line of code
behind the appropriate control's Click (or double-click) event:

DoCmd.OpenForm "frmEditRecord", acNormal, , "txtID=" & Me.txtID

where I have assumed the other form to be called frmEditRecord, and the
control bound to the Primary Key field on both forms called txtID. This will
open the edit form with only the specific record available. The expression
above will work for a numeric PK field. If your PK is text, then change to:

DoCmd.OpenForm "frmEditRecord", acNormal, , "txtID='" & Me.txtID & "'"



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 !!

Use he Nz() function on the expression. If the txtbox's controlsource is
currently, say, =DSum(xxx), change it to:
=Nz(DSum(xxx), 0)
If this doesn't work, let me know what the controlsource currently is.



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.

Good Evening Nikos,

Big Sigh.... I feel like I have hit the wall of understanding. Because of
your help, I have learned so many new things in the past week. I feel like
maybe I have gotten over confident in my desire to make things perfect.

So.. here is where I am:

1. I've decided not to worry about the calendar position. You where right
about the John Viescas calendar actually being the DTpicker. Nobody but me
seems to be worried about it, so I'll leave it.

2. I will continue to work on my question A, which you answered but I got
confused so it may take me several days.

3. the Nz() did not work for what I have. I'm not exactly sure how I got
the total into the box in the first place. A friend did some wild IIF
statement, that I don't actually understand, but since it works, I've tried
not question it.

4. I went to John Viescas' website. What alot of great information he has
there. I know it will come in handy as my knowledge increases.

** You mentioned in your most recent post that you hadn't seem my project.
Would you like me to email it to you? You seem to have an email address
here, but I wasn't sure it was a correct one. It is about 15kb and I would
be happy for you to see what I've learned from you. Maybe you could make
some gentle comments!!
Please let me know.
:-)
LisaB.

"Nikos Yannacopoulos" wrote:

Hi Lisa,

See my comments in between your post.

Regards,
Nikos

"RusCat" wrote in message
...
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.

Good! Feels much better when you dicsover on your own.



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.


OK. So the problem is the positioning, right? I assume you've solved the
which-txtbox-is-updated issue. I checked out John Viescas's site (John is an
Access MVP, well known to the NG users) in case he had the same example
available there. He does have a calendar form example (surprise, it's called
frmCalendar!) which works similar to what I proposed, but - regrettably -
doesn't get anchored to the calling form/textbox, so I couldn't see that. I
was thinking, it might be that John's order form is using the DTPicker
control within the same form instead of a Calendar control on a separate
form; the DTPicker control looks like a combo box (button with down arrow on
the right hand side) when idle, and opens to a small calendar (smaller than
the Calendar control) when the arrow button is clicked. This is always
anchored to itself by definition, being a single entity! Can you check
whether this is what it is?

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?

Yes, that's what I meant. Not a standard term, just my private gismo for the
purpose.


2. IF, I was going to try to do your second approach, where would I put
this code?

All the code procedures go in the main form's module. Rather than pasting it
directly, it is better to go through the properties, selecting the
appropriate event, and pasting the actual code between the automatically
inserted event procedure start and end lines, so you make sure the form
"registers" the procedures. If you just paste the code, sometimes it might
not (at least in my experience).


3. Will it mess up what's shown on the form, if it is suddenly visible
when
I get to the txtbox?


The code will make the calendar appear right below the textbox, over any
control(s) / subform already there (you may have to do a Format Bring to
Front on the calendar control in design view), but won't affect them in
anyway, as soon as it is closed they wil be just like they were before. A
Date and Time Picker control, like I suspect is used in John Viescas's form,
would behave exactly the same.


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.


The Calendar on the main form itself seems a more robust solution after all,
unless you want the separate form to work with multiple "main" forms. You
are in a better position to judge this, though, I haven't even seen your
project!


OK, I showed all that I have done to the boss and he was quite impressed,
so
please pat yourself on the back!

Please ask him to call mine and put in a good word for me! :-)


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?

Yes, it can. Open the subform in design view, and put this line of code
behind the appropriate control's Click (or double-click) event:

DoCmd.OpenForm "frmEditRecord", acNormal, , "txtID=" & Me.txtID

where I have assumed the other form to be called frmEditRecord, and the
control bound to the Primary Key field on both forms called txtID. This will
open the edit form with only the specific record available. The expression
above will work for a numeric PK field. If your PK is text, then change to:

DoCmd.OpenForm "frmEditRecord", acNormal, , "txtID='" & Me.txtID & "'"



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 !!

Use he Nz() function on the expression. If the txtbox's controlsource is
currently, say, =DSum(xxx), change it to:
=Nz(DSum(xxx), 0)
If this doesn't work, let me know what the controlsource currently is.



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.

Lisa,

Yes, I'd like to see your project, it will make things a lot easier. If you
are accessing the NG with Outlook (Express), click on Reply and you'll get
my address in the To box, like: nyannacoREMOVETHISBIT at in dot gr. Just do
the obvious with it before you Send - anti-spam measures... I learned the
hard way. I am on Access 2000, so if you are using a later version pls
convert back to 2000 before you send.

On the size: I doubt it could be 15KB. A new, blank .mdb in A2K before
anything is added to it, is 96KB already. Did you mean 15MB by any chance?
It can easily get to that size (and far beyond) with lots of data. If that's
the case, make a copy and remove the bulk of the data from the tables,
leaving just some representative, so it's more efficient to mail. In any
case, zipping is a good idea.

I guess we can take it all from there.

Nikos

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

Big Sigh.... I feel like I have hit the wall of understanding. Because of
your help, I have learned so many new things in the past week. I feel
like
maybe I have gotten over confident in my desire to make things perfect.

So.. here is where I am:

1. I've decided not to worry about the calendar position. You where
right
about the John Viescas calendar actually being the DTpicker. Nobody but
me
seems to be worried about it, so I'll leave it.

2. I will continue to work on my question A, which you answered but I got
confused so it may take me several days.

3. the Nz() did not work for what I have. I'm not exactly sure how I got
the total into the box in the first place. A friend did some wild IIF
statement, that I don't actually understand, but since it works, I've
tried
not question it.

4. I went to John Viescas' website. What alot of great information he
has
there. I know it will come in handy as my knowledge increases.

** You mentioned in your most recent post that you hadn't seem my project.
Would you like me to email it to you? You seem to have an email address
here, but I wasn't sure it was a correct one. It is about 15kb and I
would
be happy for you to see what I've learned from you. Maybe you could make
some gentle comments!!
Please let me know.
:-)
LisaB.

Nikos,

I sent off an email to you sometime this afternoon, with my project
attached. I hope I did it right !!

I'll look forward to hearing from you sometime over the holiday weekend.
I hope that your Thanksgiving is joyous and filled with family and loved ones.
:-)
LisaB

"Nikos Yannacopoulos" wrote:

Lisa,

Yes, I'd like to see your project, it will make things a lot easier. If you
are accessing the NG with Outlook (Express), click on Reply and you'll get
my address in the To box, like: nyannacoREMOVETHISBIT at in dot gr. Just do
the obvious with it before you Send - anti-spam measures... I learned the
hard way. I am on Access 2000, so if you are using a later version pls
convert back to 2000 before you send.

On the size: I doubt it could be 15KB. A new, blank .mdb in A2K before
anything is added to it, is 96KB already. Did you mean 15MB by any chance?
It can easily get to that size (and far beyond) with lots of data. If that's
the case, make a copy and remove the bulk of the data from the tables,
leaving just some representative, so it's more efficient to mail. In any
case, zipping is a good idea.

I guess we can take it all from there.

Nikos

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

Big Sigh.... I feel like I have hit the wall of understanding. Because of
your help, I have learned so many new things in the past week. I feel
like
maybe I have gotten over confident in my desire to make things perfect.

So.. here is where I am:

1. I've decided not to worry about the calendar position. You where
right
about the John Viescas calendar actually being the DTpicker. Nobody but
me
seems to be worried about it, so I'll leave it.

2. I will continue to work on my question A, which you answered but I got
confused so it may take me several days.

3. the Nz() did not work for what I have. I'm not exactly sure how I got
the total into the box in the first place. A friend did some wild IIF
statement, that I don't actually understand, but since it works, I've
tried
not question it.

4. I went to John Viescas' website. What alot of great information he
has
there. I know it will come in handy as my knowledge increases.

** You mentioned in your most recent post that you hadn't seem my project.
Would you like me to email it to you? You seem to have an email address
here, but I wasn't sure it was a correct one. It is about 15kb and I
would
be happy for you to see what I've learned from you. Maybe you could make
some gentle comments!!
Please let me know.
:-)
LisaB.