Make my form behave!

[SEEC]

I have a form that pulls from two tables, but I want it to show records if there is data in either table, while as of right now it is only showing if data is in both. How do I change this?

 

[boblarson]

What is the structure of your tables?

 

[SEEC]

Actually, now that you ask me that, I might be able to expand my question a bit more.

I have one table called Contact Info that has client name, social, address, phone, referral date, and a unique number we assign.

The other table on the form has information on a service we provide, and it shares the fields client name and social. That service is provided by another division, and the admin in that division wants to enter data on a form.

I tried relating the tables by those two fields, hoping putting one of the tables' client name and social fields in the form would auto fill the other table, the same as when we fill in the data sheet on the service we provide, it auto fills on the related fields in the Contact Info table.

No such luck.

The tables are still related by those fields, and as of right now, I have both client name and both social fields in the form because I couldn't figure out how to get it to carry over to both tables.

Otherwise, I'm not sure there's anything remarkable about their structure. They have no combo boxes or lookups.

Does that give you enough information?

That Contact Info table will have stuff in it, even if that other division hasn't provided a service yet. So I want that to come up when she goes to enter data on someone, since people come into her division after leaving mine. She shouldn't start a new record, but add to an existing one.

Can I do that?

 

[boblarson]

From what I've seen, you should ONLY have the Contacts table. The other one is redundant. So the form should be based on ONLY the contacts table. If you want to get the existing data from the other into the Contacts table then you would need to use an APPEND query to append contacts that do not exist in the contacts table but do in the other table (if any) and an UPDATE query to update the information from the other table into the appropriate record of the contacts table. But, what if you have two John Smiths? Which one gets the data in the social field in the second table?

The design of this is a bit difficult and might need some manual updating.

Once you have that down then you can just use the form for them to update the records and for you to add the contacts.

Does that help?

 

[SEEC]

I'm not really sure I understand. We definitely need all the tables we have.

Let me make sure I've been clear about what I'm trying to accomplish.

We have several divisions that provide different services for clients.

Each client has information in the Contact Info table.

Then each division keeps a table of details on services provided to each client, and those tables can be extensive and complex.

One of the divisions likes to enter her data into a form instead of into a table. I was trying to create a form that would automatically have all the contact info ready for her (since we will already have entered it). But what's happening is that even though someone is in the Contact Info table, they're not showing up on the form until they also have the data entered for the other service.

Is that any clearer?

Otherwise, I'm not really sure I understand your instructions.

 

[boblarson]

Then each division keeps a table of details on services provided to each client, and those tables can be extensive and complex.

Bad database design. Each division should not have a table that duplicates things, but I'm sure you can't do anything about that at this point.

One of the divisions likes to enter her data into a form instead of into a table. I was trying to create a form that would automatically have all the contact info ready for her (since we will already have entered it). But what's happening is that even though someone is in the Contact Info table, they're not showing up on the form until they also have the data entered for the other service.

Is that any clearer?

Clear - and essentially what you REALLY need is to have the contact info table be the main form and then the other table be a SUBFORM with a link to the unique contact ID. That way you can select the record you want and then if a record exists in the other table it will show up and if not you can add one.

 

[SEEC]

I think the only thing duplicated in any of the tables is the client's name. Is that a problem? and maybe social security number

 

[SEEC]

I am totally willing to do what I should to fix it--what should I do?

 

[boblarson]

I am totally willing to do what I should to fix it--what should I do?

As I mentioned above in my last post -

Make a main form using the contacts table.

Then, make a subform by dragging the other table onto the main form. If you have a shared ID field that it can use it will connect the two automatically.

 

[SEEC]

No, I meant about the bad database design with the repeating information in tables.

 

[wilpeter]

Just a question to this poster: In your original Relationship, did you set the join type to either 2 or 3 so that All records from Contacts table and just those that exist in the supporting table appear? Alternatively, did you try creating a query from those tables and set the Join Type there...and base the form on the query?

 

[missinglinq]

One of the divisions likes to enter her data into a form instead of into a table.

You really shouldn't be doing this because one user "likes to enter her data into a form instead of into a table!" You should be doing it, for all divisions, because end users should never enter data directly into a table!