Updating 2 tables from one form

[Paul Watts]

Table 1 contains basic details for each of my contracts and table 2 shows the results of monitoring each contract on a regular basis. I want to maintain a history of contract "status" in table 2 but maintain "latest status" in table 1. Each table is updated using separate forms but I want to automatically update the "latest status" field in table 1 when I update table 2. Any ideas?

Paul

 

[Trevor G]

Look to use an update query against your fields and criteria then you can run that from the After Update Event from your status field.

 

[Paul Watts]

Hi Trevor and thanks for your response.

Derrr, what's an "After update event"?

I have looked at using an Update query but the book says it provides for a change to values in a field by applying an arithmetic formula (e.g. to multiply field values by a factor). In my case, I want to copy the value entered into table 2 directly into table 1. Can this be done using an update query?

 

[Trevor G]

Yes as I stated. The update query can just update the field based on the 2 tables, you would create a query and select both tables then look to do the following:

Select to create the relationship between the tables by draging from one field to the other which has a matching ID if it isn't already related.

Double click the ID Field from the Main table and then you would select field from the second table that you want to update then change the query type to an Update you would then Adjust the Table name to the table you want to update and then set the Criteria to select from the table with the data in it and the field which has the current data. You would then run the query.

Advice test this on a copy of your database otherwise if you get the query wrong it will clear out the main table which currently has the correct data.

Attached is a small example. Look at the Customer Table and you will see it has customer name and address. Then look at the Transactions table the Address do not appear. Then open the Update query in design view to explore how it has been set up then run it and it will tell you it will update the 2 records, once run look at the Transactions table and it will show the names and addresses

 

[Paul Watts]

Thanks for the detailed response. I will have a go and let you know, in due course, how I get on. It may be a few days ...............

 

[Trevor G]

Paul strip down a copy of your database into another one attach it to your thread and tell which is the field names from the main table and then the table to update and I will create it for you and post back the SQL code and also the database if that will help you out.

Database has to be less than 2 meg so you might need to zip it up first.

 

[Paul Watts]

Hi Trevor.
Back again, at long last. Sorry but I've had to give priority to other issues.

1. Table 1 has each contract number repeated in several records due to regular monitoring events defined by "Season" and "Monitoring type" (the status of any contract in table 1 may change at any monitoring event - I want only the latest status in table 2). Can I, therefore, select the appropriate monitoring record by filtering using other fields (i.e. Season, Monitoring type) in the update query?

2. I assume that I can automate the process so that it happens at the click of a button after a monitoring event. Is that right?

3. Will the update query work on access tables in a linked file?

By the way, your example didn't download for some reason.

Paul

 

[Paul Watts]

Hi Trevor.
I have had a go but without success. I need you to tell me what to put into the "Criteria" row and "Update to" row against each field used in the query. I currently have the fields from table 1:-
Contract
Contract status
Season
Monitoring type

and from table 2:-
Latest contract status

If you recall, I need to copy "Contract status" for a selected "Season" and "Monitoring type" from table 1 into "Latest contract status" in table 2 for all contracts. I also need to be able to do this at the click of a button (I'm not the user).

Best wishes

Paul

 

[Paul Watts]

Hi Trevor.
Eureka!! Please ignore my last 2 posts.
I have fixed the problem as you have advised. I had not enabled "Certain content" which meant that the update query wouldn't activate. It does now.

Thanks again.

Paul

 

[Pick9811]

Hello Paul,

I've had similar stuff in the past... I'll try to give you a hand, but keep in mind I'm a noobie.

I use forms to enter data, and fire events that update tables... which is where the "After Update" event is that Trevor was talking about... the tables just store the data, the form is where your event will fire from. So, if you create a form based on one of your tables, and you want it to update/insert data in two tables you can do that one of two ways:

-Use SQL to insert/update both tables, which takes two update queries
-Or Use and SQL query to insert/update into one table and bind the form and fields to the data in the other table

There is a distinction between "Insert" and "Update" queries though. Your "Insert" query will produce a brand new record in your table, and the "Update" query will modify a current record in a table. So, once you create the form, right click and hit properties (unless the property sheet is already open on the right). Then under the "Events" tab on the property sheet you'll see the "after update" option. Click in that box, then click the "..." on the right side. Then it will ask you what you want to do (Macro Builder, Expression Builder, Code Builder), select code builder to take you to your VBA.

There will now already be a sub set up that looks like:

Private Sub Form_AfterUpdate()

End Sub

You just need to put the code in the middle there so it looks like:

Private Sub Form_AfterUpdate()

Dim db As DAO.Database
Set db = CurrentDb

db.execute "INSERT INTO Table1Name (Column1, Column2) " & _
"VALUES ( '" & TxtBoxOne & "', '" & TxtBoxTwo & "' )"

db.execute "INSERT INTO Table2Name (Column1, Column2) " & _
"VALUES ( '" & TxtBoxOne & "', '" & TxtBoxTwo & "' )"

Set db = Nothing

End Sub

You can copy/paste that code and it should work after you change "Table1Name" to your first table's name, Column1&2 to your Column names and TxtBoxOne&Two to your Text Boxes names... change appropriately for Combo box, list box etc... as long as the name matches it works. By the way, the " & _ and then quotes on the next line is like a carriage return, so you can take that out if you want it on one line.

An update query would be slightly more difficult, because you would have to find and filter the data you wanted to update but it would be written in the following format:

UPDATE TableName SET Column1Name = [Value1], Column2Name = [Value2] WHERE [SomeColumn] = [SomeValue]

That should all be on one line (I didn't put in the " & _ ). If you wanted it to populate those values from a textbox, combo, etc you'd replace "[Value]" with '" & TxtBoxOne & "' like the above.

Sorry, if I missed my point of aim and either wrote over your head or way under your level of expertise...

Pick

 

[Pick9811]

Apparently you figured it out while I wrote my book...

 

[Trevor G]

Hi Trevor.
Eureka!! Please ignore my last 2 posts.
I have fixed the problem as you have advised. I had not enabled "Certain content" which meant that the update query wouldn't activate. It does now.

Thanks again.

Paul

Well done and thanks for letting me know you have a working solution.