Combo box showing primary values (1 Viewer)

[stu_c]

Hi all
I have a combo box (CmbStaffDetails) box showing a list of staff from a query named (QRYStaffDetails_InPost) this query shows what staff members are currently working.

I Have three tables
TBLStaffDetails
StaffID (Primary Key / AutoNumber)
Forename
Surname
Location (Lookup from TBLOfficeLocations)
Inpost (Yes/No)

TBLOfficeLocations
LocationID (Primary Key / AutoNumber)
Unit Code
Area

TBLStaffRoles
RoleID (Primary Key / AutoNumber)
JobTitles
PayBand

When I click on the drop down list on the form to choose a staff member the Office location shows the LocationID and not the Unit code, and same with the StaffRole showing RoleID not JobTitles,

When I go into the actual Query it shows the correct Text

how do I show this on the form?

any help please?

 

[theDBguy]

Hi. Sounds like you're using "lookup fields" at the table level. If so, suggest you take them out. To fix your problem, you'll have to modify the query to join the lookup tables and then pull the text descriptions/names instead of the IDs. Hope that helps...

 

[stu_c]

Hi mate
Thanks for the quick Reply, yes I have used the lookup from the Table Level, I have never done it from the Query side, can you provide any help on how to do this? didn't even know that was possible

Hi. Sounds like you're using "lookup fields" at the table level. If so, suggest you take them out. To fix your problem, you'll have to modify the query to join the lookup tables and then pull the text descriptions/names instead of the IDs. Hope that helps...

 

[vba_php]

When I click on the drop down list on the form to choose a staff member the Office location shows the LocationID and not the Unit code, and same with the StaffRole showing RoleID not JobTitles,

r u sure the issue isn't the column widths?

 

[stu_c]

Vba
thanks for the reply,
on my table it shows the text
My widths were on 0cm,2cm,2cm

if I put a >0cm value in the primary key shows not the text?

r u sure the issue isn't the column widths?

View attachment 81074

 

[theDBguy]

Hi mate
Thanks for the quick Reply, yes I have used the lookup from the Table Level, I have never done it from the Query side, can you provide any help on how to do this? didn't even know that was possible

Okay, let's say you have an Employee table and in it, you had a Foreign Key for the Office location. However, you decided to use a lookup field to show the Office Name instead of the OfficeID in that field. So, if your Combobox query says something like:

SELECT EmpID, EmployeeName, OfficeID FROM Employees

Then, all I'm saying is you may have to change it into something like this:

SELECT EmpID, EmployeeName, OfficeName FROM Employees INNER JOIN Offices ON Employees.OfficeID=Offices.OfficeID

Hope that helps...

 

[stu_c]

sorry I am not too great with access still really learning , I have no idea what you mean

 

[vba_php]

sorry I am not too great with access still really learning , I have no idea what you mean

if that's the case stu, the best thing I would recommend for you is to upload the file and let someone SHOW you how to do this. otherwise, this thread might get quite long.

 

[stu_c]

Hi mate
this is a quick mock-up of my database, there is a lot more info but thought be best keep it minimal
https://drive.google.com/file/d/15h6mL5gTmotq4-z749B5noLvGWHafubW/view?usp=sharing

if that's the case stu, the best thing I would recommend for you is to upload the file and let someone SHOW you how to do this. otherwise, this thread might get quite long.

 

[vba_php]

ok stu there's quite a bit wrong here.

1) your combo does NOT pull from the query. the sql is:

SELECT [TBLStaffDetails].[StaffID], [TBLStaffDetails].[Forename], [TBLStaffDetails].[Surname], [TBLStaffDetails].[Location], [
TBLStaffDetails].[Role], [TBLStaffDetails].[InPost]
FROM TBLStaffDetails ORDER BY [Surname], [Forename], [StaffID];

2) the data in your table is the SAME as the data in your query. i'm pretty sure you don't want that, right?
3) your quote:

the Office location shows the LocationID and not the Unit code

is true because of #1 and #2 I've just pointed out.

try reworking your stuff and then posting again you need to include OFFICE LOCATIONS (field = unit code) in your combo's sql, for one thing. it looks like you already have the relationships set up to do this.

 

[stu_c]

Hi mate
sorry for the delay, the relationships etc were already done for me automatically I didn't touch them, I have changed it about a little, the lookups now look from the queries but still have the above issue rather than any further suggestions

https://drive.google.com/file/d/15OxoYk4euJuCCD9Po7TwQbIXv8ymS68v/view?usp=sharing

 

[strive4peace]

hi Stu,

be sure to change the Field SIZE property in the lower pane for Short Text Fields, unless they're notes and need to actually be 255

Its helpful to name the fields the same when they are the same. For instance, in TBLStaffDetails, field should be RoleID
I noticed you used StaffIDFK instead of StaffID. Some like to add FK to foreign key fields, but I like the keep the names the same. This is personal preference, whatever is comfortable for you. Its easier to be the same, and I like easy

Also, "Role" is a reserved word and shouldn't be used for a name

Problem names and reserved words in Access by Allen Browne

Microsoft Access tips: Problem names and reserved words in Access

List of problem field names and reserved words in Microsoft Access and JET

allenbrowne.com

I also changed Location to LocationID

on relationships, be sure the check the box to Enforce Referential Integrity

changed the RowSource for your combo to include 2 more tables to provide the text values you want to see

attached is your edited database. To attach a database to a post, ZIP it up To do this, you can right-click the filename in Windows Explorer and Send to > Compressed (zipped) folder

 

[stu_c]

Hi mate thank you for the below, I would have ZIPPED the file but its been disabled on our computers , ill have a look and see what you have done and compare, thank you

hi Stu,

be sure to change the Field SIZE property in the lower pane for Short Text Fields, unless they're notes and need to actually be 255

Its helpful to name the fields the same when they are the same. For instance, ZIin TBLStaffDetails, field should be RoleID
I noticed you used StaffIDFK instead of StaffID. Some like to add FK to foreign key fields, but I like the keep the names the same. This is personal preference, whatever is comfortable for you. Its easier to be the same, and I like easy

Also, "Role" is a reserved word and shouldn't be used for a name

Problem names and reserved words in Access by Allen Browne

Microsoft Access tips: Problem names and reserved words in Access

List of problem field names and reserved words in Microsoft Access and JET

allenbrowne.com

I also changed Location to LocationID

on relationships, be sure the check the box to Enforce Referential Integrity
View attachment 81153

changed the RowSource for your combo to include 2 more tables to provide the text values you want to see

attached is your edited database. To attach a database to a post, ZIP it up To do this, you can right-click the filename in Windows Explorer and Send to > Compressed (zipped) folder

 

[strive4peace]

you're welcome, Stu ... perhaps I am a mate-ette though it doesn't much matter, call me whatever you like

 

[stu_c]

just a quick thing, If on my form I was to add another field for location to the form for staffs location, if I create a new record and select the staff if automatically adds their location which is fine, if for what ever reason I change the location on that record it changes the previous record of this staff members location, if there a way to keep the previous record as the last recorded?

Mockup Database3.accdb

drive.google.com

 

[strive4peace]

Stu, if the location may change on the PersonalRecords, then you should also put LocationID in that table. When a record is created, it could have an initial value of wherever the staff person is at that time, as indicated in the StaffDetails table.