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!
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?
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
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:
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:
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
Set rst = Nothing
This technique is easily reusable and can be quickly adapted if the data structure changes as only the SELECT statement must be modified.