[Solved] Dropdown List on Form but entries are duplicated

[jsvanc]

I have 2 tables, Company and Contacts.

I have a dropdown list that I populate on a form called Company Name. It is bound to a sql query that populates the list from the Company table.

I have another object on the form called Contact Name.

I want the user to be able to select the company from the dropdown list, type in the contact name, click the save button and have the data saved to the Contact Name Table along with the CompanyID in the Contact Name Table.

Now, when I select the company name and type in the contact name, a new company is created in the Company Table along with the Contact Name in the Contacts Table. Not sure what to do to get this to work correctly.

Any help is appreciated. Thank you.

[Nocton]

Now, when I select the company name and type in the contact name, a new company is created in the Company Table along with the Contact Name in the Contacts Table. Not sure what to do to get this to work correctly.

Have you got the Contact Name control in a separate sub-form with data from Contacts linked to the main form by the Company ID?

[jsvanc]

Yes which is why I didn't think it would save a new company. I attached a screenshot of my form. Thank you.

[Nocton]

I would have thought you should have your frmContactName as the main form where you select the company and your MainForm as the subform where you enter the contact details.

[UnklDonald418]

To accomplish what you are attempting you need a Filter table.
A Filter table is a special table that always has only one record stored in it.
Go to Tools-SQL and execute the following command

Code: Select all

CREATE TABLE "FILTER" (
ID BOOLEAN NOT NULL PRIMARY KEY,
"CompanyID" Integer NOT NULL
);

Next go to View->Refresh Tables so you can see the newly created table in your list of tables.
Open your form document in the Edit mode and open the Form Navigator.
Select MainForm and open the Properties dialog.
On the Data tab Content line select the "FILTER" table.
Look down the list of properties and set
Allow additions to No
Allow modifications to Yes
Allow deletions to No
Next select frmContactName and change the Link master field to "CompanyID".
Also add a Push button to frmContactName and set the Action property to Refresh Form
Now select your List Box and set the Data field to "CompanyID". You shouldn't need to change the List Content or the Bound field.

Exit the Design Mode and select a company name in the List Box and press the new Push Button. If there is an existing Contact Name it should display on the form. You can edit the name or if it is blank enter a name. Press your Save button to store your new entry.

[jsvanc]

@UnklDonald418. I've done all that, but now the Company Name Dropdown box is greyed out and I can't select anything. I've looked through and it doesn't appear that it should be greyed out.
Thanks.

[UnklDonald418]

The only thing I can think of that would cause a List Box control to be greyed out is if it has been disabled. On the General tab of the Properties: List Box dialog make sure that the Enabled property is set to Yes.

If that doesn't help then I would suggest that you upload a copy of your database so someone here can see if they can solve the problem.
How to attach a document here

[jsvanc]

Attached is the copy of the database. Thank you.

[UnklDonald418]

Ok, I think I see why the List Box control on the form document AddNewContact is greyed out.
On the Data tab of the Form properties dialog for MainForm change Add data only to No.
Also, prime the Filter table by entering a number (1 would be good) into the CompanyID field.

Since you also have the Add data only property set to Yes on frmContactName the Contact Name Text Box Contact Name will not display any existing values.

[jsvanc]

I guess my problem is that I didn't even need the subform. That is where all the problems were.

I ended up putting the Company Name Dropdown List under the Main form, deleting the subform, adding a sql select statement to populate the dropdown box then I could select the company, and type in the new contact name. All is working fine now.

Thanks for all the help.