How do I "auto populate" a field on Access Forms

Question

![](https://learn-attachment.microsoft.com/api/attachments/4cd4d29f-4f82-4019-a1a9-d6907350a06e?platform=QnA

Answer 1

> > > > > > > > > > When I select the drop-down menu under 'Proto category' I need to have ' category time' auto-populate against what info it has. For example, they both share one table. One scenario would be 'if the proto category is T1  then the category time is 3 hours'. My table has each row as to what it needs to be per category. > > > You say "they both share one table". I'm guessing that you have a table like this: > > > > tblProtoCategory > > ProtCategoryID (PK) > > ProtoCategory > > CategpryTime > > > > The question that needs to be answered before you do this is whether Category Time can change over time. For example will T ALWAYS be 3 hours? Or will Category Time be edited for the prototype due to other factors. > > > > If the answer to either question is yes, then it is valid to have a Category time field in the Prototype table. And your code, in the After Update event should be simple. In the After Update Event of the Proto Category field would look like this: > > > > Me.CategpryTime = Me.cboProtoCategory.Column(2) > > > > This assumes that the RowSource of the ProtoCategory combo is: > > Select ProtoCategoryI, ProtoCategory, Categorytime FROM tblProtoCategory > > > > I have been on vacation and I have just come to test this. Upon checking this, Category time will not change, it will always be fixed, so I'm told. An example, is pasted below | | | | | | | | | | | | **Prototyping\_tbl** | | Proto Format Prototyping | Category Time Prototyping | | | | | | | | | | ID | | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | | T1 | 48 | | | | | | | | | | 2 | | T2 | 24 | | | | | | | | | | 3 | | T3 | 24 | | | | | | | | | | 4 | | T4 | 48 | | | | | | | | | | 5 | | T5 | 24 | | | | | | | | | | 6 | | Transt Trial | 48 | | | | | | | | | | 7 | | White Proto | 24 | | | | | | | | | | 8 | Would I have to go the VBA route or can I go via macro route too?

Answer 2

Thanks for the tips guys, i'll sure give those a run. I'll post if I face further problems.

Answer 3

When I select the drop-down menu under 'Proto category' I need to have ' category time' auto-populate against what info it has. For example, they both share one table. One scenario would be 'if the proto category is T1  then the category time is 3 hours'. My table has each row as to what it needs to be per category.

You say "they both share one table". I'm guessing that you have a table like this:

tblProtoCategory

ProtCategoryID (PK)

ProtoCategory

CategpryTime

The question that needs to be answered before you do this is whether Category Time can change over time. For example will T ALWAYS be 3 hours? Or will Category Time be edited for the prototype due to other factors.

If the answer to either question is yes, then it is valid to have a Category time field in the Prototype table. And your code, in the After Update event should be simple. In the After Update Event of the Proto Category field would look like this:

Me.CategpryTime = Me.cboProtoCategory.Column(2)

This assumes that the RowSource of the ProtoCategory combo is:

Select ProtoCategoryI, ProtoCategory, Categorytime FROM tblProtoCategory

Answer 4

The example which Duane has given is based on the fact that the UnitPrice columns in each table, i.e. that to which the form is bound, and the referenced table from which the combo box's RowSource property gets its data, are each functionally determined solely by the primary key of the respective tables.  In the Northwind example the current unit price of a product can change over time, but the unit price for each order should remain static as that current at the time the order was made.

You should only do similarly if the CategoryTime column in the form's table is functionally determined by the table's key in the same way.  If, on the other hand, it is functionally determined solely by the key of whatever is the referenced table, you should not have a CategoryTime column in the form's table.  To do so would introduce redundancy, as it would be transitively dependant on the key, and the table would not be normalized to Third Normal Form (3NF).  In that scenario the value should be returned in an unbound text box control in the form by referencing the Column property of the combo box in the text box's ControlSource property.

Answer 5

I believe you want the category time to default to a value based on the proto category. The time field should be a column in the row source of the category combo box. You can then use some code in the after update of the combo box to set the value of the time. This is demonstrated in the Northwind template with selection of product and setting of the unit price.

Private Sub ProductID_AfterUpdate() 

10        On Error GoTo Err_Handler 

          Const COL_UNITPRICE     As Integer = 2 

20        If IsNull(Me.ProductID.Column(COL_UNITPRICE)) Then 

              'This can happen when changing product category on a new row without entering any other data. 

30            Me.ProductID = 0     'Assigning Null is not possible with this required field. 

40        Else 

50            Me.UnitPrice = Me.ProductID.Column(COL_UNITPRICE)  'Column at index 2 has the hidden UnitPrice. 

60        End If 

Exit_Handler: 

70        Exit Sub 

Err_Handler: 

80        clsErrorHandler.HandleError Me.Name, "ProductID_AfterUpdate", True 

90        Resume Exit_Handler 

End Sub