Code not working

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

Guest

I have 3 tables
table 1 is the main table that is populated from the main form
table 2 is populated from the sub form attached to the main form and info is
cleared once information has been transfered to table 3

I have coding to update table 1 using the main form and table 2 using the
sub-form when a submit button is clicked on the main. After all these tables
are updated a query is run to update table 3 so table 2 can be cleared.

However, the code I have tried to use to update column 1in table3 from
table1 doesnt work.

The feild is called BANo and it is the primary key which links table 1 and 3
together. I would like the number from BANo field in table 1 to be added into
BANo field in table 3 once the query to update table 3 from table2 has been
run.

the code I have tried to use is

Dim rs As DAO.Recordset
Dim kc As DAO.Recordset
Dim kct As DAO.Recordset
Dim ba

Set rs = CurrentDb.OpenRecordset("Table1")
Set kc = CurrentDb.OpenRecordset("Table3")
Set kct = CurrentDb.OpenRecordset("Table2")

ba = rs!BANo 'the ba is meant to store the BANo from Table 1

Do
If kct![Order] <>"" Then 'if the Order field in table 2 doesn't
= a number then
kc.addnew 'Add to table3
kc![BANo] = ba 'under the BANo field the BANo which is stored
in ba from table 1
Exit Do
End If
Loop Until kct![Order] = 0 ' stop the loop once there is no more numbers in
the Order field in table 2

I hope someone can help me with this
 
Noemi,

You need to call 'kc.Update' after the 'kc![BANo] = ba' line otherwise your
..AddNew command is useless. It is only when the recordset .Update method is
called that the record is saved to the table.

Also, your code is a bit difficult to follow. Condiser revising your code
using better table, field and variable names. Also, you should try to
declare all variables with a specific type, for example, 'Dim ba As Long'.
Also your 'Do....Loop Until' loop only makes one change to the table -
consider using a query or SQL statement to make the change, otherwise when
the tables get bigger, that code is going to be slow!

Hope this helps,

Wayne Phillips
http://www.everythingaccess.com/forums
 
Wayne,
Thanks for the help, I will give it a go.

Wayne Phillips said:
Noemi,

You need to call 'kc.Update' after the 'kc![BANo] = ba' line otherwise your
..AddNew command is useless. It is only when the recordset .Update method is
called that the record is saved to the table.

Also, your code is a bit difficult to follow. Condiser revising your code
using better table, field and variable names. Also, you should try to
declare all variables with a specific type, for example, 'Dim ba As Long'.
Also your 'Do....Loop Until' loop only makes one change to the table -
consider using a query or SQL statement to make the change, otherwise when
the tables get bigger, that code is going to be slow!

Hope this helps,

Wayne Phillips
http://www.everythingaccess.com/forums

Noemi said:
I have 3 tables
table 1 is the main table that is populated from the main form
table 2 is populated from the sub form attached to the main form and info is
cleared once information has been transfered to table 3

I have coding to update table 1 using the main form and table 2 using the
sub-form when a submit button is clicked on the main. After all these tables
are updated a query is run to update table 3 so table 2 can be cleared.

However, the code I have tried to use to update column 1in table3 from
table1 doesnt work.

The feild is called BANo and it is the primary key which links table 1 and 3
together. I would like the number from BANo field in table 1 to be added into
BANo field in table 3 once the query to update table 3 from table2 has been
run.

the code I have tried to use is

Dim rs As DAO.Recordset
Dim kc As DAO.Recordset
Dim kct As DAO.Recordset
Dim ba

Set rs = CurrentDb.OpenRecordset("Table1")
Set kc = CurrentDb.OpenRecordset("Table3")
Set kct = CurrentDb.OpenRecordset("Table2")

ba = rs!BANo 'the ba is meant to store the BANo from Table 1

Do
If kct![Order] <>"" Then 'if the Order field in table 2 doesn't
= a number then
kc.addnew 'Add to table3
kc![BANo] = ba 'under the BANo field the BANo which is stored
in ba from table 1
Exit Do
End If
Loop Until kct![Order] = 0 ' stop the loop once there is no more numbers in
the Order field in table 2

I hope someone can help me with this
Click to expand...
Click to expand...
 
Back
Top