Update record with previous record

[SchoonDog]

hey everyone,

I thought this would be pretty simple but I am running into some troubles.

I want to replace a null ID in a table with the previous ID in the same table if ID is null.

I have been trying VBA but I can't seem to do it. I can do it fine in Excel but that is way to manual.


Any thoughts to get me going in VBA?

Thanks,
SD

 

[zevw]

How are the ID setup is it incremental and each new number get the next highest number if so run a DMAX on the table and with update query place that value in the new record. if not is there something unique in the previous record that can be a criteria for your update query.

The way to go is with an update qwuery.

 

[PaulBricker]

If you want the value from the previous record to be the default for the current new record you can put this in the On Got Focus event for the textbox

If Me.NewRecord Then
SendKeys "^'"
Else
End If

Then when someone enters that field it will insert the value from the previous record automatically. If you want to change the value you can, if not, just tab on thru.

Paul

 

[SchoonDog]

I am using a imported text file as my table so I am not manually entering all of the data to begin with. This is begining to give me a headache

SD

 

[jjlogan]

Text files: My limited experience - I read file into an array variable or User Defined Variable. Then made changes to the variable values. Then output to data back to a text file. In essence, you are inputting and then writing back over "old" data.
Jeff

 

[zevw]

If this is a text file then import this file into a table

after you have all data

create a recordset on this NEW table

Dim rst As recordset
Dim IdFld

Set rst = currentdb.openrecodset("New Table Name&quot
Do While rst.eof = False
rst.movefirst
rst.edit
If Not IsNull(rst!<id field name>) Then
IdFld = rst!<id field name>
Else
rst!<id field name> = IdFld
End If
rst.update
rst.movenext
Loop

This will go through the Id field and place the previous value if the id field is empty.