Results 1 to 11 of 11
  1. #1
    Join Date
    Feb 2012
    Posts
    3

    Unanswered: Auto fill fields based on selection in Combo Box

    I am a rookie to Access. I have searched high and low for the solution to this problem but cannot find the answer anywhere. I appreciate any input. I am using Access 2010.

    I have a single table with various fields
    Included in this table are fields for the Salesperson ID#, the Salesperson first name, and Salesperson last name
    I have created a mock "purchase order" form based on this table
    On this form, I have created a combo-box where the user selects the salesperson ID # (the combo-box is populated with S01, S02, and S03)
    **When a user of this form selects a Salesperson ID# I would like to have the Salesperson's first name and last name auto populate into the form (this is what I cannot figure out how to do)

    I do not want to set up additional tables with relationships, I want all the info to be in a single form

    Do I need to create a macro?

    Thanks in advance for ANY and all help

  2. #2
    Join Date
    Jun 2005
    Location
    Richmond, Virginia USA
    Posts
    2,763
    Provided Answers: 19
    Set up your Combobox using the Wizard and include the Fields you need, from Left-to-Right.

    If, in the Combobox they appear, Left-to-Right, as

    Salesperson ID SalespersonFirstName SalespersonLastName

    the code would be
    Code:
    Private Sub YourComboBoxName_AfterUpdate()
         Me. Salesperson ID =  Me. YourComboBoxName.Column(0)
         Me. SalespersonFirstName = Me. YourComboBoxName.Column(1)
         Me. SalespersonLastName = Me. YourComboBoxName.Column(2)
    End Sub
    Notice that the Column Index is Zero-based.

    Also, if you actually include the Octothorp or pound sign (#) in your Field/Control name, it would be a good idea to drop it. It has special meaning in Access VBA code and can confuse the Access Gnomes no end!

    Linq ;0)>
    Last edited by Missinglinq; 02-22-12 at 17:35.
    Hope this helps!

    The problem with making anything foolproof...is that fools are so darn ingenious!

    All posts/responses based on Access 2003/2007

  3. #3
    Join Date
    Feb 2012
    Posts
    3
    Thanks for your quick response but I still can't get it to work. the ONLY information in the combo-box is the Salesperson ID# (S01, S02, and S03). I already have the combo-box on the form (that much I can do)

    I tried using the wizard like you suggested but I was never prompted to enter in code like you said

    Am I missing something?

  4. #4
    Join Date
    Mar 2009
    Posts
    5,442
    Provided Answers: 14
    Quote Originally Posted by bosox1789 View Post
    the ONLY information in the combo-box is the Salesperson ID# (S01, S02, and S03)
    That's precisely what you need to change if you want to use missinglinq's solution. The combo must contain 3 columns (2 can remain invisible): SalespersonID, SalespersonFirstName and SalespersonLastName.

    The code must be entered manually.
    Have a nice day!

  5. #5
    Join Date
    Feb 2012
    Posts
    3
    I see what you're saying, but that's not exactly what I was looking for. I guess I wasn't clear when I explained how I have my form set up.

    There is a combo-box loaded with the Salesperson ID#
    There is a separate field on the form for the salesperson first name and a separate field for the salesperson last name

    When the salesperson ID# is selected from the list, I want the first name and last name to auto-populate into the appropriate fields with that salesperson's first name and last name

    Hopefully this clarifies my problem

  6. #6
    Join Date
    Mar 2009
    Posts
    5,442
    Provided Answers: 14
    The problem was clear from the beginning. When an ID is selected in the combobox, you then need to retrieve 2 other values from a table or a query to populate the 2 textboxes.

    There are several solutions:

    1. Create a dynamic query with a WHERE clause based on the selected ID and open a recordset to retrieve the values from it:
    Code:
    Dim strSQL As String
    dim rst as DAO.Recordset
    strSQL = SELECT SalespersonFirstName, SalespersonLastName FROM SomeTable WHERE ID = " & Me.ComboSomething.Value 
    Set rst = CurrentDb.OpenRecordset(strSQL, dbOpenSnapshot)
    Me. SalespersonFirstName.Value = rst!SalespersonFirstName
    Me. SalespersonLastName.Value = rst!SalespersonLastName
    rst.Close
    Set rst = Nothing
    2. Use 2 domain functions (DLookup) with the same criteria argument also based on the selected ID:
    Code:
    Dim strCriteria As String
    strCriteria = "ID = " &  Me.ComboSomething.Value 
    Me. SalespersonFirstName.Value = DLookup("SalespersonFirstName", "SomeTable", strCriteria)
    Me. SalespersonLastName.Value = DLookup("SalespersonLastName", "SomeTable", strCriteria)
    3. Use missinglinq's solution and retrieve the data already present in the combo itself, which, for several reasons, is the most efficient.
    Have a nice day!

  7. #7
    Join Date
    Feb 2012
    Posts
    1
    If any of you are pulling your hair out, wondering why you can't get sinndho's first code example (above) to work, there's a simple syntax error in the example he/she provided.

    Add a double quotation to the appropriate spot in the line of code below (I have colored it red):

    strSQL = "SELECT SalespersonFirstName, SalespersonLastName FROM SomeTable WHERE ID = " & Me.ComboSomething.Value

    Other than that, the code works flawlessly.
    By the way, thanks for providing it!

  8. #8
    Join Date
    Jun 2013
    Posts
    1
    I just implemented the missinglinq's very first solution, and it is perfectly working. However, the form fields that are filled automatically are not stored into the tables. Is there any way I can link the form fields to the table fields?
    Thanks,
    Last edited by emsadoon; 06-10-13 at 16:16.

  9. #9
    Join Date
    Sep 2006
    Location
    Surrey, UK
    Posts
    995
    Provided Answers: 2
    Are your form fields bound to columns in the underlying table?
    10% of magic is knowing something that no-one else does. The rest is misdirection.

  10. #10
    Join Date
    Jun 2013
    Posts
    1
    Even though this thread is old I just wanted to point out a different method that works flawlessly for me:

    First create your single form and apply the table that is used as a record source. Add all fields you want to show (in this above case the name)

    Then add a combo-box, in the properties under the "Data"-tab, go to "Row source" and add something like "Select Name, ID From Table_That_Is_Used_As_RecordSource". This selects the Name and ID of all rows from the Table. Make sure that the ComboBox' "Control Source" is empty. Go to the properties tab "Other" and give the ComboBox a Name. Next go to the properties tab "Event" and there to "On Change". Click on the 3 Dots and choose "Code".

    Edit your code to something like this:

    Code:
    Private Sub ComboName_Change()
        Me.RecordSource = "Select * From Matrix_Import_Varianten Where ID = " & ComboName.Column(1)
        Me.Refresh
    End Sub
    Thats it. How does it work? The ComboBox selects all rows of the table. When choosing one value, the onChange code is executed. It changes the recordSource of the form according to the value that is chosen.
    This way all fields that are bound to the record source can still be used to edit data. Hope it helps

  11. #11
    Join Date
    Sep 2013
    Posts
    1
    Hello Sinndho,

    I am new to MS access. I am creating student database system in access. i have different fields in it like (Ref.No, student name, email id, course start date, visa status, student id etc.) I have set my database to generate id of my own choice (starts from 120001). When student come for admission we usually assign Ref.No and after his visa approval we assign Student ID manually. Now, at this point, my database is able to generate Id automatically, so it is also generating id's for both Under-process and Declined applications. I want my database to generate automatic id after the application is approved. I mean, database should automatically generate student id, once we select "visa approved" manually. How can it be done?

    Second, I have two more fields, document submitted and document pending. In document submitted I have put drop down menu with the name of various documents like (CV, Ielts, Visa Copy, Photo ID, academic certificates etc). Now, I want, if I select "two documents" from document submitted field, the pending documents should automatically print in document pending field.
    Please let me know if it is possible. If you want I can send you my database in your email for your consideration.
    I shall be very thankful to you. Please write me at sarabjit.bhatia@gmail.com.

    Thanks
    Sarab

Posting Permissions

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