need checkboxes in transient subform recordset



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 :pkAgentRegion, 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
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.


Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question