Combining Line Items From Query onto a Form

[Shipper225]

Where would I put the term since Access won't let me put it right into the criteria or total fields?

 

[vbaInet]

In the Control Source property of a textbox (for example).

=DSum("Qty", "TableName", "...Criteria here...")

 

[Shipper225]

Thank you for working with me on this, I'm sure it's getting annoying with all the added questions but I just keep failing somewhere along the lines. I tried the DSum with this formula:

=DSum([UnitsShipped],[tblDepthRecord])

UnitsShipped being the Field I want summed up, tblDepthRecord being the table it is stored in and wasn't sure what the criteria was needed since I just want the total per shipment displayed in a manner that will allow me to enter data into the form based off the query and update all the related line items.

 

[vbaInet]

At least you gave it a go. Each parameter needs to enclosed in double quotes like I wrote it and for the criteria part you could do something like:

=DSum("[UnitsShipped]","tblDepthRecord", "[ShipmentID] = " & [ShipmentID])

 

[Shipper225]

I always give it a few attempts and a bit of my own research before figuring I'm failing badly Here is the code I tried:

=DSum("[UnitsShipped]","tblDepthRecord","[BOL #] = " & [BOL #])

BOL # being the reference field (that I made in the query) that the cells should be grouped on. Problem is that it's returning a #Error. Now I've tired checking the " mark and moving it around a bit but still get the same problem. So my question becomes this: Can I use a field I've made or do I need to use the criteria field that is in the DepthRecord table, I ask because that actually uses 2 fields to give me data for the BOL # field.

 

[vbaInet]

Ok, what is the data type of [BOL #]?

 

[Shipper225]

Here's the field info from the query screen:

BOL #: ("JB-" & [tblWorkOrders.JobNumber] & "-" & [ShipmentNumber])

JobNumber being an alpha numeric field and ShipmentNumber being a number field. Also it is sorted by Ascending if that matters at all. This works so that JobNumber: 206A with ShipmentNumber: 3 is displayed as: JB-206A-3 and is sorted in the proper order.

 

[vbaInet]

So [BOL #] exists in the query? In that case you can use the query as the source/domain in place of the table. Then your DSum() will look like this:

=DSum("[UnitsShipped]","[COLOR=blue]QueryName[/COLOR]","[BOL #] = [COLOR=red]'[/COLOR]" & [BOL #] & "[COLOR=red]'[/COLOR]")

Because [BOL #] is a Text field, note the need to surround it in single quotes as highlighted above.

 

[Shipper225]

Ok that did wonders getting rid of the error. I used the code:

=DSum("[UnitsShipped]","qryFrt-tracker","[BOL #] = '" & [BOL #] & "'")

As you instructed but now I have a blank form with no data being pulled over from the query. This is what the form is:
COR # (display in Ascending order) BOL # (display in ascending order) Ship Date, QTY Shipped (where this formula is stored) FRT Cost, Invoice Number, and Invoice Paid (yes no check box)

 

[vbaInet]

That's not the cause of your blank form. Setting the Control Source doesn't affect your form's Record Source in anyway

You may have changed something in error or tried to open the Record Source. Check the Filter and Data Entry properties. Data Entry should be No.

 

[Shipper225]

Wow something so simple. Thanks again you've been a great help on all of this.

 

[vbaInet]

Happy to help Shipper225!

 

[Shipper225]

Ok so I guess I was wrong. Ran the form with more data in it and I am able to edit the 3 fields I need but it is not combining the line items as I had originally set out to do. My hopes had been to combine all the fields for the same shipment input the data and have it pass through to each line item. This way I'm only looking at 50 lines opposed to 900 lines with duplicate data.

 

[vbaInet]

Are you saying you want to get rid of the Duplicates?

 

[Shipper225]

Yes, right now I have 1 shipment with 18 line items i.e. JB-206A-1 will show up 18 times on the report, if I can get it to show up only once with a total of 18 (like the formula we worked on does) and lets me update that one line item pushing the data to all related ones I'd be all set. Maybe I'm trying to do to much in one form I'm not sure until I try and fail.

 

[vbaInet]

Maybe I'm trying to do to much in one form I'm not sure until I try and fail.

Perhaps you are. Revise your query or look into using a subform.

 

[Shipper225]

So I worked around the idea by creating a search form that will allow me to bring up all the line items from the shipments by searching the BOL # field. I wrote a code that I figured would let me fill out the text fields on the top of the form and have it automatically fill in the corresponding fields in the search results but it isn't completing them all, it is only filling in the top line. Attached is my code:

Private Sub FillIn_Click()
On Error GoTo ErrID_Err

    If (PD) = True Then
        Me.FrtCost = Me.Freight
        Me.InvoiceNumber = Me.Invoice
        Me.Paid = Me.PD
   End If
   
ErrID_Exit:
   Exit Sub

ErrID_Err:
   MsgBox Error$
   Resume ErrID_Exit

End Sub

I know I am missing something that would tell the code to fill in all related fields just can't figure out what it is.

 

[vbaInet]

What's wrong with the bound form?

 

[Shipper225]

The bound form being the first form I created? Sorry still learning all the terms of access

 

[vbaInet]

Correct! Why are you trying to fill in the controls using code?

It's also been 2 days since your last post so I may have forgotten what the whole thread is about.