All objects disappearing from Form - Think I know why, but not how to fix.

[IncidentalProgrammer]

So I'm having that issue where when there are no records to display, everything vanishes from form view. I've tried creating dummy records, but nothing is working. I'm afraid the issue may have something to do with how the tables are structured. I needed to figure out how to connect these particular fields on the form, anyhow.



In this database, we're tracking info on several different types of entities--insurance companies, agents, clients, accounts, etc. So I made one underlying table for all data that they would need in common (such as address, name, phone number, etc) that I call "Entities", and then each has their own table for what data types they have that would be unique. The problem is that some forms require info on multiple entities. This particular form needs 3: Account, Carrier (insurance company), and Agent/Producer.

On the "Policy" table, I have to connect each policy to both the appropriate Account and Carrier, so I created separate Entity ID fields for those to link them to the appropriate tables. However, the Entity Name fields are all drawing from the same Entity table. How can I relate them to the correct ID field so that they pull the appropriate entity, and could that potentially solve my "no record" problem?

[John_G]

It sounds like you may have an issue with the structure of the database.

What is the "Entity" table, and what is it used for, and what is the Entity Name field?

It's not clear if you are doing so, but if you are using a field to identify which table to relate to (Entity Name?), then don't - that doesn't work.

[IncidentalProgrammer]

That's what I'm terrified of.

The Entity table contains fields for the data types that all entities have in common. We have Agents, Companies, Clients, Accounts, etc, and they all have to have an ID, Name, Address, etc, so I made the Entity table to house that kind of data, and then each has their own table with the data types that are unique to them (for example, commissions, territories, etc). Doing it this way simplified a lot of other things, but it's made forms referencing mutiple entities a little complicated. I think I've figured out a way around it though, using subforms for each entity type on this form instead of the little outlined boxes I was trying to before.

No, I was trying to use the Entity ID's and have them populate the appropriate Entity name for their section. After another couple of coffees, I decided this was a bad idea, and started a second attempt form using subforms instead. I'm still working on it, but I think it may work better than what I was trying to do. I just hate using subforms because of the little bar at the bottom (you know, the blue search bar). Is there any way to hide that bar? Because in this case, I don't want them searchable; they need to stay as-is, based on the policy being displayed.

[John_G]

I'd suggest you get rid of the Entity table - it's wrong to have it (IMO). Yes, the Agents, Companies, etc. all have ID's, name, address and so on, but other than looking the same, and having similar field names, they are not 'common' data. There is nothing wrong with having address (etc.) data in several tables for different entities.

Now, for the Policy table and form for example, you can use combo boxes to select the appropriate other entities, if any, and keep only the ID values in the Policy table.

That will help with the blank forms problem.