Restricting entries in a ComboBox

hi Duncan. if i understood correctly, you're saying that the database i sent
you works "correctly", or as desired. but yours does not, and you've either
configured it the same - or in some respects haven't been able to get a
specific configuration to "stick".
so it sounds like the version-A2003/format-A2000 setup we're using is the
same, and is working the same. my guess is that your form, or your database
itself, is corrupted.
suggest you open a new database, and STOP. before you do anything else, go
to Tools, Options, General tab and *uncheck* the box beside "Track name
AutoCorrect info". click Apply, then OK. compact the database.
next, import the two tables involved. import the form. create a *new* form,
and copy all your controls, and the code from the VBE window, into the new
form. make the configuration changes to the new form, so it matches the
"working" form i sent you. delete the "old" form, and compact the database.
then try out the new form.
post back with results.

hth


"Duncan Edment" wrote in
message ...
Tina,

This is not working!!

I'm using A2003, saving the resulting file in A2000 format.

The first difference that I notice between your database and mine, is that
your
text control--txtProjectDescription--appears "on top of" the combo
control--cboProjectID. I also note, that in the Tab Order, the text
control
appears before the combo control. This is exactly as you said it should
be
done.

However, on mine, the text control is not shown when the form is in design
mode.
It appears to be 'behind' the combo control. No matter what I try, I
cannot get
the text control to stay on top of the combo control.

If I try to bring the text control to the front--via Format|Bring To
Front--the
text control appears at the bottom of the TAB control list. Moving it
back up,
to before the combo control, places it "behind" the combo again.

Also, from looking at your database, I notice a difference when a new
record is
created. When you press TAB after entering the "TimeSheet ID", the cursor
is
placed in the combo control. With mine, it is placed in the text control,
and
the text control contains the value "#Name?".

I've checked, double-checked & triple-checked all properties, controls &
values
and they match what you have in your database, as well as what you have
said in
previous posts. However, it still wont work.

Would you prefer if a sample copy of the database were e-mailed to you, so
you
can have a look at it?

Many thanks

Duncan

--
Newsgroups are like one big sandbox that all of us
UseNet kiddies play in with peace & harmony.

Spammers, Cross-Posters, and Lamers are the
people that pee in our big sandbox.

"tina" wrote in message
...
see comments "in line".

"Duncan Edment" wrote in
message ...
Tina,

My head is hurting now! I still cannot get this to work!

If you don't mind, I'll describe--in it's entirety--the form, table(s)
and
controls that I'm using, and see if you can help me with it...please?

Table 1 - tblProjects

fldProjectID = Number
fldProjectDescription = Text
fldValidUntilDate = Date/Time
fldIsWork = Yes/No

Table 2 - tblTimesheet

This is the main table that stores all information relating to
projects
worked on by employees. The field I am concerned with is:

fldProjectID = Number

This field is linked to the table tblProjects, linking to the
fldProjectID
field.

As per your instructions, my main form has two controls on it for the
Project
Description:

Control 1 - cboProjectID

ColumnCount = 3
ColumnWidths = 11.401cm;1.801cm;2.501cm
Visible = Yes
ControlSource = tblTimeSheet.fldProjectID
RowSource* = SELECT tblProjects.fldProjectDescription, _
tblProjects.fldProjectID, _
tblProjects.fldValidUntilDate FROM _
tblProjects WHERE _
(((tblProjects.fldValidUntilDate)Date()))
_
ORDER BY tblProjects.fldProjectID;
* I altered the RowSource here to reflect the way the information
should
be displayed and sorted.


not sure why you want to show the user all 3 columns, rather than just
the
fldProjectDescription. also not sure why you want to sort the list by ID
if
you expect the user to choose a project by its' description. in any
event,
you can sort by fldProjectID, and still only *show* the description
field,
if that's what you'd prefer.


BoundColumn* = 2
* Once again, I had to alter the value above. With the value you
suggested--1--all I was getting displayed in the field was the
Project
code and not the text. This was also making it difficult to
select
any values.


