Geek Answers Handbook

Help with query design in MS-Access

Tableview

CredTypeID is the number that CredType is a credential type

I need a request to display the credentials in the drop down list, so I can change the credentials by selecting a new one.

Currently, I need to know the CredTypeID number in order to change credentials.

I just want to select it from the drop-down list.

Currently, to change Betty Smith to RN, I need to enter "3" in the CredTypeID. I just want to be able to select "RN" from the drop-down list.

Here is the table and sql view (from access)

Tablelayout

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;
+6
sql ms-access
source share
2 answers

This should do the trick, will work in the data view and automatically configure the field as a type of drop-down list if you add the field to any new forms.

  • Open the Lawson_Employees table in design.
  • Click on the CredType field and click on the “Search” tab at the bottom of the screen.
  • Change DisplayControl to Combobox

  • Change the source of the Rowsource as the following query:

    SELECT CREDTYPEID, CREDTYPE FROM tblCredTypes ORDER BY CREDTYPE ASC

  • Set columncount = 2

  • Set the column width to "0;"
  • Set LimitToList = Yes
  • Make sure BoundColumn is set to 1

If you have already added the Lawson_Employees.CredTypeID field to the form, delete it and then re-add it to automatically configure it so that you can select a friendly label instead of an identifier.

+4
source share

If you enter data through a form, you create a drop-down list that uses two columns for the list of values ​​(CredTypeID and CredType), and then sets the width of the first column to zero. Hey presto, a field in which access is treated as having a CredTypeID but is displayed with CredType.

I do not think that you can use this trick directly in the query results, however.

+3
source share

More articles:


All Articles