Help me! I couldn't do this in Access while I can do it with VFP in seconds.
There is a lookup table something like:
create cursor LookUps (Id i, Lookuptype c(10), ValueText c(10), ValueNum i)
Insert into LookUps values (1,'XCodes','XCode1',100)
Insert into LookUps values (2,'XCodes','XCode2',200)
Insert into LookUps values (3,'XCodes','XCode3',300)
Insert into LookUps values (4,'YCodes','YCode1',10)
Insert into LookUps values (5,'YCodes','YCode2',20)
Another table needs to reference that lookup (XValue,YValue are redundant but that's how they want it):
create cursor myTable (XField i, XValue i, YField i, YValue i)
This table's fields would show on a form like this:
XField: XCode1 XValue: 100
YField: YCode2 YValue: 20
All are comboboxes and data is defined as:
XField [Lookup] Tab:
Table/Query
select [Lookups].[ValueText],[Lookups].[Id]
from [Lookups] where [Lookups].[LookUpType] = 'XCodes';
BoundColumn: 2
ColumnCount: 2
This one correctly shows up the corresponding Valuetext.
The other one is not enabled on form but again is a combo:
XVAlue [Lookup] Tab:
Table/Query
select [Lookups].[ValueNum],[Lookups].[Id]
from [Lookups] where [Lookups].[Id] = [myTable].[XField];
BoundColumn: 2
ColumnCount: 2
In OnChange code of XField:
Me.XValue.Value = Me.XField.Value
But it doesn't show the XValue all the time if there is a change even though when I open the table correct Id is recorded.
TIA
Cetin
Update: Solved.
Cetin