Combo Box Refuses Choice

[PlarfySoober]

I have a combo box that doesn't return the choice made by the end user.

There are two related tables, Applicants and JobTitle. Applicant's key field
is <Badge> and JobTitle's key field is <JobTitle>. Both fields are text-type.

Applicant is dependent on JobTitle. No index (I used to program a bit in
dBase, where indexing was highly important, but haven't come across what it's
for in Access, since Keys seem to be what indexes were in dBase).

Data input is focused on Applicants, and when I get to the combo box for
JobTitle (either inputting direct to the table or through a form), whatever I
choose, I get an inconsistent result in that Utilization Manager will give me
Claims Examiner, etc.

In case it's important, in displaying the combobox, for whatever reason it
displays another column (<ReportsTo>), next to the <JobTitle> column, and the
second column obscures most of the column I actually want, <JobTitle>.
Probably irrelevant and a different problem. But in the spirit of
thoroughness, and I have no idea what is relevant and what is not.

I hope someone can help with this. It's the third time I have posted the
problem, with no solution. How can I get the combobox to return the choice
made by the user?

Thanks.

Don.

 

[KARL DEWEY]

A silly question - is the field in the table where the selection is stored a
lookup field?

If so, then one lookup is different from the other.

The table for the combo to select Utilization Manager probably has a 5 but
that is the number Claims Examiner in the other table.

 

[PlarfySoober]

Karl,

Thanks for the reply.

It appears from your reply that I am completely off base here. The Applicant
table's <JobList> field is text. So is the <Joblist> field in the JobList
table. Applicant, except for the combobox is pretty straightforward. Joblist
table is a list of job titles, each of which is a record with a couple of
other columns, notably a <ReportTo> field.

It sounds like I need to make one of these tables a bit more exotic than I
have.

Actually, it sounds like I am supposed to put all the job titles in one
record, separated by, maybe, commas, trying to read between the lines of your
response. Is that the case?

Don.

 

[KARL DEWEY]

put all the job titles in one record, separated by, maybe, commas
No.

You did not answer my question -- is the field in the table where the
selection is stored a lookup field?
Open the table in datasheet view (not the normal way to view data) and see
if the field is a pulldown. If it is then that might be the problem.

I would like for you to post the table structure of both tables. Table and
field names and their datatype.

Post sample data from both.

 

[PlarfySoober]

Karl,

Thanks for following up. If I understand your directions (from another
thread on the same topic that sort of died), you need the first line of each
table copied and inserted. Here is the Applicant table:

Badge PositionAppliedFor Last Name First
Name ReceivedDate InterviewDate Email Address City Postal Code Home
Phone Work Phone Fax
Number SourceName ResumeHighlights GradeResume GradeEducation GradeExperience GradeResidence GradeReliability GradeImpression Explanation CommunicatedInterest CommunicatedRejection PhoneNotes
achau Claims Coordinator ****
Anna 1/5/2010 A****[email protected] Westminster 92683 714-***-**** CSULB
EDUCATION Cal State University, BS Health Care Administration
Expected Graduation: Long Beach BA Economics December
2009 5 9 4 7 5 7 "Great education, not much work experience.

And here is the data from the JobList table:

JobTitle Level Manager Management_Level
Associate Medical Director 02 Medical Director Yes

Somehow, I think this is NOT what you wanted, but it's my understanding.

Don.

Experience in communication with students, others." 1 /5 /2010 "1/5/10
Received phone call. 714-360-7233
1/5/10 Received phone call: Made appt. 1/6/10"

 

[PlarfySoober]

Ken,

Thanks for the reply.

First, a clarification. You're right, but the names are JobTitle, for the
table, and JobTitles, for the field.

Some of what you say is a bit over my head, but:

You were right, in the RowSource attribute, I had all the fields SELECTed.
Took all out except JobTitle and made sure ORDER was JobTitle. Now I get a
blank in my form where the ComboBox appears. Oddly, it shows two columns,
both blank. Here is the RowSource Statement:

SELECT JobTitles.JobTitle FROM JobTitles ORDER BY [JobTitle]; or,
SELECT JobTitles.JobTitle FROM JobTitles ORDER BY JobTitles.JobTitle;

When I use the RowSource that calls for displaying ALL the fields, I get
text in the ComboBox, but if I limit it to display JobTitle, it is blank with
two columns showing. Probably, it would show more columns if the form's field
were wider.

Appears I indexed the JobTitle field, non-unique.

Don.

The corresponding field in the Applicants table is PositionAppliedFor. You
are saying it should be indexed non-uniquely

No, you certainly don't want multiple title in one row; that's very bad
design because the table is not then in First Normal Form which requires each
row in a table to have only one value of each attribute. Your present table
structures sound OK. It could be argued that a 'surrogate' numeric
JobTitleID as the key of the JobList table has advantages, but assuming all
job titles are unique string expressions, there is no bar to using a
'natural' key as you are doing.

I'm assuming that by 'JobList field' in your last post you mean the JobTitle
field you referred to in your first post, so for this example I'm assuming
the table is JobList and the column (field) is JobTitle and this is the
primary key of JobList. In which case the JobTitle combo box on the form
based on the Applicants table would be set up as follows:

ControlSource: JobTitle

RowSource: SELECT JobTitle FROM JobList ORDER BY JobTitle;

You should then be able to select a job title from the combo box's list as
the value for the JobTitle field in the current record.

