Three different fields in one record referencing to three different records in another table

[H. Martins]

Hi.

I have TableA and TableB

TableA has 3 fields where types of file extensions (for different
purpose) are supposed to be stored.

Table B has many extension type strings for these TableA three fields
(.tif, .jpg, .pdf, .gif, etc).

3 combo boxes refer to Table B to get and store TableB keys in TableA
appropriated fields.

When I set up table retaionship, Acces 'creates' also TableB_1 and
TableB_2 and show a diagram where two other similar to tabelB tables
show up.

So long so good. I can create records in TableA having chosen, for
each different file purpose, the respective extension type
(.pdf, .jpg, etc),and travel between them.

Now I need to put in a TextBox what has been chosen in one of the 3
combo's.

I import TableB and it's fields to the form (Form Record Source), bot
then I can't travel between TableA records apparently, because
different TableB recods are connected to 1 tablaA record. If I don't
do this I can't (or I don't know how) write code to fill the TextBox,
somethins like:

txtbxLinkExam.Value = 'somelhing else' & [TableB!strExtension]

because it says it can't find it. I suppose because 3 different TableB
records are connected to TableA

Ok I suppose I must do something like this:

txtbxLinkExam.Value = 'somelhing else' & 'the content of some field in
TableB whose key number is in TableA some-other-efield'

I suppose this is some sort of indirect referencing.

Can I have some help, please?

H. Martins

 

[Arvin Meyer [MVP]]

You can only do this in a form, not in a table. If you have combo boxes in a
table, you really need to get rid of them, write queries to take their
place, or just use forms:

http://www.mvps.org/access/lookupfields.htm

In a form, it is very simple to fill a textbox with the value that you
select from the combo (air code):

Sub MyCombo_AfterUpdate()
Me.txtWhatever = Me.MyCombo.Column(0)
End Sub

Column(0) is the first column in the combo. Adjust the index for other
columns.

If you have existing data, that you need to fill and there is more than a
dozen or 2 rows, write an update query to take the value and fill TableA.