Reading data from a table (newbie MS Access)


I realize the newbieness of this question, but google searches are not helping me.

I've created an MS Access database and am trying to automatically update a cell in a row when another cell is updated with data from a drop-down menu. I've created a sub "afterupdate" for the text box in the form, and can simply create a "select case" to update the cell based on what value is entered.

However, the Select Case would be very long, and I already have the list of values populated in another table in the database. Is there a way to set the value of the new cell to whatever the corresponding value is?


Table 1:

Mode — Time

A ——— 1:00

B ——— 2:00

C ——— 4:00

So, in my form, when I select "A" for one cell, another cell would automatically update with the information "1:00". Again, "Select Case" works, I am wondering if there is a faster way.

Best Solution

If you wish to do this in a form, lets try

  • Create a form with a combo box called cmbMode.
  • On the Properties/Data change Row Source to SELECT * FROM tblModes.
  • On the Properties/Format change Column Count to 2.
  • On the Propeties/Format change Column Widths to 3,0
  • Create a Text box called txtTime.
  • On the Properties/Data change Control Source to =[cmbMode].[Column] (1).

That should auto update on change.


Just as an after thought, this will not update values to a table, as the text box will not be bound to a field.

You could change the combo box AFTER_UPDATE event to use (if the textbox is bound to the TIME field) to use

Time.Value = cmbMode.Column(1)
Related Question