CODEWITHSUNDEEP
ms-accessselectrowmatch
Sunius
Sunius

Reputation: 2907

Microsoft Access selecting records from another table matching current record

It seems I've hit a brick wall in MS Access 2010.

It's kind of hard to explain what I'm trying to achieve, so I'll start with a basic example. Let's say we have two tables: A and B.

A:

ID     Price    Item
1      5        ABB
2      4        ATV
3      2        CCC

B:

ID     Price limit    Chosen item
1      3    
2      4
3      5
4      6

What I'm trying to achieve is create a Relation from table A to B. Each record in table B has to have associated table A record. It should be based on table A field 'Price' and table B field 'Price limit' in a way that the price of selected record from Table A is lower than the price limit imposed in table B record.

That is, the possible table A records for the first table B record is only CCC, for second - ATV and CCC, while for third and fourth all records are valid.

As far as my limited access knowledge goes, I've figured that I should write a query in "Chosen item" field row source property. I've tried writing it myself, however, without success. Here's what I've come up with:

SELECT [Table A].[ID], [Table A].[Item] 
FROM [Table A] 
WHERE [Table A].[Val] > [Val];

But it does not work. Could somebody please point me to the right direction?

Upvotes: 1

Views: 4442

Answers (1)

Fionnuala
Fionnuala

Reputation: 91356

I think that you want something like this where the dropdown box only contains relevant items:

enter image description here

You can set this up by setting the row source of the combo to say,

select item from ta where price<=forms!tb!pricelimit

And adding a little code

Private Sub Form_Current()
   Me.Chosenitem.Requery
End Sub

Note that this method comes with a warning. Usong dropdowns like this on a continuous form can seriously mess up the display of your data on any rows other than the current row. In this case, the display is fine, because the bound column and the data to be displayed are the same, however, if the selection was:

 Row Source: select id,item from ta where price<=forms!tb!pricelimit
 Bound Column: 1
 Column Widths : 0cm;2cm

Data would appear to disappear from records when the selection for the current record produced a list that did not contain IDs for other rows. That is to say, if the selection for the current rows returned IDs 1 and 2 and the next row already had ID 3 chosen, the combo for the next row would appear to empty, and so on down the page.

Upvotes: 2

Related Questions