Creating a Lookup Table Using a Do...Until Loop in VBA

[tpolney]

Hi Experts: I'm trying to create a lookup table from the below text
file, here is some data:

ID Field2 Field3 Field4
2122 TOTAL SCH RC-26.B.
2193 B65110000 TAXES MTM G/
2192 B64610000 TX MTM G/L D
2191 B64600000 DEF G/L TERM
2171 TOTAL SCH RC-26.A.
2158 B60000001 RETAINED EAR
2157 B60000002 RETAINED EAR
2156 B60000003 RETAINED EAR
2155 B60000004 RETAINED EAR
2148 TOTAL SCH RC-25
2147 B54080000 APIC STOCK O
2146 B54000000 PAID IN SURP
2142 TOTAL SCH RC-24
2140 B52000000 CAP STK/COMM
2136 TOTAL SCH RC-G-4
2134 B47520000 DEF.GAIN/LOS
2115 B46462000 A/P DUE TO C
2114 B46460099 A/P FDR SETT


I need some code that will copy and paste Field4 down to the blank
cells below it, until it comes to a non-blank cell, then it will skip
the non-blank cell and copy that one down until it reaches the next
non-blank, etc. Here is the code I am using currently but am finding
2 major problems. First, my variable SQLstmt does not seem to be
declared properly and second, I'm getting an infinite loop. Any help
would be much appreciated. Please excuse my amateurish code. Thank
you.

Public Sub get_M27()

Dim db As DAO.Database
Set db = CurrentDb
Dim SQLstmt As String

' Sort Column ID in descending order
DoCmd.OpenTable "M27_c"
DoCmd.GoToControl "ID"
DoCmd.RunCommand acCmdSortDescending

DoCmd.GoToControl "Field4"
DoCmd.GoToRecord acTable, "M27_c", acGoTo, 2

SQLstmt = "SELECT Field4 FROM M27_c"

Dim rst1 As DAO.Recordset
Set rst1 = db.OpenRecordset(SQLstmt, dbOpenDynaset)

Do Until rst1.EOF

If (SQLstmt) <> "" Then

DoCmd.GoToRecord acTable, "M27_c", acPrevious, 1
DoCmd.RunCommand acCmdCopy
DoCmd.GoToRecord acTable, "M27_c", acNext
DoCmd.RunCommand acCmdPaste

Else

End If

DoCmd.GoToRecord acTable, "M27_c", acNext

Loop

DoCmd.Close acTable, "M27_c"

End Sub