Field/Record Sync - Multiple Tables

[kaonashi]

It seems there are a lot of flavors out there to accomplish (Dlookup, INSERT INTO, etc) but I am not just "getting it".

I use Access 2007 to track orders for a customer. There are 5 tables used for the various data (I connect to a Sharepoint site, using lists as the tables and had to break the info up into 5 tables so Sharepoint would handle it - 4 other folks use the same database and the share drive access over VPN is SLOW).

Again, there are 5 tables involved - on the main form, I pull all of the info together in varying degrees using a "Mainpage" query of all the tables - and I pick and choose what is needed on the main form.

The relationship setup is One to Many:

Table1

ASRN (PRIMARY KEY)
Field1
Field2
Field3 (etc)
​

Table2

ASRN
Field1
Field2
Field3 (etc)
​

Table3

ASRN
Field1
Field2
Field3 (etc)
​

Table4

ASRN
Field1
Field2
Field3 (etc)
​

Table5

ASRN
Field1
Field2
Field3 (etc)
​

The common field is ASRN.

The problem I am having is ensuring that when a new ASRN number is entered while creating a new record from the main form, that the corresponding 4 tables have their ASRN fields filled (with the newly entered ASRN from the main form). This is to ensure that, if one of us entered data into the main form (whose fields are aggregated from the 5 tables via an query) that record would be there to associate with in the other tables.

I have attempted (without success) to use the After Update event for the mainform ASRN field and populate this new number across the 4 other tables to ensure a record exists to capture any entered data which may reside on the other 4 tables. What I am experiencing now is that when I enter a new ASRN number, enter data throughout the main form, and move to the next record (saving the data), record entries in the other 4 tables are being created but do not have an ASRN - and thereby not "associated" to the main page record.

I think i should also mention that the main form I am using is based on Table1 - and I use the aggregated query to display the associated data from the other tables.

Can anyone give me any advise on this? I really don't know how to spell out the code for a DLOOKUP statement let alone INSERT INTO in order to accomplish this. It's a crime that I have to work around this Sharepoint solution - but in reality, it is quite responsive (compared to the share drive) over VPN when searching through the database.

I apologize for the agonizingly long post - and if I veered off course and neglected anything please let me know. I can also send over a cut down version of the database (that I am working on locally) to anyone wishing to see what I am trying to do...

Thanks

Kay

 

[Trevor G]

Kay you could use some VBA code like the following, I have only refered to one table in the example:

I am using to textbox on a form and you could place the code behind a command button.

Function updateTables2()
Dim dbs As DAO.Database
Dim rst As Recordset
Set dbs = CurrentDb()
Set rst = dbs.OpenRecordset("TableName", dbOpenTable)
With rst
.AddNew
!Field1 = txtBox.Value
!Field2 = txtbox2.Value
.Update
End Function