N
NKTower
Access 2003 ADP, SQL Server 2005
I need to populate a many-to-many table and would like to fill various data
fields identified by the key pair with the same user-supplied initialization
data. Here's a sketch of the tables involved
tAgent ; pkAgentID, AgentName, etc.
tRegion : pkRegionID, RegionName, etc.
tAgentRegion
kAgentRegion, fkAgentID, fkRegionID, other columns as needed
tTechnician : pkTechnicianID, TechnicianName, etc.
tTechRegion : pkTechRegion, fkTechnician, fkRegion, various data items - the
target table
Real world situation:
1 At an agency, can have multiple simultaneous users, but all act as a
particular Agent.
2 Technicians are associated with an agency
3 A technician may choose to operate in technician-selected regions and this
may change from time-to-time
Processing scenario:
User brings up a form for associating a technician to technician's choice of
region(s)
Main form is bound to tTechnician
Subform (sfm_AgentRegion) currently lists regions associated with agency via
tAgentRegion binding. No checkbox at present.
Mainform contains various unbound data fields - about a dozen - dates,
rates, etc.
I need to populate tTechRegion for subset of regions identified by (new)
checkbox in sfm_AgentRegion rows with the unbound data. Initially each row
in tTechRgion will have the same data, but it will then change over time.
Current system works by individual record selector in subform, but there is
no checkbox per row. Button on main form then posts the data. If there are
8 regions, that may mean 8 selections of region and 8 post button clicks.
Would like to have checkboxes and one button click to post the unbound data
to all by walking through the checked rows.. Yes I could do it via
double-click event in subform, but I'd like visual representation.
Problem: How do I create recordset for sfm_AgentRegion with a bit
field/checkbox such that it is transient, unique to each workstation rather
than Agent since more than one user can be 'the agent'?
I was thinking perhaps a table with key fkTech/fkAgentRegion and data
columns bitSelected and RegionName.
Purge it on form current when Tech changes, populate it from tAgentRegion,
then walk the subform recordset on btn_POST click.
I need to populate a many-to-many table and would like to fill various data
fields identified by the key pair with the same user-supplied initialization
data. Here's a sketch of the tables involved
tAgent ; pkAgentID, AgentName, etc.
tRegion : pkRegionID, RegionName, etc.
tAgentRegion
kAgentRegion, fkAgentID, fkRegionID, other columns as neededtTechnician : pkTechnicianID, TechnicianName, etc.
tTechRegion : pkTechRegion, fkTechnician, fkRegion, various data items - the
target table
Real world situation:
1 At an agency, can have multiple simultaneous users, but all act as a
particular Agent.
2 Technicians are associated with an agency
3 A technician may choose to operate in technician-selected regions and this
may change from time-to-time
Processing scenario:
User brings up a form for associating a technician to technician's choice of
region(s)
Main form is bound to tTechnician
Subform (sfm_AgentRegion) currently lists regions associated with agency via
tAgentRegion binding. No checkbox at present.
Mainform contains various unbound data fields - about a dozen - dates,
rates, etc.
I need to populate tTechRegion for subset of regions identified by (new)
checkbox in sfm_AgentRegion rows with the unbound data. Initially each row
in tTechRgion will have the same data, but it will then change over time.
Current system works by individual record selector in subform, but there is
no checkbox per row. Button on main form then posts the data. If there are
8 regions, that may mean 8 selections of region and 8 post button clicks.
Would like to have checkboxes and one button click to post the unbound data
to all by walking through the checked rows.. Yes I could do it via
double-click event in subform, but I'd like visual representation.
Problem: How do I create recordset for sfm_AgentRegion with a bit
field/checkbox such that it is transient, unique to each workstation rather
than Agent since more than one user can be 'the agent'?
I was thinking perhaps a table with key fkTech/fkAgentRegion and data
columns bitSelected and RegionName.
Purge it on form current when Tech changes, populate it from tAgentRegion,
then walk the subform recordset on btn_POST click.