RE: 2nd table need to add more fields other than from the 1st tabl

Tech-Archive recommends: Fix windows errors by optimizing your registry

Here is my way to do in VBA. I have a sub that is called from another sub. It
receives passed parameters from the sub call. I declare some codes for
opening the record set as following,
------------------------------
Sub InsertDataToTBL2(intA As Integer, intB As Integer, intC As Integer, intD
As Integer)
Dim connectString As String
connectString = "Provider=Microsoft.Jet.OLEDB.4.0;" & _
"Data Source = C:\Access\db1.mdb"


Dim myDB As ADODB.Connection
Set myDB = New ADODB.Connection
myDB.Open connectString

Dim rsTBL2 As ADODB.Recordset
Set rsTBL2 = New ADODB.Recordset
rsTBL2.Open "TBL2", myDB, adOpenDynamic, adLockOptimistic, adCmdTable

If Not rsTBL2.EOF And Not rsTBL2.BOF Then
Do Until rsTBL2.EOF
rsTBL2.MoveFirst
rsTBL2.Fields(0) = strValue
rsTBL2.Fields(1) = rsTBL1.Fields(1)
rsTBL2.Fields(2) = rsTBL1.Fields(2)
Loop
rsTBL2.MoveNext
End If
End Sub
-------------------------
>From the sub procedure that calls this sub procedure, I process each field
from TBL1. I want to assign values from TBL1 to TBL2. In addition, I need to
calculate some added fields in TBL2. In other word, TBL2 is a copy of TBL1
plus a couple more new fields with values that are calculated from TBL1's
data.

Anyway, when I run this sub, it does not enter into the loop and skip those
code. Do you know why it can not enter the loop. Also, the way I do, i.e.,
process each record from the first tbl and then call the second sub procedure
to put those data into TBL2 and then go back to do the 1st record...I suspect
it is not a good way to do. Do you have any idea to solve this problem?

Thank you,
Curie

"Klatuu" wrote:

> You will not need to use an array. There are a couple of methods you could
> use instead. One would be to manipulate the tables using VBA and
> programmatically add new records or update data in existing records of the
> second table. Another would be to construct either an append or an update
> query, depending on whether you are only adding new rows to table 2 (append)
> or modifiying data in table 2 (update). I am guessing it is adding new rows,
> so probably an append is in order. then for Update To row of the fields you
> need to add, do the calculation.
>

.

Relevant Pages

  • Re: Bias in rand for excel 07
    ... that was statistically different from expectation. ... Sub SimplerVersion() ... "yttrias" wrote: ... There is no RANDfunction in VBA. ...
    (microsoft.public.excel.programming)
  • Re: Bias in rand for excel 07
    ... F1 with a dynamic formula that will update whenever the RAND() functions ... Sub SimplerVersion() ... "yttrias" wrote: ... There is no RANDfunction in VBA. ...
    (microsoft.public.excel.programming)
  • Re: merging documents into templates - automation problem
    ... The code to launch ... Private Sub UserForm_Initialize ... UserForm_Initialize is an EVENT for the UserForm, called by VBA when the ... the code is in a code module for a form. ...
    (microsoft.public.office.developer.automation)
  • RE:reasons as requested
    ... Sub TestThis() ... > im sure i can get vba to do what i want with this function but at the moment ... as a pivot table for this instance would not ... >> first time it is used from the top. ...
    (microsoft.public.excel.programming)
  • Re: Circular dependencies between modules
    ... Public Sub SetData ... MODULE" is set, by VBA, to "yes". ... >> AddedEmpsignals that a new employee has been added ... >> to a new employee in its underlying file ...
    (microsoft.public.access.formscoding)