If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
Creating a Lookup Table Using a Do...Until Loop in VBA
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 |
Thread Tools | |
Display Modes | |
|
|