my instructions called for only one column to *show* in the droplist -
fldProjectDescription. that's why columns 1 and 3 were set to zero
width, as
0";1";0"
but from what you described here, you made the adjustments correctly. so
if
you want to show the Description, ID and Date in the droplist, it
shouldn't
affect the outcome of the code one way or the other.


LimitToList = Yes
Enabled = Yes
Locked = No
TabStop = No

When I use the code you suggested for the cboProjectID events, the
following happens:

In the GotFocus event, Me!cboProjectID.Column(2) is the
ValidUntilDate
field, if I am correct.

correct.


This being the case, the date is always populated
and so the Locked property of the control cboProjectID will never
be
set
to "True" here. Am I right?


no, the date column is not "always populated".
remember you are dealing with a mixture of records, and some of them
were
added when a project was active - but now it's not active. so when you
look
at a record where the project date is "expired", there will be no
records
returned by the combo box SELECT statement - and so no date value in
"Column(2)" for that record.
that was the crux of your initial post: when you restricted the
droplist to
"not expired" projects, then the project control was "blank" for any
record
with an expired project - because the source record had been filtered
out of
the combo box row source.


Control 2 - txtProjectDescription

ControlSource* =
=IIf([fldProjectID] Is
Null,Null,DLookUp("[fldProjectDescription]", _
"tblProjects","[fldProjectID] = " & _
Forms!frmTimesheet!fldProjectID))

* So that I understand what is going on here, am I right in
thinking:
If the fldProjectID if Null--this would be the one from the
main
table--tblTimeSheet--then set the control to Null. Otherwise,
lookup
the value in the field fldProjectDescription, within the table
tblProjects,
where the value held in fldProjectID is the same as the value
in
the
control fldProjectID on the form frmTimesheet.

correct, though technically i guess it would be more accurate to say
that
the expression is refering to the value of the fldProjectID field in the
form's underlying table.



If my thinking is correct, then the lookup above is wrong.
There
is no
control on the form named 'fldProjectID'. Did you mean this
to be
[tblTimesheet]![fldProjectID]?


no, i didn't mean that. as far as i know, you can't refer to a table
directly in that manner.


Another problem I have noted with this control is when I
create a
new
record. Tabbing into text field, displays an initial value of
'#Name?'
when I try to add a new record. From the ControlSource above,
I
would expect that a new record would have a fldProjectID of
Null,
so
it should display a Null value in the text control. I've
tried
various
combinations of controls and field values for this, and I just
can't get
it to work.


the expression i gave you worked fine for me, including the reference to
fldProjectID. but i created my test db as an A2000-format db *using
version
A2003*. if you're using version A2000, perhaps it doesn't like the
reference. i changed my references to

=IIf([cboProjectID] Is Null, Null, DLookUp("[fldProjectDescription]", _
"tblProjects","[fldProjectID] = " & _
Forms!frmTimesheet!cboProjectID))

and found that my db is happy with either version. so perhaps the
changed
version will work for you.


Sorry for the long post!! I just need to get my head around a few
things,
some
of which I have probably messed up when translating from your code to
mine.

The only other thing I miss is the ability to enter data and the combo
to
match
it with the first entry in its list.

hmmm, i'm afraid you lost me completely there. if you mean autofill, as
in,
you type a "P" and the first selection on the droplist beginning with P
fills in....the solution i built does do that - at least on my pc.


But still, I always did want to have my
cake AND eat it!!

Many, many thanks for your help with this Tina, and I patiently await
you
reply.
If you think your test db will explain things better, please feel free
to
e-mail
me it. Just remove the underscores and everything in between.


i am going to send you a copy of my db as soon as i post this reply.
hopefully between having my copy to analyze, and the above notes, you'll
have the help you need. if not, post back. we'll keep trying, and maybe
somebody else will step in with better ideas.


Rgds

Duncan

--
Newsgroups are like one big sandbox that all of us
UseNet kiddies play in with peace & harmony.

Spammers, Cross-Posters, and Lamers are the
people that pee in our big sandbox.

Tina,

