Am I Structuring This Correctly? Optional combobox attached to the main record (1 Viewer)

[JMongi]

I have a few different bound comboboxes that have a query (just the full list table plus a conactenation field) as record source for list selection. They store the FK in the main record. Howver, not all of these are required on the main records and I enable/disable the combo boxes as needed via user selections. All of this works fine except that the disabled comboboxes still require a value. Am I doing this incorrectly? Sorry if this is a basic thing.

 

[MajP]

Sounds OK, but what do you mean that the disabled combos still need a value?

 

[JMongi]

Before I can save the main record, it wants a value to store in the field bound to the disabled combobox.

Ex: "You cannot add or change a record because a related record is required in table 'tblMotors'."

 

[The_Doc_Man]

OK, that message suggests that you have a serious misunderstanding of relationships, because that tells me you have pointed the relationship arrow to show that the record in question is a CHILD record and doesn't yet have a parent. You are enforcing relational integrity (whether you intended it or not).

Stated another way - what you are calling a "main record" is not a main record based on the way you built it.

 

[JMongi]

It's quite possible.
Here are the relationships. I've cut the other parts out as they are not currently implemented on the form.

 

[The_Doc_Man]

If I understand what I am seeing, your "arrows" are inverted. Look at your "1" and "infinity" symbols over the links. A one/many relationship in your MAIN would be that you have one pointer (in the main) that could point to many different things (for example, you have compressors among your other things.) I take that to mean that the main item can have one of many possible compressors in it. But your arrows point the opposite way and that is why you are getting the error message.

Edit your relationships so that you have all the records in MAIN and only the matching members in the Compressor table. (And of course do the same for the other related tables.)

 

[JMongi]

Ok. I changed all of the join types as you said and I still get the same error. I posted the table relationships earlier. Here is the query design relatonships:

 

[JMongi]

@The_Doc_Man - I don't doubt your expertise, but I THINK my relationships are fine the way they were.
I've checked a bunch of different posts on here and they all have the same type of relationship when they have a list table related to a main table via a FK. A 1 on the list table and an infinity on the main table.

If I pick something from each combobox, everything works fine. This is the typical way of things.

The only issue is when I try to make one of them optional. How would you (or anyone else) make an optional combobox that has a list table(query) as its source?

 

[Minty]

Assuming you can have a null value in the LeaseIDFK , that needs to be a left join and not an Inner join.
I think that is what Doc is trying to describe to you.

Your foreign key linking needs to always be a left join if it's optional. If not its effectively saying "You must have a leaseIDFK"
You would only use an Inner join where it is a mandatory field, e.g. Every Order must have a CustomerIDFK in it.

 

[MajP]

Most likely you have a zero default value in your foreign keys. This is the default property setting. Make sure to remove. Disregard most of the previous information IMO. That does not make sense.

 

[MajP]

To verify look at the table in datasheet view. Does a new record show any 0.

 

[JMongi]

@MajP - Spot On! Thanks!
@Minty - If that is the case, why does every tutorial using this type of setup (and quite a few posts from experts demonstrating this idea) not alter the default join type at all?

Just to be 100% clear. Removing the default value of 0 from the number fields that were FKs allows me to save a record whether a combobox selection has been made or not. Relationships for all FKs are intact and referential integrity is being enforced. Everything is working fine with no errors.

 

[Pat Hartman]

PS, your relationships were correct as originally posted. Please don't forget to change them.

why does every tutorial using this type of setup (and quite a few posts from experts demonstrating this idea) not alter the default join type at all?

And why does Access set the default for long integers (which are almost always FKs to 0)? Because they're both wrong.

The defaults for FKs should ALWAYS be set to null and that means that you need to do it for every single FK in every single table you define. MS has vacillated on this over the years. In some versions, they took the correct view and defaulted numbers to null and in others, they went back to defaulting to 0. A better solution, and more likely to cause the least pain for newbees, is to default long integers to Null but other numeric data types to 0.

