Update Another Tables Record

[KadeFoster]

Hey all,

I am trying to update a record in another table when i create an update in the current table. I finally figured out the SQL to get it to update for the selected record, but the problem i am having now is that it updates the previous Status not the one that i just set.

CurrentDb.Execute "UPDATE InspectionRequestT SET [InspectionStatus]='" & InspectionStatus & "' WHERE [InspectionRequestID] =" & InspectionRequestID

The image shows where i am updating the status. InspectionExplorerF and the other form InspectionListF is where i want it to update. Its like a delay update lol
Suggestions welcome

 

[pbaldy]

What event is the code in? Use a variable and debug it so you can see what values are being seen:

Debugging

 

[MajP]

You did not show us the event. If you have not left the record then the value is not comitted. Try
Me.dirty = false
Before the update.

 

[plog]

UPDATE queries always give off a whiff of an improperly structured database. Add to it the smell that you are storing the same data in 2 different tables and I have to dig deeper into the cause of the odor:

Why are you updating data in another table instead of adding a record in that table? If the status on 11/11 was 'Pending' and on 12/12 you change it to 'In Progress', you lose the ability to find out when it was in a Pending status because you overwrote that data.

Why is the status stored in 2 places? Theoretically Table1 could show Pending but Table2 could show In Progress, is that something you want or should the 2 tables always have the same status?

 

[KadeFoster]

What event is the code in? Use a variable and debug it so you can see what values are being seen:

Debugging

Its under an triggered by an Update Button

 

[pbaldy]

Its under an triggered by an Update Button

Then you probably need to commit the record as recommended.

 

[KadeFoster]

UPDATE queries always give off a whiff of an improperly structured database. Add to it the smell that you are storing the same data in 2 different tables and I have to dig deeper into the cause of the odor:

Why are you updating data in another table instead of adding a record in that table? If the status on 11/11 was 'Pending' and on 12/12 you change it to 'In Progress', you lose the ability to find out when it was in a Pending status because you overwrote that data.

Why is the status stored in 2 places? Theoretically Table1 could show Pending but Table2 could show In Progress, is that something you want or should the 2 tables always have the same status?

Am still learning, and yeah i agree duplication is not good.

I have one table where i keep the records for the Inspections and the main table keeps the information regarding that inspection.

What i was trying to do is Update the status of the Inspection Overview each time the user adds an update. But doing it the way that i have has created duplicate entries Status field.

 

[plog]

I would put this (and all other form issues) aside and focus on getting the right table structure.

Can you complete your database's Relationship Tool, expand all tables to show all fields and then post a screenshot back here? Or just upload a copy of your database with any personal infromation stripped.

 

[KadeFoster]

I would put this (and all other form issues) aside and focus on getting the right table structure.

Can you complete your database's Relationship Tool, expand all tables to show all fields and then post a screenshot back here? Or just upload a copy of your database with any personal infromation stripped.

My Database is split.

 

[plog]

Even better. Complete the back end Relationship tool or send a copy of that database.

 

[KadeFoster]

Even better. Complete the back end Relationship tool or send a copy of that database.

Well there you go, i didn't think that worked with a split database. Poor knowledge on my behalf.