?
Solved

Auto fill blank fields in tables

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

Hire Technology Freelancers with Gigs

Work with freelancers specializing in everything from database administration to programming, who have proven themselves as experts in their field. Hire the best, collaborate easily, pay securely, and get projects done right.

Question has a verified solution.

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

Sometimes MS breaks things just for fun... In Access 2003, only the maximum allowable SQL string length could cause problems as you built a recordset. Now, when using string data in a WHERE clause, the 'identifier' maximum is 128 characters. So, …
MSSQL DB-maintenance also needs implementation of multiple activities. However, unprecedented errors can hamper the database management. In that case, deploying Stellar SQL Database Toolkit ensures fast and accurate database and backup repair as wel…
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 …
Enter Foreign and Special Characters Enter characters you can't find on a keyboard using its ASCII code ... and learn how to make a handy reference for yourself using Excel ~ Use these codes in any Windows application! ... whether it is a Micr…
Suggested Courses

569 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