Auto populate a cell in Access

Access has DLookup(), which is probably an equivalent to XLOOKUP. But in Access, we usually just use a dropdown Combobox for something like that.

Unfortunately, the differences between Access -- a relational database development environment -- and Excel usually mean that analogies to "doing things the way we do them in Excel" can often lead us astray.

That means it's better to avoid thinking about tasks like this in terms of Excel experience at all. Figure out what Access needs you to do and do that instead.

Ken and theDBGuy have offered the appropriate Access method for this task, i.e. a Combobox. If you focus on implementing that, you'll be fine.

Travel Planner1.accdb

">

I am a complete novice at Access, and I am trying to create a program that when a user enters in a airport city code it will automatically populate the city into a different box. I know in Excel I can use XLOOKUP, but I am not sure how to get the data to auto populate after they select an item from a combo box. Is there an equivalent XLOOKUP for Access? Any guidance would be appreciated. I am afraid it is going to require a script of which I have no idea. Here is a link to the file. For some reason I wasn't able to put screen shots in here. Travel Planner1.accdb

This is a standard practice in Access, but you need to think about it differently. Using lookup tables (like a list airport codes) is standard operating procedure. But its not done the way you may be thinking.

You seem to be developing a travel planner of some sort. In such an app you would probably have a table representing trips being taken. In that table you might have fields for originating and destination airports. Those fields would be related to the Airport lookup table based on the Airport code. You would NOT have a field to store the airport city since that information would be in the Airports table. And you don't store the same info in multiple tables!

There are multiple ways to do what you want. They both start with a combobox on a form that has a Rowsource looking up against the Airport table. The Rowsource would include the airport city column as a hidden column. When the user make a choice of the airport code, you have two choices. In Choice A, you have a textbox on the form with a Controlsource like

=cboorigAirport.column(x)

Once a selection is made the text box displays the City. In Choice B you use VBA code in the After Update event like:

Me.txtOrigAirportCity=Me.cboorigAirport.column(x)

The foundation of an efficient database app is a properly normalized table structure. If you aren't familiar with Normalization rules, you should research them BEFORE you start design9ing forms etc.

You might like to take a look at DatabaseBasics.zip in my Dropbox public databases folder at:

https://www.dropbox.com/scl/fo/0scigd3r48hx5xrev2jrf/AB0-GMdTgMAO5O1cGdr3QW0?rlkey=ib6bs6g9jqcrywwzivur3265t&dl=0

In this little demo file the third form in the section on 'entering data via a form/subforms' includes a combo box to select a customer.  This combo box is bound to the CustomerID foreign key column in the Orders table on which the form is based.  If you drop down the combo box's list you'll see that it returns multiple columns making up the customer's name and address.  The numeric CuistomerID column is hidden, however, by setting the first dimension of the control's ColumnWidths property to 0cm.

Below the bound combo box are unbound text boxes which reference the Column property of the combo box in their ControlSource properties, e.g. =[cboCustomer].Column to return the first line of the address.  The Column property is zero-based, so Column(2) is the third column returned by the combo box's RowSource query.  A very important point to understand is that the customer's name and address are not stored in columns in the Orders table, only the numeric CustomerID.  If the name and address values were to be stored in the table this would introduce redundancy, and the table would not be normalized to Third Normal Form (3NF), leaving it wide open to the risk of update anomalies.

The orders form also contains an Items Ordered subform.  In this the current UnitPrice of the item selected is returned by the combo box's RowSource query, though not in this case shown in the drop-down list.  Unlike the customer's address data, however, the UnitPrice is stored in the subform's OrderDetails table, and is assigned to a column by the following code in the combo box's AfterUpdate event procedure:

Private Sub ItemID_AfterUpdate()

    ' assign current unit price from Items table

    ' to UnitPrice column in OrderDetails table

    Me.UnitPrice = Me.ItemID.Column(1)

End Sub

The reason the value is stored in the subform's table rather than simply being referenced like the address data in the parent form, is that the price of items changes over time, but we want each order to use the price which was current at the time the order was made, regardless of any subsequent changes in the price.  Consequently there is no redundancy, and the table is correctly normalized to 3NF. See Normalization.zip in my same Dropbox folder for as simple as possible, but no more so, introduction to the process of normalization in a relational database.

In your case the city names associated with each airport code will not normally change over time so you would reference them in the same way as the address data in the above example.  If by any chance a city name was to change, then its name would be changed in the referenced Cities table, and the change would be reflected in your form.

I'd add one thing to Scott's reply, with regard to his statement 'In Choice B you use VBA code in the After Update event like: Me.txtOrigAirportCity=Me.cboorigAirport.column(x)'.

Because the city is functionally determined by the airport code, and would not be stored at a column position in the referencing table on which the form is based, the txtOrigAirportCity control would be unbound.  Consequently you could only assign a value to it if the form is in single form view.  If this were to be done in a form in continuous forms view the value assigned to the control by the above code would be the same in every row in the form.

Moreover, if this method were to be used in a form in single form view it would also be necessary to repeat the code in the form's Current event procedure, so that the city name showing in the control would be refreshed to that determined by the current airport code as the user moves from record to record in the form.

Referencing the Column property of the combo box in the city text box's ControlSource property on the other hand would ensure that the correct city name would show in the text box whether the form is in single form or continuous forms view.

There is another simple way this can be done, whatever view the form is in, which would be to base the form on a query which joins the referencing table to the referenced Airports table on the AirportCode foreign and primary key columns in the tables.  In the form a combo box would be bound to the AirportCode foreign key column and a text box would be bound to the non-key City column in the Airports table.  The Locked property of this text box should be set to True (Yes) so that a user cannot edit it.  It's important that, in the relationship between the two tables referential integrity should be enforced, and the two key columns on which the tables are related should be indexed appropriately, uniquely in the case of the Airports table, and non-uniquely (duplicates allowed) in the case of the referencing table.