Loop through records and update

[primagic]

Hi

I have the following code which loops through the records in a recordset and tells me if the previous record is the same as the current record.

 Dim rs As DAO.Recordset
   Dim db As DAO.Database
   Dim qd As DAO.QueryDef
   Dim AccountNumber As String
   
   
   Set db = CurrentDb
   Set qd = db.QueryDefs!Query1
   qd.Parameters![Forms!frmDashboard!InvoiceFromDate] = [Forms]![frmDashboard]![InvoiceFromDate]
   qd.Parameters![Forms!frmDashboard!InvoiceToDate] = [Forms]![frmDashboard]![InvoiceToDate]

Set rs = qd.OpenRecordset
    
AccountNumber = rs!AccountNumber
Do While Not rs.EOF
If rs!AccountNumber = AccountNumber Then
'same Account number
MsgBox "Same account"
Else
'different Account Number
MsgBox "different account"
End If
rs.MoveNext
Loop
'Catch the last owner here
rs.Close

What I now need to do is, update the current record with the next invoice number (taken from the tblInvoices using DMax) and move to the next record (which it does) and if its the same account number then add the same invoice number otherwise if its different get the last invoice number and add 1

Thanks

 

[Andrzejek]

Please post your Query1 SELECT statement.

Plus, show us the code that you acctually have, because what you showed us here will not work: you assign just in one place
[tt]AccountNumber = rs!AccountNumber[/tt]
so the AccountNumber will always be the same no matter where you move in your rs

Have fun.

---- Andy

 

[Andrzejek]

My best guess at this time: (teh same AccNo wil get the same InvNo, if that's what you want)

Set rs = qd.OpenRecordset
[blue]
InvNo = "SELECT MAX(invoicenumber) + 1 FROM tblInvoices"
[/blue]
AccountNumber = rs!AccountNumber
Do While Not rs.EOF
    If rs!AccountNumber = AccountNumber Then
        'same Account number
        MsgBox "Same account"[blue]
        UPDATE tbl SET invoicenumber = invNo
        WHERE AccNo = AccountNumber[/blue]
    Else
        'different Account Number
        MsgBox "different account"[red]
        AccountNumber = rs!AccountNumber[/red][blue]
        InvNo = InvNo + 1
        UPDATE tbl SET invoicenumber = invNo
        WHERE AccNo = AccountNumber[/blue]
    End If
    rs.MoveNext
Loop

rs.Close

Have fun.

---- Andy

 

[primagic]

Thanks for your help

This is my query1 sql.

SELECT  PermTESTAC.ID, PermTESTAC.AccountNumber, PermTESTAC.Field8, PermTESTAC.InvoiceID
FROM PermTESTAC
WHERE (((PermTESTAC.Field8) Between [Forms]![frmDashboard]![InvoiceFromDate] And [Forms]![frmDashboard]![InvoiceToDate]))
ORDER BY PermTESTAC.AccountNumber;

I just ran the code and it is issuing the invoice number for the first record but then its using the same invoicenumber for every record after that whether they have different accountnumbers or not.

What I am trying to do is, if the current accountnumber is the same as the previous, issue the same Invoice Number otherwise issue a new invoice number using Dmax +1

 

[SkipVought]

also, it is best to post MS Access questions in one of the many MS Access forums like forum705

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[primagic]

I realised after I posted that it was in the wrong forum. If I could move it I would

 

[Andrzejek]

Did you put the RED line, of something with the same logic in your code:

    Else
        'different Account Number
        MsgBox "different account"
        AccountNumber = rs!AccountNumber[red]
        InvNo = InvNo + 1[/red]
        UPDATE tbl SET invoicenumber = invNo
        WHERE AccNo = AccountNumber
    End If
    rs.MoveNext
Loop

That should give you next invoice number.

Have fun.

---- Andy

 

[primagic]

I get a syntax error on the line

UPDATE tbl SET invoicenumber = invNo

I am using Access by the way.

this is my current code

Dim rs As DAO.Recordset
   Dim db As DAO.Database
   Dim qd As DAO.QueryDef
   Dim AccountNumber As String
   
   
   
   Set db = CurrentDb
   Set qd = db.QueryDefs!Query1
   qd.Parameters![Forms!frmDashboard!InvoiceFromDate] = [Forms]![frmDashboard]![InvoiceFromDate]
   qd.Parameters![Forms!frmDashboard!InvoiceToDate] = [Forms]![frmDashboard]![InvoiceToDate]

Set rs = qd.OpenRecordset

InvNo = "SELECT MAX(invoicenumber) + 1 FROM tblInvoices"

AccountNumber = rs!AccountNumber
Do While Not rs.EOF
If rs!AccountNumber = AccountNumber Then
'same Account number
UPDATE tbl SET invoicenumber = invNo
WHERE AccNo = AccountNumber
Else
'different Account Number
AccountNumber = rs!AccountNumber
InvNo = InvNo + 1
UPDATE tbl SET invoicenumber = invNo
WHERE AccNo = AccountNumber
End If
'rs.MoveNext
Loop
'Catch the last owner here
rs.Close
MsgBox "Invoice Number added"

 

[Andrzejek]

Of course you will get syntax errors, because I did not give you ready code to copy/paste and use, I gave you a 'pseudo code', the logic to follow.

Line:[tt]
InvNo = "SELECT MAX(invoicenumber) + 1 FROM tblInvoices"[/tt]
will NOT get you the last invNo + 1, but that's the SQL I would use. You put it the right way in your code.

I just try to show you the way, but you need to do the work.

The update whould look something like:

str = "UPDATE tblYourTable SET InvoiceNumberField = " & invNo & _
" WHERE AccNo = " & AccountNumber

Conn.Execute str

Have fun.

---- Andy