Auto Populate fields in MS Access Form

Question

Is there a way to automatically populate fields in an MS Access form? Lets say the user makes a selection from a specific combo box on the form, is there something that can be done to automatically select the other fields on the form based on the PK?

Id like to add that the fields to auto populate would come from various tables..

***ammendment

I need to return multiple values once i select a specific record in the combo box. Can someone help? The multiple values will come from a query that returns values like this:

ID          Code             Count
24          TST                4  
24          BPB                7
24          SSS                10

In the form, the combo box would chose the ID number. Once I choose an ID number of 24, i want to return all 3 records above that come from a query called Project_Error_Final (in this example there are 3 values to return, but i want the query to return any records with ID = 24). The VBA code i have so far is:

Private Sub cboProjectID_Change()

Dim VarComboKey As Integer
Dim VarObjective As Variant
Dim VarStartDate As Variant
Dim VarEndDate As Variant
Dim VarRiskCategory As Variant
Dim VarTarDatSet As Variant

Dim VarErrorCount As Variant
Dim VarErrorCode As Variant

VarComboKey = Me.cboProjectID.Value

VarObjective = DLookup("[Objective]", "[Project_HDR_T]", "[Project_ID] = " & VarComboKey)
Me.txtObjective = VarObjective

VarStartDate = DLookup("[Start_Date]", "[Project_HDR_T]", "[Project_ID] = " & VarComboKey)
Me.txtStartDate = VarStartDate

VarEndDate = DLookup("[End_Date]", "[Project_HDR_T]", "[Project_ID] = " & VarComboKey)
Me.txtEndDate = VarEndDate

VarRiskCategory = DLookup("[Risk_Category]", "[Project_HDR_T]", "[Project_ID] = " & VarComboKey)
Me.txtRiskCategory = VarRiskCategory

VartxtTarDatSet = DLookup("[Targeted_Dataset]", "[Project_Targeted_Dataset]", "[Project_ID] = " & VarComboKey)
Me.txtTarDatSet = VartxtTarDatSet

VarErrorCount = DLookup("[Count_Error_Codes]", "[Project_Error_Final]", "[project_ID] = " & VarComboKey)
Me.txtErrorCount = VarErrorCount

VarErrorCode = DLookup("[ErrorCode]", "[Project_Error_Final]", "[project_ID] = " & VarComboKey)
Me.txtErrorCode = VarErrorCode

End Sub

The value in question is the VarErrorCount and VarErrorCode. In the VBA code above, only a single value is returned. But, I am looking for multiple VarErrorCount and VarErrorCode values to be returned in my form once the ID combo box field is selected. In this particular example VarErrorCode should return "TST", "BPB" and "SSS." The VarErrorCount should return the corresponding VarErrorCode values: "4","7","10"

Why are you not using a query that includes all the relevant tables and a bound form with bound controls? MS Access makes life very easy and nearly everything can be done without code. There should be no need to do anything as complicated as the above. — Fionnuala, Mar 14 '13 at 15:56
That still doesnt answer the question of how to return multiple values for a particular field. I get what your saying. Even if I simply used a query instead of the above code, i would still run into an issue of returning multiple Error Codes and Error Counts for each ID. Regardless I would need multiple queries to reference my form; one being the Project_Error_Final which aggregates the numbers in the output above. — JT2013, Mar 14 '13 at 16:03
Hi again user 1195703. I'll be happy to take a look at this. In general, I would also recommend that in the future you open a new question in these cases. My concern is that since you already accepted my answer to your initial question, that you may not be able to award the bounty to anyone else who might answer your revised question. — JAGAnalyst, Mar 14 '13 at 20:29
Can you confirm whether you expect that there will ever be [Code] values other than "TST", "BPB", and "SSS"? The most streamlined answer will depend on whether you always want counts for these three codes only, or if you want counts for all possible Codes (you could have more than three fields on your form, or always show only the top 3) — JAGAnalyst, Mar 14 '13 at 20:33
Can you also give us a little more information on how you're using this form? Is this being used as a report? — JAGAnalyst, Mar 14 '13 at 20:35
Hi @JAGAnalyst there could potentially be many more values other than TST, BPB and SSS. I'm designing a database that is going to be used for tracking qualitative and quantitative analysis with respect to Quality Management. This particular form is to be able to pull up a specific project_id and display many fields of information that would be stored elsewhere in the database. The idea is NOT to store the information again, but simply to display the fields i need for the purposes of creating a final PDF report. Majority of the fields in the form are puled from tables and a handfull will be new — JT2013, Mar 15 '13 at 00:10
the table above is generated by a query that i have made and for reporting purposes, i need all records pertaining to a particular project_id to show up on the final report. I hope this helps... — JT2013, Mar 15 '13 at 00:11

