Using Look Ups in a Table

[DevilsSlide]

Can I do this and where am I going wrong or am I wasting my time because it just can't be done?

In the Data Sheet View of a table [tblOKd] I want to list only individual members (as a Combo Box) assigned to a team as defined in [tblIndiv] based on the selection in the field [tblOKd].[GrpName].

I've tried many different combinations in "Row Source", as close as I can get when I try to populate the records in Data Sheet view of the table I get a MsgBox - ('Enter Parameter Value' & 'tblOKd.TeamName'). Using 'SELECT [tblIndiv].[IndivName] FROM tblIndiv WHERE (([tblOKd].[TeamName]=[tblIndiv].[TeamName]));' in the "Row Source" of the table properties tab for that field it does only list members of that team, but when I add a new record for a different team it still only lists the same members.

What am I doing wrong???

I have listed a samplified version of my tables, fields and values below:

Tables Used
Table Name: tblTeamName
Field Name: TeamName
Record Values: Team1 thru Team3

Table - tblIndiv
Field Name - TeamName
Field Name - IndivName
Record Values: User1 thru User9
*I've assigned User1 to 3 to Team1
*I've assigned User4 to 6 to Team2
*I've assigned User7 to 9 to Team3

Table Name: tblOkd
Field Name: OKdID (Auto Number Key)
Field Name: TeamName (LookUp Combo Box using DISTINCT)
Field Name: OKdBy (LookUp Combo Box)

Relations:
[tblTeamName].[TeamName] to [tblIndiv].[TeamName]
[tblIndiv].[TeamName] to [tblOKd].[TeamName]

Please Help!!!
John

 

[Darrylles]

Hi DevilsSlide,

When using a combo as a 'lookup', remember that if you add another record to the 'lookup' combo's table - then you need to refresh that combo's data before it can display the new record.

Use the combo's Requery method.

Regards,

Darrylle

"Never argue with an idiot, he'll bring you down to his level - then beat you with experience." darrylles@totalise.co.uk

 

[DevilsSlide]

Darrylle,

Thanks - I didn't have a ReQuery when I first posted this. I did add one and that got it working fine in the Form View, but what I'd was really hoping be able to do was have that also function at the Table Level. I'd like to be able to have Staff/Co-Workers entering & updating in the Form(s) and with some of the Data be updating directly in the table.

So I think what I'm trying to firgure out is a way to call a Requery on an Update of the 'table![tblOKd]![TeamName]' field where it looks only at the 'Current Record Only' (that brings up another issue I'm having with Conditional Formatting, but that will be for a post in another area) when changed in the Table.

Bottom line: I want to be able to do what the Form does but directly in the Table without needing Forms.

Any Thoughts?

Thanks
John