1

I'm trying to loop through records in a table with part no's and with matching part no's, increment a value in the pos field. I asked a question earlier(Object variable or With block variable not set Access vba) with the same project but for a different step of the process (running a query). I haven't found a question that addresses this problem in Access yet, but I have taken some pointers from this question: Code to loop through all records in MS Access . Now, I'm trying to write the code to loop through the records and increment the value in the pos field.

The query that will run:

SELECT CTOL.ID, CTOL.BOM_PART_NAME, CTOL.CII, CTOL.[PART FIND NO], CTOL.CSN, CTOL.AFS, CTOL.EQP_POS_CD, CTOL.LCN, CTOL.POS_CT, CTOL.SERIAL_NO, CTOL.PART_NO_LLP, [CTOL_Asbuilt].[PART-SN], [CTOL_Asbuilt].[PART-ATA-NO], [CTOL_Asbuilt].[PW-PART-NO]
FROM CTOL LEFT JOIN [CTOL_Asbuilt] ON CTOL.[PART FIND NO] = [CTOL_Asbuilt].[PART-ATA-NO];

Code so far (credit to Kostas K for helping me on the other question):

Option Compare Database
Option Explicit

'Const adOpenStatic = 3
'Const adLockOptimistic = 3

Function queryDatabase()

Dim db As DAO.Database
Dim rs As DAO.Recordset
Dim qdf As QueryDef
'Dim rsQuery As DAO.Recordset
Dim rows As Variant


Dim part_find_no() As String
Dim eqp_pos() As Integer
'Dim strSQL As String

Dim i As Integer
Dim j As Integer
'Set objConnection = CurrentDb.OpenRecordset("CTOL")

Set db = CurrentDb

Set qdf = db.QueryDefs("SicrProcess")

Set rs = qdf.OpenRecordset(dbOpenDynaset)

If rs.EOF Then GoTo Leave
rs.MoveLast
rs.MoveFirst


For i = 1 To rs.RecordCount
    Debug.Print rs.Fields("PART FIND NO") & " " & rs.Fields("EQP_POS_CD")
    rs.MoveNext
Next i

Leave:
    On Error Resume Next
    rs.Close
    Set rs = Nothing
    qdf.Close
    Set qdf = Nothing
    Set db = Nothing
    On Error GoTo 0
    Exit Function

ErrProc:
    MsgBox Err.Description, vbCritical
    Resume Leave
End Function

The two fields I want to loop through are PART FIND NO in the CTOL table and EQP_POS_CD in the CTOL table. The value in EQP_POS_CD should be incremented by 1 if the last PART FIND NO is identical to the current no. The result set should contain the other fields selected in the query as well. Is there anything else I need to add to get it output? Right now, I'm not too concerned as to how it's output, whether in a window or in datasheet format (though I would appreciate pointers on this if anyone knows). Any pointers on how to accomplish this? Also, if there's anything I'm missing in my question that's important, or if there's something I could do better when posting a question, I'm open to discussing it. I'm also open to discussion of how to approach the problem. Thanks!

Erik A
  • 31,639
  • 12
  • 42
  • 67
evvdogg
  • 37
  • 1
  • 9
  • Do you really need to save this increment to table? An incremented sequence can be calculated without VBA. One way is to use textbox RunningSum property in a report. Another is DCount() expression in query but domain aggregates can perform slowly. Fairly common topic. – June7 Jun 08 '17 at 18:47
  • That might work. I'm thinking I could possibly use a DCount expression to retrieve the count for each PART FIND NO. This can be done through just a query? I'm not trying to get the amount of each part no though. Identical PART FIND NO's will have a value incremented by 1 of the last PART FIND NO. – evvdogg Jun 08 '17 at 19:03
  • Yes, it can. Here is one example of the DCount() expression. http://www.accessforums.net/showthread.php?t=66362&highlight=DCount – June7 Jun 08 '17 at 19:09
  • Could you show me an example? I'm having a hard time following. Most examples seem to be for unique records, but I'm trying to assign a value to each record with same value in part find no. – evvdogg Jun 08 '17 at 19:58
  • The referenced link is an example. I show a possible query solution for your data in an answer. Post your attempted DCount() code for analysis. – June7 Jun 08 '17 at 20:16

1 Answers1

0

Possibly:

SELECT CTOL.*, [CTOL_Asbuilt].[PART-SN], [CTOL_Asbuilt].[PART-ATA-NO], [CTOL_Asbuilt].[PW-PART-NO],
DCount("*", "CTOL", "[Part Find No]=" & [Part Find No] & " AND ID<" & [ID])+1 AS Seq
FROM CTOL LEFT JOIN [CTOL_Asbuilt] ON CTOL.[PART FIND NO] = [CTOL_Asbuilt].[PART-ATA-NO];
June7
  • 19,874
  • 8
  • 24
  • 34
  • I'm getting an error with the ID field, as ID is in both tables. – evvdogg Jun 08 '17 at 21:30
  • Then use appropriate table name prefix - would that be CTOL? Wouldn't hurt to give the ID fields unique name in each table. – June7 Jun 09 '17 at 00:55
  • Okay, I did that. It didn't update the field value of EQP_POS_CD, however. All of the rows have the value of 1 for that field when I run the query. – evvdogg Jun 09 '17 at 18:26
  • It won't update the table. It is just a query calc. Post your attempted SQL statement and sample data. Can edit your original question. – June7 Jun 09 '17 at 18:31
  • But the data values should be accurate in the query results. They're just the value of 1 on my end, oddly. I'm still confused why we're getting different results. The data was imported from Excel, and that one field didn't import properly. I can't post the data, unfortunately, as it is proprietary. – evvdogg Jun 09 '17 at 18:37
  • I'm not questioning your results, however. I'm questioning why it's working here though. Maybe it's more an issue of the data. – evvdogg Jun 09 '17 at 18:38
  • I'm going to see if it works on another sample of data for me. I'm beginning to think the code actually does what it's supposed to do, but there may be an issue with how the data is being pulled or imported into Access. I'm going to investigate that. – evvdogg Jun 09 '17 at 18:49
  • You could test the principal of the code on a dummy table like I did, with data you have confidence in. – June7 Jun 09 '17 at 19:08
  • Yeah, I'll give that a try. – evvdogg Jun 09 '17 at 21:04