score 1 · Answer 1 · answered Mar 11 '13 at 16:59

Yes there is!

Obviously, you need to be able to relate the combo box selection to the value you wish to be populated into the other field(s). Assuming that you have a 1:1 relationship with the PK (since you want to display only one value in your form), you can use the AfterUpdate event plus the DLookup() function to retrieve a related value using the PK.

As a simple example, I set up a table named Foods as follows:

FoodID, FoodName, FoodCategory

1, Orange, Fruits

2, Chicken, Poultry

3, Almond, Nuts

4, Lettuce, Vegetables

In the form, I have a control that selects the FoodID as the PK bound value named ComboFoods, and an unbound text box control named TextFoodCategory that we will populate with the FoodCategory from the Foods table.

I've assigned the following code to the AfterUpdate event of the Combo Box so that when the value of the combo box changes, the text box will be populated:

Private Sub ComboFoods_AfterUpdate()

'Create a variable to store the combo box primary key selection

Dim VarComboKey As Integer

'Create a variable to store the DLookup results

Dim VarFoodCat As Variant

'Capture the primary key of the combo box

VarComboKey = Me.ComboFoods.Value

'Retrieve the related field value

VarFoodCat = DLookup("[FoodCategory]", "[Foods]", "[FoodID] = " & VarComboKey)

'Set the value of the text box to the variable

Me.TextFoodCategory.Value = VarFoodCat

This will return the FoodCategory that is related to PK. This is all using one table, but the DLookup statement can be modified to reference any query or table that contains the PK.

Please note that DLookup will only work properly when the PK is unique in the data you are referencing. It will not work in a one to many relationship unless you specify other criteria that restrict the results to one record. There are other ways to use SQL queries and recordsets within VBA if you need to return multiple records, but that this out of scope for this question.

This worked when tested - best of luck!

im not very proficient in VBA, but i dont understand what "ComboFoods" is supposed to represent? and what does "Me." and "Value." actually mean? — JT2013, Mar 11 '13 at 19:07
In my example, ComboFoods is the name of the Combo Box control that selects the PK. Me.* is shorthand for referencing the current database and form prior to referencing the control. You can fully qualify this instead if you prefer. Overall, the idea is to use DLookup or a query in conjunction with the AfterUpdate event. — JAGAnalyst, Mar 11 '13 at 19:27
ive got this to work perfectly fine. However im realizing that i need to return multiple values (ie. 1 to many). I will ammend my question — JT2013, Mar 14 '13 at 00:29
I'll try. Now that you need multiple records there are a lot more considerations depending on what exactly you want to accomplish. I will post additional comments against your main question rather than here against my initial answer. — JAGAnalyst, Mar 14 '13 at 20:17

RichardC · Accepted Answer · 2013-03-15T15:45:41.450

1

With regards to your multiple returns, you can't use a DLookup, but I will show you how you can achieve the result you want, as per your description.

In this particular example VarErrorCode should return "TST", "BPB" and "SSS." The VarErrorCount should return the corresponding VarErrorCode values: "4","7","10"

Change your last 4 lines above the End Sub to the following:

Dim dbs as DAO.Database
Dim rst1 as DAO.Recordset
Dim rst2 as DAO.Recordset

