Combine First & Last Name for Combo Box

[Huddle]

I know how to combine the first and last name in a form but I need it combined in the table so I can use it in a drop down combo box.



I used SELECT tbl_Facilities_July2010.[Inspector First Name], tbl_Facilities_July2010.[Inspector Last Name] FROM tbl_Facilities_July2010; in the value list row source in the look up section of my new field "Inspector Full Name".

I pull this listing from an outside source where the names come in seperate and don't want to have to change the data of the table everytime.

I don't necessarily need the fields combined for the table but I need it combined for the drop down box. Is there another way to do this?

I need to have both names bound in the table so I can't use it like a normal combo.

[jzwp11]

You would create a query that concatenates the first and last names (you would not do it in the table) and base the combo box on that or amend the row source of the combo box directly. The row source of a combo box is a query

The row source type of the combo box must be set to table/query not value list. A value list implies that you type the actual names as a list in the row source property.

SELECT tbl_Facilities_July2010.[Inspector First Name] & " " & tbl_Facilities_July2010.[Inspector Last Name] As [Inspector Full Name] FROM tbl_Facilities_July2010

I need to have both names bound in the table so I can't use it like a normal combo.

I'm really not sure what you are trying to say here.

[Huddle]

I think I'm totally off on this so let me explain what I want to do and you can give me direction on how to accomplish it.

In Tbl_WorkOrder2 I have the following field: BldgNumber, Commander & InspectorFullName (which I can use InspectorFirstName & InspectorLastName just as well). plus additonal fields that do not partain to this task.

In Tbl_Facilities I have: FacilityNumber, InspectorFirstName, InspectorLastName, AreaCommander, plus additional fields

On Fm_WorkOrder2 I want to be able to put in the building number and the inspector's and commander's names autofill.

I created Q_BuildingAutoFill by connecting Tbl_Facilities and Tbl_WorkOrder2 by the FacilityNumber and BldgNumber bringing down the BldgNumber, FacilityNumber, AreaCommand, InspectorLastName, and InspectorFirstName.

I currently have the form record source as tbl_WorkOrder

Once the data is entered I will need to export the data in Tbl_WorkOrder2 to an excel sheet showing the inspector's and area commander's name, as well as the other data.

So where do I go from here or where do I need to backup to?

[jzwp11]

Do you have two work order tables? You mention Tbl_WorkOrder2 and tbl_WorkOrder. Why would you need 2?

If a building is joined to a facility and a work order applies to a building then you only need to join the work order and building (you can then bring everything together in a query). Can a work order apply to many buildings within a facility? Can a work order apply to many facilities?

As to inspectors and commanders, these are people. You should have a table of people and just reference the pkPeopleID as a foreign key where necessary. There is no need to relist the person's name in the other tables.

tblPeople
-pkPeopleID primary key, autonumber
-txtFName
-txtLName
-fkRoleID Foreign key to tblRoles

tblRoles (inspector, commander etc.)
-pkRoleID primary key, autonumber
-txtRoleName

I don't know how the people (commander and inspector) fall into your application, but if the commander is a person who runs the facility and the inspector is also tied to a facility then that describes a one-to-many relationship between the facility and the people.

If the inspector is only tied to a work order then that is where the fkPeopleID reference should go. If you have more than one person tied to a workorder than you will need a separate but related table.



You would then use a query to bring all of the information together and export that to Excel.

[Huddle]

Do you have two work order tables? You mention Tbl_WorkOrder2 and tbl_WorkOrder. Why would you need 2?

There is just one. I had backed it up using WorkOrder2 when I was making a big change. I have changed everything back to WorkOrder.

If a building is joined to a facility and a work order applies to a building then you only need to join the work order and building (you can then bring everything together in a query). Can a work order apply to many buildings within a facility? Can a work order apply to many facilities?

The building is the facility. The facility list is imported and that is what they named it so I wanted to keep it named that. Building is what I use in my main table and form. Each WorkOrder would only apply to one building/facility.

As to inspectors and commanders, these are people. You should have a table of people and just reference the pkPeopleID as a foreign key where necessary. There is no need to relist the person's name in the other tables.

The inspectors and commanders on already on the facility list that is imported. I want to use that import as the facility lookup table becuase it may change as they add facilities.

tblPeople
-pkPeopleID primary key, autonumber
-txtFName
-txtLName
-fkRoleID Foreign key to tblRoles

tblRoles (inspector, commander etc.)
-pkRoleID primary key, autonumber
-txtRoleName

I don't know how the people (commander and inspector) fall into your application, but if the commander is a person who runs the facility and the inspector is also tied to a facility then that describes a one-to-many relationship between the facility and the people.

If the inspector is only tied to a work order then that is where the fkPeopleID reference should go. If you have more than one person tied to a workorder than you will need a separate but related table.

You would then use a query to bring all of the information together and export that to Excel.


So I guess what I am asking is how to AutoFill the InspectorFirstName, InspectorLastName and AreaCommander when the building/facility number is entered.

[jzwp11]

The inspectors and commanders on already on the facility list that is imported. I want to use that import as the facility lookup table becuase it may change as they add facilities.

I would extract the inspectors/commanders and put them in the tblPeople I suggested. Then relate the people to the facility

tblFacility
-pkFacilityID primary key, autonumber
other fields

tblFacilityPeople
-pkFacilityPeopleID primary key, autonumber
-fkFacilityID foreign key to tblFacility
-fkPeopleID foreign key to tblPeople

Once the people are tied to the facility, you just need to create a query that pulls the information you need. In your query you will get a result record for each person tied to a facility/workorder. To show the names of the inspector/commander within 1 resulting record in the query will take a couple of nested queries.