Update and Append queries not working

[Sam Summers]

Hi everyone,

I hope i can describe this correctly? I have been working on this particular problem on and off for around 5 weeks now and after trying so many ideas and orders of operation it still will not work.
Basically what i am trying to do is after entering a new status from the 'Maintenance' and 'Service' tab which is selected by double clicking on the corresponding vessel as in Screenshot 2.
After this i need to update either the 'Service' and or 'Maintenance' Table depending on whether an item is updated from the ServiceListSubform or MaintenanceListSubform and to also Append the change to the corresponding ServiceHistory Table or MaintenanceHistory Table.

Once again i must be missing something fundamental either in the sequence or the code event order?

I have had a few 'Write conflict' messages when i have tried certain ways?

I just dont know what i am doing wrong?

Many many thanks if anyone can help me

 

[June7]

Agree, this could likely be 1 table instead of 2.

Why does code want to update a record when ViewVessel form opens? Don't need recordsetclone and query object for that. Just edit the record on form.
Me!Status = IIf(Me!Status Like "Out*", "In Service", "Out Of Service")

Why not just save "In" or "Out"? Or make this a Boolean Yes/No field?

 

[Sam Summers]

I dont know how i can incorporate Service and Maintenance into the same table as they record different things and some different components?

The Recordsetclone code was given to me from a previous problem by Arnel.

Thats all i know at this stage?

 

[June7]

Did you try suggested code?

Relationships is a confusing spider web of links and quite possibly includes circular associations. https://www.codeproject.com/articles/38655/prevent-circular-references-in-database-design

I think Status and Vessel tables are not correctly linked.

Why do tables have both StatusID and Status fields?

Service and Maintenance have many common fields and then each have a few the other doesn't. Yes, if combined, not every field will apply to every record but the structure might be simpler to manage. It is a balancing act between normalization and ease of data entry and output. "Normalize until it hurts, denormalize until it works" - https://completedeveloperpodcast.com/episode-249/#:~:text=The rule: Normalize until it hurts; denormalize until,and bite you when you get under load.

Fleet maintenance db is a common topic. Search the forum and will get a lot of discussions.

 

[June7]

I have used the record selector DblClick event but I put a label on form instructing user to do so to open that record on another form.

 

[Sam Summers]

Wow amazing replies - Thank you. I am going to look at all this right now and hopefully fix it.

In answer to your questions Pat:

Tech is only for Servicing and only one Tech responsible for each service when conducted.
When i deploy the app i go through it with the users as we work for the same company so its all in house.

I will post back here once i have sorted the rest out