assign values from 1st tbl to 2nd tbl

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I'd like to assign table1's values to table2. Table1 has 3 fields and table2
have 4 fields. Table2 is empty. The example of these 2 tables as following,
tbl1([ItemName], [Quantity], [PricePerItem])
tbl2([ItemName], [Quantity], [PricePerItem], [TotalPrice])

I use recordsets and manipulate fields in the first table to yield the new
value in the 2nd table, i.e, to calculate [TotalPrice], I create a function
and call it from the main program. But I don't know how to open both
recordsets at the same time and get data from each row of tbl1, call the
function, get [TotalPrice], and insert those values into recordset2 and go to
the next row of tbl1, insert data into the corresponding row in tbl2, and so
on until EOF.
Can you show me how to do that?
Thank you, Tim
 
Try openning two recordset:

Function FunctionName()
Dim DBNAme As Database, MyRec1 As Recordset, MyRec2 As Recordset

Set DBNAme = CodeDb
Set MyRec1 = DBNAme.OpenRecordset("Table1")
If Not MyRec1.EOF Then
Set MyRec2 = DBNAme.OpenRecordset("Table2")
While Not MyRec1.EOF
MyRec2.AddNew
MyRec2!Field1 = MyRec1!Field1
MyRec2!Field2 = MyRec1!Field2
MyRec2!Field3 = MyRec1!Field3
MyRec2.Update
MyRec1.MoveNext
Wend
End If
End Function
 
Thank you for your help. The code shows me how to assign tbl1's values to
tbl2's values. But how can I calculate the [TotalPrice], since I have to
calculalte the [TotalPrice] for every rows (i.e., records) in tbl1?

Should I use an array to store all [TotalPrice] values first, and then use
your code to add new record into the tbl2, with the [TotalPrice] is added as
following.
MyRec2!Field4 = array(1)? If so, how can I do that?

The secod approach is call a fucntion, i.e.,
MyRec2!Field3 = CalculateTotalPrice(), to return a value???

Can you show me which approach would be better and how to do it?

Thank you,
T.

Ofer said:
Try openning two recordset:

Function FunctionName()
Dim DBNAme As Database, MyRec1 As Recordset, MyRec2 As Recordset

Set DBNAme = CodeDb
Set MyRec1 = DBNAme.OpenRecordset("Table1")
If Not MyRec1.EOF Then
Set MyRec2 = DBNAme.OpenRecordset("Table2")
While Not MyRec1.EOF
MyRec2.AddNew
MyRec2!Field1 = MyRec1!Field1
MyRec2!Field2 = MyRec1!Field2
MyRec2!Field3 = MyRec1!Field3
MyRec2.Update
MyRec1.MoveNext
Wend
End If
End Function


Tim said:
I'd like to assign table1's values to table2. Table1 has 3 fields and table2
have 4 fields. Table2 is empty. The example of these 2 tables as following,
tbl1([ItemName], [Quantity], [PricePerItem])
tbl2([ItemName], [Quantity], [PricePerItem], [TotalPrice])

I use recordsets and manipulate fields in the first table to yield the new
value in the 2nd table, i.e, to calculate [TotalPrice], I create a function
and call it from the main program. But I don't know how to open both
recordsets at the same time and get data from each row of tbl1, call the
function, get [TotalPrice], and insert those values into recordset2 and go to
the next row of tbl1, insert data into the corresponding row in tbl2, and so
on until EOF.
Can you show me how to do that?
Thank you, Tim
Click to expand...
Click to expand...
 
What is the calculation? and does it based on the values from table1

Tim said:
Thank you for your help. The code shows me how to assign tbl1's values to
tbl2's values. But how can I calculate the [TotalPrice], since I have to
calculalte the [TotalPrice] for every rows (i.e., records) in tbl1?

Should I use an array to store all [TotalPrice] values first, and then use
your code to add new record into the tbl2, with the [TotalPrice] is added as
following.
MyRec2!Field4 = array(1)? If so, how can I do that?

The secod approach is call a fucntion, i.e.,
MyRec2!Field3 = CalculateTotalPrice(), to return a value???

Can you show me which approach would be better and how to do it?

Thank you,
T.

Ofer said:
Try openning two recordset:

