Navigation Form only generating subforms

[bhorwath]

I'm trying to create a database that tracks RVs on private property. I have three tables with basic relationships set.





The problem becomes when a vehicle is added without any owner information. If I know the Owner information, I know the Vehicle information. But I still need to track the vehicles on property even if I'm unable to contact the owner.

Using a Navigation Form with tabs for all three forms seems to only allow for subforms to add information. So, when a vehicle is added I get the following error:



I have to believe there a way to setup a navigation form where one tab is the form and the other tabs are the subforms. I've tried scouring the internet for similar issues for the last two days and haven't found any similar issues.

I'm hoping someone here might have an answer.

I appreciate any assistance.

[Micron]

Your problem may be that a navigation form only loads one form at a time. When you open formB, formA closes. If you already knew that then I guess it's not your issue. Typical design would be main form (in this case I'd say for vehicle) and main form subform for related records. Have you designed it so that Owner is the parent record? Then you cannot have child records without a parent. Either reconsider which is which, or ensure there is a parent record, or remove the relationship.

EDIT - it looks like you're using lookup fields too.
The evils of lookup fields - http://access.mvps.org/access/lookupfields.htm

[bhorwath]

Thank you for the response. It appears that the problem is that a navigation form only loads one form at a time, I was not aware of that. The parent is the Owner table since it is common for one owner to have multiple vehicles. Each vehicle could have multiple visits on property so it's a one-to-many relationship. I need the child record created with a blank parent that can later be completed when the information is available. Otherwise, how will it be able to track anything? I thought having the vehicle record created without enforcing the integrity of the parent relationship would allow this.

It is starting to sound like Access can't do this most basic thing...

[Micron]

It is starting to sound like Access can't do this most basic thing

No database should. It violates the very notion of a relational database wrt parent/child records.

If I know the Owner information, I know the Vehicle information

But do you know the owner info if you know the vehicle info? I'm guessing yes, so...

Your child table (vehicle) needs a related field to link to its parent record, yes? Then put whatever you know into the parent record. That will create a parent record id that you put into the child record, which creates a child record id (assuming you're using autonumber fields as id fields). However, if you make the owner name (or whatever) a required field then you either need to enter dummy data (TBD) or don't make it a required field. As long as your vehicle form shows the owner id, you have a way to know which owner belongs to the child record you create. It should not be an issue to update the parent record when you get the info you need. However, I do accept that id fields are usually not shown on forms; there has to be something that points you to the owner info that you have. If you use a form/subform design as suggested, your main form owner is TBD. Just don't allow duplicates of owner name I guess. If need be, make the owner info a composite index so that you can have two different John Smiths as owners.

[bhorwath]

Thank you Micron. I really appreciate the insight. I will read the link regarding lookup fields when I get a moment.

To clarify though, I'm more likely to know the vehicle info than the owner. It's not uncommon where a vehicle is observed on property but no contact is made with the owner of the vehicle. The presence of the vehicle still requires notation in the database.

My point is, that this simple event should be common and easily addressed. Where unavoidable circumstances dictate that a child table is filled without any parent information. How about creating a blank entry and just let me edit it later? If it matches an existing entry just eliminate the duplicate and reassign the key to the child to match the existing entry? It does sound pretty obvious, but...

I feel like I have two options...
1) Add a field to the parent owner table for the license plate of the vehicle observed, which would create a PK for the rest of the info, or...
2) Use an indexed (no duplicates) field on the Vehicles table and that becomes a second PK for the vehicle table instead to create the owner information later. But, this may present user challenges if entering owner information first.

I will probably go with the first option as that is least likely to run into identical recurrences.

Just seems weird that there is no programmed setting to workaround this common issue. My first project in access with just three basic tables can't even launch off the drawing board without having to reinvent the wheel. Seems odd to me.

[davegri]

See if this will work for you. Main form is vehicle with a tab control. Tab control has 2 tabs, one for owner and the other for stay. You can enter vehicle and leave the tabs blank until you have data for them.
Added lookup table for vehicle type and combo on form to choose.
Note that this is not a navigation form. I don't use those - prefer tab control for the subforms.

horwath-davegri-v01.zip

[bhorwath]

