For... ...Next Loop relating to Query

[BSJWright]

Hi

Here is my problem - I am trying to assign record values within a query (only about 30 records) to an array in order to automatically insert a debit note number. Currently I have a list of suppliers that I am going to raise debit notes on each month, and each debit note for each supplier will have a different (sequential debit note number). I want to use the supplier code as the identifier in order to get Access to look at another table and insert debit note numbers accordingly. The number will apply to more than one record in another table, so an autonumber is not viable. In addition, the debit note numbers were at about 1100 when I transferred my system into Access. Here is the code in my module so far:

Sub InsertDebitNoteNumbers()
    Dim SupCode$()
    Dim objField As Field
    Dim CurrentRecord As AcRecord
    
    LastDNNumber = DMax("[Debit Note Number]", "NCR/Debit Note Log")
    TotaltoDebit = DCount("[Total Cost (Local Currency)]", "Companies to be debited", "abs([Total Cost (Local Currency)])>0")
    DoCmd.OpenQuery "Companies to be debited", , acEdit
    Application.Echo True
    For SCode = 1 To TotaltoDebit
            DoCmd.GoToRecord acDataQuery, "Companies to be debited", acGoTo, SCode
'           This is where I need help - I want to assign a value to SupCode$(SCode), which should be the record that Access is currently on, as selected above.
    Next SCode
    
    DoCmd.Close acQuery, "Companies to be debited", acSaveNo
    DoCmd.OpenTable "test", acViewNormal, acEdit
    DoCmd.RunCommand acCmdApplyFilterSort
    DoCmd.Close acTable, "test", acSaveYes
    DoCmd.OpenTable "test", acViewNormal, acEdit
    
    SCode = 1
    
    For DNNumber = LastDNNumber + 1 To LastDNNumber + TotaltoDebit
'               This code will insert the debit note number into the required field (Code not written yet, but this should be straightforward).
    Next DNNumber
    DoCmd.Close acTable, "test", acSaveNo

End Sub

I've also tried putting the query into a form and trying to do it that way, but it doesn't work. If anyone can help, I would be most grateful.

 

[ecniv]

Would it be easier to open a recordset object, loop through getting the values you want to an array, then either using multiple updates or another loop with a recordset to update those values accordingly in the other table?
(ADO or DAO)

As to the code you have, you'd need to redim to a load more than you're likely to ever get, then hold a variable pointer to the last array position filled (first one is 0). This pointer moves as you add record numbers.

 

[BSJWright]

Would it be easier to open a recordset object, loop through getting the values you want to an array, then either using multiple updates or another loop with a recordset to update those values accordingly in the other table?
(ADO or DAO)

As to the code you have, you'd need to redim to a load more than you're likely to ever get, then hold a variable pointer to the last array position filled (first one is 0). This pointer moves as you add record numbers.

If it's easier - how do I do this, though? I have my background in Excel and although I've programmed Access with VBA before, this is the most difficult thing I've tried to do. I had thought about exporting the query to Excel programatically, then doing the job in Excel, and re-importing (all through Access)? Is this more trouble than it's worth?

 

[BSJWright]

Adding fields to a table from a query using recordsets.

Would it be easier to open a recordset object, loop through getting the values you want to an array, then either using multiple updates or another loop with a recordset to update those values accordingly in the other table?
(ADO or DAO)

As to the code you have, you'd need to redim to a load more than you're likely to ever get, then hold a variable pointer to the last array position filled (first one is 0). This pointer moves as you add record numbers.

I've finally solved it! If anyone has a similar problem in the future, here is the code that I used:

Sub InsertDebitNoteNumbers()
Dim rsNumDNs As DAO.Recordset
Dim rsInsertDNNumbers As DAO.Recordset
Dim db As DAO.Database

LastDNNumber = DMax("[Debit Note Number]", "test")
TotaltoDebit = DCount("[Total Cost (Local Currency)]", "Companies to be debited", "abs([Total Cost (Local Currency)])>0")

Set db = OpenDatabase("W:\Shared\Debit Note Database.mdb")
Set rsNumDNs = db.OpenRecordset("Companies to be debited", dbOpenDynaset)
Set rsInsertDNNumbers = db.OpenRecordset("test", dbOpenDynaset)

DoCmd.OpenQuery "Companies to be debited", , acEdit
rsNumDNs.MoveFirst
SCode = 1

Do
    Supcode$(SCode) = rsNumDNs![Supplier Code]
    rsNumDNs.MoveNext
    SCode = SCode + 1
Loop Until rsNumDNs.EOF

DoCmd.Close acQuery, "Companies to be debited", acSaveNo
DoCmd.OpenTable "test", acViewNormal, acEdit
DoCmd.RunCommand acCmdApplyFilterSort
DoCmd.Close acTable, "test", acSaveYes
DoCmd.OpenTable "test", acViewNormal, acEdit

SCode = 1
DNNumber = LastDNNumber + 1

Do
    rsInsertDNNumbers.MoveFirst
    Do
        If Supcode$(SCode) = rsInsertDNNumbers![Supplier Code] Then
            If rsInsertDNNumbers![Don't Debit?] = False Then
                If rsInsertDNNumbers![Debited?] = False Then
                    If rsInsertDNNumbers![Hold?] = False Then
                        rsInsertDNNumbers.Edit
                        rsInsertDNNumbers![Debit Note Number] = DNNumber
                        rsInsertDNNumbers.Update
                    End If
                End If
            End If
        End If
        rsInsertDNNumbers.MoveNext
    Loop Until rsInsertDNNumbers.EOF

    DNNumber = DNNumber + 1
    SCode = SCode + 1
Loop Until DNNumber > LastDNNumber + TotaltoDebit

DoCmd.Close acTable, "test", acSaveNo

End Sub

I didn't realise it was that easy.