Add a number to existing data

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

Guest

How can I add a autonunmber from table1 into table 2, however there is
already data contianed in Table 2 (which was copied over from Table 3) in
other columns and I need the autonumber to be entered next to the data.

The following code I have tried but one add's a new row and the other edits
the existing autonumber in the table which is not what I want.

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

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

ba = rs!BANo 'This is where I have stored the autonumber created when table
1 is updated from the main form.

If kct!Order <> "" Then
kc.Edit or kc.AddNew
kc!BANo = ba
kc.Update
End If

Thanks
 
Hi,


If the autonumber is from Table2, you don't need to specify it, it will
be supplied automatically.

In my mind, the easiest way is to use a query. You have to find the
record in table2 that fields from table1 will be "joined" to. If that
"lookup" is made through a common field, such as ClientID, then, bring
table1 and table2 in the grid, join the two table through their common field
ClientID, edit the join-link to include ALL records from table1 and those
matching in table2. Next, bring the fields from table2 (except the
autonumber). You can change the SELECT query into an UPDATE (through the
menu or the tool bar), you update table2 with fields from table1. If you
look at the SQL statement, if may look like:


UPDATE table1 LEFT JOIN table1
ON table1.ClientID=table2.ClientID

SET table2.ClientID=table1.ClientID,
table2.ClientName=table1.ClientName,
table2.ClientPhoneNumber = table1.ClientPhoneNumber



Note: it is preferable to experiment on a copy of the data, rather than on
real data.

Hoping it may help,
Vanderghast, Access MVP
 
Back
Top