Solved

Auto fill blank fields in tables

Posted on 2008-06-26
5
288 Views
Last Modified: 2013-11-27
I have a database that was derived from a report.  The data looks something like this:

Obj              ObjSource               Date                   Ref#                Amt
22122          SOURCE1             9/24/07             DR3245              100.00
                                                10/15/07            CT3199                88.00
                                                 12/3/07             DR3210              100.00
22123          SOURCE2            8/15/07              CT3455                50.00
                                                9/15/07              GT3011              200.00

The blank spaces in the OBJ and OBJSOURCE fields should be filled with the OBJ and OBJSOURCE above it, but I can't figure a way to get those fields to auto fill with the information and change appropriately.  In Excel, I would copy and paste or autofill - is there a way to do it in Access?  If there was a field I could use as a reference I could do it, but there is no correlation between any of the other fields in the table and what has to appear in OBJ and OBJ source.  Any suggestions?  I am not a Visual Basic person,  so it would have to be an update query of some sort or code I can copy and paste after changing the field names.

0
Comment
Question by:TheSeeker1974
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 2
  • 2
5 Comments
 
LVL 23

Expert Comment

by:irudyk
ID: 21874357
Try the following:
Go into this table, select and copy all records
Paste the records into Excel
Use Excel to do your autofills
Copy the applicable cells from Excel
Go back to your Access table
Select all records
Paste the changes

If you only need to do this the one time (or infrequently) then the above is probably the easiest way to go
0
 

Author Comment

by:TheSeeker1974
ID: 21874401
Thanks for the suggestion, but first of all the report is about 50,000 rows and secondly until I find a better way to get the data it will continue to come in like this for the forseeable future, so I'd really like to find a way to automate the process as much as possible.
0
 
LVL 23

Accepted Solution

by:
irudyk earned 250 total points
ID: 21874713
Try the following code:
Function UpdateRecords()
 
Dim rst As DAO.Recordset
Dim Obj As Long
Dim ObjSource As String
 
Set rst = CurrentDb.OpenRecordset("SELECT * FROM Table1")
 
rst.MoveFirst
 
While Not rst.EOF
    If rst.Fields("Obj") <> Obj Then
        Obj = rst.Fields("Obj")
        ObjSource = rst.Fields("ObjSource")
    Else
        rst.Edit
        rst.Fields("Obj") = Obj
        rst.Fields("ObjSource") = ObjSource
        rst.Update
    End If
    rst.MoveNext
Wend
 
rst.Close
Set rst = Nothing
 
End Function

Open in new window

0
 
LVL 143

Expert Comment

by:Guy Hengel [angelIII / a3]
ID: 21874743
"above" or "below" does not exist in sql language, unless you have a clear ORDER BY.
so, unless there is a field that defines the sequence of the rows, this will be difficult (read impossible) inside a query.
0
 

Author Closing Comment

by:TheSeeker1974
ID: 31470941
Worked like a charm.  Thank you!
0

Featured Post

Announcing the Most Valuable Experts of 2016

MVEs are more concerned with the satisfaction of those they help than with the considerable points they can earn. They are the types of people you feel privileged to call colleagues. Join us in honoring this amazing group of Experts.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

The Windows Phone Theme Colours is a tight, powerful, and well balanced palette. This tiny Access application makes it a snap to select and pick a value. And it doubles as an intro to implementing WithEvents, one of Access' hidden gems.
In part one, we reviewed the prerequisites required for installing SQL Server vNext. In this part we will explore how to install Microsoft's SQL Server on Ubuntu 16.04.
In Microsoft Access, learn the trick to repeating sub-report headings at the top of each page. The problem with sub-reports and headings: Add a dummy group to the sub report using the expression =1: Set the “Repeat Section” property of the dummy…
With Secure Portal Encryption, the recipient is sent a link to their email address directing them to the email laundry delivery page. From there, the recipient will be required to enter a user name and password to enter the page. Once the recipient …

690 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question