Thank you for the information and examples Davegri. Unfortunately, unless I totally misunderstand, this will not allow for one owner entry to link to multiple vehicles? That becomes a problem. The current system allows for that and it would be necessary in this database. The current system does not allow for tracking current vehicles on property, their length of stay, and the days between departure and subsequent arrivals, which is the goal of this project.

I have been playing with tab forms and that does seem to be what will be required to get this up and working. I've changed the Owner table to require only a license plate but currently having a challenge getting the data from the parent table to the field in the child table for the vehicle plate.

Eh, one thing at a time.

[madpiet]

If a vehicle can only be owned by one person then you'd put the OwnerID column in the Vehicle table and then create a relationship back to the Person table. if you have vehicles that have no known owner, you could create a dummy Person record and link all the "unknown owner" vehicles to that record. Not perfect, but it may be your only option. I wouldn't put LicensePlate into the Owner table, because what do you do if one person owns multiple vehicles? You can retrieve any/all license plates of an owner by following the relationship to Vehicle... like this:

SELECT p.FirstName, p.LastName, v.PlateNumber, v.Make, v.Model
FROM person p INNER JOIN vehicle v ON p.PersonID = v.OwnerID

[davegri]

Thank you for the information and examples Davegri. Unfortunately, unless I totally misunderstand, this will not allow for one owner entry to link to multiple vehicles?

Nothing stops you from entering the same name as owner of more than one vehicle. In order to display owner/vehicle for reporting, you would use a query:




In order to uniquely identify the owners, you would need more than just the name. The driver's license would work in a combobox. If it already exists, the combo after update could fill in the first, last name, state etc. If the driver's license doesn't exist, a new owner record needs be created with the new DL no, name, and so on.

[madpiet]

Vehicle_FK doesn't belong in the Owners table. The only time it would belong there is if (1) you didn't care about other vehicle information and (2) each Owner could own at most one vehicle.

[davegri]

Vehicle_FK doesn't belong in the Owners table. The only time it would belong there is if (1) you didn't care about other vehicle information and (2) each Owner could own at most one vehicle.

No problem with my schema. Owner with 2 vehicles gets 2 records in owner table.

[bhorwath]

If a vehicle can only be owned by one person then you'd put the OwnerID column in the Vehicle table and then create a relationship back to the Person table. if you have vehicles that have no known owner, you could create a dummy Person record and link all the "unknown owner" vehicles to that record. Not perfect, but it may be your only option.

Yes, I agree. But, as you also mentioned the problem with using the vehicle plate in the owner information presents a problem with multiple vehicles. May seem like an isolated issue but a quick review within the current system identified that over 30% of visitors own more than one, and that's just from what is documented with contacts.

So, I realize that won't work. I also realize that I may need to use a bridging table to create a many-to-many relationship with more than one owner to more than one vehicle. Ugh but that seems like a headache. I'm not an advance user in Access but I realize the users of this database are not very savvy with tech so this needs to be an intuitive process to be adopted and successful.

I regret to say that the text provided at the end of your post went well over my head. It might as well by ancient sanskrit. My apologies but I do greatly appreciate your suggestions. It has given me considerable insight towards progress. Thank you.

[bhorwath]

No problem with my schema. Owner with 2 vehicles gets 2 records in owner table.

This would be incredibly problematic. Many owners that are subject to restrictions, exclusions, or subject to ongoing investigations for criminal conduct would potentially be overlooked because of multiple entries for duplicate information. The relevant information would need to be duplicated possibly more than once for each contact which would complicate the whole system a be a potentially huge liability issue.

Multiple owner entries is just not going to be approved for use.

Thank you though for the suggestion. The ideas you presented have given me pause to consider suitable alternatives.

[madpiet]

Person---(1,M)--owns--(M,1)--Vehicle would mean that (1) One person can own more than one vehicle, and that (2) each vehicle can be owned by more than one person. (Might make notifications a pain, though).

Why not post all your business rules (maybe an entity or two at a time) and see if people here can help you design something that will cater to that?

[Minty]

Surely the answer here is a junction table to avoid the dependant join either way?

tblVehicles, tblOwners , tblOwnersVehicles

That way neither is reliant on the other to exist but can be joined in the desired way?

Edit: Just realised that is what madpiet is suggesting... I need more coffee