Results 1 to 4 of 4
  1. #1
    Join Date
    Aug 2012
    Posts
    70

    Unanswered: [SIMPLE] How can I convert a primary key into the value within a form?

    I have a basic form which has a couple of foreign keys in it from other tables.

    For example, the key for a customer.



    Can I have the form, in place of the ID (Which would mean nothing to the user), display the name of the customer?

    I have a query which has one column as the ID and another as the customer name, can I use this in some way?

  2. #2
    Join Date
    Mar 2009
    Posts
    5,442
    Provided Answers: 14
    Depending on what the form is intend for, there are several methods.

    1. Bind the form to a query that associates data from different tables. Instead of:
    Code:
    SELECT Column1, Column2, ..., FK_Customers
      FROM Sales;
    Use:
    Code:
    SELECT Sales.Column1, Sales.Column2, ..., Customers.Customer_Name
      FROM Sales INNER JOIN
           Customers ON Sales.FK_Customers = Customers.PK_Customers;
    2. Let the form do the job. Add a combobox to the form with:
    - RowSourceType = Table/Query
    - RowSource = "SELECT PK_Customers, Customer_Name FROM Customers;
    - Bound Column = 1
    - ControlSource = PK_Customers
    - ColumnCount = 2
    - Column Width = 0;1.5
    - LimitToList = Yes

    3. Use a VBA procedure to fetch the value of Customer_Name
    - Add an unbound textbox to the form and name it Text_Customer_Name.
    - Use the following code:
    Code:
    Private Sub Form_Current()
    
        Me.Text_Customer_Name = DLookup("Customer_Name", "Customers", "PK_Customers = " & Me.FK_Customers)
        
    End Sub
    Have a nice day!

  3. #3
    Join Date
    Aug 2012
    Posts
    70
    #2 was the one I am used to.

    I had seen that method before in this video:
    https://www.youtube.com/watch?v=NiHRjxQEw80


    Thanks

  4. #4
    Join Date
    Mar 2009
    Posts
    5,442
    Provided Answers: 14
    You're welcome.
    Have a nice day!

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •