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
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
Code:
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