Link to home
Start Free TrialLog in
Avatar of VoodooFrog
VoodooFrogFlag for United States of America

asked on

How to create a data entry grid in access 2007

I have a three table relationship in my database that I am having trouble designing a good entry for for.  Table 1 is related to table 2 in a one to many relationship.  Table 2 is related to table 3 in a one to many relationship.  This database is to enter new quotes into our system.  Table 1 is for individual parts, table 2 is for the quantities that I want to put prices in for the part, and table 3 is the list of the actual charges for the quantity.  All of the charges for a given part will be the same for each quantity.

What I am trying to do is create a sort of grid entry to speed up the data entry of tables 2 and 3.  A pivot table can create a view that is very nice, but nothing can be entered through it.  So I am trying to find a way to mimic the look of a pivot table but be able to still enter data.  

An example:

Part 1 (from table 1) needs to be quoted for a run of 50, 100 and 150 units.  (qty is table 2)  each of these qty's needs to have material costs, labor costs, and shipping costs assigned per unit.  I want to create the following:

PART 1 CHARGES:

        Qty    50      100     150
Chg
Material      
Labor  
Shipping

which can then be filled out as:

PART 1 CHARGES:

        Qty    50      100     150
Chg
Material      2.0     1.5       1.0  
Labor         1.0     .75       .5
Shipping     .25      .2        .15

I do not want to have a fixed limit on the number of quantities or charges that can be entered in a given quote.  I also do not want to have to do this in excel, that is how we are currently doing things, but information is not being saved as well as it should be.    I also have not been able to find a way to do this form in a standard form/subform method.  I was hoping to use pivottables, but those seem to be view only.

Some thoughts that I have thought about but don't know how to implement:  embed an excel sheet inside an access form (not sure if that is possible)  Create a temporary table which could represent the frid better (not sure how to create a table on the fly, and not sure how to format it even if I could)
Avatar of harfang
harfang
Flag of Switzerland image

I have tried out several solutions for this:

a) Create a form with a bunch of textboxes, looking like a grid.

This means you will fill the values form code, and use the after_update events to write the values back to the table. The fact that you will need to trap several events for each control makes the module rather unreadable. You can use advanced class module techniques, but it's never simple.

b) Create a temporary table for editing.

This might be a little less work. A global "save changes" button can write back the data to the original table. Especially useful if the table can grow in one direction: the user can easily add new records. In your case, it would mean presenting the data like this:

Qty    Material    Labour     Shipping
 50
100
150

So that the quantities and the costs can be created in one go.

c) Export to Excel and re-import.

This is the most complex, but also the most versatile. Editing is done in Excel and the table can potentially grow in both directions. Quite a bit of work, though, due to the many errors that need to be anticipated, the Excel automation, and the interface.

Using an embedded Excel sheet is just as much work. And probably much less stable.

d) Use a FlexGrid control.

I have never done that in a production database (only played with it), but that would be the standard way to do it in VB. If you can find a good ActiveX FlexGrid control, you can do something similar to a), hopefully with a cleaner program (easier to maintain).


As you see, you are in for some non-trivial programming. Perhaps someone has a ready-made sample you can use, but I never saw one.

I suggest you go with b), as you already know about cross-tab queries. And it's simple enough for us to help you build it here.

(°v°)
Avatar of VoodooFrog

ASKER

As I've been searching the last few minutes options b and d both appeal to me.  

-- b is sitll leaving me a little unsure of how to dynamically create a table -- I have done  a lot of recordsets, but never a table.  That seems like a good amount of work (which is okay of course) I am fairly versatile at programming, so I consider this a possiblity, if I can get a little push in the right direction to start.  

 --d  is appealing becasue it sounds as though the control would be already to handle the grid and I would just have to push some information into it.  Any thoughts on where to find said flex grid?  
About FlexGrid, I have tried a freeware version (but can't find it now, it was quite a while back) and also the Microsoft Flexgrid, but that requires a developer's license (e.g. VB, Visual Studio, etc.). If you try on a form (Insert | ActiveX control) and choose "Microsoft Flexgrid control", you'll know if you have such a license.

As for the temp table, you can easily create a make-table-cross-tab query to generate the data. Make sure you use fixed column heading (the "column headings" property of the query).

To put the temp table's data back into the original table, that will require a small set of action queries: either delete all existing data for the part and use one append query per column to write it back, or use a combination of delete, update, and append queries.

(°v°)
Note: please delete your other question: http:/Q_22738655.html

This one does appear in Database, misc., and Access, and you cannot ask the same question for a total over 500 points. As long as nobody answered, you can still delete it. Later, you would have to request for it in community support.

(°v°)
how do I create a table from a crosstab query?  If I switch the query to a make table query, it loses the crosstab functionality...
ASKER CERTIFIED SOLUTION
Avatar of harfang
harfang
Flag of Switzerland image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Thank you for your help!
Welcome! -- (°v°)