Build a form for pivottable type dataentry

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I have a problem... I am trying to build a form that allows data entry "accross" the form instead of down the form

My table is structured like this

CREATE TABLE [SalesForecast]
[SKU] [int] NOT NULL
[CustomerID] [nvarchar] (15) NOT NULL
[Period] [int] NOT NULL
[Year] [int] NOT NULL
[SalespersonID] [nvarchar] (10) NOT NULL
[ForecastQty] [int] NULL DEFAULT (0)
[SalePrice] [money] NULL

But when a user enters data I want them to see something like this

Item -- Sales Person -- Customer -- January -- March -- April -- May -- June -- July -- August -- etc..

For each month I want them to fill in the ForecastQty

Any suggestions on ways to do this

thanks
Bradley
 
This kind of entry form will involve a bit of programming. Without actually
trying it, this approach should work.

A Crosstab query can display the results with the layout you desire, but is
read-only. You can create a form in Continuous view, with the crosstab as
its RecordSource.

In the Enter event of each of the Jan - Dec text boxes, SetFocus to another
text box that is unbound. Copy the value into the unbound box. You can also
set its Left property so it appears in the same place and the user is
unaware that it is a different box.

In the AfterUpdate event of the unbound box, write the data to your table by
executing an Append query statement (or Update statement if the record
already exists). To show the updated value on the form will then require a
Requery, and a return to the correct row.

--
Allen Browne - Microsoft MVP. Perth, Western Australia.

Reply to group, rather than allenbrowne at mvps dot org.

Bradley said:
I have a problem... I am trying to build a form that allows data entry
Click to expand...
"accross" the form instead of down the form.
My table is structured like this:

CREATE TABLE [SalesForecast] (
[SKU] [int] NOT NULL ,
[CustomerID] [nvarchar] (15) NOT NULL ,
[Period] [int] NOT NULL ,
[Year] [int] NOT NULL ,
[SalespersonID] [nvarchar] (10) NOT NULL ,
[ForecastQty] [int] NULL DEFAULT (0),
[SalePrice] [money] NULL )

But when a user enters data I want them to see something like this:

Item -- Sales Person -- Customer -- January -- March -- April -- May --
Click to expand...
June -- July -- August -- etc...
 
Back
Top