Record Data Manipulation

Heres a bit of a stickler that I have come across. I am exporting data from and antiquated database into a Tab Delimited file. From the file I import the data to Access, no problem so far...
Now within the file there are 3 fileds (Field1, Field2, Field3 for discussion). Field1 contains a type of unique record number, the other two fields contain text related to the Field1 value. The problem is that the additional records related to first Field1 value have no entry in Field1. This is from a flat file type database, so I can identify the the records only by order they are brought in. I need to add the Field1 value to all of the following records containing a blank Field1. When it comes across the next record containing a Field1 that is not Null, then it will not make any changes, and add that value to the following records that have a blank Field1
In addition, I need to have this action carried out in a manner that can be processed automatically by the database (module or query).
In my quest to get figured out, I had attempted creating modules that run a loop to look at and modify the records, but none have worked (VBA is not my strongest point yet). To make these attempts easier, I have added 2 additional fields - Field0 (autonumber) and Field4 (yes/no). I have ran an append query so that all records with a Field1 = Null are also showing True in the yes no field. I have added this for later use to easily identify the related records on sight.
One item that I have found usefull, THERE WILL ALWAYS BE A VALUE IN THE FIRST RECORDS FIELD1.
An example of what this would look like as table data
Field1*****Field2*****Field3*****Field4
00001*****33333****Jack******Smith
00002**************Barb******Smith
00003**************Greg******Smith
00004*****44444****Jhon*******Doe
00005*****55555****Jane******Thomas
00006*****66666****William****Jones
00007**************Janet******Jones
Any suggestions?

This code will update the field2 value to last one that was not blank

I seem to be encountering and outside procedure error. Would this be best servered as a module or code for an on_click command?

jeremie,

You must fix the data before it enters the table. Unless the
sequence numbers in Field1 are legit and not compromised by
future import operations.

The following code should fix the data prior to putting it
into your table.

Put this procedure on the OnClick event of a
command button:

' **********************************************
Dim dbs As DataBase
Dim rst As RecordSet
Dim sql As String
Dim buf As String

Dim Field1 As String
Dim Field2 AS String
Dim Field3 AS String
Dim Field4 AS String

Dim LastField2 As String

Set dbs = CurrentDb
sql = "Select * from MyTable"
Set rst = dbs.OpenRecordset(sql)

Open "C:\YourFile.txt" For Input As #1

Line Input #1, buf
LastField2 = ""

While Not EOF(1)
Field1 = Trim(Mid(buf, 1, 10))
Field2 = Trim(Mid(buf, 11, 10))
Field3 = Trim(Mid(buf, 21, 10))
Field4 = Trim(Mid(buf, 31, 10))
If Field2 = "" Then
Field2 = LastField2
Else
LastField2 = Field2
End If
'
' Insert record
'
rst.AddNew
rst!Field1 = Field1
rst!Field2 = Field2
rst!Field3 = Field3
rst!Field4 = Field4
rst.Update
Line Input #1, buf
Wend

Set rst = Nothing
Set dbs = Nothing
' ************************************

Didn't test it, but that's the general
idea.

hth,
Wayne

Maybe its my version, but the Dim dbs As DataBase line of code isnt recognized.

I put it on inside a button_click event.
As for your problems with Dim dbs as Database try Dim dbs as DAO.Database and see if that solves it, you'll also need to Dim rst as DAO.Recordset.

I believe in A2K, which I don't have installed that this occurs because ADO is higher up the library hierarchy so you must explicitly reference that you require a DAO object.

jeremie,

It is probably a reference problem.

Get the code in design view and promote the
DAO reference above ADO.

Then just for fun, put the cursor at the
left margin and left-click. This will set
a breakpoint. F8 will single-step, F5 will
run to completion. If you hover over a
variable, the debugger will display its
value.

hth,
Wayne