Set dbs = CurrentDb
Set rst1 = dbs.OpenRecordset("SELECT [Count_Error_Codes] FROM [Project_Error_Final] WHERE [project_ID] = " & VarComboKey)
If rst1.RecordCount > 0 Then
    rst1.MoveFirst
    Do Until rst1.EOF
        VarErrorCount = VarErrorCount & rst1!Count_Error_Codes & ","
        rst1.MoveNext
    Loop
    ' Remove the last comma
    VarErrorCount = Mid(VarErrorCount, 1, Len(VarErrorCount) - 1)
End If

Set rst2 = dbs.OpenRecordset("SELECT [ErrorCode] FROM [Project_Error_Final] WHERE [project_ID] = " & VarComboKey)
If rst2.RecordCount > 0 Then
    rst2.MoveFirst
    Do Until rst2.EOF
        VarErrorCode = VarErrorCode & rst2!ErrorCode & ","
        rst2.MoveNext
    Loop
    ' Remove the last comma
    VarErrorCode = Mid(VarErrorCode, 1, Len(VarErrorCode) - 1)
End If

rst1.Close
Set rst1 = Nothing

rst2.Close
Set rst2 = Nothing

dbs.Close
Set dbs = Nothing

Me.txtErrorCount = VarErrorCount
Me.txtErrorCode = VarErrorCode

edited Mar 15 '13 at 15:45

answered Mar 15 '13 at 15:01

RichardC

794
4
13

i get a type mismatch error when i run the above code. "Run time error 13." I noticed one issue which is in the rst2 loop. It should read ErrorCode not Error_Code. Any clue why i get the type mismatch? I have VarErrorCode and VarErrorCount defined as Variant. – JT2013 Mar 15 '13 at 15:18
Which line are you getting the type mismatch error on? Can you put a breakpoint in and step through to see? Also, good spot on the typo, changed it now. – RichardC Mar 15 '13 at 15:32
it looks like this line of code is causing the problem: `VarErrorCode = Mid(1, VarErrorCode, Len(VarErrorCode) - 1)` The ErroCode value is technically a string. As the output is **TST, BPB, SSS**. So I doubt we could use that formula.... – JT2013 Mar 15 '13 at 15:34
That should work, provided at least one record was found in rst2, as it will be initialized with a string and therefore the `Mid` function should work. You could always re-define the variables as strings, and maybe initialize them to `""`, though the latter part probably isn't needed. – RichardC Mar 15 '13 at 15:39
I just redefined `VarErrorCode as String`. Still get the same error...Type MisMatch. And instead of separating each record by a comma, is there a way to simply display each record below the other...ie. Table format? – JT2013 Mar 15 '13 at 15:42
Sorry, just checked and got my arguments in the `Mid` function the wrong way around. Have edited to reflect the correct call. (That's what happens when writing code without Intellisense!) – RichardC Mar 15 '13 at 15:45
any insight into how to have each record display below each other opposed to using commas? – JT2013 Mar 15 '13 at 16:28
You could do it using a multi-line textbox, and then changing the `& ","` in my code to `& vbCrLf`, then change the `Mid` statements to `VarErrorCode = Mid(VarErrorCode, 1, Len(VarErrorCode) - Len(vbCrLf))`, however really you are probably better looking at a subform or something and then displaying it properly using Child/Master fields. Have a look here for starters: http://office.microsoft.com/en-gb/access-help/about-subforms-HP005188395.aspx?CTT=3 – RichardC Mar 15 '13 at 16:30
Glad it worked out! In addition to parent-child subform, another way would also be to have the recordset return the full set of counts by code and filter on ID, using your query that covers everything as the record source for the form. – JAGAnalyst Mar 15 '13 at 19:25
is there a reason why the first line of Count_Error_Codes and ErrorCode show up as "0" and blank? Is there any way i can supress that first record so that i do not see a zero or blank value? – JT2013 Mar 21 '13 at 16:06
This sounds more like an issue in your data. There is nothing in the code which would make a 'blank' record appear first. – RichardC Mar 21 '13 at 16:52

Auto Populate fields in MS Access Form

2 Answers2