How to populate value from a specific record?

[illusionek]

I use a simple code (below) to import text files from a folder. All works well but now I need to populate a value from particular record for all records from this file.

I have attached an example of my table after importing all text files.

I need to add another column and based on this example I would like to see PO000072 in Field9 for the first 21 rows then PO000073 for another 21 rows and PO000074 for others. Number of rows will vary depending on the file.

I know I will need to use a stagging table for this, I just do not know how to point Access to PO000072 etc. The whole line can be easily identified because it will always start with DH at the beginning.

Can anyone help?

Function ImportText()
Dim strFile As String
 
'   Set file directory for files to be imported
    strPath = "C:\Documents\"
'   Tell it to import all Excel files from the file directory
    strFile = Dir(strPath & "*.txt*")
 
'   Start loop
    Do While strFile <> ""
        ' Import file
        DoCmd.TransferText acImportDelim, , "Table1", strPath & strFile, False
        ' Loop to next file in directory
        strFile = Dir
    Loop
End Function

 

[illusionek]

Can anyone help please?

 

[pbaldy]

I looked at this yesterday and my only thought was importing the text file line by line, tracking that value as you go. That was because I don't know that you can count on the sort order in the table remaining as it is if you tried to process it after import. It just occurred to me that if you add an autonumber field to that table, it may give you a field to ensure the data remains in the correct order. If so, it's a fairly simple matter of stepping through the table with a recordset, updating a variable each time the DH line changes, and using that variable to populate the new field.

 

[illusionek]

Many thanks for your valuable feedback. However I still struggle how to identify the right record. In Excel I would say for example read value from D4 if D1 = DH but how to achieve this in Access?

 

[pbaldy]

Not much different; presuming a recordset:

If rs!FieldName = "DH" Then