Hello. It's me. Sore head...match-sticks holding my eyes
open...more coffee inside me than must be good for
me...still looking at this blessed form!

OK, what have I done so far? I've followed your
instructions, to the letter, and created a new database
etc. Still no joy. The control txtProjectDescription
still appears to sit "behind" the control cboProjectID. I
cannot get it to sit "in front" of it.

I think this is the main cause of all my problems. What I
can't understand is, why your database does it OK and mine
doesn't. Right now, I am at work using Access 2000 and
teh database is still in A2000 format. However, this
blessed form still refuses to work.

1. I cannot get the text control to sit in front of the
combo control.

2. The text control still contains the value "#Name?" when
a new record is created.

3. When a new record is created and TAB is pressed to move
to the combo / text control, it would appear as though the
text control is obtaining focus. This would explain why I
can't use the first letter of the project name to move to
the first item in the list.

Many thanks for your patience and understanding

Duncan

--
Newsgroups are like one big sandbox that all of us
UseNet kiddies play in with peace & harmony.

Spammers, Cross-Posters, and Lamers are the
people that pee in our big sandbox.

-----Original Message-----
hi Duncan. if i understood correctly, you're saying that
the database i sent
you works "correctly", or as desired. but yours does not,
and you've either
configured it the same - or in some respects haven't been
able to get a
specific configuration to "stick".
so it sounds like the version-A2003/format-A2000 setup
we're using is the
same, and is working the same. my guess is that your
form, or your database
itself, is corrupted.
suggest you open a new database, and STOP. before you do
anything else, go
to Tools, Options, General tab and *uncheck* the box
beside "Track name
AutoCorrect info". click Apply, then OK. compact the
database.
next, import the two tables involved. import the form.
create a *new* form,
and copy all your controls, and the code from the VBE
window, into the new
form. make the configuration changes to the new form, so
it matches the
"working" form i sent you. delete the "old" form, and
compact the database.
then try out the new form.
post back with results.

hth


"Duncan Edment"
wrote in
message ...
Tina,

This is not working!!

I'm using A2003, saving the resulting file in A2000
format.

The first difference that I notice between your
database and mine, is that
your
text control--txtProjectDescription--appears "on top
of" the combo
control--cboProjectID. I also note, that in the Tab
Order, the text
control
appears before the combo control. This is exactly as
you said it should
be
done.

However, on mine, the text control is not shown when
the form is in design
mode.
It appears to be 'behind' the combo control. No matter
what I try, I
cannot get
the text control to stay on top of the combo control.

If I try to bring the text control to the front--via
Format|Bring To
Front--the
text control appears at the bottom of the TAB control
list. Moving it
back up,
to before the combo control, places it "behind" the
combo again.

Also, from looking at your database, I notice a
difference when a new
record is
created. When you press TAB after entering
the "TimeSheet ID", the cursor
is
placed in the combo control. With mine, it is placed
in the text control,
and
the text control contains the value "#Name?".

I've checked, double-checked & triple-checked all
properties, controls &
values
and they match what you have in your database, as well
as what you have
said in
previous posts. However, it still wont work.

Would you prefer if a sample copy of the database were
e-mailed to you, so
you
can have a look at it?

Many thanks

Duncan

--
Newsgroups are like one big sandbox that all of us
UseNet kiddies play in with peace & harmony.

Spammers, Cross-Posters, and Lamers are the
people that pee in our big sandbox.

well, at this point i am at a loss. i didn't to anything "special" to my
database; in case you're wondering if i used some magic - no. too bad i
didn't, actually, i could just share my secret with you and put us both out
of our misery! g
as for the text box getting the focus, in my form it does get the focus when
you tab from the previous field - and then the GotFocus event runs, deciding
whether it is appropriate to move the focus to the combo box, or leave it on
the text box. that's what creates the more-or-less seamless interface for
the user.
you asked in your post yesterday if i would take a look at your database. i
was hoping that my suggestions would work for you then; since you're still
stuck - yes, you can send your database to me if you want, and i'll take a
look.
make a copy of the database, and do the following *to the copy*:
1. delete any proprietary data, and enter a few "dummy" records.
2. *compact the database*.
3. zip to under 1 MB in size.
4. refer to the newsgroups in the email subject line, and email to me at
ttaccKILLALLSPAMess1 at yahoo dot com
removing all the CAPITAL letters.
today is friday, so if you send it today, i'll take a look and try to get
back to you by monday.


