Normally you wouldn't be viewing your data at the table level so the data in there is less important? It is then the relationships in forms and reports that would show you what you want to see.

Here is how my manager at work set up a database we are working on. It let's you see the names instead of the numbers in DataSheet View.

Thank you Debbie. I previously set up a database in a similar way that you have done. But using that method does cause some problems at times. I was told on the forum, that I shouldn't use that method. I am trying a different way, based on Richard's Tech help but I am obviously doing something incorrectly. Thanks for reaching out.

Colin what is the RowSource of your WorldID ComboBox? And, the Record Source for the form itself?

If your form is bound to the BirdDataT and the ComboBox is Bound to WorldID, then the only thing that will get updated in the BirdDataT is the bound column. You either need to have two separate fields:

1. One to select the World ID
2. One to enter the Bird Name

Or, you need to use some code to set the value of the Bird Name based on what is entered into the non-bound column.

Or, you need to set up a List Items edit form as shown in the List Items Edit Form video and then have some mechanism to put that in your table, if this is the format you want.

The better method, if Bird Name is bound to the World ID, is to have a "WorldT" where records have the WorldID and Bird Name, among other fields and remove the Bird Name field from your BirdT and use a query when necessary to join all the related data.

Thank you Don. Your second paragraph was correct - the form is bound to the BirdDataT and the combox is bound to WorldID.
My current WorldT is a table that imported from Excel that lists all the birds in the world. When I imported it, I gave it an Autonumber field which is the WorldID. In your last paragraph, how do I bind the Bird Name to WorldID - I am relatively inexperienced in Access. Thank you for your suggestions

Since the Name is already in the WorldT, I would just remove the Bird Name field from Bird T. There is no reason to have it in two places.

Then create a simple query that joins the BirdT to the WorldT in the Query Editor to be the form's record source if you need to see the name separate from the combo box. That way if you select a bird from the combo box, it will automatically populate the name field.

See the following image from the Query Editor. It may not be exactly like your tables, but should give you the idea.

If you're explicitly trying to move/copy information from the WorldT to your BirdT, then that's a different matter. If that's the case, let us know and we can help you with that as well or point you to videos.

Here are a couple videos for working with queries and the relationships used:

Relationships
Relational Combo
Outer Joins

Just out of curiosity, what information in the WorldT is not in BirdDataT and also what information in BirdDataT is not in WorldT?

Hi Don. Thank you for your great help. That Query works as I wanted. However, What I am trying to do- when I see a new bird to be able to add that from the WorldT to my BirdT. I will eventually add photos to the form that shows the individual bird as per the chosenID along with the date the bird was seen, but I know how to do all those things. On the WorldID it also lists the Scientific Name of the Bird and the Class to which the bird belongs - separate fields. If you have any suggestions then that would be fantastic. I have watched all the above videos before but they haven't fully solved my problem.

Hi Matt - just saw your post, but I think my reply to Don probably answers your question. But WorldT has all the world birds, where I want to just add to the BirdT, the birds I have actually seen and photographed.

In the case you're trying to transfer the info to birdt, then I would add all of the fields from worlds to the world id combo box so you only need to pull that data once then I would use the after update event to copy that information from the combo to the selected fields when selected

But you could just leave the info in worldt and use the query join method to view as needednunless you are worried about something changing in world in the future

If I understand correctly, WorldT (comprehensive list of bird names) is a table of all of the birds you might ever see.  BirdT is a table of the bird sightings you have had.

By relating the WorldID to the BirdT, you have connected every field in that WorldT record to your BirdT record.  This means you should not need the BirdName, ScientificName, or ClassName in BirdT because it is already in the related WorldT record.  

What Donald has said is correct.  I am just trying to explain the table structure.  Hopefully it helps.

Thank you Don and Matt for your help and suggestions. Don, your second last post talked about copying the data to the BirdT in the after update event. What do I have to do to actually copy it?

In that case, you have to make sure that your form is still based directly on BirdT and not a joined query.

In the AfterUpdate event of the WorldIDCombo (adjust control names as needed) using VBA:

Private Sub WorldIDCombo_AfterUpdate()

     ScientificName = WorldIDCombo.Column(2)
     ClassName = WorldIDCombo.Column(3)

End Sub

This assumes that your world id combo box is set up with the following columns:

WorldID, BirdName, ScientificName, ClassName

Column order is zero based, so the first is 0, the second 1, and so on.

You could also create a query that updates BirdT similarly and then just add a button to your form that you click after changing the WorldID Combo that would run that query.

Append Query
Build a Query

Thank you Don for your patience and your understanding. I am very grateful. That is now working as I had been wanting it to be setup. Much appreciated.

Happy to help