Why is my subform not responding to a change in my main form?

[CharissaBelle]

I have a form with a subform and I can't see why the subform doesn't update when I change the Trip Number in the main form. The file is too big to upload here, so I put it in my dropbox. See link below. Any help would be greatly appreciated. Thank you.



https://dl.dropboxusercontent.com/u/...(upload).accdb

[ranman256]

is the subform linked to the master form via subform properties:
link master fields: ID
link child fields: ID

[CharissaBelle]

They weren't, but I've fixed that now and it's still not updating.

Here's the link to the updated file in dropbox.

https://www.dropbox.com/s/chvbsi6hbo...%29.accdb?dl=0

[CharissaBelle]

Okay, I've been playing a little with the form and I don't know why I had two different queries. I've changed it to where the main form and subform are based on the same query. Also, I've added a field for TripID on both the main form and the subform so I can see what is happening. I have a combobox on the main form for LBCTripNumber. When I choose a different trip number from the combobox, the mainform updates appropriately. The subform does nothing. The Master Field is TripID and the Child Field is TripID. I have also tried making the Child Field the FK, Trip_ID, and the same thing happens. I'm stumped.

[orange]

Did you try to do a compact and repair, then zip your database?
People don't often go to an outside link to retrieve data.

[davegri]

There are only 4 trips, right? The main form, TRIPS, query source should only show 4 records. It's showing 22.

[Micron]

#1 don't type in the name of the linked fields - use the property sheet buttons. Your main form has no such field TripID. It is Trip_ID.
#2 you cannot have a parent/child link between a form and sub form if both forms don't contain at least one control that references the same field. You don't.
#3 a compact/repair as suggested, was necessary to get it to a point where it would even allow me to attempt to create the link
#4 your subform query provides no filter/criteria at all - i.e. it returns all records. If you want subform records to reflect main form control(s) content, you have to make that part of the query: Forms!frmMain.Mycombo

I don't see the sense in some of the things you've done:
- your main form query returns 22 fields but your form only uses 6
- the subform is the same way
- you have combos in the subform? As soon as you fix the issues with the parent/child setup, you won't be able to use them to add records since there will be no value in the related child field for the new record. You will need vba to set the child field value to be equal to the parent of the main form (likely on the combo AfterUpdate event).
- having main form record navigation buttons if you want to present subform records based on main form control selections.
Sorry if I've misunderstood your goal.

[CharissaBelle]

Oh dear, I feel like I may be in over my head... I'm going to mark this as solved and see if I can use your suggestions and start from scratch on this form.

Thank you all very much.

[CharissaBelle]

Testing 06.28.16 (upload).zip

Thank you orange. I have now been able to attach a zip of my current db with changes.

#4 your subform query provides no filter/criteria at all - i.e. it returns all records. If you want subform records to reflect main form control(s) content, you have to make that part of the query: Forms!frmMain.Mycombo

I realize this would go in the criteria section of my query under TripID, but I can't quite find the right syntax. Could you help me with that?

I don't see the sense in some of the things you've done:
- your main form query returns 22 fields but your form only uses 6
- the subform is the same way
- you have combos in the subform? As soon as you fix the issues with the parent/child setup, you won't be able to use them to add records since there will be no value in the related child field for the new record. You will need vba to set the child field value to be equal to the parent of the main form (likely on the combo AfterUpdate event).
- having main form record navigation buttons if you want to present subform records based on main form control selections.
Sorry if I've misunderstood your goal.

I've fixed the top two. As for the combos in my subform, I am looking for a way to input when new trips are added. There are two different numbering systems for the trips. Postal trip numbers may be repeated by different contracts, so our company (LBC) had to come up with our own numbering system. However, we need to be able to look it up by either our number or the postal contract/number combination depending on who we are talking to. I figured the best way to do this was to break the trips into segments. Each segment belongs to a postal contract/trip number combination and an LBC trip number. My main form contains fields that apply to all the segments. I figured it would be easy to just input all the trip segments and apply them to the trip record from the main form.

