[Last Call] Learn how to a build a cloud-first strategyRegister Now

  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 280
  • Last Modified:

Combo box not retaining information

I have a form which holds client details and multiple contacts within that client. I have another form which is called Frm Jobs where the client name is selected  and the row source is SELECT [Tbl clients].[Customer ID], [Tbl clients].Client FROM [Tbl clients] ORDER BY [Tbl clients].Client;.

When I select this client I then want to select the correct contact so I have a combo box where the user selects the correct Contact ID and it then fills two other text boxes with the first and second names.The row source for the Contact ID is SELECT [Tbl Client Contacts].[Contact ID], [Tbl Client Contacts].[First Name], [Tbl Client Contacts].[Second Name], [Tbl clients].[Customer ID] FROM [Tbl clients] INNER JOIN [Tbl Client Contacts] ON [Tbl clients].[Customer ID]=[Tbl Client Contacts].[Customer ID] WHERE ((([Tbl Client Contacts].[Second Name]) Is Not Null) And (([Tbl clients].[Customer ID])=Forms![Frm Jobs]![Customer ID])) ORDER BY [Tbl Client Contacts].[Second Name];

What is happening is that I select the client and it is showing me the correct contacts within that client, I can select the one I want but when I go back into the form, the information is not retained.
1 Solution
Hello Dotrooney

A form cannot "retain" information as such. For that you need a table, and a field in that table.

What is the Record Source for [Frm Jobs]? If it is, say, the [Tbl Jobs], then that table should have a field like [Contact ID], and you could bind that field to the relevant combo box. In that case, when you select a contact in a company for a job, the table will indeed retain the data.

By the way, your query for the contact combo can be simplified, because it does not need the link to [Tbl clients], it only uses the [Customer ID] field. In other words:

SELECT [Contact ID], [First Name], [Second Name]
FROM [Tbl Client Contacts]
WHERE [Second Name] Is Not Null And [Customer ID]=Forms![Frm Jobs]![Customer ID]
ORDER BY [Second Name];

Hope this helps,

Featured Post

Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Tackle projects and never again get stuck behind a technical roadblock.
Join Now