|
| 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
November 15th, 2008, 06:42 PM
posted to microsoft.public.access.tablesdbdesign
|
|||
|
|||
Creating a Lookup Table Using a Do...Until Loop
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 
|
|
#2
November 15th, 2008, 10:50 PM
posted to microsoft.public.access.tablesdbdesign
|
|||
|
|||
|
Creating a Lookup Table Using a Do...Until Loop
you've posted this same question three times in the space of 30 minutes,
twice to this ng, and once to microsoft.public.access.modulesdaovba. please don't multi-post. for more information, see http://home.att.net/~california.db/tips.html#aTip10. wrote in message ... 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
|
|
#3
November 15th, 2008, 11:14 PM
posted to microsoft.public.access.tablesdbdesign
|
|||
|
|||
|
Creating a Lookup Table Using a Do...Until Loop
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. wrote in message ... 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
|
|
#4
November 16th, 2008, 07:41 AM
posted to microsoft.public.access.tablesdbdesign
|
|||
|
|||
|
Creating a Lookup Table Using a Do...Until Loop
On Nov 15, 6:14*pm, "Pete D." wrote:
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. wrote in message ... 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- Hide quoted text - - Show quoted text - 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?
|
|
#5
November 16th, 2008, 08:19 PM
posted to microsoft.public.access.tablesdbdesign
|
|||
|
|||
|
Creating a Lookup Table Using a Do...Until Loop
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? wrote in message ... On Nov 15, 6:14 pm, "Pete D." wrote: 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. wrote in message ... 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- Hide quoted text - - Show quoted text - 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?
|
|
#6
November 19th, 2008, 03:51 PM
posted to microsoft.public.access.tablesdbdesign
|
|||
|
|||
|
Creating a Lookup Table Using a Do...Until Loop
On Nov 16, 3:19*pm, "Pete D." wrote:
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? wrote in message ... On Nov 15, 6:14 pm, "Pete D." wrote: 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. wrote in message .... 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- Hide quoted text - - Show quoted text - 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 - 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.
|
|
#7
November 19th, 2008, 10:56 PM
posted to microsoft.public.access.tablesdbdesign
|
|||
|
|||
|
Creating a Lookup Table Using a Do...Until Loop
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. wrote in message ... On Nov 16, 3:19 pm, "Pete D." wrote: 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? wrote in message ... On Nov 15, 6:14 pm, "Pete D." wrote: 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. wrote in message ... 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- Hide quoted text - - Show quoted text - 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 - 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.
|
| Thread Tools | |
| Display Modes | |
|
|
Linear Mode
