Form auto-fill and foreign key

[BrankoG]

Hi,

I learned a lot trying to resolve this one, but unfortunately I am still missing something.

I am modifying Contact Management Database template and using Northwind 2007 as a guide/reference. I have added a couple more tables (relevant here is Companies table), did relations, keys, etc. While new to Access, I do have some database experience. I have modified Contacts Extended query to include relevant tables (one right and one left join). Also, I have modified Contact Details form to include new page (or tab) with company information for the contact. Since it is expected to be more than one contact from single company, I have used combo box to allow users to select existing company from the combo box and all other fields will be auto-filled. If entry is not in the list, new form opens and company details can be added.

My problem is that when I select existing company from combo box, fields get filled, other fields manually filled, etc. - and when I try to save, I get a new company entry instead of linking to existing record.

I set the field not to allow duplicates - that helps with consistency, but is not solving the problem as I get an error report stating that no duplicates are allowed. I tried to go around the problem by creating a hidden text box, linked to the foreign key and trying to set the value there (in this case, that is Contacts.CompanyID), but I got the runtime error 3341.

Checking further on this, I realised that query that I am using is creating a new Company record for each form start and CompanyID is automatically assigned next increment value that cannot be changed.

My question is - is it the problem with the query and I need to modify it (or somehow use a different one) or it could be done in some other way?

Query is:
SELECT IIf(IsNull([Last Name]),IIf(IsNull([First Name]),[E-mail Address],[First Name]),IIf(IsNull([First Name]),[Last Name],[Last Name] & ", " & [First Name])) AS [File As], IIf(IsNull([Last Name]),IIf(IsNull([First Name]),[E-mail Address],[First Name]),IIf(IsNull([First Name]),[Last Name],[First Name] & " " & [Last Name])) AS [Contact Name], Contacts.*, Companies.*, Owners.*
FROM Owners RIGHT JOIN (Contacts LEFT JOIN Companies ON Companies.CompanyID=Contacts.CompanyID) ON Owners.OwnerID=Contacts.[Owner 1]
ORDER BY IIf(IsNull([Last Name]),IIf(IsNull([First Name]),[E-mail Address],[First Name]),IIf(IsNull([First Name]),[Last Name],[Last Name] & ", " & [First Name])), IIf(IsNull([Last Name]),IIf(IsNull([First Name]),[E-mail Address],[First Name]),IIf(IsNull([First Name]),[Last Name],[First Name] & " " & [Last Name]));

Thanks and regards,
BrankoG

 

[BrankoG]

Since there were no replies, and I have managed to solve the issue, here is what I have concluded and done. Maybe someone will find it helpful. Also, if somebody has any insight or comment, please do reply.

As I have realised, the problem was not in the query, but in the Combo box. Which I find strange, as it is widely used for auto-fill and I have seen it recommended everywhere. I am probably missing something, but this is what happened in my case - when using Combo box and code very similar to that of Northwind (Order Details form, Customer ID combo box), I manage to auto-fill in the desired fields, but Company ID is always set to the new auto-increment value in Companies table and cannot be changed to a desired value. It is happening as soon as selection is made in combo box (my guess is that as soon as any field related to Companies table/record is set, new record is created and linked). Since, of course, I am not tolerating double-entries, I had to find a way to update Company ID before anything else is done, related to Company record.

So, what I did is transforming my Combo box into a text field and adding a small search button before it. Also, I have added Contacts.CompanyID field to the form, but hidden. Button is launching a small form that only has a combo box, without any code, just linked to appropriate fields and table. Two buttons on that form - close and select. Select just writes the CompanyID to the hidden field of the previous form. And - that's it. No other code required, all fields are auto-filled, since CompanyID is linked to the appropriate existing record.

In this solution, all code that I needed is:
Private Sub cmdSelect_Click()
Forms![Contact Details]![txtCompID] = Me.CompanyID
DoCmd.Close acForm, "Company Picker"
End Sub

Hope this helps...

Best regards,
BrankoG