VBA Code to auto update a field in another table?

[Tman]

hi access forums

Is there a VBA code that can automatically update the same fields in other related tables, for example when the user selects a 'shop' in the front page survey, this response auto updates to other tables (just that field)? eg attached as an after update on the home page submit button?

I have tried the append query, and I don't think that is as user friendly or as professional as a vba code would be (updating quietly in the background) as there is around 10 tables to update?



any help appreciated!

thanks
TB

[pbaldy]

One method:

  Dim db                      As DAO.Database
  Dim rs                      As DAO.Recordset

  Set db = CurrentDb()
  Set rs = db.OpenRecordset("TableName", dbOpenDynaset)

  rs.AddNew
  rs!FieldName = YourValueHere
  rs!AnotherField = SomeOtherValue
  rs.Update

  Set rs = Nothing
  Set db = Nothing

[Tman]

One method:

  Dim db                      As DAO.Database
  Dim rs                      As DAO.Recordset
 
  Set db = CurrentDb()
  Set rs = db.OpenRecordset("TableName", dbOpenDynaset)
 
  rs.AddNew
  rs!FieldName = YourValueHere
  rs!AnotherField = SomeOtherValue
  rs.Update
 
  Set rs = Nothing
  Set db = Nothing

Thanks pbaldy, however I couldn't get to work.

I copied this into the form for "tbl1" in the after event VBA, and when I went to new on that form with this added in, I got an error re the fields.

With the fields below, I think this is where i get it wrong.

I gather I change the FieldName to my FieldName, however the value I was not sure of. Is there some additional code that goes in here? The value will be whatever the value is in tbl1, eg;

rs!FieldName = YourValueHere

change to:

rs!Site_ID (field in table 2) = [tb1] (table of where I want data from)[Site_ID] (field from table 1 where i want the data from)

Am I reading that right?

Should there be code where you have "Your value here" (ignore mine above as i dont know what it would be!? The value is whatever the value in Table 1.

Many thanks!
TMan

[pbaldy]

From your initial description, I assume that the desired value is on the same form, so

rs!Site_ID = Me.Site_ID

or whatever the name of the control is that contains the desired value. If it's actually only in a table, you'd either need another recordset or a DLookup to get it from there.