Function FunctionName()
Dim DBNAme As Database, MyRec1 As Recordset, MyRec2 As Recordset

Set DBNAme = CodeDb
Set MyRec1 = DBNAme.OpenRecordset("Table1")
If Not MyRec1.EOF Then
Set MyRec2 = DBNAme.OpenRecordset("Table2")
While Not MyRec1.EOF
MyRec2.AddNew
MyRec2!Field1 = MyRec1!Field1
MyRec2!Field2 = MyRec1!Field2
MyRec2!Field3 = MyRec1!Field3
MyRec2.Update
MyRec1.MoveNext
Wend
End If
End Function


Tim said:
I'd like to assign table1's values to table2. Table1 has 3 fields and table2
have 4 fields. Table2 is empty. The example of these 2 tables as following,
tbl1([ItemName], [Quantity], [PricePerItem])
tbl2([ItemName], [Quantity], [PricePerItem], [TotalPrice])

I use recordsets and manipulate fields in the first table to yield the new
value in the 2nd table, i.e, to calculate [TotalPrice], I create a function
and call it from the main program. But I don't know how to open both
recordsets at the same time and get data from each row of tbl1, call the
function, get [TotalPrice], and insert those values into recordset2 and go to
the next row of tbl1, insert data into the corresponding row in tbl2, and so
on until EOF.
Can you show me how to do that?
Thank you, Tim
Click to expand...
Click to expand...
Click to expand...
 
Here is the calculation:
[TotalPrice]=[Quantity]*[PricePerItem]

Thank you,
Tim


Ofer said:
What is the calculation? and does it based on the values from table1

Tim said:
Thank you for your help. The code shows me how to assign tbl1's values to
tbl2's values. But how can I calculate the [TotalPrice], since I have to
calculalte the [TotalPrice] for every rows (i.e., records) in tbl1?

Should I use an array to store all [TotalPrice] values first, and then use
your code to add new record into the tbl2, with the [TotalPrice] is added as
following.
MyRec2!Field4 = array(1)? If so, how can I do that?

The secod approach is call a fucntion, i.e.,
MyRec2!Field3 = CalculateTotalPrice(), to return a value???

Can you show me which approach would be better and how to do it?

Thank you,
T.

Ofer said:
Try openning two recordset:

Function FunctionName()
Dim DBNAme As Database, MyRec1 As Recordset, MyRec2 As Recordset

Set DBNAme = CodeDb
Set MyRec1 = DBNAme.OpenRecordset("Table1")
If Not MyRec1.EOF Then
Set MyRec2 = DBNAme.OpenRecordset("Table2")
While Not MyRec1.EOF
MyRec2.AddNew
MyRec2!Field1 = MyRec1!Field1
MyRec2!Field2 = MyRec1!Field2
MyRec2!Field3 = MyRec1!Field3
MyRec2.Update
MyRec1.MoveNext
Wend
End If
End Function


:

I'd like to assign table1's values to table2. Table1 has 3 fields and table2
have 4 fields. Table2 is empty. The example of these 2 tables as following,
tbl1([ItemName], [Quantity], [PricePerItem])
tbl2([ItemName], [Quantity], [PricePerItem], [TotalPrice])

I use recordsets and manipulate fields in the first table to yield the new
value in the 2nd table, i.e, to calculate [TotalPrice], I create a function
and call it from the main program. But I don't know how to open both
recordsets at the same time and get data from each row of tbl1, call the
function, get [TotalPrice], and insert those values into recordset2 and go to
the next row of tbl1, insert data into the corresponding row in tbl2, and so
on until EOF.
Can you show me how to do that?
Thank you, Tim
Click to expand...
Click to expand...
Click to expand...
 
Also, when I debug to check if the records are inserted in to tbl2, it shows
the values but when I open the table2, it's empty. Do you know why?
Thanks

Tim said:
Here is the calculation:
[TotalPrice]=[Quantity]*[PricePerItem]

Thank you,
Tim


Ofer said:
What is the calculation? and does it based on the values from table1

Tim said:
Thank you for your help. The code shows me how to assign tbl1's values to
tbl2's values. But how can I calculate the [TotalPrice], since I have to
calculalte the [TotalPrice] for every rows (i.e., records) in tbl1?

