Adding the count of items from one table to another

[DMT Dave]

Hi Guy's, I can't figure out this one, 2 issues with it also which I'm not understanding, any help would be appreciated

tblAssign may have:

7 records for delno 1 (from strBody)
4 records for delno 2 (from strBody)
3 records for delno 3 (from strBody)
etc.........

These are coming up in the immediate window correct as one single line

del 1 Joe bloggs Town - PostCode 7 Items
del 2 Fred Bloogs Town - PostCode 4 Items
etc...

in tblAssign there will be 7 records for 7 items del 1
4 records for 4 items del 2

In tblJobSheets

There is one field per delno so i am trying to add the following:

!Del1 = Joe Bloggs - Town - PostCode - 7 Items
!Del2 = Fred Bloggs - Town - PostCode - 4 Items
etc....



Issue 1: I can't work out how to run through the records from rs2 to rs and add to different fields where the delno matches ? ie: above !Del1 etc.... or send results from the immediate window which is correct

Issue 2: I thought this would work but doesn't, if the job sheet number (iJS) doesn't have a DelDate in the DelDate field, then this is the one to edit and add data, I have used If IsNull DelDate but this is coming up with:

No Current Record, when i hover mouse over debug code

!Driver = rs2.Fields("Driver")

, error is !Driver = Null ( I Know this is the case as thats how I am trying to decipher which job sheet to update)

Forgive me if i am going bananas

Dim rs As DAO.Recordset, rs2 As DAO.RecordsetDim strVehicle As String, strDriver As String, strSQL As String, strBody As String
Dim dteDelDate As Date, dtDelDate As Date
Dim iJS As Integer, iDrops As Integer
Dim varItems As Variant


iJS = Me.txtCurrentSheet
strVehicle = Me.cboVehicle
strDriver = Me.cboDriver
dtDelDate = Me.txtDelDate
dteDelDate = Format(dtDelDate, "mm/dd/yyyy")


iDrops = DMax("DelNo", "tblAssign", "[Vehicle] = '" & strVehicle & "' And [Driver] = '" & strDriver & "' And [DeliveryDate] = #" & dtDelDate & "#")


strSQL = "SELECT tblAssign.DeliveryDate, tblAssign.DelNo, Count(tblAssign.ItemType) AS CountOfItemType, tblAssign.DelTo, tblAssign.Town, tblAssign.PostCode, tblAssign.Driver, tblAssign.Vehicle " _
    & "From tblAssign " _
    & "GROUP BY tblAssign.DeliveryDate, tblAssign.DelNo, tblAssign.DelTo, tblAssign.Town, tblAssign.PostCode, tblAssign.Driver, tblAssign.Vehicle " _
    & "HAVING (((tblAssign.DeliveryDate) = #" & dteDelDate & "#) And ((tblAssign.Driver) = '" & strDriver & "') And ((tblAssign.Vehicle) = '" & strVehicle & "')) " _
    & "ORDER BY tblAssign.DelNo;"
    
    Set rs2 = CurrentDb.OpenRecordset(strSQL)
    Do Until rs2.EOF
    varLifts = rs2("CountOfItemType") & " " & "Items"
        strBody = strBody & rs2("DelNo") & " " & rs2("DelTo") & " " & rs2("Town") & " " & rs2("PostCode") & " " & varItems & vbNewLine
    rs2.MoveNext
    Loop
        
    Debug.Print strBody
    
    Set rs = CurrentDb.OpenRecordset("Select * From tblJobSheets WHERE IDNumber = " & iJS)
        If IsNull(rs.Fields("DelDate")) Then
        With rs
        .Edit
        !Driver = rs2.Fields("Driver")
        !DelDate = rs2.Fields("DeliveryDate")
        .Update
        .Close
        End With
        
        End If

Hope i have explained enough to help you to help me

Kindest

[Minty]

Why not just join your count query to your delivery/driver records? Or am I missing something?

You shouldn't be storing that result if it can be calculated from other sources.

[Welshgasman]

You are at rs2.EOF when you are trying to the use the rs2 data?
Also if you hover on a line where the variable/field is being set, then it will not show the set value, you need to look AFTER that line, or look at what is setting it.

[DMT Dave]

Ahh ok guy's thank you, I haven't really messed around much with queries apart from generating and copying SQL

Minty, when you say join, is the query set to other than Select Query ?

[Minty]

Perhaps this would be easier if you gave us some sample data from your various tables and your desired results.
I can't work it out from the description in post#1.

To me it sounds like you could create a query (or queries) that totalled up the driver deliveries directly without messing with looping around record sets.

[DMT Dave]

Hi Minty, hoping this will help you to help me

in tblAssign, there may be
based on DeliveryDate, Vehicle, Driver from tblAssign
7 records for del no 1 based on
6 records for del no 2
4 records for del no 3

tblJobSheets has fields
DelDate needs to be Delivery date from assign
Del1 needs to be Data for all 7 records from tblAssign
Del2 needs to be data for all 6 records from tblAssgin
del3 needs to be data for all 4 records from tblAssign

bearing in mind we only need to edit the tblJobSHeet by job sheet number, there are already blank records to use with just vehicle registration in there

so we are saying
del1 from tblAssign to field Del1 in job sheets data from assign delno 1> DelTo = Joe Bloggs-some town-some postcode- 7 items
del2 from tblAssgin to field Del2 in job sheets data from assign delno 2 > Fred Bloggs -sometown - some postcode - 6 items

Hope I've explained correctly fingers crossed

Prior to all this, a text box will show the next available job sheet number so thats the one to edit where, its called Forms!frmMainMenu!frmIndex3!txtCurrentSheet, this will display in this sample db as 4586, this is not a problem its transfering a number of records from assign to just 1 record based on the txtCurrentSheet number

Please forgive me if i haven't again explained properly but hope this helps to help

[DMT Dave]

i think file attached

[DMT Dave]

cant attach a file for some reason, ive made a template