Need some help with passing data through relationships

[Adiankur via AccessMonster.com]

My problem is as follows. I have a 9 table database, with 4 tables just
holding the various codes that are single entities. The tables I have that
are involved in the following problem are: Staff, Clients, Staff positions,
Client authorizations and staff assignments.
Staff positions exist because one staff person can hold several different
positions at one time, and I added that table rather than have position 1,
salary 1, position 2 salary two in the staff table.
Staff is linked to staff positions and staff assignments.
Clients is linked to authorizations
authorizations is linked to staff assignments
Staff--staff positions---Staff assignments---authorizations---clients
--->>>>
<<<----
Client names and staff names reside only on their individual tables, but when
I want to fill in details for staff assignments, its difficult to know what I
am assigning, as the the staff positions and authorization tables only hold
codes and not the names.
Staff is linked to position by employee#
Staff position is also linked to assignments by employee number, I could
directly link staff and assignments I guess.
clients linked to auth by client id, auth linked to staff assignments by auth
auto number.
Staff assignment has just Auto number, Employee#, and Auth ID auto number
How can I set this up so I am choosing names on the assignment table, but its
only filling in the employee number and auth id auto number from their
respective tables?
thanks for your time and patience, and if you have some information, thanks
for that as well.

 

[Steve Schapel]

Michael,

A common way to do this is on the form you are using for data entry, use
a combobox for the EmployeeNumber (for example). You can control the
behaviour via the properties of the combobox, for example put the Staff
table as the combobox's Row Source, and assuming the first two fields in
this table are EmployeeNumber and EmployeeName, you would set the
combobox's Column Count property to 2, Bound Column to 1, and Column
Widths to 0;4 (the 4 is a guess... whatever width you newed in order to
show the name adequately). Hope that makes sense. Then, the combobox's
drop-down list will show the names, and the selected entry in the
combobox will show the name, but the data entered behind the scenes into
the table will be the ID. Hope that makes sense.

Another approach is that often this sort of data would be getting
entered on a continuous view subform, which is a subform of the Staff
form. So in that case, setting the Link Master Fields and Link Child
Fields properties of the subform to the EmployeeNumber field, you won't
even need to enter the employee, as it will be automatically entered for
you, according to the current Staff record on the main form.

 

[Adiankur via AccessMonster.com]

Thank you very much. That is exactly what I was looking for and I will
implement that immediately.

Michael,

A common way to do this is on the form you are using for data entry, use
a combobox for the EmployeeNumber (for example). You can control the
behaviour via the properties of the combobox, for example put the Staff
table as the combobox's Row Source, and assuming the first two fields in
this table are EmployeeNumber and EmployeeName, you would set the
combobox's Column Count property to 2, Bound Column to 1, and Column
Widths to 0;4 (the 4 is a guess... whatever width you newed in order to
show the name adequately). Hope that makes sense. Then, the combobox's
drop-down list will show the names, and the selected entry in the
combobox will show the name, but the data entered behind the scenes into
the table will be the ID. Hope that makes sense.

Another approach is that often this sort of data would be getting
entered on a continuous view subform, which is a subform of the Staff
form. So in that case, setting the Link Master Fields and Link Child
Fields properties of the subform to the EmployeeNumber field, you won't
even need to enter the employee, as it will be automatically entered for
you, according to the current Staff record on the main form.

My problem is as follows. I have a 9 table database, with 4 tables just
holding the various codes that are single entities. The tables I have that

[quoted text clipped - 24 lines]

thanks for your time and patience, and if you have some information, thanks
for that as well.

 

[Adiankur via AccessMonster.com]

Actually, another thought on this is if it will work when the relationships
run through a couple tables? will I need to use multiple subforms when it
comes to client information being a couple tables away or should I just have
the subform containing another subform?

Thank you very much. That is exactly what I was looking for and I will
implement that immediately.

Michael,

[quoted text clipped - 22 lines]

 

[Steve Schapel]

Michael,

You can put a subform on a subform, but in this case the first subform
can't be in continuous view, it has to be single view (one record at a
time) so you have to scroll through the records. Another approach is to
put both subforms on the main form, and an unbound textbox on the main
form, with it's Control Source referring to the ID field of the first
subform. You can then set the Link Master Fields property of the second
subform to this textbox. That way, the first subform can be continuous
view, but the second subform will show the records related to the
current record on the first subform.

Hope that helps. It's a bit complicated to talk about in general terms,
without a specific example.