Design Question

[RitaM]

I created an inventory database for a nonprofit. I have a table for finished part numbers and another table for component part numbers.

Now they are assembling school kits (20 items for each kit). I'd like to create an easy form for data entry. I'd like to have a form where they could just enter a finished part number and quantity assembled. Based upon that entry, I'd like my inventory to automatically deduct all the component parts from inventory.

I can do this manually with a form and a subform, but I'd love to make data entry easier.

Thanks!

 

[StarGrabber]

Hi,

tell me if I'm wrong: a 'finished part number' represents the number of a kit.

If so, then the users have to create one by adding components to it. I think you will need a third table containing only the component part numbers (and perhaps the quantity), which are a part of a kit, i.e. the kit number is the foreign key.


I suggest you use a form without a subform, but with...

- a combobox which offers all the kit numbers and the entry '(new)'

- a combobox which offers all the component part numbers

- a listbox which displays these part numbers, i.e. the items of the kit

- some command buttons, like 'Add to kit', which should be pressed by the users after a selection in the part numbers combobox has been made


If it should be possible to set a quantity, an additional textbox is required (near the part numbers combobox).

If the 'Save' button is pressed, the listbox items are to be evaluated in a loop. By means of an update query you deduct the component parts from inventory.

What do you think?


StarGrabber

 

[RitaM]

Structure Explained

I'm sorry for not explaining the database better.

