[Last Call] Learn how to a build a cloud-first strategyRegister Now

x
?
Solved

How to create a data entry grid in access 2007

Posted on 2007-08-02
8
Medium Priority
?
606 Views
Last Modified: 2013-11-28
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)
0
Comment
Question by:VoodooFrog
  • 5
  • 3
8 Comments
 
LVL 58

Expert Comment

by:harfang
ID: 19620524
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°)
0
 

Author Comment

by:VoodooFrog
ID: 19620583
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?  
0
 
LVL 58

Expert Comment

by:harfang
ID: 19620736
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°)
0
Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
LVL 58

Expert Comment

by:harfang
ID: 19620989
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°)
0
 

Author Comment

by:VoodooFrog
ID: 19625014
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...
0
 
LVL 58

Accepted Solution

by:
harfang earned 2000 total points
ID: 19625589
You need to create your crosstab and save it (say as qxtbThisProduct) and then build the make-table query on that:

SELECT * INTO zttblTempTable FROM qxtbThisProduct

I don't know of a way to write that as a single query. But qxtbThisProduct will be static, so it's not a big problem.
(°v°)
0
 

Author Comment

by:VoodooFrog
ID: 19626576
Thank you for your help!
0
 
LVL 58

Expert Comment

by:harfang
ID: 19626616
Welcome! -- (°v°)
0

Featured Post

NEW Veeam Agent for Microsoft Windows

Backup and recover physical and cloud-based servers and workstations, as well as endpoint devices that belong to remote users. Avoid downtime and data loss quickly and easily for Windows-based physical or public cloud-based workloads!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

How much do you know about the future of data centers? If you're like 50% of organizations, then it's probably not enough. Read on to get up to speed on this emerging field.
Windows Explorer let you handle zip folders nearly as any other folder: Copy, move, change, and delete, etc. In VBA you can also handle normal files and folders, but zip folders takes a little more - and that you'll find here.
In Microsoft Access, when working with VBA, learn some techniques for writing readable and easily maintained code.
Have you created a query with information for a calendar? ... and then, abra-cadabra, the calendar is done?! I am going to show you how to make that happen. Visualize your data!  ... really see it To use the code to create a calendar from a q…

829 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question