#error on field calculating value

[rpeare]

So you're saying you haven't modified the table structure at all?



My suggestion was that you split your ORDERS into 2 tables, 1 table that contains all the information specific to the ORDER, 1 table that contains all the PRODUCTS related to that ORDER. The way you have it you will have to duplicate a lot of information on every line. Let's say a customer orders 10 different items from you and wants them all shipped to the same address. You will have to fill in the shipping information on 10 lines to consistently get the information to show on any shipping labels etc. Not to mention both your form and subform are using the same basic data source (ORDERS table) which is a big big problem.

[burrina]

Thanks, that's a good idea.However it causes a structural problem with my Category/Orders subform I use to input products from different categories. I implemented your suggestion and it caused my Dllookup code to not work and I did not know how to fix it. I have come up with a workaround of sorts to solve the problem. Your scenario of having to fill in duplicate shipping information is incorrect though.On my main form you can enter once the shipping information and then enter as many products as you want on the same order form.The only drawback is that you can only one item at a time, if you want to enter many items at a time right now you can't.

This is the feature I want to implement. I have made a form with a listbox on it where you can select many items from the category/products but have not figured out a way to make it enter those products on the subform TimeAndCatProdSub which is on my main form

What you don't realize is that this db is part of a much larger db and is an add-on module. I wish I could go back and re-design a lot, but it is just not practical.

Remember, I am a advanced beginner here. Attached is the form and query I would like to use to make the subform TimeAndCatProdSub use to enter many items at a time. Maybe there is a better way no doubt but it is what I have come up with for now.

[rpeare]

Your scenario of having to fill in duplicate shipping information is incorrect though.On my main form you can enter once the shipping information and then enter as many products as you want on the same order form.The only drawback is that you can only one item at a time, if you want to enter many items at a time right now you can't.

This is in fact exactly what I'm saying. Your structure will NOT support (easily) multiple items purchased on the same PO. Period.

To handle that you have to do what I've said twice now (split the orders table into order information and item information). If you can't or won't alter this structure I'm going to have to bow out of this conversation because you are going to create a *TON* of problems down the line trying to do things the way you are. Every reliable outlet for purchase order databases will tell you you should organize your data the way I've suggested and by basing both your main form and subform you're making the situation much much worse (though it may not be apparent at the moment)

If you want to work on correcting the structure to make your multiple items per purchase order workable I can help with that but I can't, in good conscience, offer you a temporary solution for a poorly designed data structure.

[burrina]

Thanks, but the changes you suggested make the database unusable. I realize it is a better way but do you see the problem it creates for me? The code behind the form TimeAndCatProdSub will NOT work if the changes are made, I have tried it already. If you are willing to show me a way to make it work I will gladly make these changes since i have already created another table,query and form with the NEW product details separated.

[rpeare]

Let's be clear, the changes do not make the database unusable, they just demand that you do more coding/design work to incorporate and update your database so that it will do what you want.

I can probably write a simple database that involves taxable/non-taxable and a discount factor but it would not be based on your database if that's what you're looking for. It would be up to you to adapt an example to your needs.

[burrina]

Thanks for your help.By unusable I mean that I can't figure out how to re-write the code so the subform will work again, yes I agree. What I am after now is what I can't find on the net in my searches and it appears that I have stumped the band on the forum. I know I am not the first guy to want to be able to enter multiple items at once on a form. I have been using a listbox with some code to try and accomplish this, but with no luck. I have seen some Purchase Order db's but NONE with the multiple item feature. Do I juts need to call it a day and move on or is there some other way to approach this? I am at my coding limit here and can go no further. I have been concentrating on window dressing and some minor adjustments for the db. Does anyone have a form that will let them add multiple items to another form or even the same form, I sure would like to see it if it exists. Here is what I am trying at the moment. My idea was to use this to add multiple items to the subform TimeCardCaAndtProdSub form.