Complex Dynamic Table Data Entry Form

[asgard]

I have a table of Products with X items, and a table of SalesReps with Y
items. Both lists are dynamic -- let's say that we regularly add products
and hire sales reps (and let's assume that we do not remove them, so data
integrity is maintained). I would like to have a dynamic data entry form
that captures unit sales for each sales rep for each product (time period is
irrelevant). So, I would like to have a single table-shaped form that as the
row headers has the X product names, as the column headers has the Y rep
names, and within the table X*Y text boxes where I can enter the units sold
by each rep. If tomorrow I add a product and bring up the form again, I
would get a table of (X+1)*Y cells and would be able to override the old
values and new ones (for product X+1).

I would structure the tables in the following way:

tblProduct
ProductId (PK)
ProductName

tblSalesRep
SalesRepId (PK)
SalesRepName

tblSalesData
ProductId (PK)
SalesRepId (PK)
SalesAmount

My questions are: i) is this doable in Access?, and ii) if so, is there a
sample database that demonstrates this functionality?, or if not, is there an
elegant workaround that would help me accomplish the same thing?

Many thanks for your help!

 

[Allen Browne]

No. There's not a simplistic way to do that.

It is easy enough to create a crosstab query where the product names are Row
Heading, the sales reps are Column Heading, and the quantity of sales are at
the intersection point of the matrix. But this is read-only: you cannot
enter data there.

It would be possible to create a Continuous (or Datasheet) view form with
255 text boxes placed side-by-side (that's the max number of sales reps
Access can handle here), and write VBA code in Form_Open to dynamically
generate the crosstab and assign the first 255 sales reps to the Control
Source of the initally-unbound text boxes so they show in the correct
columns, presumably hiding the unused text boxes and changing the Left and
Width of the existing ones to make best use of available screen space. Then
you would also need to code an unbound text box for data entry. As soon as
you click on any text box, it would need to copy its contents into the
unbound text box, set its Left so it appears in the same place, and SetFocus
to it. In the AfterUpdate of this unbound box, you would need to execute an
action query to append, delete, or update the appropriate value in the
table, then requery the form, and then find the correct record again on the
form (so it displays the new data.)

As I said, not a simple solution.

 

[Peter Hibbs]

You could use a flex grid control to display the results of your
crosstab query on a form, you can dynamically program the flex grid
control at run time so that it changes the number of columns and rows
to fit the output from the query. Have a look at my 'Crosstab Query in
Flex Grid' demo program at :-
http://www.rogersaccesslibrary.com/...p?FID=21&SID=5debzz2c87c6957859f13b1d2zcccfb4

If you don't already have the flex grid control installed on your PC
then download the 'Flex Grid Demo.mdb' file on the same site which
shows how to install and register the control.

HTH

Peter Hibbs.