Solved

Auto fill blank fields in tables

Posted on 2008-06-26
5
283 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

Free Tool: Path Explorer

An intuitive utility to help find the CSS path to UI elements on a webpage. These paths are used frequently in a variety of front-end development and QA automation tasks.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
T-SQL: Stored Procedure Syntax 3 34
StoredProcedure to JSON query faulty syntax 2 29
Hide shared folder for some users 2 30
run FileCopy code from a string 6 20
Describes a method of obtaining an object variable to an already running instance of Microsoft Access so that it can be controlled via automation.
AutoNumbers should increment automatically, without duplicates.  But sometimes something goes wrong, and the next AutoNumber value is a duplicate.  This article shows how to recover from this problem.
Familiarize people with the process of utilizing SQL Server views from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Microsoft Access…
Using Microsoft Access, learn some simple rules for how to construct tables in a relational database. Split up all multi-value fields into single values: Split up fields that belong to other things into separate tables: Make sure that all record…

726 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