auto-filled fields (1 Viewer)

[cbabi2]

Hi everyone, I have this table tblTeam with fileds namely staff no, staff name, code, role type, and gender...
codes are assigned to different role type in another table and role types corresponds to a certain gender which was defined in another table..
Can you help me so that the field roleType & Gender will autofilled on the form frmTeam which was link to tblTeam after I select the code on the form frmTeam.

Database attached for reference

Thanks

 

[Gasman]

Firstly I would not have spaces in field names? Use the captions for that.
I would also have the StaffName as a combo, so that I can bring in the required data as extra columns for the combo.
Then it is a simple matter of referring to each column on the AfterUpdate event of the combo.?

Otherwise use Dlookup() function in the same event for each item of data.

You are also using table lookups, which are not recommended. Just look for the data yourself.

 

[June7]

tblRoleMale and tblRoleFemale should be one table with another field for Gender. But don't these two tables just duplicate tblRoleCode?

Female cannot be Coach or Assistant Coach? Frankly, don't understand rationale for any of these gender-based role assignments.

Shouldn't RoleID in tblGender really be GenderID?

If you are not going to save designated PK values as foreign keys in related tables, it is meaningless to designate them as PK. Change the designations or use the keys as designated.

 

[cbabi2]

I now remove the spaces in the field names, however I cant change the table lookups to avoid mis typing since I am not the only one who will use this database. I am new on Access . I just want that when I am creating a new record thru the form I want it that when I finished selecting the code the gender and role type field will be autofilled as per data on tblRoleCode.

Thanks again

 

[Gasman]

I now remove the spaces in the field names, however I cant change the table lookups to avoid mis typing since I am not the only one who will use this database. I am new on Access . I just want that when I am creating a new record thru the form I want it that when I finished selecting the code the gender and role type field will be autofilled as per data on tblRoleCode.

Thanks again

So use DlookUp() ?
However do pay attention to June7's advice.

 

[June7]

In textboxes on form, enter these expressions as ControlSource:

=[Code].[Column](1)

=[Code].[Column](2)

This will display data from combobox columns but will not save to table. Should not even save into tblTeam since that is duplicating data between tables. Build queries that join tables to retrieve related data and output onto a report. This is the reason for primary and foreign keys - which I have already pointed out you have not properly assigned.

Why would the case of multiple users prevent you from making design changes to the lookups? Won't perform any differently from user point of view. BTW, should not build lookups in table anyway, although since your lookups aren't saving the PK and displaying alias value, not so bad.

 

[cbabi2]

Hi guys,

please bear with me as I am just new with access and I dont understand the comments or may maybe I have not told clearly what I really want for the db.....

1. the form shall be used only on creating new records so I am confused of why do I have to make it a combo box.... on this this form my aim is to just let the user key-in the staff number and the name, the code can be selected from pre-defined list to avoid entering code which is not on the tblRoleCode....after selecting the role code on the form, the fields role type and gender will be auto filled with data assigned to the selected code to avoid keying the wrong gender/role type on the code.

ex. if I select CDR on the field "Code", I want the field "Role Type" auto filled with "Cheerleader" and the field "Gender" autofilled with "Female"...I dont want to happen that the code CDR will have a Role Type "Coach" and "Male" Gender.

I dont mean to offend to the gender based role but this is just a sample database and this male/female/common is 1st thing pops on my head to make it like this for me to easy identify seperations in data like car should be on land, boat should be on waters and hovercraft can go both...really no offense on that..

2. Aside for the tblRoleCode there are tblRoleMale & tblRoleFemale because I am planning next that on the form, if you skip the code and go first to select the gender like female, the only thing that comes available to select for code or role types are those assigned to females only

ex. if these db is for mens basketball team, if I choose the gender first as female, then the role "player" will not be available on the role type selection and code PLY will not be available on the code selection and so the other male roles.


Thanks again for your responses

 

[June7]

1. If you want the combobox list to be limited by the gender of individual (explore topic of cascading combobox) and this is a new record, then someone must input that gender for that new person.

2. So why would you say you want the gender automatically displayed based on Code selection? I gave you suggestion for this.

3. Your statements of what you want are contradictory. Why design for 2 when ultimate goal is 1?