Combo box not retaining information

Posted on 2006-06-08
Last Modified: 2006-11-18
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.
Question by:Dotrooney
    1 Comment
    LVL 58

    Accepted 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

    What Security Threats Are You Missing?

    Enhance your security with threat intelligence from the web. Get trending threat insights on hackers, exploits, and suspicious IP addresses delivered to your inbox with our free Cyber Daily.

    Join & Write a Comment

    I originally created this report in Crystal Reports 2008 where there is an option to underlay sections. I initially came across the problem in Access Reports where I was unable to run my border lines down through the entire page as I was using the P…
    Overview: This article:       (a) explains one principle method to cross-reference invoice items in Quickbooks®       (b) explores the reasons one might need to cross-reference invoice items       (c) provides a sample process for creating a M…
    Familiarize people with the process of retrieving data from SQL Server using an Access pass-thru query. Microsoft Access is a very powerful client/server development tool. One of the ways that you can retrieve data from a SQL Server is by using a pa…
    In Microsoft Access, when working with VBA, learn some techniques for writing readable and easily maintained code.

    755 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    20 Experts available now in Live!

    Get 1:1 Help Now