I am working on a project for a Food Pantry and have imported data that needs some attention. The export routine
would not export reoccurring data, therefore, I have fields without a family code number
an example:
Family_Name Family_City ID c_Num_Total Family_ aa_FMAS_ID
1
2
Amxxxxxxx, Joe Deterboro 3 1.00 FM00929-2017
Anzzzzzz, Jim Hellenborough 4 1.00 FM00698-2017
Andqqqqq, Joan Milford 5 3.00 FM00430-2017
6
7
Arrrrr, Ellen Folenburg 8 2.00 FM01170-2017
Explanation of imported data
field 1 = Family Name
field 2 = Family City
field 3 = autonumber to insure that I keep the correct order
field 4 = a number that has determined how many people in the family unit (includes the name in Family Name) so a 3 means 2 others in family unit
field 5 = a family id
I need to start with first record
If it is empty then skip to next record
test to see if that one is empty - is so, skip
if it has data, I need to copy the contents of field aa_FMAS_id to the
following blank aa_FMAS_id field until I hit another aa_FMAS_id with data
If that field has data then I need to capture that aa_Fmas_id and copy it to the following blank records.
etc. etc
If I have holes in my theory, please help set me on a better path. Actually, I was thinking of copy to an added field, so I did not overwrite my data.
I am very new to VBA and struggles to put a little loop together. It does not work properly, but here it is.
Private Sub btnVBACodeUpdate_Click()
Dim dbs As DAO.Database
Dim rst As DAO.Recordset
Dim VFN As String 'VFN is Family_Name
Dim VFamilyID As String
Set dbs = CurrentDb
Set rst = dbs.OpenRecordset("Sheet1A")
rst.MoveFirst
Do Until rst.EOF
Debug.Print rst![ID]
Debug.Print rst![aa_FMAS_ID]
Debug.Print rst![Family_Name]
rst.MoveNext
Loop
End Sub
Any help will be appreciated
Thanks
Yianni