Sort Recordset and add sequential numbers (1 Viewer)

[NeverTooLate]

Hi there!
I'm writing a recordset to a table (ready for export to SAP) which has 2 sources of data (queries). I need to sort the recordset then add a sequential Item Number.
My 2 problems are, when I sort the recordset, it sorts each source separately - I need them to be sorted together.
My second problem is the sequential number. The recordset holds vehicle service information. There are many vehicles, each with many services and many items in each service. The sequential numbers are for the items in a service, start at 10 and increase by 10. So:
Vehicle 1 - Service 1 - Item 1 - 10
Item 2 - 20
Item 3 - 30
Service 2 - Item 1 - 10
Item 2 - 20
Item 3 - 30
and so on.
If I can get the sort to work I was thinking of moving to the first record and setting a variable to be the service number of that record. Then add sequential numbers until that service number changes. Then reset the variable to the now current service number and restart the counter.
Will this work?? Is a bookmark the right way to set the variable?

The below code is part of a function that creates 3 table so I'll only paste the relvant code for this table creation.

Function Create_PPS_WINPAQ_Table()

Dim db As Database
Dim source As Recordset
Dim source2 As Recordset
Dim source3 As Recordset
Dim dest As Recordset
Dim dest2 As Recordset
Dim dest3 As Recordset
Dim dest4 As Recordset

DoCmd.SetWarnings False
DoCmd.OpenQuery "qry_Cleanup_tbl_PPSservCodes"
DoCmd.OpenQuery "qry_Cleanup_tbl_PPSservOps"
DoCmd.OpenQuery "qry_Cleanup_tbl_CommonServTempl"
DoCmd.SetWarnings True

Set db = CurrentDb()
Set source = db.OpenRecordset("qry_PPSservCodes")
Set dest = db.OpenRecordset("tbl_PPSservCodes")
Set source2 = db.OpenRecordset("qry_PPSservOps")
Set dest2 = db.OpenRecordset("tbl_PPSservOps")
Set source3 = db.OpenRecordset("qry_CommonServTempl")
Set dest3 = db.OpenRecordset("tbl_CommonServTempl")
Set source4 = db.OpenRecordset("qry_CommonServTempl_CP")

Do Until source3.EOF()
    dest3.AddNew
    dest3![common servcode] = source3![commonServCode]
    dest3!Operation = source3![OperationNo]
    dest3!Description = source3!Cause
    dest3![Vendor] = source3![Vendor]
    dest3![Type Class] = source3![TypeClass]
    dest3![Data ID] = source3![DataID]
    dest3![t1 Code] = source3![T1code]
    dest3![T2 Code] = source3![T2code]
    dest3![Condition] = source3![VehicleDescription]
    dest3![Cause] = source3![Cause]
    dest3![Remedy] = source3![Remedy]
    'dest3![ItemNo] = source3![]
    dest3![item type] = source3![ItemType]
    dest3![Part No] = source3![PartNumber]
    dest3![Operation No] = source3![OperationNo]
    dest3![Subl Type] = source3![SubletType]
    dest3![DDUC Type] = source3![DeductionType]
    dest3![Quant] = source3![PartQty]
    dest3![UOM] = source3![UOM]
    dest3![Part_Description] = source3![PartDescription]
    dest3![Service] = source3![ServiceNumber]

    If source3![CategoryID] = "2" Or source3![CategoryID] = "3" Then
        dest3![Part No] = "SUBLET"
        dest3![SUBL INV NO] = "RCL itm"
        dest3![Operation No] = Null
    Else
    If source3![ItemType] = "FR" Then
        dest3![Part No] = Null
        dest3![Part_Description] = source3![Cause]
    Else
        dest3![Operation No] = Null
    End If
    End If
    
    dest3.Update
    source3.MoveNext
Loop

Do Until source4.EOF()
    dest3.AddNew
    dest3![common servcode] = source4![commonServCode]
    dest3!Operation = source4![OperationNo]
    dest3!Description = source4!Cause
    dest3![Vendor] = source4![Vendor]
    dest3![Type Class] = source4![TypeClass]
    dest3![Data ID] = source4![DataID]
    dest3![t1 Code] = source4![T1code]
    dest3![T2 Code] = source4![T2code]
    dest3![Condition] = source4![VehicleDescription]
    dest3![Cause] = source4![Cause]
    dest3![Remedy] = source4![Remedy]
    'dest3![ItemNo] = source4![]
    dest3![item type] = source4![ItemType]
    dest3![Part No] = "SUBLET"
    dest3![Subl Type] = source4![SubletType]
    dest3![DDUC Type] = source4![DeductionType]
    dest3![Quant] = "1"
    dest3![UOM] = "EA"
    dest3![SUBL INV NO] = "RCL itm"
    dest3![Part_Description] = "Customer Payment"
    dest3![Service] = source4![Service]

    
    dest3.Update
    source4.MoveNext
Loop
    
dest3.Sort = "[common Servcode]" & "[Service]"

Thanks for your help.

 

[pbaldy]

I'm in the middle of making dinner, so only have a minute. I'd open the recordset on an SQL statement that did my sorting:

Set source = db.OpenRecordset("SELECT...ORDER BY Field1, Field2")

I'd handle the sequential item with two variables; one for the sequential item and one for the service item. Before the loop:

SequentialVariable = 10
ServiceVariable = source!ServiceField

Inside the loop

If ServiceVariable = source!ServiceField Then 'same service code, increment the variable
  SequentialVariable  = SequentialVariable  + 10
Else  'new service code, reset both
  SequentialVariable  = 10
  ServiceVariable = source!ServiceField
End If

Gotta stir the sauce! I'll check back later.

 

[NeverTooLate]

Dear Paul,
You are my Saviour!! I can't thank you enough! Simple instructions that worked a treat!!
The Solution:

Dim itemCounter As Integer
Dim dest5 As Recordset

Set dest5 = db.OpenRecordset("qry_CommonServTempl_update", dbOpenDynaset)
itemCounter = 0
servicevariable = dest5![Service]

Do Until dest5.EOF
   dest5.Edit
    If servicevariable = dest5![Service] Then
        itemCounter = itemCounter + 10
        dest5![itmno] = itemCounter
    Else
        itemCounter = 10
        servicevariable = dest5![Service]
        dest5![itmno] = itemCounter
    End If
    
    dest5.Update
    Debug.Print itemCounter
    dest5.MoveNext
Loop

Hope the dinner turned out well.

 

[pbaldy]

Homemade spaghetti sauce and a bottle of wine...what could be better?

I was thinking as I ate I should have started the variable at 0; glad you sorted that out.

 

[NeverTooLate]

Hi again!
One last question - when I open a recordset and using your example:
Set source = db.OpenRecordset("SELECT...ORDER BY Field1, Field2")
can I use a query that I've created but change the filter criteria on the fly? I want to use the same query but get 2 different results for 2 different reports. I guess it's not difficult to write the SQL twice and just alter the filter but I thought I'd ask anyway.
Thanks

 

[pbaldy]

Not sure what you mean by changing the filter criteria on the fly. You can use a DAO QueryDef to change the SQL of a saved query. You could also open a recordset with an additional query:

db.OpenRecordset("SELECT * FROM QueryName WHERE Whatever")

 

[NeverTooLate]

The 2nd option you gave is more what I was thinking about. Thanks again
Erica

 

[pbaldy]

Happy to help Erica!