Creating a Lookup Table Using Do...Until Loop

[tpolney]

Hi Group: I'm trying to create a lookup table from the following
information, this is sample data only:

ID Field1 Field2 Field3
Field4
2122
* TOTAL SCH RC-26.B.
2193 107 B6511000010200 TAXES MTM G/
2192 107 B6461000020200 TX MTM G/L D
2191 108 B6460000020205 DEF G/L TERM
2171
* TOTAL SCH RC-26.A.
2158 986 B6000000010375 RETAINED EAR
2157 107 B6000000010235 RETAINED EAR
2156 108 B6000000010235 RETAINED EAR
2155 108 B6000000010235 RETAINED EAR
2148
* TOTAL SCH RC-25
2147 981 B5408000020000 APIC STOCK O
2146 986 B5400000020200 PAID IN SURP
2142
* TOTAL SCH RC-24
2140 975 B5200000010200 CAP STK/COMM
2136
* TOTAL SCH RC-G-4
2134 703 B4752000020000 DEF.GAIN/LOS
2115 420 B4646200010255 A/P DUE TO C
2114 977 B4646009980400 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 the next non-blank cell, then it
will 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 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

 

[tina]

it's hard to to tell from the way the post comes up on my reader, but it
looks like you're trying to set up the data to display the way you might
want to display it in a report. can you explain what your goal is, in
populating this table, and in populating it by the method you posted?

hth

 

[tpolney]

it's hard to to tell from the way the post comes up on my reader, but it
looks like you're trying to set up the data to display the way you might
want to display it in a report. can you explain what your goal is, in
populating this table, and in populating it by the method you posted?

hth





















- Show quoted text -

I accidently posted this question on more than 1 message board. The
other posting is in Table Design. I thought I deleted this one but
apparently not. Please forgive my oversight. I very much appreciate
the response, and still need to find a solution, but please see the
below posting. I have included some additional information. Thank
you.

http://groups.google.com/group/micr.../browse_thread/thread/bf12e5cab00f9d01?hl=en#