Update and Append queries not working (1 Viewer)

Sam Summers

Registered User.
Local time
Today, 17:09
Joined
Sep 17, 2001
Messages
939
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
 

Attachments

  • Fleet Management.zip
    740.9 KB · Views: 321
  • Screenshot 1.png
    Screenshot 1.png
    27.9 KB · Views: 308
  • Screenshot 2.png
    Screenshot 2.png
    12.5 KB · Views: 282

Pat Hartman

Super Moderator
Staff member
Local time
Today, 13:09
Joined
Feb 19, 2002
Messages
42,971
Service and Maintenance are essentially the same thing and belong in the same table. Some items are scheduled and others are not. That is the basic difference. I don't have time to look at the db now.

When you get write conflict messages, you are almost certainly conflicting with yourself by running an update query from a bound form where you are attempting to update the dirty record the form is bound to.
 

June7

AWF VIP
Local time
Today, 09:09
Joined
Mar 9, 2014
Messages
5,423
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?
 
Last edited:

Sam Summers

Registered User.
Local time
Today, 17:09
Joined
Sep 17, 2001
Messages
939
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

AWF VIP
Local time
Today, 09:09
Joined
Mar 9, 2014
Messages
5,423
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.
 
Last edited:

Pat Hartman

Super Moderator
Staff member
Local time
Today, 13:09
Joined
Feb 19, 2002
Messages
42,971
There is a very strange reference between Vessel and Status. That doesn't make any sense at all. It implies that a status code belongs ONLY to a single vessel since it makes status a child of Vessel. Status should be a simple lookup table and should always be the 1-side of any relationship. Also as June pointed out, StatusID is the FK NOT Status so we should only see StatusID NOT Status in all the many-side tables and never see both.

It also seems unusual to track the TechID for service and not for maintenance plus, can't more than one tech work on each maintenance/service event? I would think that this should be a m-m relationship rather than 1-m.

PS, I looked at the app and couldn't figure out how to get into any forms from the opening form. Is there really no menu? When I looked at the code, I saw that I had to double click on the RecordSelector. That wasn't obvious at all. Normally you use dbl-click in a field or use a button. I don't believe I've ever seen anyone use the RecordSelector for this purpose before.

Also, having to do an update to view any other form is also odd. How do you view a maintenance record without updating the status?
 

June7

AWF VIP
Local time
Today, 09:09
Joined
Mar 9, 2014
Messages
5,423
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.
 

Pat Hartman

Super Moderator
Staff member
Local time
Today, 13:09
Joined
Feb 19, 2002
Messages
42,971
@June7, You can do whatever you want as long as the functionality is obvious or clear. You made your interface clear so that works for me:)

The really bad thing about this interface is having to update something you might not want to update just to look at what you have..
 

Sam Summers

Registered User.
Local time
Today, 17:09
Joined
Sep 17, 2001
Messages
939
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
 

Users who are viewing this thread

Top Bottom