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

  • Thread starter Thread starter tpolney
  • Start date Start date
T

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 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
 
You not getting much feed back because...what are you trying to do? If your
copying and pasting things in a record and it isn't for something like
dispatching 4 different vehicles for the same event with different
requirements then this screams the tables may not be normalized. Then you
talk about cells which is an excel item. Also what happens when you get to
the end of your records, you'll end up with a continuous loop as it will not
find a non-blank cell so you'll create records until the disk drive is full.
Maybe you could post more info to this question instead of posting the same
question over and over.
 
You not getting much feed back because...what are you trying to do?  Ifyour
copying and pasting things in a record and it isn't for something like
dispatching 4 different vehicles for the same event with different
requirements then this screams the tables may not be normalized.  Then you
talk about cells which is an excel item.  Also what happens when you get to
the end of your records, you'll end up with a continuous loop as it will not
find a non-blank cell so you'll create records until the disk drive is full.
Maybe you could post more info to this question instead of posting the same
question over and over.





















- Show quoted text -
Click to expand...

Sorry, I thought I deleted the other posts. This is my first post to
a news group. Please forgive me. All I'm really looking to do is
fill in my lookup table so that each row is unique. Right now I'm
importing a text file in but its an incomplete table. My table should
look like this:

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

I'm not sure on the best way to accomplish this. I'm new to VBA. Any
suggestions?
 
So if I understand you have an ID that is unique for every record. You also
have fields 2, 3, 4. for most records but every few records you have one
record missing field 2 and 3. You are looking to pad the missing fields 2
and 3 with informatation from ones above or do you even need those fields
where missing?

You not getting much feed back because...what are you trying to do? If
your
copying and pasting things in a record and it isn't for something like
dispatching 4 different vehicles for the same event with different
requirements then this screams the tables may not be normalized. Then you
talk about cells which is an excel item. Also what happens when you get to
the end of your records, you'll end up with a continuous loop as it will
not
find a non-blank cell so you'll create records until the disk drive is
full.
Maybe you could post more info to this question instead of posting the
same
question over and over.



















- Show quoted text -
Click to expand...

Sorry, I thought I deleted the other posts. This is my first post to
a news group. Please forgive me. All I'm really looking to do is
fill in my lookup table so that each row is unique. Right now I'm
importing a text file in but its an incomplete table. My table should
look like this:

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

I'm not sure on the best way to accomplish this. I'm new to VBA. Any
suggestions?
 
So if I understand you have an ID that is unique for every record.  Youalso
have fields 2, 3, 4. for most records but every few records you have one
record missing field 2 and 3.  You are looking to pad the missing fields 2
and 3 with informatation from ones above or do you even need those fields
where missing?






Sorry, I thought I deleted the other posts.  This is my first post to
a news group.  Please forgive me.  All I'm really looking to do is
fill in my lookup table so that each row is unique.  Right now I'm
importing a text file in but its an incomplete table.  My table should
look like this:

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

I'm not sure on the best way to accomplish this.  I'm new to VBA.  Any
suggestions?- Hide quoted text -

- Show quoted text -
Click to expand...

I do not need to pad Fields 2 or 3, just Field 4. The other fields
are pretty much filled in, but Field 4 is blank. I'm not sure if I on
the right track using a Do...Until loop to filter through each record,
or even if my other code is close. Thanks for your reply, I
appreicate the help.
 
Sorry to be so thick, but I still don't understand what your trying to do.
Do this, in your sample data put [] around each fields data so I can see it.
So if I understand you have an ID that is unique for every record. You
also
have fields 2, 3, 4. for most records but every few records you have one
record missing field 2 and 3. You are looking to pad the missing fields 2
and 3 with informatation from ones above or do you even need those fields
where missing?






Sorry, I thought I deleted the other posts. This is my first post to
a news group. Please forgive me. All I'm really looking to do is
fill in my lookup table so that each row is unique. Right now I'm
importing a text file in but its an incomplete table. My table should
look like this:

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

I'm not sure on the best way to accomplish this. I'm new to VBA. Any
suggestions?- Hide quoted text -

- Show quoted text -
Click to expand...

I do not need to pad Fields 2 or 3, just Field 4. The other fields
are pretty much filled in, but Field 4 is blank. I'm not sure if I on
the right track using a Do...Until loop to filter through each record,
or even if my other code is close. Thanks for your reply, I
appreicate the help.
 
Back
Top