Update Table/Recordset Order

[SgtJarrow]

I receive an input once a month in an excel format. This is a VERY structured file and the import works great. I already have the code to strip out the blank lines and those lines that I really don't need. Now comes the fun part (sarcasm!!!). I need to step through each record of the table and check one field. Based on that field I need to update each record appropriately. More specifically, Field 1 is a generic system-generate ClientId number that may change from month to month and Field 2 is my generated ClientId. I need to check if Field 1 is not Null. If not Null, get my Id from Field 2 and move to next record and set the Id to my Id, until you encounter a new Id in Field 1. Lost you yet???? The code below seems to work, but the data is not always imported in the same order....and that's the problem. If the order is not correct, the code does not work correctly. I think the problem may lie is sorting the recordset prior to stepping through each line and updating, but I have no idea how to do this.....Never been an issue for me until now. I have supplied some sample data....Any Suggestion???? A Big Thanks!

Input
Field1 Field2 OtherFields
1 EEM ..............
..............
..............
2 FQK ..............
3 WOK ..............
4 DDQ ..............
..............

Should end up with
Field1 Field2 OtherFields
1 EEM ..............
EEM ..............
EEM ..............
2 FQK ..............
3 WOK ..............
4 DDQ ..............
DDQ ..............

But sometime the last line in group 1 is imported first and then it doesn't get the proper code in field 2. Here is my code:

'****************Start Code***************
Dim strMichaelImport As String, strMichaelLocation As String, strSQL As String, db As Database, _
rs As Recordset, strAccount As Variant
strMichaelImport = "tblImportMichaelShine"
strMichaelLocation = "H:\Dublin\Billing\Client Billing\Robert\PGGM\Input\MichaelShine.xls"
DoCmd.TransferSpreadsheet acImport, acSpreadsheetTypeExcel97, strMichaelImport, strMichaelLocation, _
False
strSQL = "DELETE tblImportMichaelShine.F1, tblImportMichaelShine.F2, tblImportMichaelShine.F3, tblImportMichaelShine.F4, tblImportMichaelShine.F5, tblImportMichaelShine.F6, tblImportMichaelShine.F7, tblImportMichaelShine.F8, tblImportMichaelShine.F9 " _
& "FROM tblImportMichaelShine " _
& "WHERE (((tblImportMichaelShine.F1) Is Null) AND ((tblImportMichaelShine.F2) Is Null) AND ((tblImportMichaelShine.F3) Is Null) AND ((tblImportMichaelShine.F4) Is Null) AND ((tblImportMichaelShine.F5) Is Null) AND ((tblImportMichaelShine.F6) Is Null) AND ((tblImportMichaelShine.F7) Is Null) AND ((tblImportMichaelShine.F8) Is Null) AND ((tblImportMichaelShine.F9) Is Null));"
DoCmd.RunSQL strSQL
strSQL = "DELETE tblImportMichaelShine.F1, tblImportMichaelShine.F2 " _
& "FROM tblImportMichaelShine " _
& "WHERE (((tblImportMichaelShine.F1) Is Null) AND ((tblImportMichaelShine.F2) Is Not Null));"
DoCmd.RunSQL strSQL
Set db = CurrentDb
Set rs = db.OpenRecordset(strMichaelImport, dbForwardOnly)
rs.MoveFirst

With rs
Do While Not .EOF
If Not IsNull(.Fields(0)) Then
strAccount = .Fields(1)
.MoveNext
Do While IsNull(.Fields(0))
.Edit
.Fields(1) = strAccount
.Update
.MoveNext
Loop
Else
Do While IsNull(.Fields(0))
.MoveNext
Loop
End If
Loop
End With
Set rs = Nothing

MeExit:
Exit Function

MeError:
Call ErrorHandler(Err.Number)
Resume MeExit
'*************End Code*************
Programming isn't a profession of choice.
It's a profession of calling...
"Hey Programmer, your application broke again!"

Robert L. Johnson III, A+, Network+, MCP
robert.l.johnson.iii@ssmb.com

 

[mflancour]

I thought of a backwords way to do this if you dont get any better responces.

You could use Automation to fill series with a step value of 1 in a new column from a1 to eof. then no mater how it imports you can sort by this column.

 

[jimmythegeek]

Some observations:

1) It is ok to put blank lines between lines of code for easibility of reading.
2) Whenever your FROM statement is a single table, you do not need the table name prefixes in the rest of the SQL Statement.
3) Dimension your variables of like data types on seperate lines
4) You cannot delete certain fields, you can only delete an entire record, so when you use a DELETE statement, you only need DELETE * FROM ...
5) You cannot edit a ForwardOnly recordset, I changed it to dbOpenDynaset.
6) I used strSQL a third time to add the ORDER BY you are looking for (F2 may not be the field you want to sort by, if not, change it).
7) Not positive on whether the looping code will work the way you have it coded without being able to test it, but the rest of the code should be ok.

Give that a shot.

============================================
Dim strMichaelImport As String, strMichaelLocation As String, strSQL As String
Dim rs As Recordset
Dim strAccount As Variant

strMichaelImport = "tblImportMichaelShine"
strMichaelLocation = "H:\Dublin\Billing\Client Billing\Robert\PGGM\Input\MichaelShine.xls"

DoCmd.TransferSpreadsheet acImport, acSpreadsheetTypeExcel97, strMichaelImport, strMichaelLocation, False

strSQL = "DELETE * FROM tblImportMichaelShine WHERE (((F1) Is Null) AND ((F2) Is Null) AND ((F3) Is Null) AND ((F4) Is Null) AND ((F5) Is Null) AND ((F6) Is Null) AND ((F7) Is Null) AND ((F8) Is Null) AND ((F9) Is Null));"
DoCmd.RunSQL strSQL

strSQL = "DELETE F1, F2 FROM tblImportMichaelShine WHERE (((F1) Is Null) AND ((F2) Is Not Null));"
DoCmd.RunSQL strSQL

strSQL = "SELECT * FROM " & strMichaelImport & " ORDER BY F2"
Set rs = CurrentDB.OpenRecordset(strSQL, dbOpenDynaset)

With rs
.MoveFirst
Do Until .EOF
If Not IsNull(.Fields(0)) Then
strAccount = .Fields(1)
.MoveNext
Do While IsNull(.Fields(0))
.Edit
.Fields(1) = strAccount
.update
.MoveNext
Loop
Else
Do While IsNull(.Fields(0))
.MoveNext
Loop
End If
Loop
End With

Set rs = Nothing

MeExit:
Exit Function

MeError:
Call ErrorHandler(Err.Number)
Resume MeExit
============================================ Jim Lunde
compugeeks@hotmail.com
We all agree your theory is crazy, but is it crazy enough?