List box in subform

[Saylindara]

I wonder if there's anyone else out there working on a database on a Sunday
afternoon (again).

I have a main form Event and a subform EventJobTitleStatus. At present I
have a combobox on the subform for the JobTitle and another for the Status
and this works fine. But really as I have to put the event Status against
every job title (and there are lots of them) it would be better if I had the
job titles in a list always on the subform and a combobox to enter the status
against each job title. Is that possible? List box didn't work.

 

[tina]

not quite sure of what you're doing here. are you saying that each event has
a status, and you're entering that status (the same identical status) for
each job title associated with the event? if so, then i'd say that status is
a description of the event, not of associated job titles, and should be
stored in the event table - or in its' own child table, if you need to store
historical data as the status of an event changes.

or do the various job titles, associated with a single event, each have a
specific status that may differ from the status of other job titles linked
to the same event? please clarify the situation.

hth

 

[Saylindara]

Thank you for replying. Your second paragraph is correct. The event can be
classified as mandatory, desirable, optional or inapplicable depending on the
job title. This data is then used to calculate mandatory events per employee
etc. It all works very well having a combobox for the job title and another
for the status but it is very tedious to do and there is the danger that a
job title could be missed. The job title also has a profession associated
with it and at present I have a text box linked to the job title combo box
which also works well and I would like that as well if it is possible to have
a list instead.

 

[tina]

okay, i think i understand where you're going with this. if all event
records have the same associated job title records - for example, every
event has related child records for job title a, b, c, d, and e - then i
would suggest auto-populating the subform with job title records, each time
a new event record is created in the mainform. then just tab into the
subform and assign a status to each existing job title record. you can do
this by running an Append query that adds records to the event job titles
table, using the primary key of the current event record in the mainform.
then just requery the subform, and viola - there's your list, ready for
status assignments.

hth

 

[Saylindara]

That sounds exactly what I need. I've never tried an append query so I'll
read up on it and then give it a go. Thanks very much for your help.

 

[Ron2006]

That sounds exactly what I need. I've never tried an append query so I'll
read up on it and then give it a go. Thanks very much for your help.





- Show quoted text -

A suggestion is to have a separate table of just those job titles. The
append query would read that table as it's primary table and append
new records to your related child table using the job title table for
the titles and passed information for the foreign key(s) necessary to
make this set of child records related to the master record. That way
you can add new job titles whenever needed.

 

[Saylindara]

I have got a separate table for the job titles. The subform is from a link
table EventJobTitleStatus. I have comboboxes for the job title from the
JobTitle table and status from the Status table. Event is the main form. If
the append query puts the job titles on the subform I will still need the
Status combobox to assign the event status to each job title. So I'm hoping
that's the way it works.

 

[tina]

no reason why it shouldn't, hon. i have a setup that does the same thing,
which i use every day at work. and you're welcome

 

[Saylindara]

I created the append query to attach Job Titles from the JobTitle table to
the link table and that worked. Then I created the subform using the link
table. If I open the subform on its own all the job titles are listed and I
can use a combobox to asign the status. When I open the main form though
neither the list of job titles shows on the subform nor the status I assigned
to them. What am I doing wrong?

 

[tina]

when you append the records into the link table, make sure you're including
the foreign key, which is the primary key value of the mainform record.
also, you need to make sure you've properly related the tables in the
Relationships window, and enforced referential integrity in the links. and
finally, make sure the subform is properly linked to the mainform, via the
LinkChildFields and LinkMasterFields properties of the subform control
within the mainform.

hth

 

[Saylindara]

It works perfectly now thank you

when you append the records into the link table, make sure you're including
the foreign key, which is the primary key value of the mainform record.
also, you need to make sure you've properly related the tables in the
Relationships window, and enforced referential integrity in the links. and
finally, make sure the subform is properly linked to the mainform, via the
LinkChildFields and LinkMasterFields properties of the subform control
within the mainform.

hth

 

[tina]

you're welcome

It works perfectly now thank you