Auto Populate Fields & New Entry

[Jarede]

Hi,

Need help figuring this out. I have a projects table and a contacts table. The projects table has a one to infinity relationship to the contacts.
I have a subform in the project form where you can add contacts to that specific project. I have this setup to auto populate the contact fields based on entering the contact name.



What I'm trying to achieve is if a contact does't currently exist, this subform would also allow you to enter the information without having to go back to the contacts form and enter it.
Currently it will not allow you to do this, i can only get it to pull existing contacts. I don't have the in force integrity checked in the relationship.

Thanks!

[always404]

The first thing I would check is that the "Allow Additions" property in the property sheet is set to "Yes"

Secondly, what is the info autopopulating to? Is this a continuous form? a Single form? a Subform with a datasheet?
Where would you want to input the information for a new entry? Knowing the UI setup would be helpful

[Jarede]

I will try your first suggestion. Secondly it's a sub from data sheet imbedded in the project form. The new information would be input in this subform.

The goal is if someone's information had previously been entered on another project subform it would be able to easily pull into the new project and associated with the new projectID by typing a name, then the previous email, phone, ext would populate from the previous records. I have tried this with the combo box method and with a query and can't get either to let me enter new customers in the subform. I'm sure this is something fairly easy I'm overlooking. Could be your allow additions.

[always404]

Actually from your description, I'm pretty sure the one to many relationship isn't going to work out.
There might be a fundamental design flaw by using that approach/

If you have individual projects that have their own contacts (one project to many contacts), then separate projects wont be able to see each others contacts.

This screenshot should illustrate what i mean: http://i.imgur.com/yzEskBd.png
Anytime a contact is created, it can only be assigned to one project (foreign key can only be assigned one value, which is the ID of the project)

You could either:

1) have one contact to many projects
because the contacts table doesn't necessarily need to know what projects other people have

or

2)have many to many relationship
this is more complex, see here: http://ms-access-tips.blogspot.com/2...ationship.html
A third table comes into play...

Either way, you need to do some redesign if you want the form to work the way you described.
There might be a messy work around, by basing the subform on queries or having some crazy conditionals in VBA, but it would be faster to rework the relationships.

[Jarede]

I think the Many to Many is the key. Once this is setup do i use a query with all three tables to allow the subform to enter the data that doesn't already exist
in the contacts table, or the "tblProducts" from the tutorial link?

[always404]

yeah, that would work
I've uploaded a sample database that does as you describe

[Jarede]

Thanks for your efforts that is perfect. I will try to change the contact Id combo box to sort by name so I can type the name to search if its existing