Access - Update Values on a form based off Drop down list Selection

Question

Hi, Start off with the basics. I am new to access and pretty much am using google to help me muddle through all this.

I have a form where I want to have a drop down box with engineer names in. Then based upon the engineer that is selected, update the information fields for phone, job title, email and a tick box of that engineer from a table. I have the engineers name set as the primary key.

I then should be able to alter data if needed and I will add a save record button to the form (Somehow).

If any one can help that would be brilliant, but I will need walking through any code that may need to be enetered.

Answer 1

No code (of yours) needed.

Select the Engineers table in the Nav Pane.

Create an auto form (Ribbon > Create tab > Form)

Design the form and drop a combobox on it.

Use the wizard:

Answer 2

Note that people's names are NOT a good choice of primary keys. A PK field must be unique, and should also be stable (not changing over time) and short (for efficiency); names fail all three of these. I'm John W. Vinson, and I used to work at a university where there was a Professor John W. Vinson; I got his junk mail and he got one of my paychecks! Your table of Engineers should have a simple, numeric EngineerID (an Autonumber is convenient) and fields for FirstName, Surname, Phone, Email, etc.; these fields should exist ONLY in the table of Engineers, and be displayed as needed on your form or report.

Answer 3

First, Read John's response. I totally agree.

Second, If the form you are building is to enter or edit the engineer info, then using the Search combo as Tom suggested is the way to go.

However, if you are selecting an engineer to assign to a project or something similar, then you should use a different approach. Let's assume your form is to enter info about projects and one of the fields in the engineer assigned to the project. You would, then, have a combobox control assigned to the EngineerID field that would be a Long Integer datatype to match to the Autonumber PK (as John suggested). If you need to display and edit the engineer's info on this form, you would use a subform, bound to the Engineer's table.

Answer 4

If you care to follow the good advice given by Richard Feynman to his students in the quote below my signature, you might like to take a look at FindRecord.zip in my public databases folder at:

https://onedrive.live.com/?cid=44CC60D7FEA42912&id=44CC60D7FEA42912!169

In this little demo file the first option, 'Open form to go to a record' illustrates a slightly more refined solution than that provided by the wizard.  In the form is an unbound combo box control  in which a contact can be selected by name, or the user can elect to enter a new contact.

Firstly the RowSource property of the control is as follows:

SELECT ContactID, FirstName & " " & LastName,

1 As SortColumn, LastName, Firstname 

FROM Contacts 

UNION

SELECT 0, "<New Contact>", 0,"","" 

FROM Contacts

ORDER BY SortColumn, LastName, FirstName;

The first part of this UNION query returns a list of contacts by name, concatenating the first and last names into a single column.  Note, however, that the name is not the primary key.  Personal names should not be used as keys as they can legitimately be duplicated.  I worked with two Maggie Taylors.  Instead the primary key is an autonumber column, ContactID, which is hidden by setting the ColumnWidths property of the control to zero.  The BoundColumn property is 1, so when a contact is selected by name, the value of the control is the ContactID value for the selected name.  The control's ColumnCount property is 2 because we want it to return the first two column, hiding the first one.  The query also returns 1 as the value of a column named SortColumn.  This is not a column in the table, but is computed by the query.  The rows are then sorted by LastName first, then FirstName.

The second part of the UNION operation returns 0 as the ContactID column, the literal string "<New Contact>" as the name, 0 as the SortColumn value and zero length strings as the LastName and FirstName columns.

When the rows from each part are combined by the UNION operation the "<New Contact>" row from the second part is returned as first in the list by virtue of the SortColumn value being 0, which sorts before the 1 in the first part.  The rest of the rows are then in LastName, FirstName order.

When a row is selected the control's AfterUpdate event procedure is executed.  This is as follows:

Private Sub cboGotoContact_AfterUpdate()

    Const MESSAGETEXT = "No matching record"

    Dim ctrl As Control

    Set ctrl = Me.ActiveControl

    If Not IsNull(ctrl) Then

        If ctrl = 0 Then

            ' go to new record and move focus to FirstName control

            DoCmd.GoToRecord acForm, Me.Name, acNewRec

            Me.FirstName.SetFocus

        Else

            With Me.RecordsetClone

                .FindFirst "ContactID = " & ctrl

                If Not .NoMatch Then

                    ' go to record by synchronizing bookmarks

                    Me.Bookmark = .Bookmark

                Else

                    MsgBox MESSAGETEXT, vbInformation, "Warning"

                End If

            End With

        End If

    End If

End Sub

After the initial declarations the code first checks that an item in the list has been selected, i.e. it is not Null.  If this is the case it checks to see if the ContactID value of the row selected is 0.  This will be the case if the <New Contact> row has been selected from the list.  If this is the case the form is moved to an empty new record and focus is moved to the FirstName control.

Otherwise, an existing contact has been selected, so the code finds that ContactID in a clone of the control's recordset.  The found row will have a hidden Bookmark value, so the code then sets the form's Bookmark value to that of the found record in the clone.  This has the effect of moving the form to that record.

If no record is found in the recordset clone which matches the contact selected in the control a message to that effect is displayed to the user.  This could be the case if, for instance, the form has been filtered and the user selects a contact not included in the filtered records returned.

The form does not need a Save button.  If you enter or edit data the amended record is automatically saved if you move to another record or close the form.