looping my records?

[Nirious]

I have a subform in another form (the main form). The subform exists out of continuous forms representing records of a table, (table1).
The form (so one of the continuous forms) contains some fields, but not all of them are from the table (which is the recordsource for the subform), some are from the upper form and some are calculated.

So lets say I have fields A, B, C, D and E which are on the subform, so all of the continuous forms have thses fields.
The values from A and B come from records of table1
C and D are two fileds which are filled by the main form
E is a field that is calculated from some of the other fields, let's say A, C and D.

Now I have a new empty table which needs to be filled with the records of the subform. But not all fields are needed, only A,D and E need to be in the new table.

So eventually I would need a table which would have equally much records as the subform had continuous forms, and each new record would contain the fields A,D and E from the corresponding continuous form in the subform.

Did that make any sense? So what i'm asking is: how do I loop through the records in the subform, putting some values(fields) of each records in a new table (the table already exists).

That's it,
hope you can help me

nirious

 

[Crilen007]

VB.

Have to code it.

If you need more elaboration let me know.


Ah, here is an example that I use to move records from one table to another

Dim rst As New ADODB.Recordset
Dim rstTarget As New ADODB.Recordset
Dim rstSQL As String

rstSQL = "Select * from tblsource" ' Put whatever SQL you want here
    
rstTarget.Open "tblTarget", CurrentProject.Connection, adOpenDynamic, adLockPessimistic

rst.Open rstSQL, CurrentProject.Connection, adOpenDynamic, adLockPessimistic

CurrentProject.Connection.Execute "Delete * from tblTarget" ' DELETES TARGET TABLE Do not have to do this

    Do While Not rst.EOF
            With rstTarget
                .AddNew
                !ID = rst!Field1
                !IDType = rst!Field2
                !EmpName = rst!Field3 & " " & rst!field4
                .Update
            End With
        rst.MoveNext
    Loop

!ID = rst!Field1


in this,

ID is the field name in the target table
Field1 is the field name in the source table


You have to have the table created first to do this
Also, you should have an autonumber index to keep it clean.

 

[Nirious]

thx for the reply

but, I still don't get how I can put field-values into a new table.
This code appears to be working with two tables, and not with a form.

Let me give you an example:

I have my form set on continuous forms. It's linked to a table and has has an equal amount of (form)records as the table.(let's say 4)
I have put three fields on the form. Two that are linked to fields in the table and one that I can put something in at my desire.

eg:
table1_row1_value1 table1_row2_value1 mytext <-- this was record one on the form
table1_row1_value2 table1_row2_value2 othertext <-- this was record two on the form
table1_row1_value3 table1_row2_value3 still_other_text <-- this was record three on the form
table1_row1_value4 table1_row2_value4 text <-- this was record four on the form

Now I have a button "Do It" which takes the the last two fields of every record on my continuous form and inserts it into a new table, table2.

Eventually, after all records are looped throuogh, table2 would contain this:

table1_row2_value1 mytext
table1_row2_value2 othertext
table1_row2_value3 still_other_text
table1_row2_value4 text

And as you can see, I need to put a value that can't be found in table1 into table2. I would need something like this:
"INSERT INTO table2 ( ExTable1Value, TheText) VALUES ('" & Row2FieldonTheForm & "', '" & textFieldonTheForm "');"

But that only gives me one record, and I need to do it for all records. Thus my question:
How do I loop though all records on a continuous form.

 

[Crilen007]

while not eof

 

[Crilen007]

I think it would be easier to do this from the table, not trying to take the data from the form, but the table that the form is linked to.

 

[Nirious]

yes, I know, but I have fields on my form that aren't present in my sourcetable(the one linked to the form).
Should I than make new fields in the sourcetable for every field on the the form? Or what should I do with it. If so than what would you do with the calculated fields?

 

[Crilen007]

put the calculated fields into a table as well.