Cannot get Many to Many relationship to work (1 Viewer)

kobiashi

Registered User.
Local time
Today, 14:35
Joined
May 11, 2018
Messages
258
so i have set up a many to many relationship and a form bound to a query to capture the data

in the form, there is a sub form,

the form is to capture cleans on a vehicle, the sub form is what vehicles are being cleaned

when i save it, the junction table updates fine, but the deepclean table where the deepclean_ID primary key is, doesnt.

please can someone take a look at it and see what im doing wrong
 

Attachments

  • testdatabase.accdb
    524 KB · Views: 290

jdraw

Super Moderator
Staff member
Local time
Today, 10:35
Joined
Jan 23, 2006
Messages
15,363
Tell us more about the "business" in simple, clear window shows a 1 to Many relationship between 2 tables???
 

kobiashi

Registered User.
Local time
Today, 14:35
Joined
May 11, 2018
Messages
258
Tell us more about the "business" in simple, clear window shows a 1 to Many relationship between 2 tables???

ok, so i work for a railway, there are 149 vehicles, and everything is captures against the vehicles.

one aspect is the vehicles require deep cleaning every 70 days, and it varies between two and three vehicles being deep cleaned everynight
 

jdraw

Super Moderator
Staff member
Local time
Today, 10:35
Joined
Jan 23, 2006
Messages
15,363
OK. You're dealing with railway vehicles. It seems the issue is deep cleaning these vehicles. Locomotive, coal car, passenger car, dining car, sleeping car????

Is Deep Cleaning the only cleaning or is there some other cleaning involved?

Do you only clean vehicles based on some time schedule?
Do some vehicles get priority deep cleaning because of what they have been used for/hauled?

What differentiates 2 vs 3 vehicles deep cleaning per night?
 

kobiashi

Registered User.
Local time
Today, 14:35
Joined
May 11, 2018
Messages
258
OK. You're dealing with railway vehicles. It seems the issue is deep cleaning these vehicles. Locomotive, coal car, passenger car, dining car, sleeping car????

Is Deep Cleaning the only cleaning or is there some other cleaning involved?

Do you only clean vehicles based on some time schedule?
Do some vehicles get priority deep cleaning because of what they have been used for/hauled?

What differentiates 2 vs 3 vehicles deep cleaning per night?
there is also standard cleaning, but that wont be captured on this database, then there is external washing, which will be captured

the deep cleaning is based on a time scale, every 70 days
external washing is every 7 days
ther difference between two and three vehicles being cleaned per night, depends how far they are in to their deep cleaning program, and if some thing has happened that has postponed cleaning for a few nights, which can put the program back a little, so the cleaning of three cars per night is just normally catch up.


there is no priority in terms of how what they haul, there is a list based on whats due next, but its based on the 70 day period.


the vehicles are passenger car vehicles, there are no haulage vehicles
 

jdraw

Super Moderator
Staff member
Local time
Today, 10:35
Joined
Jan 23, 2006
Messages
15,363
How do you know when a vehicle was last deep cleaned? Do you keep history?
Do you have an up to date list?

If you record the lastDeepCleanDate for each vehicle, you can easily get a list of vehicles needing deep cleaning. This could be your schedule.
eg. identify those vehicles whose lastDeepCleanDate is <Today's date -70 days
That will show you those vehicles that have not been Deep cleaned in the last 70 days in oldest to newest Date sequence.

You could also record LastExtWashDate and determine which vehicles are requiring external cleaning in a similar manner.

I'm not sure what the tables you have are meant to represent. Nor, how much data you intend/need to capture. I see it as a single table possibly, but don't know the details.

Form(s) to show the vehicles for DeepCleaning or ExternalWashing and update the LastDeepCleanDate or LastExtWashDate would seem sufficient, but your needs may be different.

Good luck with your project.
 
Last edited:

kobiashi

Registered User.
Local time
Today, 14:35
Joined
May 11, 2018
Messages
258
How do you know when a vehicle was last deep cleaned?
Do you have an up to date list?

If you record the lastDeepCleanDate for each vehicle, you can easily get a list of vehicles needing deep cleaning. This could be your schedule.
eg. identify those vehicles whose lastDeepCleanDate is <Today's date -70 days
That will show you those vehicles that have not been Deep cleaned in the last 70 days in oldest to newest Date sequence.

I'm not sure what the tables you have are meant to represent. Nor, how much data you intend/need to capture. I see it as a single table possibly, but don't know the details.

Good luck with your project.

thanks for the reply


ill be montoring the upcoming deep cleans, with come query calculations, thats not the issue.

when i save the form, it doesnt seem to save the deepclean_ID, in table_deepcleans
 

jdraw

Super Moderator
Staff member
Local time
Today, 10:35
Joined
Jan 23, 2006
Messages
15,363
I get this error when opening and trying your form.



Removed inline image no longer relevant and too large
 

Attachments

  • KobiashiError.png
    KobiashiError.png
    41.4 KB · Views: 294
Last edited:

kobiashi

Registered User.
Local time
Today, 14:35
Joined
May 11, 2018
Messages
258
sorry, there was a query missing, ive uploaded a new version
 

Attachments

  • testdatabase.accdb
    616 KB · Views: 276

jdraw

Super Moderator
Staff member
Local time
Today, 10:35
Joined
Jan 23, 2006
Messages
15,363
How do you enter a new DeepCleaning?
 

jdraw

Super Moderator
Staff member
Local time
Today, 10:35
Joined
Jan 23, 2006
Messages
15,363
This what I'm seeing.


If I turn navigation buttons ON I can enter a new record by moving to the end of current recordset.
 

Attachments

  • K2Form.png
    K2Form.png
    32.4 KB · Views: 281

kobiashi

Registered User.
Local time
Today, 14:35
Joined
May 11, 2018
Messages
258
yeh thats right

i've added a button, in frm_Main, so you can see a new record being created, youll see it doesnt save the deepclean_ID
 

Attachments

  • testdatabase.accdb
    616 KB · Views: 277

jdraw

Super Moderator
Staff member
Local time
Today, 10:35
Joined
Jan 23, 2006
Messages
15,363
I do not use macros. But I think you have to add a new record to your DeepCleans Table,
then use that new record Id for you subform DeepCleansID. You need the record in the 1 side of the 1 to Many.
You can add multiple Vehicles to the subform and they will all be associated with that latest DeepCleansID.

So when you open the form from your button, I think you also have to add a new record to your DeepCleans Table.
 

kobiashi

Registered User.
Local time
Today, 14:35
Joined
May 11, 2018
Messages
258
I do not use macros. But I think you have to add a new record to your DeepCleans Table,
then use that new record Id for you subform DeepCleansID. You need the record in the 1 side of the 1 to Many.
You can add multiple Vehicles to the subform and they will all be associated with that latest DeepCleansID.

So when you open the form from your button, I think you also have to add a new record to your DeepCleans Table.

yeh, thats what i have been trying to do, but i am unsure as to why its not creating a new deepclean_ID
 

kobiashi

Registered User.
Local time
Today, 14:35
Joined
May 11, 2018
Messages
258
just a quick update


i have resolved this issue, which was, it wasent saving the primary key, from the main form, this was being caused by, a field that was being auto populated, it was a Date field, because there was no input it wasnt assigning a new id to the form
 

jdraw

Super Moderator
Staff member
Local time
Today, 10:35
Joined
Jan 23, 2006
Messages
15,363
Good stuff. Glad you have it resolved.
 

Users who are viewing this thread

Top Bottom