Copy a value from table "A" to Table "B" using a form event

[Larft]

In my Access 2002-2003 database that I use to keep track of lab projects I would like to add a second table that records testing data, the first table stores information about the project itself, description, due date etc. I could skip all of this by just adding the fields to my existing table but I would prefer to separate the data in a second table. The problem is that the common link "LWR#" needs to be present in second table and it's pretty silly to have to enter it manually in the second table. Data is added to table #1 through an "Add" form and edited using a second "Edit" form; both are based on the same query that references both tables as well as a customer table.

How can I store the value entered into the "LWR#" text box in the "Add" form in both tables at the same, or nearly so, time? I have looked around and found some references to similar issues one that looked like it would do it was in "thread702-664580" but I get an error trying to run the edited code, I'm not much at VB coding. Is there a way to write to two control sources in different tables at the same time or is this best done as an "after insert" event in the "Add" form?

Thanks in advance!

 

[Remou]

Hi
If you have a project table and a testing table, perhaps the way to go is a subform, with LWR# as the link field. This would be useful if you have more than one test per project. In addition, the LWR# will be filled in by Access, if the link is set up properly. However, if you prefer the code method, please post the code you have, perhaps only a small change is needed.

 

[Larft]

Thanks for your quick reply!

I wasn't thinking that the subform would populate the field in the other table since the data for the link exists only on the Table "A" side initially, I'll try it out and see. It would be helpful to know how to code it, I can see where having it populate a field in another table without having to set up a subform would be useful.

Here is what I tried, again it was from thread702-664580:

Private Sub Form_AfterInsert()
Dim dbs As DAO.Database, rst As DAO.Recordset

Set dbs = CurrentDb()

Set rst = dbs.OpenRecordset("tblTesting")

With rst
      .AddNew
      [!LWR#] = Me!LWR#
      .Update
End With

rst.Close
End Sub

I had to change the period (.) to (!) between Me!LWR# or it would give me a compile error, now it says it can't find function LWR. Any help would be appreciated, I'm not sure if it will even do what I want if it will run properly.

Thanks again!

 

[Remou]

This line:
[tt] [!LWR#] = Me!LWR#[/tt]
Should look like this:
[tt] ![LWR#] = Me![LWR#][/tt]

 

[Larft]

Thanks Remou,

The code worked just great, and I managed to incorporate everything into the form. I figured out from your example how to make it update yet another table I set up to further separate the different types of data.

Here is what I ended up with:

Private Sub Form_AfterInsert()
Dim dbs As DAO.Database, rst As DAO.Recordset

Set dbs = CurrentDb()

Set rst1 = dbs.OpenRecordset("tblTesting")

With rst1
      .AddNew
      ![LWR#] = Me![LWR#]
      .Update
End With

rst1.Close

Set rst2 = dbs.OpenRecordset("tblApplication")

With rst2
      .AddNew
      ![LWR#] = Me![LWR#]
      .Update
End With

rst2.Close

End Sub

It probably could be done more elegantly than I did but since I really don't know what I'm doing in VB it worked for me .

I ended up not using the subform approach since I wanted to use a tabbed form to separte things and just tied things together with the query that the form is based on, cascading update and all.

Thanks again!