Combo boxes going "HAL" on me

  • Thread starter Thread starter Jeff Conrad
  • Start date Start date
J

Jeff Conrad

Ok, this is really bizarre. I'm using Access 97.

I was having some "issues" with two combo boxes on an
unbound form so I decided to experiment on a brand new
database with just these two combo boxes on a form and I'm
seeing the same results! I don't often use unbound forms,
but I don't think that's the issue.

In my new test database this is ALL I have:

tblTimeBuckets:
TimeBucketID Number-Byte
TimeBucket Date/Time

The table looks like this:
1 12:00:00 AM
2 12:30:00 AM
3 1:00:00 AM
..
..
48 11:30:00 PM

Easy enough.

I have two combo boxes on an unbound form (nothing else)
with Row Sources of:
SELECT tblTimeBuckets.TimeBucketID,
tblTimeBuckets.TimeBucket FROM tblTimeBuckets;

They are named cboStartTime and cboEndTime and only the
time shows in the combo box.
What I'm trying to do is to prevent the user from
selecting an End Time that is BEFORE the Start Time. Easy
enough, right? HA! Not so!

In the After Update event of the End Time combo box I have
this:

If Me.cboEndTime.Column(1) < Me.cboStartTime.Column(1) Then
Me.cboStartTime = Me.cboEndTime
End If

MsgBox Me.cboStartTime 'Here for debugging
MsgBox Me.cboEndTime 'Here for debugging

(I have also tried just using Me.cboEndTime and
Me.cboEndTime.Value, etc. with same result)

What this is supposed to do is make the Start Time equal
to the End Time if the selected value is before the Start
Time.

I cannot possibly list all the different results I've
seen! Sometimes it works, but most often it does not.
Totally bizarre. It rarely works for anything below 10 AM!

Here's one of MANY weird tests:
1. Select say 1:00:00 PM in first box
2. Select say 1:30:00 PM in second box
Message box 1 says 27, message 2 says 28 All fine
3. Now select 10:00:00 AM in second box
Message box 1 says 27, message 2 says 21 but the start
time combo box does not change at all!!!

Other times it works flawlessly. There is no rhyme or
reason to this. Any ideas why this is not working?

Thanks,
Jeff Conrad
Bend, Oregon
 
I've often noted that the format of the columns in a multicolumn combo box
are changed to text format, even though they started as number or date/time.

Try wrapping the Time() function around the combo boxes' column values in
your comparisons and see if that works better for the logic test.
 
Ken, have I told you recently how much of a genius you are?
You da man Ken!

Your assumptions were spot on about format. I'm pretty
sure that was the underlying problem. I was unable to work
through using the Time() function as you suggested.
I tried this (excuse the line wrapping):

If (Time(Me.cboEndTime.Column(1))) < (Time
(Me.cboStartTime.Column(1))) Then
Me.cboStartTime = Me.cboEndTime
End If

But I always got a type mismatch error. Now maybe that
targets the exact problem, but I think I just have the
syntax wrong. I've never used the Time function so I
probably have it all screwed up. How exactly is that
supposed to be coded?

At any rate I decided to try this:

Dim BegTime As Date
Dim EndTime As Date

BegTime = Me.cboStartTime.Column(1)
EndTime = Me.cboEndTime.Column(1)

If EndTime < BegTime Then
Me.cboStartTime = Me.cboEndTime
End If

Works EVERY TIME now!! Yee Haa!! I experimented on my real
database and it works flawlessly now.

Incidentally, I have to share something with you. The
actual reason I was putting this code in at all was
because my "Save" button code wouldn't catch this problem.
At the very start of my save code I did the test to make
sure the End Time was not before the Start Time and put up
a message box. But about 90% of the time it wouldn't catch
it and skip right over! Very frustrating. I put a
breakpoint in the code to see what would happen. When I
would hover the mouse over the Me.cboStartTime line it
would usually say "21" (whatever number WITH the quotation
marks), but the Me.cboEndTime would say 19 (whatever
number WITHOUT ANY quotation marks)!!

So maybe it was having a problem recognizing the formats.
Very interesting. So I've added the new test code to the
Save button and left in the code for the After Update
event. Everything works great even after doing a lot of
testing.

This had me baffled for many, many hours today and last
night and I've very grateful for your help in solving it.
I can't thank you enough. My Ducks beat Michigan and we
get this solved: what an AWESOME day!

Thanks again,
Jeff Conrad
Bend, Oregon
 
Jeff Conrad said:
Ken, have I told you recently how much of a genius you are?
You da man Ken!

Your assumptions were spot on about format. I'm pretty
sure that was the underlying problem. I was unable to work
through using the Time() function as you suggested.
I tried this (excuse the line wrapping):

If (Time(Me.cboEndTime.Column(1))) < (Time
(Me.cboStartTime.Column(1))) Then
Me.cboStartTime = Me.cboEndTime
End If

But I always got a type mismatch error. Now maybe that
targets the exact problem, but I think I just have the
syntax wrong. I've never used the Time function so I
probably have it all screwed up. How exactly is that
supposed to be coded?
Click to expand...

Great work on Ken's part. I think he was mistaken in saying to use the
Time function, though; I suspect what he meant to write was the
TimeValue function.
 
Hi Yoda,

Ahh haa, I think you may be right. I changed the code to
TimeValue and it compiles and works perfectly. I tested
using it many times and it worked every time.

Well this is really cool; now I have two ways of solving
it. This will really help in the future.

Thanks for the added info and help,
Jeff Conrad
Bend, Oregon
 
See, I'm not a genius yet!

Dirk is absolutely correct. I meant TimeValue function. Thanks for saving my
reputation, Dirk!

Glad you got it to work, Jeff!
 
Genius is always a relative term Ken, but you'll always be
one in my book!

Thanks again,
Jeff Conrad
Bend, Oregon
 
Back
Top