Need Hlp VBA coding

[yianni]

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

[ssanfu]

I need to start with first record
If it is empty then skip to next record

If what is empty? The first field or the whole record???


You show what the data looks like. Show what you want the end result to be..

You have

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

And you want???

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				FM00430-2017
7				FM00430-2017
Arrrrr	Ellen Folenburg	8	2.00	FM01170-2017
9				FM01170-2017

Why do you have family names that are numbers????
Why do the city names look like people names??

[CJ_London]

just as an aside

autonumber to insure that I keep the correct order

an autonumber's only purpose is to ensure uniqueness, it does not ensure an order or anything else. It may appear to have an order, but it is not guaranteed. Plus it always has to be populated, and in your example, it isn't.

[yianni]

I need to clarify - need to add a field (first field) for autonumber

[yianni]

Data from my post got scambled, probably due to the way in which I posted it. I am new at this.
The first field was an autonumber. 1 though 9 (3,4,5 8 ended up in the ID column)
The second field is Family_Name

(blank field)
(blank field)
Amxxxxxxx, Joe
Anzzzzzz, Jim
Andqqqqq, Joan
(blank field)
(blank field)
Arrrrr, Ellen
(blank field)

The next field is Family_City
(blank field)
(blank field)
Deterboro
Hellenborough
Milford
(blank field)
(blank field)
Folenburg
(blank field)


The next field has ID was the first field which should have 1 through 9

The next field c_Num_Total has correct data

and the last field with the FM numbers is correct

I am sorry about the data scramble.

The files that I inherited will not be what I end up with. I need to make relational tables; therefore
my need to populate the field Family_as_FMAS_ID

Thanks, I hope you can get me started with vba code to do this.

Thanks

John (Yianni)

[ssanfu]

This is what you have???

ID	Family_Name	Family_City	c_Num_Total	Family_ aa_FMAS_ID
1
2
3	Amxxxxxxx, Joe	Deterboro	1.00	FM00929-2017
4	Anzzzzzz, Jim	Hellenborough	1.00	FM00698-2017
5	Andqqqqq, Joan	Milford	3.00	FM00430-2017
6
7
8	Arrrrr, Ellen	Folenburg	2.00	FM01170-2017

If Yes, explain what you want to happen or show (a picture?) what the results should be.....


You do know that a table has no inherent order, unlike an Excel spreadsheet.
If the above was ordered by family name, the blank rows would be at the top.

[Micron]

yianni; IMHO, the best way to chart data here is to create the data & layout in Excel as though it were a table. Centre everything, select, copy to clipboard and paste in your post. Usually, you'll get a nice table. Sometimes you have to tweak the resulting table width as the default is sometimes not wide enough. Easily done if you "Go Advanced".
Just an FYI.

[yianni]

Thanks Steve,

Yes, that is it. I want to loop through the table and when I find an FM_aa_FMAS_ID field with data and an empty field after it - that field must be populated with the data from the previous FM_aa_FMAS_ID field.

In the row for ID = 5 the field c_num_Total is populated with 3.00. This means that there are 3 people in the family (one is Andqqqqq, Joan and therefore 2 others). I need those 2 other records to be populated with her Family_ aa_FMAS_ID alphanumeric id. I need this same routine to loop through all records and populate the Family_ aa_FMAS_ID .

by the way Arrrrr, Ellen should be 8 instead of 9.

Would it be more cautious to add a temporary field to the table and write to that?

Thanks

Yianni

[CJ_London]

I really cannot see the benefit of what you doing - why populate an otherwise empty record with a family key (Family_ aa_FMAS_ID)?

What happens if Amxxxxxxx, Joe returns for another visit, either in Deterboro or somewhere else? What happens if next time he brings more or fewer people?

Since the Family_ aa_FMAS_ID field is some sort of calculated value - how is this calculated?

What do you do with the data once you have it populated as you describe?

[yianni]

I need to populate it in order to create my relational tables. Data was exported from another program and is in a flat file.

[CJ_London]

So you are going to populate these relational tables with blank records?

[ssanfu]

(Changed the 9 to an 8.....)

The questions Ajax asked are what I was going to ask...

Again,
You do know that a table has no inherent order (unlike an Excel spreadsheet)??
If the above was ordered by family name or any field other than "ID", the blank rows would be at the top.

Appending empty rows is not a good idea.....

[yianni]

Hi Steve,

I struggled but accomplished what I need to parse the flat file into 2 table

It may be crude but it worked. I am sending it to.
Appreciate your help

Private Sub cmdLoop_Click()
Dim rs As Recordset
Dim vFID As String
Dim vCnum As Integer
Dim prev_FMASNUM As String
Dim curr_FMASNUM As String
Set rs = CurrentDb.OpenRecordset("sheet1")
rs.MoveLast
rs.MoveFirst
Do While Not rs.EOF
Debug.Print rs![aa_FMAS_Id]
If IsNull(rs![aa_FMAS_Id]) Then
Else
vFID = rs![aa_FMAS_Id] 'place contents in VFID
rs.Edit
rs![FID] = vFID 'Update the field FID with data
rs.Update
End If

Debug.Print "previous " & vFID
Vprev_FMASNUM = vFID
rs.MoveNext ' go to next record

'If the next records are blank we want to update them with the number from the previous records

Do While IsNull((rs![aa_FMAS_Id]))
'Debug.Print Vprev_FMASNUM
rs.Edit
rs![FID] = Vprev_FMASNUM
rs.Update
rs.MoveNext
Loop


'End If

Loop
End Sub


Thanks

Yianni

[yianni]

Hi,

They don't have blank records only blank field because the export routine in the clients program did not support redundant fields. It exported blanks. If I lost the key field I could not split into 2 table.
I solved it
Thanks for support

Yianni

[ssanfu]

Glad you got it working....

-----------------------------------------------
Do you have these 2 lines at the top of every module?

Option Compare Database
Option Explicit

If not, you should...
-----------------------------------------------

I don't know where the field "FID" came from???

-----------------------------------------------

There is an error - undeclared variable (2 places) - "Vprev_FMASNUM" does not exist (was not declared )

-----------------------------------------------
I also got an "No current record" error .... but I made a dB for testing, so I might have done something different than you.

-----------------------------------------------

You should also make sure to close and destroy the recordset:

    Loop
    'clean up
    rs.Close
    Set rs = Nothing
End Sub