Combining Line Items From Query onto a Form (1 Viewer)

[Shipper225]

Yeah I managed to squeeze in a day off, it's shocker around here for that. I have been trying to figure the easiest way to update multiple line items using a form or a query. We tried using DSum but that would give me the total pieces per truck load. I tired using the count and or sum from the query but that didn't allow me to update any of the line items.

 

[vbaInet]

We tried using DSum but that would give me the total pieces per truck load.

With reference to the above, what is your objective? I thought that was sufficient?

 

[Shipper225]

The DSum formula we worked on still displayed all the line items instead of condensing them down to a single record that would update all the line items in the referenced table.

 

[vbaInet]

I think it will be easier if you upload a stripped down version of your db and tell me where the problem is.

 

[Shipper225]

Here is a basic version.

 

[vbaInet]

And ...

I think it will be easier if you upload a stripped down version of your db and tell me where the problem is.

 

[Shipper225]

As I mentioned when I run a shipment through the query I have 18 lines per shipment thus the form displays 18 lines instead of 1 line listing a QTY of 18. When I do have it display the 1 line I need to be able to type into the Freight cost, invoice and the Invoice paid fields to have it update all the related records.

 

[vbaInet]

I'm just making sure you've only got one form and query in there? The problem query/form.

 

[Shipper225]

Yeah the whole database is far to big to upload here. Should be 1 form, 1 query, 3 related tables

 

[vbaInet]

Ok, I will probably have a look at it later tonight or tomorrow.

 

[Shipper225]

Well thank you for any input you can give. As I said I started a new form thinking it may be easier to just do a search with fields to fill in and have a code that would plug in the data but I couldn't get the code to pass the data to all related records.

 

[vbaInet]

You didn't include any sample data.

 

[Shipper225]

My apologies, it was to large to post as one full unit. Attached is a database with just the table, copy the table over the existing one that was with the first attachment.

 

[vbaInet]

Now that you've included some sample data you didn't include the form and query.

 

[Shipper225]

Ok, as I mentioned since the size of the table with all the data is to large for one attachment you have to take the depthrecord database and put that table into the frt cost sample database

 

[vbaInet]

The less work I can do the better. I have too many windows open here and I'm helping lots of other people

Could you merge both of them and ZIP it. That will shrink to less than 2MB.

 

[Shipper225]

Attached. Used WinZip to compress all the data down.

 

[vbaInet]

So, you have three tables. I mentioned in post #36 that you need to look into using a subform. You need a subform for your other tables. That's the only way of getting rid of duplicates and still be able to manipulate it.

 

[Shipper225]

Ok, I'll give it a shot but just to show my inexperience I may be back if I can't figure out the subform. Thank you for the help.

 

[vbaInet]

No problemo!

Just to clarify, you will have a main form and two subforms. But play around with it and see what you come up with. Bear in mind that nothing is impossible so if there's something you think can't be done, just ask before dismissing the idea.

Happy developing!