Finished Parts Table: (just numbers--example 2404 for International School Kit)
Component Parts Table: (dropdown field for finished part and component part #)
Inventory Transaction: (table for all transactions--adding inventory, out when they ship)

I made up another table "Kits". I want the user to under enter date, finished part #, number of kits completed. I would like the data entered here to automatically deduct the component parts from the inventory.

Example:
Date: [1/1/13]
Finished Part: [2404]
Quantity of kits completed: [10]
**********************************
If 10 kits are completed, I'd like 20 pens, 20 pencils, 10 rulers, 10 erasers to be deducted from inventory. In the component part table, I indicate the quantity of items in each kit. I can do this manually, but I would love to make this easy.

Component Parts in 2404
1111 Pens (2/kit)
1112 Pencils (2/kit)
1113 Ruler
1114 Erase
etc.

 

[RitaM]

How to autofill?

My form
KitID: [1] Autonumber
Date: [1/1/13]
Finished Part: [2404] dropdown list from Finished Part's Table
Quantity: [10]

Subform**************************
KitID: [1] Foreign Key
Component (component parts automatically fill?)
Part Quantity
[111] [20] (qty in component part's table x qty entered above)
[112] [10] (1 in each kit)
[113] [20] (2 in each kit)
***********************************

 

[StarGrabber]

Thank you for the explanations but I still have some questions. I'm not able to get through it yet. Would you mind to upload your database?

 

[RitaM]

OK I changed field names so I'm embarrassed to send you this very "messy" database. I thought I would have time to clean it up, but I haven't had time to do it.

It is split so you'll have to relink "HopePacks" to the back end. It opens to a switchboard. I put the form in RED "Kits". I explained what I wanted to happen on this form (if it's possible). Currently I can make it happen manually, but I'd love to automate it.

I'd like the user to just enter a Finished Part Number. Then I would like to automatically deduct all of the component parts from inventory. I hope it makes sense.

I've never attached a database, so hopefully this will work.

Thanks so much!

 

[StarGrabber]

Hi Rita,

you don't have to be embarrassed about your databases. Compared to others I've seen, yours is only 'middle-messy'. But to tidy a database of this size (primarily
the data structure and the code), it will take you a lot of time and effort. ---

But for now we try to make the best out of what we have: in the form 'fKits' you can see the procedure 'Private Sub KitsCompleted_AfterUpdate()' I made for you (besides of two small ones above).

Please note, that I blend some database objects. You might blind them out again.

A question remains: from which amount (or whatever) you want the component parts to be subtracted?

StarGrabber

 

[RitaM]

My inventory totals are generated from the table [Inventory Transactions].

I enter finished product and it autofilled!!!!! You are amazing. I have to run my son to football practice now, and I just had a second to look at this. I'm soooo excited!! I can't wait to see how you got this to work. Thanks so much!!!!

 

[StarGrabber]

Thank you for the joyful feedback. I wonder if you are able to generate your inventory totals now as I did nothing special for it.

 

[RitaM]

Sorry for the delayed response. It took me several days, because the database was a mess!!! Ugh. The next time I innocently change table names and fields I'll think twice. It really messes everything up. It took me awhile to get all the forms & queries working again.

I'm just in awe of your [After Update] event! Are you self taught or are you a professional programmer? I can't believe how this will streamline the work! I've taken advanced Access Classes before, but it's information overload. I've never heard of "Snapshot" so I'm excited to google and learn more about all the code.

Thanks so much again. You went way over and above anything I could ever hope for!!!!

 

[StarGrabber]

Thanks again for your pleasant response.

The way I realised your automation is - even if it doesn't look like to you - "quick & (middle-)dirty"! There is a disadvantage: remember the comment line "'Open a recordset which contains all records of the table 'InventoryTransactions':" The problem is the word 'all'. If the users keep adding transaction records assiduously, the recordset gets bigger and bigger over the years. And this makes your application every time slower (i.e. bad responsiveness).

In the meantime I tried to improve this functionality. You will find a solution without this downside attached. Instead of 72 code lines we now have more than the double. One reason for that is the table structure of your database. - Another reason is the intention of keeping the code as much comprehensible as possible: I divided the original - very large and complicated looking - procedure into several smaller ones. The procedure 'KitsCompleted_AfterUpdate' is now the "manager" of the function routines below. I hope this helps you to get through. - And I added the procedure 'KitsCompleted_BeforeUpdate'.

But I also changed some other "details" in 'fKits', 'sfKits' and the related query 'qKitssf':

- in 'fKits' the tabstop order of the controls (if it's not more convenient now than before, please change it back)

- in 'fKits', in the subform control 'sfKits' I removed the linking field between sub and main form. I compensated that by adding a criterion in the query 'qKitssf' (field 'KitID')

- in 'sfKits' the control source property (i. e. the SQL string) of the combobox 'ComponentPart' (do you really need a combobox there?)

And I strongly recommend you to rename the field 'Date' in the table 'InventoryTransactions' (e.g. to 'InvTrDate'). 'Date' is the name of a VBA function (i.e. a reserved expression), so, if you use it, Access doesn't know if you mean the table field or the function. You can prove this by opening the query 'qKitssf' in design mode. Have a look what happens to the second field.

Please report if anything does not work.

 

[RitaM]

Thank you so much for all the extra work you did. I did spent time getting queries renamed and forms working. I was hoping I could just go in and copy/paste the code into the form Kits. I can't even work with the forms, because I'm getting errors. I hate to bother you when you've done so much, but I was hoping you could take a look at my revised back end and front end. Maybe it's just something minor that I messed up in my copy and paste. I'm copied and pasted code in before, but I must have done something wrong with field names.

Attached is the revised back end.

 

[RitaM]

Here is the front end.

 

[StarGrabber]

Hello Rita,

what I've found:
- routine 'KitsCompleted_BeforeUpdate' was in the module twice (if you get an VBA error telling you somewhat like 'ambiguous expression', then you should search your modules for double procedures).

- when 'fKits' opens, the field value 'InventoryTransactions.Date' is requested. The reason for that is, you've renamed the field in the table 'InventoryTransactions' but not the corresponding field in the query 'qKitssf' afterwards. I suppose you've opened that query before renaming the field in the table.

I also had a look at your form 'fKits'. As explained in my previous post, I changed some things there. In the frontend file you've uploaded this time, everything is like before, so I assume you have changed only the code in the module.

To get your application working again do the following:

- Delete the forms 'fKits', 'sfKits' and the query 'qKitssf' in your frontend file.

- Import these three objects from the last file I've returned to you.

- Rename the date field in table 'InventoryTransactions' (if not already done in the backend file you currently use)

- Adapt this field in the query 'qKitssf'.

(If you have to upload more than one file you can zip them all together).

Good luck!

 

[RitaM]

I'm not sure what happened/what I've messed up now. Now when I open "fKits", this message is popping up "formulare!fkits!qKits". I've been trying to figure things out, but I'm not getting anywhere. I did go back and saw this in the earlier file "qKits". I am attaching the front and back end databases that I'm using.

You've been so patient in helping me, but I understand if this is taking up too much of your time. I tried to follow your directions.

 

[StarGrabber]

You sound quite desperate. Don't worry, I will support you until you succeed.

The message "formulare!fkits!qKits" which popped up was my fault. I apologise!

I've added 'qCompPartsForSubform'. Why? For two reasons. 1. It did not work the way it should (that is to show the parts right after selection of a 'FP'). 2. For the subform 'sfComponentParts' you need exactly 5 fields. But 'qComponentParts' has 15! So I changed the record source from 'qComponentParts' to 'qCompPartsForSubform'.

I didn't touch 'fKits ORIGINAL' and 'sfKits ORIGINAL' so they are really original.

Do you have further issues?

 

[RitaM]

When I open the form [Kits], it seems it's working with the exception of plugging Component Part numbers. isn't getting added.

To see what's left in inventory, I open the form [Products] on the switchboard. It should show kit's completed. A while ago I changed the field name from "component parts" to CP.

I don't want to "mess" anything up. Am I doing something wrong? I just opened the front end that you last sent and linked it to the backend.

Thanks so much!

 

[StarGrabber]

Sorry, there was something I've overlooked. See, if it works now (including form 'Products').

I set the property 'Allow append' in 'sfComponentParts' to 'No'. Because of the relation between the tables 'ComponentParts' and 'CompontPartsActual' it's not possible to add new parts in this subform. But if I remember well, it never was.

If further problems arise don't hesitate to post.

 

[RitaM]

IT WORKS!!!!!!!!!!!! Thank you so much!!! You are amazing!

 

[StarGrabber]

Hi Rita,

don't exaggerate...

In case this thread is coming to an end, let me add some thoughts.

In the meantime I had a look to some "other corners" of your big application. In summary I would say you made the same "mistake" I did years ago. I started developing without any knowledge and then I went on and on. Words like "architecture" or "code structure" were foreign to me! - But one day I got stuck. Definitely stuck. And it dawned on me what went wrong.

You developed an application nearly without VBA code. And most of the existing code was made - loosely speaking - by the Access assistant. O.k., this was and is
exactly the purpose of Access, set the barrier for database beginners as low as possible and provide a tool for RAD (rapid application development). And small applications don't need code implicitly. But as you've seen now, there are functions you cannot implement using some Access assistants. -

Then, I had to decide if either I wanted to dispense with "sophisticated functionality" or... start from scratch! - You can imagine which decision I made (otherwise I wouldn't have been able to help you).

Imagine a skyscraper. If you want to change any field name in some of your tables it's like when you change a primary wall in the basement. What then happens you have experienced now. And you surely know the slogan "don't change a running system". But if every developer would obey this counsel there would be no evolution. So, if you don't want to start from scratch, continue changing field names, but slowly, one after the other, step by step. After a change, work with the application for a few days. If everything is o.k. release it to your users and take the next step. And above all: make a backup prior to every change. Write a change log.

Finally I want to give you only one but very precious link: http://www.access-programmers.co.uk/forums/showthread.php?t=172363
I want to indicate especially point # 3.

All the best to your non profit project!