The reason that you are getting the inconsistent results might be due to the
inclusion of the ReportsTo column in the combo box's list. If there are
multiple job titles associated with the same ReportTo value, its possible,
depending on how the combo box has been set up, that selecting one job title
will in fact select the first job title in the list associated with the
ReportTo value with which the selected one is associated. By returning only
the JobTitle column in the combo box's RowSource as described above, this
should be avoided.

BTW the JobTitle column in the Applicants table should be indexed non-
uniquely (duplicates allowed). The JobTitle column in Applicants is a
foreign key referencing the primary key JobTitle column of JobList. A
primary key column is indexed uniquely automatically by virtue of its being
defined as the primary key. The corresponding foreign key column in a
referencing table should be indexed non-uniquely to enhance performance.

Ken Sheridan
Stafford, England

Karl,

Thanks for the reply.

It appears from your reply that I am completely off base here. The Applicant
table's <JobList> field is text. So is the <Joblist> field in the JobList
table. Applicant, except for the combobox is pretty straightforward. Joblist
table is a list of job titles, each of which is a record with a couple of
other columns, notably a <ReportTo> field.

It sounds like I need to make one of these tables a bit more exotic than I
have.

Actually, it sounds like I am supposed to put all the job titles in one
record, separated by, maybe, commas, trying to read between the lines of your
response. Is that the case?

Don.

A silly question - is the field in the table where the selection is stored a
lookup field?

[quoted text clipped - 31 lines]

Don.

--
Message posted via AccessMonster.com


.

 

[PlarfySoober]

Ken,

Right! and good work. I got rid of the columns, column count was as you
said, wrong, but I still have one (yay) blank column. The indexes were as you
suggested.

I count this as progress, thanks.

Don.

Sounds to me like you might have the other properties of the combo box not
quite right. Check that these properties are set up as follows:

Bound Column: 1
ColumnCount: 1
ColumnWidths: Leave blank

Either of the two RowSource properties you used should work. Assuming the
JobTitle values are all different in the JobTitles table, however, it should
be indexed uniquely (no duplicates) in the JobTitles table, but non-uniquely
in the Applicants table (duplicates allowed).

Ken Sheridan
Stafford, England

Ken,

Thanks for the reply.

First, a clarification. You're right, but the names are JobTitle, for the
table, and JobTitles, for the field.

Some of what you say is a bit over my head, but:

You were right, in the RowSource attribute, I had all the fields SELECTed.
Took all out except JobTitle and made sure ORDER was JobTitle. Now I get a
blank in my form where the ComboBox appears. Oddly, it shows two columns,
both blank. Here is the RowSource Statement:

SELECT JobTitles.JobTitle FROM JobTitles ORDER BY [JobTitle]; or,
SELECT JobTitles.JobTitle FROM JobTitles ORDER BY JobTitles.JobTitle;

When I use the RowSource that calls for displaying ALL the fields, I get
text in the ComboBox, but if I limit it to display JobTitle, it is blank with
two columns showing. Probably, it would show more columns if the form's field
were wider.

Appears I indexed the JobTitle field, non-unique.

Don.

The corresponding field in the Applicants table is PositionAppliedFor. You
are saying it should be indexed non-uniquely

No, you certainly don't want multiple title in one row; that's very bad
design because the table is not then in First Normal Form which requires each

[quoted text clipped - 60 lines]

Don.

--
Message posted via AccessMonster.com


.

 

[PlarfySoober]

Ken,

Thanks again. I was not clear, I think.

I now have a dropdown with only one column, so far so good, but no contents
shown. So although there is only ONE column, there is nothing there.

Don.

Don:

So, the combo box's list is now showing the correct values and the selected
value is being inserted into the field in the underlying table, but you see
an extra blank column in the list? Provided the ColumnCount property is 1 I
can't at first sight see why that should be, I'm afraid.

Ken Sheridan
Stafford, England

Ken,

Right! and good work. I got rid of the columns, column count was as you
said, wrong, but I still have one (yay) blank column. The indexes were as you
suggested.

I count this as progress, thanks.

Don.

Sounds to me like you might have the other properties of the combo box not
quite right. Check that these properties are set up as follows:

[quoted text clipped - 45 lines]

Don.

--
Message posted via AccessMonster.com


.

 

[PlarfySoober]

Ken,

NOW we're getting there. When I changed the Column width (which was still
set up for three columns, it looked like) to blank, the contents showed up.

And, thanks to you, I can now select Positions to correspond to Job Titles,
just as I wished to.

Thanks very much.

Don.

Don:

Weird! Lets reiterate. The properties of the combo box should be:

RowSourceType: Table/Query

RowSource: SELECT JobTitles.JobTitle FROM JobTitles ORDER BY JobTitles.
JobTitle;

BoundColumn: 1
ColumnCount: 1
ColumnWidths: Left blank

Its ControlSource property should be whatever is the name of the foreign key
field in the Applicants table into which you want to enter the selected title
(Position?). The form's RecordSource should be the Applicants table or an
updatable query based on it.

Provided the JobTitles table has values in its JobTitles column you should
see those listed alphabetically in the combo box's drop down list.

Ken Sheridan
Stafford, England

Ken,

Thanks again. I was not clear, I think.

I now have a dropdown with only one column, so far so good, but no contents
shown. So although there is only ONE column, there is nothing there.

Don.

Don:

[quoted text clipped - 21 lines]

Don.