- Microsoft Access
Update a table field with value above it
Hello,
I need to update a table: tblImportToAccess.[TradeNo
with the value that is in the row above it.
![Pic]()
In the pic above, I need the nulls on [TradeNo] and [CoName] to populate with 123 and XYZ respectively for the first null but the second null I would need it to populate with 333 and ABC respectively (much like dragging down the cell above if it were in excel).
My dataset is much larger and there are different TradeNo and CoName.
I am not sure if this would best be done by way of an update query but if so then I have a part of it below and looking for the remainder.
I am a novice user of Access. I can easily do this in excel with a macro but I want to to perform this in access after I import the excel data.
UPDATE tblImportToAccess SET
WHERE (((tblImportToAccess.[Trad
Thank you.
I need to update a table: tblImportToAccess.[TradeNo
with the value that is in the row above it.
In the pic above, I need the nulls on [TradeNo] and [CoName] to populate with 123 and XYZ respectively for the first null but the second null I would need it to populate with 333 and ABC respectively (much like dragging down the cell above if it were in excel).
My dataset is much larger and there are different TradeNo and CoName.
I am not sure if this would best be done by way of an update query but if so then I have a part of it below and looking for the remainder.
I am a novice user of Access. I can easily do this in excel with a macro but I want to to perform this in access after I import the excel data.
UPDATE tblImportToAccess SET
WHERE (((tblImportToAccess.[Trad
Thank you.
It is important to understand that a database has no concept of row order or row number, unless defined by a query, that tells the database how rows are ordered, e.g. by stating "ORder by OrderDate"
Just use this code
Function UpdatePrevious()
Dim rst As DAO.Recordset
Dim tmpTradeNo As Long
Dim tmpCoName As String
Set rst = CurrentDb.OpenRecordset("tblImport")
With rst
tmpTradeNo = .Fields("TradeNo")
tmpCoName = .Fields("CoName")
.MoveNext
While Not .EOF
If IsNull(.Fields("TradeNo")) Then
.Edit
.Fields("TradeNo") = tmpTradeNo
.Update
End If
If Len(Nz(.Fields("CoName"), "")) = 0 Then
.Edit
.Fields("CoName") = tmpCoName
.Update
End If
If Not IsNull(.Fields("TradeNo")) Then tmpTradeNo = .Fields("TradeNo")
If Len(Nz(.Fields("CoName"), "")) > 0 Then tmpCoName = Nz(.Fields("CoName"), 0)
.MoveNext
Wend
End With
rst.Close
End Function
As long your very first record has values this code should the job
Hi John and thanks for the response.
I do have an error though.
I had a update code to the "real' field names. I posted the question and changed the names for better reading.
The field names I changed were as follows:
[TradeNo] to the real field name of [Trade #] and [CoName] to [Buy CP].
I am not sure if that is why when I run I get a type mismatch error on tmpTradeNo = .Fields("Trade #") (7th line)
Not sure if I can have any spaces or if the "#" is what is causing the error.
here is the code as I have it now after updating the field names to the "real" field names
Function UpdatePrevious()
Dim rst As DAO.Recordset
Dim tmpTradeNo As Long
Dim tmpCoName As String
Set rst = CurrentDb.OpenRecordset("tblImportToAccess")
With rst
tmpTradeNo = .Fields("Trade #")
tmpCoName = .Fields("Buy CP")
.MoveNext
While Not .EOF
If IsNull(.Fields("Trade #")) Then
.Edit
.Fields("Trade #") = tmpTradeNo
.Update
End If
If Len(Nz(.Fields("Buy CP"), "")) = 0 Then
.Edit
.Fields("Buy CP") = tmpCoName
.Update
End If
If Not IsNull(.Fields("Trade #")) Then tmpTradeNo = .Fields("Trade #")
If Len(Nz(.Fields("Buy CP"), "")) > 0 Then tmpCoName = Nz(.Fields("Buy CP"), 0)
.MoveNext
Wend
End With
rst.Close
End Function
Several years ago I had to do the same thing and built a training demo to show how to do it in VBA code.
The attached database may seem a little more involved for your needs, but it shows you how to accomplish this "fill-down" with a demo containing all the code to do it. Just use the fill-in procedure and provide the input parameters for any table/field where you need to "fill-down", and it will take care of it. The procedure works for any table/fields - no hard coding.
The table to pick in the demo is the "Imported Data" table. The row-ordering field is "roworder", and the field to fill in is "Company". Repeat for the "Department" column fill-in. The "tmpFillInTable" is used by the code to manage the fill-in.
Hope this helps.
FillDownDemo.accdb
Create-an-Access-Auto-Fill-Process-.docx
The attached database may seem a little more involved for your needs, but it shows you how to accomplish this "fill-down" with a demo containing all the code to do it. Just use the fill-in procedure and provide the input parameters for any table/field where you need to "fill-down", and it will take care of it. The procedure works for any table/fields - no hard coding.
The table to pick in the demo is the "Imported Data" table. The row-ordering field is "roworder", and the field to fill in is "Company". Repeat for the "Department" column fill-in. The "tmpFillInTable" is used by the code to manage the fill-in.
Hope this helps.
FillDownDemo.accdb
Create-an-Access-Auto-Fill-Process-.docx
Best practice would be to fix the offending column names to remove the embedded spaces and special characters. Otherwise, you always have to enclose these bad names in square brackets.
Hello,
Mark, thank you. It is a little bit above me though.
John, please see pic of the error I get. Maybe it is simple.
[Trade #] is short text in the table. Just to recap what I said up above, I changed your code from [TradeNo] to what it really is in the db which is [Trade #]. I know the name is not appropriate but I am importing from Excel and need to stick to the field names. Thank you.
Please refer to my earlier comment.
PS - you don't have to use the Excel column names as your table's column names.
Hi Pat, ok i understand however I need to keep the names as the ones found in excel. I do not have control over the excel file names.
That's not what I said. You don't need to change the names in the excel file. Link to the spreadsheet of importing. Then you can use a query to append the data from the spreadsheet into properly named columns for you to use for the rest of the app.
Ahh I see what you are saying. Sorry for my misunderstanding. I have now updated my append query to change the bad names into good names. I didnt think that I could change the names of the fields to whatever I wanted within the append. I feel much better now that those bad names are gone. Thank you so much....
-
![]()
received a Solution
In Access using a Data Macro to update a second table when value is entered -
![]()
wrote an Article
![]()
Deploy and update a Microsoft Access application with one click
-
![]()
created a Video
![]()
Highlight the Current Record on an Access form with a Yellow background color
-
Explore Cloud Class® ![]()
Certification: MOS Microsoft Office Specialist - Access 2016 Part 1
Premium Content
You need an Expert Office subscription to comment.Start Free Trial