Should I use an array to store all [TotalPrice] values first, and then use
your code to add new record into the tbl2, with the [TotalPrice] is added as
following.
MyRec2!Field4 = array(1)? If so, how can I do that?

The secod approach is call a fucntion, i.e.,
MyRec2!Field3 = CalculateTotalPrice(), to return a value???

Can you show me which approach would be better and how to do it?

Thank you,
T.

:

Try openning two recordset:

Function FunctionName()
Dim DBNAme As Database, MyRec1 As Recordset, MyRec2 As Recordset

Set DBNAme = CodeDb
Set MyRec1 = DBNAme.OpenRecordset("Table1")
If Not MyRec1.EOF Then
Set MyRec2 = DBNAme.OpenRecordset("Table2")
While Not MyRec1.EOF
MyRec2.AddNew
MyRec2!Field1 = MyRec1!Field1
MyRec2!Field2 = MyRec1!Field2
MyRec2!Field3 = MyRec1!Field3
MyRec2.Update
MyRec1.MoveNext
Wend
End If
End Function


:

I'd like to assign table1's values to table2. Table1 has 3 fields and table2
have 4 fields. Table2 is empty. The example of these 2 tables as following,
tbl1([ItemName], [Quantity], [PricePerItem])
tbl2([ItemName], [Quantity], [PricePerItem], [TotalPrice])

I use recordsets and manipulate fields in the first table to yield the new
value in the 2nd table, i.e, to calculate [TotalPrice], I create a function
and call it from the main program. But I don't know how to open both
recordsets at the same time and get data from each row of tbl1, call the
function, get [TotalPrice], and insert those values into recordset2 and go to
the next row of tbl1, insert data into the corresponding row in tbl2, and so
on until EOF.
Can you show me how to do that?
Thank you, Tim
Click to expand...
Click to expand...
Click to expand...
 
If [Quantity] and [PricePerItem] are values from table1, then try this then

Function FunctionName()
Dim DBNAme As Database, MyRec1 As Recordset, MyRec2 As Recordset

Set DBNAme = CodeDb
Set MyRec1 = DBNAme.OpenRecordset("Table1")
If Not MyRec1.EOF Then
Set MyRec2 = DBNAme.OpenRecordset("Table2")
While Not MyRec1.EOF
MyRec2.AddNew
MyRec2!Field1 = MyRec1!Field1
MyRec2!Field2 = MyRec1!Field2
MyRec2![TotalPrice]=MyRec1![Quantity]*MyRec1![PricePerItem]
MyRec2.Update
MyRec1.MoveNext
Wend
End If
End Function

About the empty table, check if you didn't forget the line MyRec2.Update

Tim said:
Also, when I debug to check if the records are inserted in to tbl2, it shows
the values but when I open the table2, it's empty. Do you know why?
Thanks

Tim said:
Here is the calculation:
[TotalPrice]=[Quantity]*[PricePerItem]

Thank you,
Tim


Ofer said:
What is the calculation? and does it based on the values from table1

:

Thank you for your help. The code shows me how to assign tbl1's values to
tbl2's values. But how can I calculate the [TotalPrice], since I have to
calculalte the [TotalPrice] for every rows (i.e., records) in tbl1?

Should I use an array to store all [TotalPrice] values first, and then use
your code to add new record into the tbl2, with the [TotalPrice] is added as
following.
MyRec2!Field4 = array(1)? If so, how can I do that?

The secod approach is call a fucntion, i.e.,
MyRec2!Field3 = CalculateTotalPrice(), to return a value???

Can you show me which approach would be better and how to do it?

Thank you,
T.

:

Try openning two recordset:

Function FunctionName()
Dim DBNAme As Database, MyRec1 As Recordset, MyRec2 As Recordset

Set DBNAme = CodeDb
Set MyRec1 = DBNAme.OpenRecordset("Table1")
If Not MyRec1.EOF Then
Set MyRec2 = DBNAme.OpenRecordset("Table2")
While Not MyRec1.EOF
MyRec2.AddNew
MyRec2!Field1 = MyRec1!Field1
MyRec2!Field2 = MyRec1!Field2
MyRec2!Field3 = MyRec1!Field3
MyRec2.Update
MyRec1.MoveNext
Wend
End If
End Function


