Add new Data to multiple tables from subform

[nstratton]

I have searched and searched for an answer to this but seem to cannot come up with anything that will work.

I have an unbound form with a combobox that is used as criteria to display the contact information for a given supplier. The results are displayed in a continuous subform. The subform is based on a query from multiple tables. I can get the relevant information to show up on combobox selection. What I would like to do is be able to add another contact to the supplier selected. When I go to add I receive an error of join key not in recordset.

My understanding is since some of the relationships I created are not linked PK to PK it will not let me add records. I've seen a solution that requires making multiple subforms but that seems unnecessary especially since I want to be able to add to a supplier already selected. I have posted the SQL below.
I would like to note I am not very experienced with SQL but have grown to understand VBA quite well.

This is a first post to the site but have used solutions found here for other things and they have always worked perfectly. Thank you in advance for any help provided.

SELECT tblSuppliers.Supplier, 
tblSupplierCodes.SupplierCode, 
tblContactType.Type, 
tblContactInfo.[Contact Name], tblContactInfo.[Contact Email]

FROM (tblSuppliers INNER JOIN tblSupplierCodes ON 
tblSuppliers.ID = tblSupplierCodes.[Supplier ID]) INNER JOIN (tblContactType INNER JOIN 
tblContactInfo ON tblContactType.TypeID = tblContactInfo.TypeID) ON 
tblSuppliers.ID = tblContactInfo.SupplierID

WHERE (((tblSuppliers.Supplier)=[Forms]![frmContactMenu]![cboSupplierContactSelect])) OR 
((([Forms]![frmContactMenu]![cboSupplierContactSelect]) Is Null));

 

[JHB]

Then include the missing key field/fields in the query result, use a hidden control/controls on the form to hold the data for it.

 

[nstratton]

Wow, that simple. Should've figured it would be that easy. Thanks.
A different question but along the same lines, I've got five fields being shown (supplier, supplier code, contact type, contact name, contact email), if I were to add the information to the last three, would the first two be populated automatically? The combobox is already filtering to show one specific supplier and instead of having to type in the information for the supplier again I would just it to already be associated with the new contact, if that makes sense.

 

[JHB]

Normally you use subforms for that, but you can set the default values by code, for the fields.

 

[nstratton]

So taking away the automated part, I have added all the primary keys from all the tables in the query to the form being edited and hid them, added them to the query and it still is not working like it is supposed to. I tried added directly from the query and when I type in the name of an existing supplier, it tries to add it as a completely new supplier. Same thing happens in the other fields as well.

The end user would like the ability to add directly to the subform instead of having a separate form for data entries.

 

[JHB]

Post a stripped down version of your database with some sample data, (zip it because you haven't post 10 post jet) + the name of the form in which you've problem.

 

[nstratton]

I have added a new zip file with pictures of the relationships I have in the database as well as the error that is being generated.

The problem forms are frmContactMenu & frmContactDisplay
The problem query is qryContacts

I want to add records via the subform since the main form is unbound. I believe I have added every key field imaginable to qryContacts, which is the qry the subform is based on. I am getting considerable pressure to get this whole project done by the end of the week and this part is holding me back and driving me crazy. :banghead:

If need be I will start a new thread but this is highly important since the end user does not want to use a separate subform to add new contacts to existing supplier.

 

[JHB]

I have added a new zip file with pictures of the relationships I have in the database as well as the error that is being generated.
...

I'll have a look at it in the weekend, just now I've some other urgent tasks.

 

[JHB]

Sorry - but like it is setup here, you're not able to do what you want.
For that type of task sub- and main form normally is used or a popup form where new contacts are added.
I've looked at your table design and where you have one to one relationship you can actually just as well have it all in one table. This will among others do your queries simpler.
You only need to create tables if it has a purpose and it has none in this case.

 

[nstratton]

I had started to figure the current setup would not allow for additions but it was worth asking anyway to see if I had missed anything.

That's interesting about the tables because I had asked a question somewhere else and that was the response I received. Basically was told to split everything into it is own separate table. Said it was part of the process of normalizing the data.

http://stackoverflow.com/questions/32098889/show-all-records-for-given-field-filter-in-access-2010-table

If starting to combine some of that information into one table will make things easier I will definitely go back and fix that.

 

[nstratton]

So let me ask this then, based on what is seen in the database I posted earlier, would I be able to change the supplier associated with a given contact? I can change the name, phone and email no problems but say if someone in the QC department is now responsible for a different supplier I would need to be able to reflect that change. I'd want the change to be by a combobox so all the available suppliers will be listed.

I would hate to create a new subform just to change the supplier for a given contact but if that's what it will take...