Solved

Auto fill blank fields in tables

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

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

Master Your Team's Linux and Cloud Stack

Come see why top tech companies like Mailchimp and Media Temple use Linux Academy to build their employee training programs.

Question has a verified solution.

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

Since upgrading to Office 2013 or higher installing the Smart Indenter addin will fail. This article will explain how to install it so it will work regardless of the Office version installed.
It’s been over a month into 2017, and there is already a sophisticated Gmail phishing email making it rounds. New techniques and tactics, have given hackers a way to authentically impersonate your contacts.How it Works The attack works by targeti…
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…
With Microsoft Access, learn how to specify relationships between tables and set various options on the relationship. Add the tables: Create the relationship: Decide if you’re going to set referential integrity: Decide if you want cascade upda…

825 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