:

I'd like to assign table1's values to table2. Table1 has 3 fields and table2
have 4 fields. Table2 is empty. The example of these 2 tables as following,
tbl1([ItemName], [Quantity], [PricePerItem])
tbl2([ItemName], [Quantity], [PricePerItem], [TotalPrice])

I use recordsets and manipulate fields in the first table to yield the new
value in the 2nd table, i.e, to calculate [TotalPrice], I create a function
and call it from the main program. But I don't know how to open both
recordsets at the same time and get data from each row of tbl1, call the
function, get [TotalPrice], and insert those values into recordset2 and go to
the next row of tbl1, insert data into the corresponding row in tbl2, and so
on until EOF.
Can you show me how to do that?
Thank you, Tim
Click to expand...
Click to expand...
Click to expand...
 
I got it. Thank you for your help.
Tim

Ofer said:
If [Quantity] and [PricePerItem] are values from table1, then try this then

Function FunctionName()
Dim DBNAme As Database, MyRec1 As Recordset, MyRec2 As Recordset

Set DBNAme = CodeDb
Set MyRec1 = DBNAme.OpenRecordset("Table1")
If Not MyRec1.EOF Then
Set MyRec2 = DBNAme.OpenRecordset("Table2")
While Not MyRec1.EOF
MyRec2.AddNew
MyRec2!Field1 = MyRec1!Field1
MyRec2!Field2 = MyRec1!Field2
MyRec2![TotalPrice]=MyRec1![Quantity]*MyRec1![PricePerItem]
MyRec2.Update
MyRec1.MoveNext
Wend
End If
End Function

About the empty table, check if you didn't forget the line MyRec2.Update

Tim said:
Also, when I debug to check if the records are inserted in to tbl2, it shows
the values but when I open the table2, it's empty. Do you know why?
Thanks

Tim said:
Here is the calculation:
[TotalPrice]=[Quantity]*[PricePerItem]

Thank you,
Tim


:

What is the calculation? and does it based on the values from table1

:

Thank you for your help. The code shows me how to assign tbl1's values to
tbl2's values. But how can I calculate the [TotalPrice], since I have to
calculalte the [TotalPrice] for every rows (i.e., records) in tbl1?

Should I use an array to store all [TotalPrice] values first, and then use
your code to add new record into the tbl2, with the [TotalPrice] is added as
following.
MyRec2!Field4 = array(1)? If so, how can I do that?

The secod approach is call a fucntion, i.e.,
MyRec2!Field3 = CalculateTotalPrice(), to return a value???

Can you show me which approach would be better and how to do it?

Thank you,
T.

:

Try openning two recordset:

Function FunctionName()
Dim DBNAme As Database, MyRec1 As Recordset, MyRec2 As Recordset

Set DBNAme = CodeDb
Set MyRec1 = DBNAme.OpenRecordset("Table1")
If Not MyRec1.EOF Then
Set MyRec2 = DBNAme.OpenRecordset("Table2")
While Not MyRec1.EOF
MyRec2.AddNew
MyRec2!Field1 = MyRec1!Field1
MyRec2!Field2 = MyRec1!Field2
MyRec2!Field3 = MyRec1!Field3
MyRec2.Update
MyRec1.MoveNext
Wend
End If
End Function


:

I'd like to assign table1's values to table2. Table1 has 3 fields and table2
have 4 fields. Table2 is empty. The example of these 2 tables as following,
tbl1([ItemName], [Quantity], [PricePerItem])
tbl2([ItemName], [Quantity], [PricePerItem], [TotalPrice])

I use recordsets and manipulate fields in the first table to yield the new
value in the 2nd table, i.e, to calculate [TotalPrice], I create a function
and call it from the main program. But I don't know how to open both
recordsets at the same time and get data from each row of tbl1, call the
function, get [TotalPrice], and insert those values into recordset2 and go to
the next row of tbl1, insert data into the corresponding row in tbl2, and so
on until EOF.
Can you show me how to do that?
Thank you, Tim
Click to expand...
Click to expand...
Click to expand...
 
Back
Top