Databases
StackOverflow2013
Postsdbo
POAuto Populate fields in MS Access Form
 

Note that there are some explanatory texts on larger screens.

plurals
  1. POAuto Populate fields in MS Access Form
    primarykey
    data
    text
    <p>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?</p> <p>Id like to add that the fields to auto populate would come from various tables..</p> <p>***ammendment </p> <p>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: </p> <pre><code>ID Code Count 24 TST 4 24 BPB 7 24 SSS 10 </code></pre> <p>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: </p> <pre><code>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] = " &amp; VarComboKey) Me.txtObjective = VarObjective VarStartDate = DLookup("[Start_Date]", "[Project_HDR_T]", "[Project_ID] = " &amp; VarComboKey) Me.txtStartDate = VarStartDate VarEndDate = DLookup("[End_Date]", "[Project_HDR_T]", "[Project_ID] = " &amp; VarComboKey) Me.txtEndDate = VarEndDate VarRiskCategory = DLookup("[Risk_Category]", "[Project_HDR_T]", "[Project_ID] = " &amp; VarComboKey) Me.txtRiskCategory = VarRiskCategory VartxtTarDatSet = DLookup("[Targeted_Dataset]", "[Project_Targeted_Dataset]", "[Project_ID] = " &amp; VarComboKey) Me.txtTarDatSet = VartxtTarDatSet VarErrorCount = DLookup("[Count_Error_Codes]", "[Project_Error_Final]", "[project_ID] = " &amp; VarComboKey) Me.txtErrorCount = VarErrorCount VarErrorCode = DLookup("[ErrorCode]", "[Project_Error_Final]", "[project_ID] = " &amp; VarComboKey) Me.txtErrorCode = VarErrorCode End Sub </code></pre> <p>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"</p>
    singulars
    1. PO
      singulars
      1. PTAnswer
    1. This table or related slice is empty.
    1. PTQuestion
    1. USJT2013
    1. USJT2013
    plurals
    1. This table or related slice is empty.
    1. This table or related slice is empty.
    1. This table or related slice is empty.
    1. PO
      singulars
      1. PTAnswer
    2. PO
      singulars
      1. PTAnswer
    1. VO
      singulars
      1. POAuto Populate fields in MS Access Form
      1. USJT2013
      1. VTBountyStart
    1. COWhy 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.
      singulars
      1. POAuto Populate fields in MS Access Form
      1. USFionnuala
    2. COThat 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.
      singulars
      1. POAuto Populate fields in MS Access Form
      1. USJT2013
    3. COHi 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.
      singulars
      1. POAuto Populate fields in MS Access Form
      1. USJAGAnalyst
 

Querying!

 
Guidance
A row detail

Detail views are divided into sections. All the information in the data section comes from columns in the selected row. The other sections display data from other, related rows.

Related data can be related in a to-one or a to-many fashion. Captions of data related in a to-many fashion link to a list view showing a filtered view of the table.

Try moving around until you find a non-empty to-many entry and click on the label to get to one. You can move back to the root by clicking on the database name in the header.

SQuiL has stopped working due to an internal error.

If you are curious you may find further information in the browser console, which is accessible through the devtools (F12).

Reload