"Duncan Edment" wrote in
message ...
Tina,

Hello. It's me. Sore head...match-sticks holding my eyes
open...more coffee inside me than must be good for
me...still looking at this blessed form!

OK, what have I done so far? I've followed your
instructions, to the letter, and created a new database
etc. Still no joy. The control txtProjectDescription
still appears to sit "behind" the control cboProjectID. I
cannot get it to sit "in front" of it.

I think this is the main cause of all my problems. What I
can't understand is, why your database does it OK and mine
doesn't. Right now, I am at work using Access 2000 and
teh database is still in A2000 format. However, this
blessed form still refuses to work.

1. I cannot get the text control to sit in front of the
combo control.

2. The text control still contains the value "#Name?" when
a new record is created.

3. When a new record is created and TAB is pressed to move
to the combo / text control, it would appear as though the
text control is obtaining focus. This would explain why I
can't use the first letter of the project name to move to
the first item in the list.

Many thanks for your patience and understanding

Duncan

--
Newsgroups are like one big sandbox that all of us
UseNet kiddies play in with peace & harmony.

Spammers, Cross-Posters, and Lamers are the
people that pee in our big sandbox.

-----Original Message-----
hi Duncan. if i understood correctly, you're saying that
the database i sent
you works "correctly", or as desired. but yours does not,
and you've either
configured it the same - or in some respects haven't been
able to get a
specific configuration to "stick".
so it sounds like the version-A2003/format-A2000 setup
we're using is the
same, and is working the same. my guess is that your
form, or your database
itself, is corrupted.
suggest you open a new database, and STOP. before you do
anything else, go
to Tools, Options, General tab and *uncheck* the box
beside "Track name
AutoCorrect info". click Apply, then OK. compact the
database.
next, import the two tables involved. import the form.
create a *new* form,
and copy all your controls, and the code from the VBE
window, into the new
form. make the configuration changes to the new form, so
it matches the
"working" form i sent you. delete the "old" form, and
compact the database.
then try out the new form.
post back with results.

hth


"Duncan Edment"
wrote in
message ...
Tina,

This is not working!!

I'm using A2003, saving the resulting file in A2000
format.

The first difference that I notice between your
database and mine, is that
your
text control--txtProjectDescription--appears "on top
of" the combo
control--cboProjectID. I also note, that in the Tab
Order, the text
control
appears before the combo control. This is exactly as
you said it should
be
done.

However, on mine, the text control is not shown when
the form is in design
mode.
It appears to be 'behind' the combo control. No matter
what I try, I
cannot get
the text control to stay on top of the combo control.

If I try to bring the text control to the front--via
Format|Bring To
Front--the
text control appears at the bottom of the TAB control
list. Moving it
back up,
to before the combo control, places it "behind" the
combo again.

Also, from looking at your database, I notice a
difference when a new
record is
created. When you press TAB after entering
the "TimeSheet ID", the cursor
is
placed in the combo control. With mine, it is placed
in the text control,
and
the text control contains the value "#Name?".

I've checked, double-checked & triple-checked all
properties, controls &
values
and they match what you have in your database, as well
as what you have
said in
previous posts. However, it still wont work.

Would you prefer if a sample copy of the database were
e-mailed to you, so
you
can have a look at it?

Many thanks

Duncan

--
Newsgroups are like one big sandbox that all of us
UseNet kiddies play in with peace & harmony.

Spammers, Cross-Posters, and Lamers are the
people that pee in our big sandbox.

Thanks for the help Tina.

ZIP file on its way to you now.

Many, many thanks

Duncan

--
Newsgroups are like one big sandbox that all of us
UseNet kiddies play in with peace & harmony.