When the FK is optional, the join type should always be Left. You can make that the default to remind yourself but when you create a query, you can make the join type whatever you want.

This example db has useful code that you can use to change all the tables in a db at once. Don't forget to make a backup first

Useful code AND the effects of using bad column names

This database has several useful code procedures that you can copy into your BE to fix up defaults you don't like: 1. Change the allow ZLS string property to No If you want to make a text field required, it makes no sense at all to have the Allow ZLS property set to Yes. 2. Turn off sub...

www.access-programmers.co.uk

 

[JMongi]

Maybe it's not working as I expect or I haven't noticed it, but, all of my joins are inner including the one optional FK and it seems to be functioning properly when I set the default to null. What is the reasoning behind making the optional FK to a LEFT join?

 

[Pat Hartman]

I guess we should qualify what is on the other side of the join. When you have a dependant hierarchical relationship such as Orders, OrderDetails; the FK in OrderDetails would never be optional since it makes no sense to have details without an order. However, the Orders table in most cases has a FK to a customer. If the Customer is optional, then when you join Orders to Customers using an inner join, you will only return Orders that have a value in CustomerID with a match in Orders.

Other types of FKs that are optional are typically for data that we may or may not have such as marital status or gender. Again, our queries that join the Person table to the MaritalStatus and Gender tables using inner joins, will not return any rows when those fields are empty. If you want to return a Person regardless of whether or not these lookup values are populated, you need to use a Left join.

 

[JMongi]

Thanks @Pat Hartman! That makes sense. Now for the follow-up. Apologies in advance because this is probably form building 101...

You've seen the table structure I posted in post #5. That is a portion of the database relationship view.

When creating the query that will serve as the recordsource for my bound data entry form for tblFSR, is it best practice to have the lookup tables included in the query design?

As you can see in post #7, my current form recordsource is a qryFSR. Currently this consists of tblFSR and qryLeases. qryLeases combines some of the data I want to display from the tblLeases and its support tables. It too is a listsource for a combobox. However, since I needed to display some of those auxiliary fields from other tables, I included it in qryFSR. I have not included any of the other lookup tables in the qryFSR. I used the combobox wizard to create all of the comboboxes currently on the form; selecting the listsource (queries based on the lookup tables for some concatenation to display) and selecting the FK field from tblFSR to store the value in.

Please let me know if there is a "best practice" or a different way to go about this. I don't want to run into any gotchas further down the road that could be nipped in the bud here.

Thanks again to all who posted.

 

[Pat Hartman]

If I have combos, on the form I don't join to the lookups unless I need to display other data from the table. When I display other data from the lookup tables, I always lock the controls to avoid accidental updates. For reports, I use joins since I don't use combos on reports.

An alternate method for forms to display other data is to include it in the rowsource of the combo and then populate unbound controls in the AfterUpdate event of the combo and the Current event of the form. But the query with the join is the no code method. It just takes the conscious effort to lock certain controls.

I use the RowSource method in the rare occasions I need to copy data from the lookup table. The most common situation is for prices. Prices are a function of time so they get copied into order details when the order is placed. This is for two reasons.
1. you want the standard price at the time of the order and this is the easiest method.
2. most applications need the ability to override or discount prices so having just the lookup value doesn't work.

 

[JMongi]

If I have combos, on the form I don't join to the lookups unless I need to display other data from the table. When I display other data from the lookup tables, I always lock the controls to avoid accidental updates. For reports, I use joins since I don't use combos on reports.

Thanks for the explanation. I feel better that this is what I was thinking/understanding.
Looking back I had the basics idea correct. It was just the default = 0 thing that was tripping me up. Thinking about it now, I think that the 0/null issue may have been the root cause of some of my other issues in another app that was put on the back burner. I'll have to check and see if that's the case.

 

[Pat Hartman]

Take a look at the db I posted with the clean up code

 

[JMongi]

Forgot about that, I'll check it out