input into two tables (1 Viewer)

[mfuada]

I want to ask u guys..
can we input data into two tables by clicking only one button...
in another word could we input data into one table and update that data into another table just by one singe event (event on click)...
could we do that?
thx

 

[RainLover]

Yes you can.

 

[mfuada]

i've tried and it's work but i have a little problem, i was adding a new record to table a, and at the same time with the same data, updating the data into table b,and the problem is the data update in the first row of table b. here's my code:

Private Sub Command43_Click()
Dim reop_db As DAO.Recordset
Dim update_fc As DAO.Recordset
Set reop_db = CurrentDb.OpenRecordset("reop_fixed")
reop_db.AddNew
reop_db!date_reop = Me!reop_datetxt
reop_db!series = Me!series_reoptxt
reop_db!outstanding = Me!outs_reoptxt
reop_db.Update

Set update_fc = CurrentDb.OpenRecordset("fixed_coupon")
update_fc.Edit
update_fc!face_value_fc = Me!outs_reoptxt
update_fc.Update
MsgBox "New Outstanding = " & outs_reoptxt.Value & " "
List18.Requery
End Sub
Is there anyway to tell access to put the updated data, appropriate with the row list of data in the table (means update data "c" should be place in the 3rd row of table b where first field is "c1")

 

[RainLover]

Why not simply run an Update query with the criteria set to equal a unique key on your form.

 

[mfuada]

the update to is blank....(in the update query)
i also have tried this..
Dim reop_db As DAO.Recordset
Dim update_fc As DAO.Recordset
Dim sql As String
Dim dbs As DAO.Database
Set reop_db = CurrentDb.OpenRecordset("reop_fixed")
reop_db.AddNew
reop_db!date_reop = Me!reop_datetxt
reop_db!series = Me!series_reoptxt
reop_db!outstanding = Me!outs_reoptxt
reop_db!comment = Me!reop_comment
reop_db.Update
Set dbs = CurrentDb
'update_fc.Edit
'update_fc!face_value_fc = Me!outs_reoptxt
'update_fc.Update
sql = "UPDATE fixed_coupon SET face_value_fc = " & outs_reoptxt.Value & "WHERE series_fc=" & series_reoptxt.Value
dbs.Execute sql, dbFailOnError
MsgBox "Outstanding baru = " & outs_reoptxt.Value & " "
List18.Requery
but still got a error message in the sql statement.....
any ideas?

 

[RainLover]

> the update to is blank....(in the update query) <

Does this mean there are no records? Please explain more.

Do you realise that it is not normal practice to store the same data in two different places. What do you do if the information in one table changes. Can you guarantee that the data in the other also changes.

I will help you do as you want but I do advise against doing so.

 

[mfuada]

okey.. i'll explain a little bit my first intention on this code...
right now i have two tables: tables_a and tables_b
tables_a : no,series,face_value
tables_b : no,series,date,face_value_outs
face_value is the initial value when user input the data for the very first time, but from time to time the face_value has a lot of changes in its value, and for that i want to make a table to record the historical changes value of face_value, and then i make table_b and form_b, so when a user make a change of face_value's nominal, and when user click the update button. it will automatically input the new face_value's nominal into field face_value in table_a and face_value_outs in table_b.

i've make the unique key for the series in tables_a, because in tables_a the series can't have a duplicate, and in table_b data will be sorted by a date, the date represent the time when user make the nominal change of the face_value. so in the end i might have series with different kind of date.. to show me that is the date when user make the changes of the nominal

i hope my explanation is not too long...
thank u for your time...

 

[mfuada]

for the update query.. i enter the query design mode.... show table_a and table b
in fill this field :face_value_outs
table :table_b
and when i want to fill the "update to" there's no option to choose.. should i write it manually on that field that happen to with the ""criteria" and "or"
i've make a relation database between series in table_a with series in table_b

 

[RainLover]

Please post a copy of your DB

 

[mfuada]

Hi.. i'm really sorry.. it took me along time before answering ur post.. i tried to post my db yesterday but it alwas failed.. so hope this day it will work..
i'm not an access expert so i hope you will understand my DB,because i know it's not structured correctly...
the question that i want to ask it's in the "reop" forms...
thx

 

[gblack]

Can you bind the fields you want to update the table with in the form itself? Meaning, bind table 1 to your form, then use a simple add recorset for the second table?

If you used a bound textbox, your first table issue is solved... then on the button click you can update your second table with the tracking data... Something like this:


Private Sub Command43_Click()
Dim db As Database
Dim rs2 As ADODB.Recordset
Dim dtMyDate As Date
Dim strSeries As String

Set db = CurrentDb
Set rs2 = New ADODB.Recordset

With rs2 'Table 1
.ActiveConnection = CurrentProject.Connection
.CursorType = adOpenKeyset
.LockType = adLockOptimistic
.Open "Select * from [Name of Table1];
End With

dtMyDate = Me!reop_datetxt
strMySeries = Me!series_reoptxt

rs2.AddNew
rs2!date_reop = dtMyDate 'Add my date to table 2
rs2!series = strMySeries 'Add my series to table 2
rs2.Update

set rs2 = nothing
rs2.close

End Sub
... or code to this effect