Spammers, Cross-Posters, and Lamers are the
people that pee in our big sandbox.

"tina" wrote in message
...
well, at this point i am at a loss. i didn't to anything "special" to my
database; in case you're wondering if i used some magic - no. too bad i
didn't, actually, i could just share my secret with you and put us both out
of our misery! g
as for the text box getting the focus, in my form it does get the focus when
you tab from the previous field - and then the GotFocus event runs, deciding
whether it is appropriate to move the focus to the combo box, or leave it on
the text box. that's what creates the more-or-less seamless interface for
the user.
you asked in your post yesterday if i would take a look at your database. i
was hoping that my suggestions would work for you then; since you're still
stuck - yes, you can send your database to me if you want, and i'll take a
look.
make a copy of the database, and do the following *to the copy*:
1. delete any proprietary data, and enter a few "dummy" records.
2. *compact the database*.
3. zip to under 1 MB in size.
4. refer to the newsgroups in the email subject line, and email to me at
ttaccKILLALLSPAMess1 at yahoo dot com
removing all the CAPITAL letters.
today is friday, so if you send it today, i'll take a look and try to get
back to you by monday.


"Duncan Edment" wrote in
message ...
Tina,

Hello. It's me. Sore head...match-sticks holding my eyes
open...more coffee inside me than must be good for
me...still looking at this blessed form!

OK, what have I done so far? I've followed your
instructions, to the letter, and created a new database
etc. Still no joy. The control txtProjectDescription
still appears to sit "behind" the control cboProjectID. I
cannot get it to sit "in front" of it.

I think this is the main cause of all my problems. What I
can't understand is, why your database does it OK and mine
doesn't. Right now, I am at work using Access 2000 and
teh database is still in A2000 format. However, this
blessed form still refuses to work.

1. I cannot get the text control to sit in front of the
combo control.

2. The text control still contains the value "#Name?" when
a new record is created.

3. When a new record is created and TAB is pressed to move
to the combo / text control, it would appear as though the
text control is obtaining focus. This would explain why I
can't use the first letter of the project name to move to
the first item in the list.

Many thanks for your patience and understanding

Duncan

--
Newsgroups are like one big sandbox that all of us
UseNet kiddies play in with peace & harmony.

Spammers, Cross-Posters, and Lamers are the
people that pee in our big sandbox.

-----Original Message-----
hi Duncan. if i understood correctly, you're saying that
the database i sent
you works "correctly", or as desired. but yours does not,
and you've either
configured it the same - or in some respects haven't been
able to get a
specific configuration to "stick".
so it sounds like the version-A2003/format-A2000 setup
we're using is the
same, and is working the same. my guess is that your
form, or your database
itself, is corrupted.
suggest you open a new database, and STOP. before you do
anything else, go
to Tools, Options, General tab and *uncheck* the box
beside "Track name
AutoCorrect info". click Apply, then OK. compact the
database.
next, import the two tables involved. import the form.
create a *new* form,
and copy all your controls, and the code from the VBE
window, into the new
form. make the configuration changes to the new form, so
it matches the
"working" form i sent you. delete the "old" form, and
compact the database.
then try out the new form.
post back with results.

hth


"Duncan Edment"
wrote in
message ...
Tina,

This is not working!!

I'm using A2003, saving the resulting file in A2000
format.

The first difference that I notice between your
database and mine, is that
your
text control--txtProjectDescription--appears "on top
of" the combo
control--cboProjectID. I also note, that in the Tab
Order, the text
control
appears before the combo control. This is exactly as
you said it should
be
done.

However, on mine, the text control is not shown when
the form is in design
mode.
It appears to be 'behind' the combo control. No matter
what I try, I
cannot get
the text control to stay on top of the combo control.

If I try to bring the text control to the front--via
Format|Bring To
Front--the
text control appears at the bottom of the TAB control
list. Moving it
back up,
to before the combo control, places it "behind" the
combo again.

Also, from looking at your database, I notice a
difference when a new
record is
created. When you press TAB after entering
the "TimeSheet ID", the cursor
is
placed in the combo control. With mine, it is placed
in the text control,
and
the text control contains the value "#Name?".