Am I over complicating this?

[orange]

Do you have a clear description of what this database is suppose to support? That is a clear, simple description about the "business".
You have to get the tables and relationships set up to match/support your business. Relationships are NOT arbitrary lines between proposed tables.
Relationships are determined by your business rules.

I opened your database and reconfigured your tables and relationships. I also made them type 1 vs type 3 via the relationships editor.
Since I don't know the business, I can't comment on the relationship diagram any further.

[CharissaBelle]

I opened your database and reconfigured your tables and relationships. I also made them type 1 vs type 3 via the relationships editor. Since I don't know the business, I can't comment on the relationship diagram any further.

Thank you! I didn't know type 1 was better than type 3. I'll go through and make those changes!

Do you have a clear description of what this database is suppose to support? That is a clear, simple description about the "business". You have to get the tables and relationships set up to match/support your business. Relationships are NOT arbitrary lines between proposed tables. Relationships are determined by your business rules.

Hmmm... Clear and simple... I don't know if I can do that. I've tried and everything is just super complicated. Take this wonderful example of what should be a simple trip table and form. Instead, I've basically used the Segment table as a junction table between LBCTrip, Contract, PostOffice, and POTrip. All 4 of those tables have many to many relationships. For instance a single LBC Trip can span over multiple PO Trips and multiple Post Offices. Each Post Office will be a part of multiple Contracts, LBC Trips, and PO Trips. Each PO Trip number could be on multiple Contracts, go to multiple Post Offices, and span over multiple LBC Trips. I finally broke the trips down into their smallest part, a segment.

I've gone through and made the changes and even removed some arbitrary stuff. This is just a small portion of my much larger db that I'm posting to try to help with the main form/subform problem I'm having.

I'm wanting a form where someone can easily enter a new trip and its multiple segments. There might be a dozen segments that all share some of the same field values. I would love to have a form where someone can enter in some basic information and then enter in these dozen segments. Is there a better way to do this other than using a subform?

Updated File:
Testing 06.28.16 (upload).zip

[ssanfu]

I looked at your dB and saw several things that were causing you problems.

The queries for the main form and sub form were wrong.
The main form query should only have the table that for the main form.
The sub form should only have the table that is for the subform.
In the main form, the main form and the sub form were both linked on the main form PK field.

In moving things around, it looked to me that the contract field in the table "Segment" should be in the table "LBCTrip" (I did not move this field).

I renamed the primary key (PK) and foreign key (FK) fields to make it easier to know which is which.

I modified the queries "qryTrip" and "qryTripSub". I was then able to correct link the main form and sub form.
I created a new form to enter "LBCTrips".

For printing, you should create a report/subreport and not try to print the "Trips" form.

[orange]

Perhaps Steve (ssanfu) has guessed correctly. I'd still like you to try for a clear, simple English description of your business.

This is not simple English to me.

Hmmm... Clear and simple... I don't know if I can do that. I've tried and everything is just super complicated. Take this wonderful example of what should be a simple trip table and form. Instead, I've basically used the Segment table as a junction table between LBCTrip, Contract, PostOffice, and POTrip. All 4 of those tables have many to many relationships. For instance a single LBC Trip can span over multiple PO Trips and multiple Post Offices. Each Post Office will be a part of multiple Contracts, LBC Trips, and PO Trips. Each PO Trip number could be on multiple Contracts, go to multiple Post Offices, and span over multiple LBC Trips. I finally broke the trips down into their smallest part, a segment.

These are a few of the questions that pop out of that quote to me:

What is a segment?
What is a contract? Working on the general description that a Contract is some legal obligation between parties where one party agrees to do some service or work for another party. The terms and conditions are spelled out and agreed to by the parties.
What is an LBCTrip?

Junction table and form may be appropriate, but not yet.

