[Last Call] Learn about multicloud storage options and how to improve your company's cloud strategy. Register Now

x
?
Solved

Auto fill blank fields in tables

Posted on 2008-06-26
5
Medium Priority
?
297 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 1000 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

Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

Question has a verified solution.

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

This article describes a method of delivering Word templates for use in merging Access data to Word documents, that requires no computer knowledge on the part of the recipient -- the templates are saved in table fields, and are extracted and install…
One of the most important things in an application is the query performance. This article intends to give you good tips to improve the performance of your queries.
Basics of query design. Shows you how to construct a simple query by adding tables, perform joins, defining output columns, perform sorting, and apply criteria.
In Microsoft Access, learn how to “cascade” or have the displayed data of one combo control depend upon what’s entered in another. Base the dependent combo on a query for its row source: Add a reference to the first combo on the form as criteria i…
Suggested Courses

650 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