I've checked, double-checked & triple-checked all
properties, controls &
values
and they match what you have in your database, as well
as what you have
said in
previous posts. However, it still wont work.

Would you prefer if a sample copy of the database were
e-mailed to you, so
you
can have a look at it?

Many thanks

Duncan

--
Newsgroups are like one big sandbox that all of us
UseNet kiddies play in with peace & harmony.

Spammers, Cross-Posters, and Lamers are the
people that pee in our big sandbox.

I'd just like to say, my many thanks to Ken--yet again--to George Nicholson & to
Tina--especially to Tina.

Thanks a lot guys.

Duncan

--
Newsgroups are like one big sandbox that all of us
UseNet kiddies play in with peace & harmony.

Spammers, Cross-Posters, and Lamers are the
people that pee in our big sandbox.

"Duncan Edment" wrote in message
...
I have a Combobox on a form that lists project information. The Combobox is
setup as follows:

ControlSource = tblTimeSheet.fldProjectID
RowSource = SELECT tblProjects.fldProjectDescription, tblProjects.fldProjectID
FROM tblProjects
WHERE (((tblProjects.fldValidUntilDate)Date()));
BoundColumn = 2

The table, tblProjects, has the following fields:

fldProjectID = Number
fldProjectDescription = Text
fldValidUntilDate = Date / Time
fldIsWork = Yes / No

The basic theory is that each project has a Valid Until Date. After that
date,
no more time can be recorded against it.

What I want to do is, show the projects in the ComboBox when it is clicked.
However, if a projects ValidUntilDate is before the current system date--i.e.
now invalid--do not show it in the list for selection. however, it still
needs
to be displayed in the database, for those records where is has been entered
as
a valid project--i.e. two weeks ago, when it was a valid project code.

Is this possible? I did ask in access.forms, but the solution provided did
not
work. Can anyone help?

Many thanks & regards

Duncan

--
Newsgroups are like one big sandbox that all of us
UseNet kiddies play in with peace & harmony.

Spammers, Cross-Posters, and Lamers are the
people that pee in our big sandbox.

For the record, I apologize for not taking part in this thread after my
initial post. For some reason my newsreader didn't pick up any "responses"
until today.

--
George Nicholson

Remove 'Junk' from return address.


"Duncan Edment" wrote in
message ...
I'd just like to say, my many thanks to Ken--yet again--to George
Nicholson & to
Tina--especially to Tina.

Thanks a lot guys.

Duncan

--
Newsgroups are like one big sandbox that all of us
UseNet kiddies play in with peace & harmony.

Spammers, Cross-Posters, and Lamers are the
people that pee in our big sandbox.

"Duncan Edment" wrote in
message
...
I have a Combobox on a form that lists project information. The
Combobox is
setup as follows:

ControlSource = tblTimeSheet.fldProjectID
RowSource = SELECT tblProjects.fldProjectDescription,
tblProjects.fldProjectID
FROM tblProjects
WHERE (((tblProjects.fldValidUntilDate)Date()));
BoundColumn = 2

The table, tblProjects, has the following fields:

fldProjectID = Number
fldProjectDescription = Text
fldValidUntilDate = Date / Time
fldIsWork = Yes / No

The basic theory is that each project has a Valid Until Date. After
that
date,
no more time can be recorded against it.

What I want to do is, show the projects in the ComboBox when it is
clicked.
However, if a projects ValidUntilDate is before the current system
date--i.e.
now invalid--do not show it in the list for selection. however, it
still
needs
to be displayed in the database, for those records where is has been
entered
as
a valid project--i.e. two weeks ago, when it was a valid project code.

Is this possible? I did ask in access.forms, but the solution provided
did
not
work. Can anyone help?

Many thanks & regards

Duncan

--
Newsgroups are like one big sandbox that all of us
UseNet kiddies play in with peace & harmony.

Spammers, Cross-Posters, and Lamers are the
people that pee in our big sandbox.