Results 1 to 5 of 5
  1. #1
    Join Date
    Jul 2009
    Posts
    39

    Unanswered: Auto populate a form when entering a value in a input field

    Hi guys, I'm making a form thats for scheduling conferences about property info and what the users would like to do is when they open the form to make a new conference record all they have to do is type the property number in a input field and the rest of the fields will auto populate based on the property number, I would like it to pull this data from a data table I created that has all the property information in the county, so when they type in the prop number then the owner field, mailing field etc becomes populated. Thanks!

  2. #2
    Join Date
    Mar 2009
    Posts
    5,442
    Provided Answers: 14
    What do you have so far?
    Have a nice day!

  3. #3
    Join Date
    Jul 2009
    Posts
    39
    The only thing I have so far is just the data table with all the information I would like to pull from, I haven't made the form yet, I've been trying to find a tutorial to do this but have had no luck. I've read that using dlookup is able to do this so is that the right path I should follow?

  4. #4
    Join Date
    Jun 2007
    Location
    Maitland NSW,Australia
    Posts
    388
    You could use Dlookup but it would involve a separate Dlookup for each field that you want to populate in your form. I suggest you use a combox box with the property details, so that when the user selects a property number the form is populated by the combox fields.

    I have attached a database example. On the form frm_conference there is a field Property ID have a look at the Row Source Property this shows the query used. Also have a look at the After Update event of this field, this shows how to populate the required fields.

    You do not need to store the property details in your conference table only the property id. If you store the property details you will redundant data look at NORMALIZATION on the forum
    Attached Files Attached Files

  5. #5
    Join Date
    Mar 2009
    Posts
    5,442
    Provided Answers: 14
    Here's another solution:

    1. Create a logical link between the names of the columns in the data table and the names of the controls in the form. One of the easiest way to do that is the give the controls a name that includes a part of the names of the columns in the table (e.g. if the name of a column in the table is StartTime, assign the name Text_StartTime to the corresponding textbox control in the form). There are several ways of doing that but that one probably is the simplest.

    2. After the user made a selection, create a recordset to populate the controls on the form, like in this example:

    a) The data table is named Tbl_CF_Data. The primary key is SysCounter and among others it has columns named Client, Numero, Fund_Request, Vehicle.

    b) On the form (not bound to Tbl_CF_Data), there are four textbox controls named Text_Client, Text_Numero, Text_Fund_Request and Text_Vehicle.

    c) On the same form there is a combobox (Combo_Selection) with the following RowSource property value:
    Code:
    SELECT Tbl_CF_DATA.SysCounter, Tbl_CF_DATA.CUstomer, TBL_CF_Data.Order_Number FROM Tbl_CF_DATA;
    The bound column of Combo_Selection is the first one (SysCounter).

    d) The following code will populate the controls on the form according the the selection in the conbo box:
    Code:
    Private Sub Combo_Selection_BeforeUpdate(Cancel As Integer)
    
        Dim rst As DAO.Recordset
        Dim fld As DAO.Field
        Dim strSQL As String
        
        strSQL = "SELECT Client, Numero, Fund_Request, Vehicle " & _
                 "FROM Tbl_CF_Data " & _
                 "WHERE SysCounter = " & Me.Combo_Selection.Value
        Set rst = CurrentDb.OpenRecordset(strSQL, dbOpenSnapshot)
        For Each fld In rst.Fields
            Me.Controls("Text_" & fld.Name).Value = fld.Value
        Next
        rst.Close
        Set rst = Nothing
        
    End Sub
    This technique is easily reusable and can be quickly adapted if the data structure changes as only the SELECT statement must be modified.
    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
  •