What exactly is the business --in terms you would use to describe what your company does - to someone:
-who doesn't know you
-who has no knowledge of database or Access
-who doesn't know your environment or skill set
-who has never heard of segment, LBCTrip, Contract but asks you what they are

A clear statement of requirements will help you design your database, and will help readers understand your "task" and provide for clearer communications among all.

Good luck.

[CharissaBelle]

Perhaps Steve (ssanfu) has guessed correctly. I'd still like you to try for a clear, simple English description of your business.

This is not simple English to me.


These are a few of the questions that pop out of that quote to me:

What is a segment?
What is a contract? Working on the general description that a Contract is some legal obligation between parties where one party agrees to do some service or work for another party. The terms and conditions are spelled out and agreed to by the parties.
What is an LBCTrip?

Junction table and form may be appropriate, but not yet.

What exactly is the business --in terms you would use to describe what your company does - to someone:
-who doesn't know you
-who has no knowledge of database or Access
-who doesn't know your environment or skill set
-who has never heard of segment, LBCTrip, Contract but asks you what they are

A clear statement of requirements will help you design your database, and will help readers understand your "task" and provide for clearer communications among all.

Good luck.

I have a 4 page narrative that I don't think anyone would really want to read... Just the fact that it's 4 pages tells me it's over complicated. I've been trying to knock it down to 1 page, but I'm not much of a writer so it is taking a lot of time. We contract with the USPS (which is a government agency) which means everything is confusing. I think I'll just try to answer your questions.

-A segment is a stop on a trip (a trip is both a postal trip and a LBC trip, but a postal trip is a different thing than a LBC trip). Like a trip loads somewhere, leaves that place, arrives at another, leaves there and arrives somewhere else, etc. Each of those has a specific time it is supposed to take place. A segment has a postal trip number, LBC trip number, contract it belongs to, a time, post office, a door at the post office, and a time zone (since we operate in multiple time zones).

-A contract is a set of 5 letters/numbers. It is an identifier the USPS uses for the trips we as a contractor are running. To try to use your language, we are a party who has agreed to run these trips for the USPS. Each contract is made up of different postal trip numbers. Each postal trip number goes to multiple post offices at different times. A postal trip number could be used on multiple contracts. A postal trip could start out of two different cities (and different time zones) at a similar time and meet in the middle

-An LBC trip is our company's trip identifier. It is a 3 digit number used to identify the trip our driver will take. We use it for everything related to our business such as schedule and payroll. A LBC trip is made up of segments and also has A single LBC trip could span over post offices and postal trip numbers. This is the reason I broke up the postal trips into segments so that a segment could be part of a postal trip/contract combination and a LBC trip. Eventually, I will need to be able to quickly search for something like postal trip 5 on contract 97015 and see all the segments associated with it.

[orange]

So you have some vehicles(trucks??) that you load(with ??) and it ( including the driver)

leaves that place, arrives at another, leaves there and arrives somewhere else

Why exactly does it arrive and stop then leave these other places? I think that has a lot to do with your business.

Each of those has a specific time it is supposed to take place.

This sounds like a schedule -- some truckers call this a Route with waypoints.

From Wikipedia:

In GPS navigation, a "route" is usually defined as a series of two or more waypoints. To follow such a route, the GPS user navigates to the nearest waypoint, then to the next one in turn until the destination is reached.

A segment has a postal trip number

??

A segment is a stop on a trip

So every stop or (waypoint) is a segment?

a trip is both a postal trip and a LBC trip, but a postal trip is a different thing than a LBC trip

What is it that makes a postal trip different than an LBCTrip?

A contract is a set of 5 letters/numbers

Sorry, but this is not true!!! This may be how you and your colleagues identify/represent a contract, but your legal department/advisor will assure you that this is not a contract.

Please give us an example with details.

Perhaps it is just me, but my view is that you do not yet have the details to design a database. And, if you can't describe it clearly, NOBODY can build it.