Combobox selection to populate many text boxes problem

[batador]

Hello all,

I have a singular combobox on a form linked to an imported SharePoint table. The combobox selection allows the users to pick an ID #. I have 9 columns in the combobox selection the 1st column being the ID# of the report they want. Two of the columns contain an employee ID #. The employee ID # and their name are in another table. I cannot get the text boxes to display their names instead of the employee ID #. I have the employee ID table linked as well. I populate the text boxes in the After Update Event like this: Me.Corrected_by = Me.Combo11.Column(2), which is the employee ID #. How do I reference the name instead of the #?



Appreciate any help.

[moke123]

Join the table on EmployeeID, add it to the rowsource of the combo and select the correct column number.

[batador]

I should have been more clear, apologies. There are two names, Entered By and Corrected By, that contain the employee ID #. If I do just one of those, yes I can display the name, when I try to do both, I only get the employee ID #. Also, it appears that the combobox has trouble pulling in two different names, or EE ID # if you will, from the SharePoint table. If there is a report with only one name, it fetches that just fine. But two names, I don't see them in the combobox selection yet they are in the SP table.

[Minty]

You would need to add the employee table again with an alias.
You then join the second empID to the second "Copy" of the table.

If you post up the current SQL of the combo box rowsource we can probably work it out for you?

[batador]

Here is the SQL of the combobox. How do I make a copy of the table. I have never done that before. I like the idea though and see where you're going with it.

SELECT [Safety Learning Report].ID AS [Safety Learning Report_ID], [Safety Learning Report].[Corrected By], [Safety Learning Report].[Elimination Date], [Safety Learning Report].Created, [Safety Learning Report].[Reported By], [Safety Learning Report].Location, [Safety Learning Report].[Incident or Hazard Description], UserInfo1.Name, UserInfo1.ID
FROM [Safety Learning Report] INNER JOIN UserInfo1 ON [Safety Learning Report].[Reported By] = UserInfo1.ID
ORDER BY [Safety Learning Report].ID DESC;

[batador]

Awwww snap!!!! I saw where you were going and think I figured out how to do it! Here is the updated SQL code after I added the table again and linked the other name to it. It automatically added it with an alias as you can see. I do believe that fixed the problem as I can now see two names correctly. I am sure I will have more questions later though. But for now I do believe you helped me solve my problem. Thank you very much!!!

SELECT [Safety Learning Report].ID AS [Safety Learning Report_ID], [Safety Learning Report].[Corrected By], [Safety Learning Report].[Elimination Date], [Safety Learning Report].Created, [Safety Learning Report].[Reported By], [Safety Learning Report].Location, [Safety Learning Report].[Incident or Hazard Description], UserInfo1.Name, UserInfo1.ID, UserInfo1_1.Name, UserInfo1_1.ID
FROM ([Safety Learning Report] INNER JOIN UserInfo1 ON [Safety Learning Report].[Reported By] = UserInfo1.ID) INNER JOIN UserInfo1 AS UserInfo1_1 ON [Safety Learning Report].[Corrected By] = UserInfo1_1.ID
ORDER BY [Safety Learning Report].ID DESC;

[Minty]

Okay try this ;

SELECT [Safety Learning Report].ID AS [Safety Learning Report_ID], [Safety Learning Report].[Corrected By], [Safety Learning Report].[Elimination Date], [Safety Learning Report].Created, [Safety Learning Report].[Reported By], [Safety Learning Report].Location, [Safety Learning Report].[Incident or Hazard Description], UserInfo1.Name, UserInfo1.ID, UserInfo_Copy.[Name] as CorrectedByName 
FROM ([Safety Learning Report] INNER JOIN UserInfo1 ON [Safety Learning Report].[Reported By] = UserInfo1.ID) Inner Join UserInfo1 as UserInfo_Copy on [Safety Learning Report].[Corrected By] = UserInfo_Copy.ID
ORDER BY [Safety Learning Report].ID DESC;

It's untested but should give you an idea.

Also - please remove the spaces form your table and field names, all those square brackets become very tiresome to type out.
And as a helpful hint, in your tables change ID to something more meaningful (EmpID, ReportID etc) - leaving the primary key fields all called ID will trip you up later and makes reading things very much harder?

Finally, you may want to use a left join in case you haven't recorded a user ID in either field.

[batador]

Thank you for the reply Minty. Is there a way I can change the ID field to something else automatically when I import the table. I ask this way because I have no control over how the SharePoint tables are set up. They are controlled by our corporate HQ. I also refresh the table manually 2-3 times a day so I am not sure how that would play out. I would love to follow as closely as possible the normal forms when doing this, but my skill is limited as you can see.

[Minty]

If you can't control those field names (which is sometimes just the way of the world) - you can at least control what they are imported to.
At least the destination tables (the ones you are in control of) should have a sensible naming convention.

[batador]

Ok, I have found another problem. Now I am not catching records where there is only one name present. Most of the records have two names, but it is not mandatory to have two. I am not able to figure out how to display all of them. I have tried adding Is Null to the query builder for the names, but that doesn't work. Any suggestions?

[Minty]

Did you change the table join to an left join?
Click the arrow in the query designer, and select the option that shows all the main table records and only the ones that match in the other table

[batador]

Once again, I see how dumb I am. Yes, thank you, I went back and did as you suggested and changed to a left join and that was definitely the solution. Thank you again.

[ssanfu]

Also, be aware of reserved words in Access.

"NAME" is a reserved word (It is a Form property; Form Section property; Report property; Report Section property; Table property; Field property)

UserInfo1.Name,
UserInfo_Copy.[Name] as CorrectedByName




Reserved words should NOT be used as object names.