CredTypeID is a number the CredType is the type of Credential
I need the query to display the Credential in a drop down list so I can change the credential by selecting a new one.
Currently I have to know the CredTypeID number to change the Credential.
I just want to select it from a drop down list.
Currently to change Betty Smith to an RN I have to type “3” in the CredTypeID. I just want to be able to select “RN” from a drop down list.
Here is the table layout and sql view (from access)
SELECT Lawson_Employees.LawsonID, Lawson_Employees.LastName,
Lawson_Employees.FirstName, Lawson_DeptInfo.DisplayName,
Lawson_Employees.CredTypeID, tblCredTypes.CredType
FROM (Lawson_Employees
INNER JOIN Lawson_DeptInfo
ON Lawson_Employees.AccCode = Lawson_DeptInfo.AccCode)
INNER JOIN tblCredTypes
ON Lawson_Employees.CredTypeID = tblCredTypes.CredTypeID;
This should do the trick, will work in datasheet view and auto-set up the field as the type of dropdown you want if you add the field to any new forms.
Change the Rowsource to be the following query:
SELECT CREDTYPEID,CREDTYPE FROM tblCredTypes ORDER BY CREDTYPE ASC
Set columncount=2
If you have already added the Lawson_Employees.CredTypeID field to a form, delete it and then re-add it to get it to automatically set it up so you can select by the friendly label instead of the id.
If you are entering the data via a form, then you create a drop down list that uses two columns for it's value list (CredTypeID and CredType) and then set the width of the first column to zero. Hey presto, a field that access treats as having a value of CredTypeID, but displays with CredType.
I don't think you can use this trick directly in the query results themselves, though.
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With