Combo box with same table source

[JIM]

I have a job form where major jobs are added and closed on our system. The
form is based on tblJobs. Management wants the ability to add several
releases of the same Job Number. The Job Number and Release number are the
primary key. For example, a job number might be 020115 (02-February, 01-1st
job in Feb, 15-not sure); and release number is to be assigned
sequencially(i.e. 00, 01, 02, etc.) Both key fields are text so leading 0's
show. I got the bright idea I would make the JobNo control a combo box and
assign its table/query source to tbljob. the same table that is the form's
source. Now Access won't let me do this and I was wondering how to get
around this. My goal is to call up the original Job and use DMax and
programmatically update the release number so to have a unique primary key
and also to fill all the controls on the form with the original job info. Is
there a way to do this?

 

[tina]

first, what version of Access are you using? second, "Access won't let me do
this" is pretty vague; how about telling us specifically what is happening:
are you getting an error message? is Access shutting down? or does the
system freeze? or does the combobox simply not respond to input? is the
combobox control bound to a field in the form's RecordSource (have a
fieldname in its' ControlSource property)? details, give us details!

hth

 

[JIM]

Thanks Tina for quick response. I'm using A2003 and actually not sure what
has changed but at least now Access is not crashing when combo box is added.
I was getting several message like "Access has to close now" and then Access
would do an automatic repair and compact. The combobox control is bound to a
field in the form's RecordSource and the field is part of a two field primary
key.
I'm am now trying to change property "LimitToList" to no and Access will not
allow it. The message I'm getting is:
"MS Office Access can't set the limittolist property to no right now. The
first visible column, which is determined by the column widths property,
isn't equal to the bound column. Adjust the column widths property first,
and then set the limittolist property.
I've played with everything I can think of and compared it with other combo
boxes I'm using to no avail. Any sugesstions?
TIA

 

[tina]

i tried twice to write out an explanation, what a mess. this, for me, is one
of those "wave your arms and point" situations - hard to do in print. let me
try an example: you have the following table, as

tblCities
CityID (primary key, Autonumber)
CityName

you have a tblAddresses to store addresses, with a City field - data
typeNumber, field size Long Integer, to match the Autonumber of the CityID
in tblCities. you create a form, bound to tblAddresses, to enter and edit
address records. you add a combobox control to the form, cboCities, and bind
it to the City field (the City fieldname is in the control's ControlSource
property). you set the combobox's RowSource property to

SELECT CityID, CityName FROM tblCities ORDER BY CityName;

the combobox's other relevant properties are set as
ColumnCount: 2
BoundColumn: 1
ColumnWidths: 0"; 1"

so as you enter an address in the form, when you look at the droplist of
cboCities, all you see are city names in ascending order. and when you
review existing records in the form, what you see in the combobox control is
a city name. BUT, what's being stored in the City field of tblAddresses is a
numeric value, the CityID value from tblCities.

now think about this setup a minute. what would happen if you set the
combobox control's LimitToList property to No? you'd try to enter a city
name that wasn't in tblCities, and you'd be entering a text value, like
"Cleveland". BUT the City field in tblAddresses is a Number data type, so a
text value will err out every time. the only way for another user to know
that a number value was being saved in the City field would be to make the
BoundColumn visible, by changing its' ColumnWidth from 0" to a measurable
width.

when you understand the mechanics of how the combobox control works, and
that it is built that way to support the underlying relationships between
the fields in a parent table (tblCities.CityID) and it's related child table
(tblAddresses.City), then it makes sense that Access wouldn't want you to be
able to enter an "unsupported" value, when you can't see the existing values
in the bound column of the combobox. in the example, how would you know that
CityID and City fields are Number data type, if all you see is text
(CityName)?

so you have to ask yourself 1) why am i using combobox controls for the
primary key fields in this data entry form - is it appropriate? and 2) if it
is, why do i want to enter a value that isn't in the list returned by the
RowSource of the combobox?

hth

 

[JIM]

Thanks for the great explanation. What you said made sense. I had recently
used combo boxes with a value list and so hadn't really thought it thru. One
of the problems was that I was writting my own select statement that included
a memo field. I read on a post to always use the wizard first and make that
work then create your own combo box from scratch if desired. So I used the
wizard to create my combo box using option three and then selected the fields
I wanted and you can guess it - the memo field was not available for
selection. That also makes sense. That's what was making Access crash I
think. Anyway what I ended up doing is making two fields: the combo to do
the search and the textbox to receive the data in the case that the combo box
is used. I put some code behind the afterudate of the combo box and it's
working perfect. Thanks again for spending the time to explain, it was
helpful. By the way, I always try click on the button that says post was
helpful but there is never a check by my posts. Is there something I'm
unaware of? I answered your last post that it answered my question.

 

[tina]

you're welcome, i'm glad it was helpful - even without the arm-waving and

By the way, I always try click on the button that says post was
helpful but there is never a check by my posts. Is there something I'm
unaware of? I answered your last post that it answered my question.

i don't know, hon; i use Outlook Express to connect to the newsgroups, not
the website, so i'm not familiar with the user interface. but don't worry
about it. whether or not my post was helpful only matters to you, to me, and
to anyone else who reads the thread hoping to find something they can apply
to their own situation. and those third parties, reading behind us, have to
decide if what they read helps them or not, individually.