Adding From 1 Table To Another

[DMT Dave]

Hi Guy's please forgive me on this, i have been on Honeymoon early April and come back to being in Hospital with a life changing illness



I am now limited with vision and permanent migraine

My concentration level is health so that's why i am asking for forgiveness on somthing i should be able to do with my eyes closed prior to illness.............................

I have been absent from work a lot but trying to do some things that health allows!!!!

I am trying to update invoices from storage, the correct amount of records from storage is adding but can't seem to put all records into invoices

I think it's a looping problem that has always shot me down, i have tried moving looping sequence by thinking what i need to loop but can't appear to get it!!!!

Thank you

If Me.cboSource = "Import Storage" Then    intCust = "98"
    iInv = DMax("InvoiceNumber", "tblInvoices") + 1
    sCust = DLookup("Name", "tblCustomers", "[RecordNo] = " & intCust)
    sCustAdd1 = DLookup("Add1", "tblCustomers", "[RecordNo] = " & intCust)
    sCustAdd2 = DLookup("Add2", "tblCustomers", "[RecordNo] = " & intCust)
    sCustTown = DLookup("Town", "tblCustomers", "[RecordNo] = " & intCust)
    sCustPC = DLookup("PostCode", "tblCustomers", "[RecordNo] = " & intCust)
    sCustEmail = "tkepurchaseinvoiceuk@tkelevator.com"
    varQty = "0"
    intQty = DCount("MFG", "tblStorage", "[Days] < " & varQty)
    
    Set rs = CurrentDb.OpenRecordset("Select * From tblInvoices")
    With rs
        For i = 1 To intQty
        .AddNew
        !InvoiceNumber = iInv
        !InvoiceTo = sCust
        !InvoiceAdd1 = sCustAdd1
        !InvoiceAdd2 = sCustAdd2
        !InvoiceTown = sCustTown
        !InvoicePostCode = sCustPC
        !Nett = "2.50"
        !Vat = "2.50" * 0.2
        !Total = "2.50" * 1.2
        Me.txtCust = sCust
        Me.txtCustAdd1 = sCustAdd1
        Me.txtCustAdd2 = sCustAdd2
        Me.txtCustTown = sCustTown
        Me.txtCustPostCode = sCustPC
        Me.txtNett = intQty * "2.50"
        Me.txtVat = intQty * "2.50" * 0.2
        Me.txtTotal = intQty * "2.50" * 1.2
        
        .Update
        Next i
    End With
            
    
    sSQL = "SELECT tblStorage.DelTo, tblStorage.Town, tblStorage.PostCode, tblStorage.MFG, tblStorage.SL, tblStorage.LiftType, tblStorage.DateDue, tblStorage.Days " _
            & "From tblStorage " _
            & "WHERE (((tblStorage.Days)<0));"


    Set rs2 = CurrentDb.OpenRecordset("Select * From tblStorage WHERE Days < " & varQty)
        
        While Not rs2.EOF
            sDelTo = rs2.Fields("DelTo")
            sTown = rs2.Fields("Town")
            sDelPC = rs2.Fields("PostCode")
            sRef = rs2.Fields("MFG") & " - " & rs2.Fields("SL")
            
        
        
    Set rs3 = CurrentDb.OpenRecordset("Select * From tblInvoices WHERE InvoiceNumber = " & iInv)
    With rs3
        For i = 1 To intQty
        .Edit
        !DelTo = sDelTo
        !Town = sTown
        !PostCode = sDelPC
        !LiftNo = sRef
        .Update
        Next i
    End With
        
        rs2.MoveNext
        
        Wend
        
    Me.lstInvoices.Requery
    
    
End If

[Welshgasman]

Can you explain in English, put comments in your code even? to explain what exactly you are trying to do?
For rs3, you appear to just update the same first record multiple times? You appear to be lucky that there is always at least one record present, but then again we do not know your system.

[DMT Dave]

Hi WGM, thank you, i have sorted it after a 2nd look

Thanks for replying though!

    Set rs2 = CurrentDb.OpenRecordset("Select * From tblStorage WHERE Days < " & varQty)    Do While Not rs2.EOF
        sDelTo = rs2.Fields("DelTo")
        sTown = rs2.Fields("Town")
        sDelPC = rs2.Fields("PostCode")
        sSL = rs2.Fields("SL")
        sLiftType = rs2.Fields("LiftType")
        sMFG = rs2.Fields("MFG")
        iDays = Replace(rs2.Fields("Days"), "-", "")
        rs2.MoveNext


 
    Set rs = CurrentDb.OpenRecordset("Select * From tblInvoices")
    With rs
        .AddNew
        !InvoiceNumber = iInv
        !InvoiceTo = sCust
        !InvoiceAdd1 = sCustAdd1
        !InvoiceAdd2 = sCustAdd2
        !InvoiceTown = sCustTown
        !InvoicePostCode = sCustPC
        !DelTo = sDelTo
        !Town = sTown
        !PostCode = sDelPC
        !LiftNo = "Storage " & iDays & " Days Overdue"
        !PONumber = sSL
        !LiftType = sLiftType
        
        !Nett = "2.50"
        !Vat = "2.50" * 0.2
        !Total = "2.50" * 1.2
        Me.txtCust = sCust
        Me.txtCustAdd1 = sCustAdd1
        Me.txtCustAdd2 = sCustAdd2
        Me.txtCustTown = sCustTown
        Me.txtCustPostCode = sCustPC
        Me.txtNett = intQty * "2.50"
        Me.txtVat = intQty * "2.50" * 0.2
        Me.txtTotal = intQty * "2.50" * 1.2
        
        .Update


    End With
            
    Loop
    Me.lstInvoices.Requery

[Edgar]

Glad you fixed it, but sorry to read about your condition. I hope you find a good treatment that lets you have a life with less pain.

[DMT Dave]

Thank you Edgar