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

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

Guest

I need to add more fields in the second table. This second table is just a
copy of the first table but adding more fields (i.e., 1st table have these
fields item names, prices and the second table includes item names, prices,
subtotals, total).
Do you know how to do it programming using VBA's recordset and/or array (MS
Access 2000).
Thank you,
 
That first thing you need to consider is you are violating a basic database
normalization rule. You should never store calculated values in a table.
This is a bad idea for a lot of reasons. On rare occasions, it is reasonable
to break this rule, but it is so rare, it is not worth considering.

I suggest you rethink your design.
 
well, I import a txt file into a table in Access. This is the first table.
Based on information from the first table, and inf. from another table, I
will need to combine both of them and add a couple more fields . This is why
I create the new table. I think I should save data from tables into an array
to manipulate them. Do you know how to do it?
Thanks
 
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.
 
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
 
P.S.: Here is the error message

Run-time error '3021':Either BOF or EOF is True, or the current record has
been deleted. Requested operation requires a current record.
Do you know how to fix it?
Thank you,
 
You approach is correct. I don't quite understand whether you are adding new
records to rsTBL2 or updating existing records. In the code you posted, if
it were to enter the loop, you would end up in an infinite loop until your
database got too large to hold any more data.
I have modified your code with the assumption you are adding new records. I
also have included the code for looping through rsTBL1.

If rstTBL1.Record Count = 0 Then
MsgBox "No Data Available"
Else
rstTBL1.MoveLast
rstTBL1.MoveFirst
Do While Not rstTBL1.EOF
rsTBL2.AddNew
rsTBL2.Fields(0) = strValue
rsTBL2.Fields(1) = rsTBL1.Fields(1)
rsTBL2.Fields(2) = rsTBL1.Fields(2)
rsTBL2.Fields(3) = 'Calculate the value here
rsTBL2.Update
rstTBL1.MoveNext
Loop
End If
 
Hi Klatuu:
I already posted my reply to your below email yesterday, but I don't know
why it did not appear today. I just like to say, "Thank you" for your help.
Yes, I have to add AddNew and Update methods.
Curie
 
Back
Top