Showing Related Data in a Form

[GregWatling]

I have only been using Access for a short amount of time & am struggling to do something which I know is easy to do in Filemaker which I have been using for many years.

I have created a database of installations of equipment done by my company. In that data is the information of the account & they then fall into different categories for the type of account.



I have created a lookup which goes to a different table of data to select the type of account from a drop down list and therefore, filling in a single field with a single reference number of the account type.

But, what I want to do is show the other fields from the looked up table in the form I am using as the main viewing point of the data.

So, to cut the description short, I want to show related data in the form from a different table based on the looked up field.

i.e.
Field1 in Table1 looks up FieldA in Table2 & the data is stored in the record of Table1

In the Form of Table1, I want to show the data in fields B & C of Table2

[pbaldy]

One way:

http://www.baldyweb.com/Autofill.htm

[GregWatling]

Thanks for that, but it doesnt seem to work.

Just a note to start with, I am using Access 2003.

I have a one to one relationship, my table2 contains a list of types with 3 fields, ID, Level1 & Level2

I want to select the ID from a drop down list which I have achieved successfully & it shows the Level1 & Level2 when I select it, but only fills in the ID number.

So, what I want it to do is then show the Level1 & Level2 information next to it, but only store the ID number in the table & therefore, the other two fields are virtual fields just shown in the form

I dont want it to show a list, just the 2 fields which refer to the ID number selected.

[DepricatedZero]

I've not used 2003 but this works in 2007:

When your form is Bound to a query you can add a dropdown to sort through entries in that query. You said you've succeeded in this but it only populates one control?

Under the Design tab on the ribbon(dunno where it would be in 2003) there are two options: Property Sheet and Add Existing Fields. With that you can see what fields are available in your query and drag them onto the form. This will automatically name them and set them up so that when your form changes records, the information in them reflects the bound record.

You can also manually select your "Control Source" to a field from the query on a control you already have on the form.

In 2007 this is under the Data tab of the Properties Sheet. Not sure where in 2003 but that should at least give you an idea of where to look.

[pbaldy]

The method I posted does exactly what you describe. I'm not sure what "doesn't seem to work" means, so perhaps you can post the db here?

[GregWatling]

I havent done anything with a specific query, like I said, I am new to Access.

I have a main table (tblMain) which is then shown in a form (frmMain) that data has about 40 fields.

One field in there, I have done as a lookup to a different table (tblSectors)It is done with a Combo box with the Row Source of
SELECT [tblSector].[ID1], [tblSector].[SectorNumber], [tblSector].[SectorL1], [tblSector].[SectorL2] FROM [tblSector] ORDER BY [SectorNumber];

On the form, the row source is the same. This shows a single field box which when I click the drop down gives the image shown in the first post of this topic and it fills in the 5 figure number in the box with the heading SectorL2. this field, I need to save in the tblMain data

What I want to do is display the other two fields on the table, but not necessarily save the data in the table.

If I need to do this with a query, I might need an idiots giude to doing it.

It is annoying as I know that in Filemaker, this would be simple & take 2 minutes to achieve, but in Access, I have been trying to do this for a long time now with no success.

[GregWatling]

The method I posted does exactly what you describe. I'm not sure what "doesn't seem to work" means, so perhaps you can post the db here?

Maybe I am misreading your suggestion, or maybe I have my relationship not right.

But, when I put your suggestion in, I get a seperate box with drop down which bears no relationship to the orignal field I want to link it to

EDIT: Also, it seems to make this choice global to all records

[pbaldy]

Your existing drop down should be fine. In a textbox next to it:

=ComboName.Column(1)

should display the second column.

[GregWatling]

I'm not getting any data

I have a field with a drop down box called "Sector" That gets info from a table called "tblSectorL2"

I have created a Combo box called "Combo103" which doesnt relate to the "Sector" field.

I then created a text box called "Text105" and have put in the Control Source "=[Combo103].[Column](1)" It automatically adds the [] brackets

All of the other parts of all bits are left at default and the text box stays empty whatever I select in the drop down list

what am I missing?

[GregWatling]

If I select something in the Combo103 box I created, it does populate the new text box, but doesnt save it & if I scroll to the next record, it keeps the same data

[Missinglinq]

If I select something in the Combo103 box I created, it does populate the new text box, but doesnt save it & if I scroll to the next record, it keeps the same data

You need to

• Remove the Expression from the Control Source of Text105
• Use the dropdown to populate the Control Source of Text105 with the Field you want to use to store the data
• In the AfterUpdate event of Combo103 enter Me.Text105 = Me.Combo103.Column(1)

Linq ;0)>

[GregWatling]

OK, we are now getting somewhere (but not where I want to be)

Text105 now shows the Primary ID field of the looked up table (Not any of the fields I want to show)
Combo103 stays blank with a drop down choice of the list as in "Sector" (But not linked to it)

But, if I click on Combo103, it says "Can't fins the macro 'Me.'

[GregWatling]

I have now removed most of the fields & data, added some dummy stuff and it is attached here if I have worked out the attachments properly. It also shows what I want to see

If someone could have a look at it for me, I will appreciate it

[pbaldy]

Private Sub SectorL2a_AfterUpdate()
  'these set textboxes if you want to store the value
  Me.Text117 = Me.SectorL2a.Column(2)
  Me.Text119 = Me.SectorL2a.Column(3)
  'these display in the labels you specified
  Me.Label114.Caption = Me.SectorL2a.Column(2)
  Me.Label116.Caption = Me.SectorL2a.Column(3)
End Sub

[GregWatling]

Forgive me for sounding dumb, but what do I do with that?