Go Back   Access World Forums > Microsoft Access Discussion > Modules & VBA

 
Reply
 
Thread Tools Rate Thread Display Modes
Old 11-24-2005, 08:47 AM   #1
Ciaran_Enright
Registered User
 
Join Date: Nov 2005
Posts: 8
Thanks: 0
Thanked 0 Times in 0 Posts
Ciaran_Enright is on a distinguished road
Populate duplicate blank fields

I hope you can point me in the right direction,
I need code to populate the blank values in field1 with the value above.
i.e. 008HN is populated for each value in field 4 until 0121P
See attached
I've got code to do it in excel but not access.
Thanks Ciaran.
Attached Files
File Type: txt dup.txt (642 Bytes, 75 views)


Last edited by Ciaran_Enright; 11-24-2005 at 08:49 AM.
Ciaran_Enright is offline   Reply With Quote
Old 11-24-2005, 10:36 AM   #2
gromit
Registered User
 
Join Date: Nov 2005
Posts: 260
Thanks: 0
Thanked 11 Times in 4 Posts
gromit is on a distinguished road
Hi -

Looks like this is connected to importing data? If so, you might want to try to catch it at the import stage, rather than deal with it internally to Access.

The one potential issue that lurks here is how to determine which records lie "above" other records. At the time of import, this is pretty well defined. However, my understanding is that you can not necessarily rely on the autonumber fields to preserve order as time goes on.

okay, enough of the cautions

1. You can declare a recordset based on the table and step through each record. Set a variable equal to "last value". If the current value is not blank, then update "last value". Otherwise, write last value into the field.

The code looks something like this:

Dim rs as DAO.Recordset
Dim strLastValue as String
Set rs = dbs.OpenRecordset("tblData", dbOpendynaset)

strLastValue = ""
While not rs.EOF
if rs!field1 <> "" then
strLastValue = rs!Field1
Else
rs.Edit
rs!Field1 = strLastValue
rs.Update
Endif
rs.MoveNext
Wend

rs.Close
Set rs = Nothing

2. Export the table to Excel, work your magic there and then re-import into Access.

hth,

- g
gromit is offline   Reply With Quote
Old 11-25-2005, 07:33 AM   #3
Ciaran_Enright
Registered User
 
Join Date: Nov 2005
Posts: 8
Thanks: 0
Thanked 0 Times in 0 Posts
Ciaran_Enright is on a distinguished road
Thanks G... too busy at the mo ...later

Ciaran_Enright is offline   Reply With Quote
Reply

Thread Tools
Display Modes Rate This Thread
Rate This Thread:

Forum Jump

Similar Threads
Thread Thread Starter Forum Replies Last Post
Selecting duplicate fields from unique records in a single table dougmcc1 Queries 5 10-11-2004 08:16 AM
Find Record and Populate rest of fields. Please tell me how? MsLady Modules & VBA 9 07-07-2004 07:32 PM
Blank Fields in Reports? bissonette Reports 7 06-30-2004 07:20 AM
Checking for blank fields Kate Forms 3 12-04-2001 07:40 PM
[SOLVED] Populate fields of form after entering data in one field Wally Buckner Forms 2 04-24-2000 05:41 PM




All times are GMT -8. The time now is 10:07 AM.


Microsoft Access Help
General
Tables
Queries
Forms
Reports
Macros
Modules & VBA
Theory & Practice
Access FAQs
Code Repository
Sample Databases
Video Tutorials

Featured Forum post


Sponsored Links


Powered by vBulletin®
Copyright ©2000 - 2019, Jelsoft Enterprises Ltd.
(c) copyright 2017 Access World