Lost on using ComboBox with subform (Access 2003)

[Ann Scharpf]

I am very novice at using forms, events etc. So I've been reading the posts
here about using combo boxes and subforms and I'm pretty lost.

I created a query to list EmployeeName and TerminationDate from my Employees
table. (Did this because I saw it was the way to sort in alpha order for my
combo box. The Employees table is listed in EmployeeNumber order.)

I have a subform that shows the employee's project/hour assignments. I want
to have them come up so I can zero them out. I related the two with the
EmployeeName, which is in both the query and the subform. (I know that you
could probably write code to find the records and zero out the hours but I
wouldn't have a clue how to do that. I THOUGHT I could tackle this subform
thing...)

When I select the EmployeeName in the combo box, it is not updating the
records that display in the subform. They stick with the assignments for the
employee who appears first alphabetically in the EmployeeTable.

If it is not too much trouble, could someone please explain ... like you're
showing this to a six year old! ... how to make this work?

Thank you so much for your help.

 

[John W. Vinson]

I am very novice at using forms, events etc. So I've been reading the posts
here about using combo boxes and subforms and I'm pretty lost.

I created a query to list EmployeeName and TerminationDate from my Employees
table. (Did this because I saw it was the way to sort in alpha order for my
combo box. The Employees table is listed in EmployeeNumber order.)

I have a subform that shows the employee's project/hour assignments. I want
to have them come up so I can zero them out. I related the two with the
EmployeeName, which is in both the query and the subform. (I know that you
could probably write code to find the records and zero out the hours but I
wouldn't have a clue how to do that. I THOUGHT I could tackle this subform
thing...)

When I select the EmployeeName in the combo box, it is not updating the
records that display in the subform. They stick with the assignments for the
employee who appears first alphabetically in the EmployeeTable.

If it is not too much trouble, could someone please explain ... like you're
showing this to a six year old! ... how to make this work?

Thank you so much for your help.

It all starts with the tables... what tables do you have? what are the
relevant fieldnames and datatypes? How are the tables related?

If you're linking table by employee name, and particularly if you're using the
employee name as the primary key of the employee table, you're on the wrong
track. Primary Keys should be unique, stable (not changing over time), and
ideally short; people's names fail on all three accounts. You should have a
unique EmployeeID and fields for LastName, FirstName, MiddleName, Suffix
instead.

 

[Ann Scharpf]

Hi, John:

Actually, names are ok to use here. The timekeeping system that we use will
change the records for the current year if an employee's name changes. And,
since I'm doing funding execution analysis, I never have to go back to prior
years. (The prior year sits as its own archived database. I can still use
it to look things up but all the funding codes change from year to year, so
there is no crossover from one fiscal year to the next.)

The form I have that uses two tables works A-OK. The problem I'm having
only occurs in the second form where I use a query in the form and a table in
the subform. My query is pulling only two fields from the Employee table:
The EmployeeName (so I can use the combo box to find the record) and the
TerminationDate (so I can enter the date). I want the names to be sorted
alphabetically and a post I saw today said I needed to use a query to do that.

I don't have the EmployeeNumber in the VendorAssignments table and, in three
years, this has never caused a problem. So I'd like to leave that as is.

Is there some trick to relating the fields between a query in the main form
and a table in the subform? I saw a bunch of posts about requerying but I
didn't understand them.

 

[Ann Scharpf]

And, BTW, I do have the EmployeeNumber as the key to the Employee file. I
don't have the name separated into separate fields because I am working with
dumps from a timekeeping system which gives me the employee's name as a
single field.

 

[John W. Vinson]

Hi, John:

Sorry, Ann! I just blew by your name. Should have said hi and tempered my
answer.

Actually, names are ok to use here. The timekeeping system that we use will
change the records for the current year if an employee's name changes.

Well... I'm John W. Vinson. I once worked as a postdoc at the same university
as med school professor John W. Vinson. He got one of my paychecks; I got one

And,
since I'm doing funding execution analysis, I never have to go back to prior
years. (The prior year sits as its own archived database. I can still use
it to look things up but all the funding codes change from year to year, so
there is no crossover from one fiscal year to the next.)

The form I have that uses two tables works A-OK. The problem I'm having
only occurs in the second form where I use a query in the form and a table in
the subform. My query is pulling only two fields from the Employee table:
The EmployeeName (so I can use the combo box to find the record) and the
TerminationDate (so I can enter the date). I want the names to be sorted
alphabetically and a post I saw today said I needed to use a query to do that.

Well? Simply base the Combo Box on a Query with a sort on the name. Shat
specific problem are you having???

I don't have the EmployeeNumber in the VendorAssignments table and, in three
years, this has never caused a problem. So I'd like to leave that as is.

Is there some trick to relating the fields between a query in the main form
and a table in the subform? I saw a bunch of posts about requerying but I
didn't understand them.

No requery should be needed. You just need a matching field of matching
datatype. If the employee name is in both tables (in the same size text field
in the same format), you can just use that field as the Master Link Field and
Child Link Field; no code needed at all.

 

[John W. Vinson]

Is there some trick to relating the fields between a query in the main form
and a table in the subform?

p.s. if the combo box on the mainform is unbound (nothing in its Control
Source) you can use the name *of the combo box itself* in the Master Link
Field property of the subform.