Manually Create Continuous Form 1

[maxhugen]

I'd like to create a continuous subform, consisting of unique unbound controls, in order to present the user a screen that resembles a spreadsheet for data entry.

Background:

There is a contracts table called `sov`. Each contract has many items in `sov_item`.

We need an invoice screen, to enter amounts against contract items, ie `sov_item`. However, the amounts also need to be broken out by work type, eg Labor, Materials, etc.

Invoices tables are `invoice` (linked to `sov`), `invoice_item` (linked to `sov_item`) and `invoice_item_type`.

Rather than using the traditional subform approach, it would be far easier for a user to enter data in a format that resembles a spreadsheet. This would have "columns" like:

Contract Item | Quantity | Labor Cost | Materials Cost | Total Cost
----------------------------------------------------------------------
Item 1        |   100    |    $200    |                |    $200
Item 2        |    10    |   $1000    |    $1500       |   $2500
Item 3        |          |            |                |
Item 4        |    30    |            |    $2000       |   $2000

I could build this subform as a Single Form, using CreateControl, populating the fields as I go with any existing data, and then saving all updated data in one go from a button [Save Changes]. That shouldn't be a problem.

However, using a Continuous Form would have important benefits, such as the ability to Sort and Filter.

I have my doubts that this is possible, but I thought I'd ask if any of the very clever people on this forum had ever tried something like this?

MTIA

Max Hugen
Australia

 

[MajP]

Sure of course it is possible, and not radically difficult. But why? What functionality are you looking for that you do not get in a bound continous form? Maybe I am missing something, but that looks pretty much like a continous form. Why not use a bound subform that acts like a spreadsheet? At worst use a flexgrid.

 

[maxhugen]

G'day MajP

Yeh, maybe I've gotten myself into an unnecessary hole...

The "Contract Items" column comes from `sov_item`, which is Left Joined, so that all possible items are visible for the user to enter Invoice data against.

The Invoice Quantity is stored in `invoice_item`, but the costs (Labor etc) are further split via table `invoice_item_type`.

Are you suggesting that I use a crosstab query (to get the invoice_item_type records on one row), and then use a Datasheet as the default form view?

Max Hugen
Australia

 

[MajP]

To verify your tables

sov_Item
contractID (primary key)

Ivoice_Item
invoice_Item_ID
invoice_Item_type
invoice_Item_Quantity

But yes build a cross tab query that would display your data as you show. Bind this to a continous subform. Now that will not be editable, but you could play with a couple of form events to get the best functionality. Example:
put a double click event in quantity, labor, and material costs. If you click on a item 1, Material cost it would pop up a small form to enter the Material cost for item 1. Enter a value, close the form, requery the crosstab. You can also play with the key press events so that if the user hits an up arrow it will move to the "cell" above like a spread sheet.

The other possiblity is to build a table in the above format "Item, Quantity, Labor, Material". Lets call that tblDisplay. On the main forms on current event:
1.Delete existing records out of table display
2.have a crosstab query to return the info for the current contract
3. Do an insert query into tbldisplay.
4. No you would basically have a non normalized table for display purposes populated with the normalized data.
5. This table is editable so enter data like a spread sheet
6. When done (basically the next on current event) run an update query that updates your Invoice_Item table with the data from table display.
7. run a delete query on table display

And since it was an on current event go to step 1 and start over.

That would actually work well. I will try to demo you something if interested.

Please provide actual table names and fields if possible, in a format I used above.

 

[maxhugen]

Thanks MajP

I'd come to the conclusion to use a crosstab query behind a continuous form, with a popup for editing just as you have suggested. Nav arrows on the popup would be useful, I'll add those.

Cheers

Max Hugen
Australia