Results 1 to 6 of 6
  1. #1
    Join Date
    Sep 2002
    Location
    california
    Posts
    31

    Unhappy Unanswered: multiple combo boxes

    I have a form based on a query which includes the fields: accountid, debtorid, debtorname, clientid, clientname, contactid and contactname. Creating a form for new accounts is no problem. My problem is with the edit account form.

    Tables: tblAccounts (with field accountid, clientid, contactid); tblDebtor (with fields debtorid, debtorname, accountid); tblClients (with fields clientid, clientname); tblContacts (with fields contactid, contactname, clientid)

    When the user selects an account to edit we need to be able to change the client and the contact. So I need a combo box that list all clients, but has the currently selected client preselected when you open the form... that part I have but when I try to select another client from the combo box it doesn't work... I just get a beep.

    Also the current contact should show up, preselected when the form opens... I've got that worked out, but like the client combo box, I can't select a new contact.

    The other problem is that the contact needs to initial reflect what the contactid is in the tblAccounts, but when the client is changed then the contact combo box should show just the contacts that belong to that client.

    And of course... any changes to client or contact need to write back to the account table.

    I have been trying for days and maybe I'm trying to do something that's not possible, but I figure if anyone would know how to do this... this group would.

    Thanks in advance for your time and attention.
    Debbie

  2. #2
    Join Date
    Oct 2001
    Location
    Chicago
    Posts
    440
    A few questions:

    Lets take one control at a time.

    ClientComboBox:
    What is the Control Source?
    What is the Row Source?
    What is the Bound Column?

    Do all of your tables have primary keys defined?
    If not your recordsouce may not be updatable.


    >The other problem is that the contact needs to initial reflect what the
    >contactid is in the tblAccounts, but when the client is changed then the
    >contact combo box should show just the contacts that belong to that
    >client.

    The contact combo box needs to have it's row source reset in the ClientComboBox's AfterUpdate Event to include the value of the ClientComboBox in a where clause. Something like this:

    Private Sub ClientCombo_AfterUpdate()
    Dim strSQL As String
    strSQL = "SELECT [Contact] " & _
    "FROM [Query] WHERE [ClientID] =" & Me!ClientCombo
    Me!ContactCombo.RowSource = strSQL
    Me!ContactCombo.Requery
    End Sub

    strSQL would just be a modification to the original RowSource .

  3. #3
    Join Date
    Sep 2002
    Location
    california
    Posts
    31

    multiple combo boxes

    Firstly, thanks so much for your help. Here is the information you requested.

    Tables do have primary fields:
    tblAccounts is accountid
    tblDebtor is debtorid
    tblClients is clientid
    tblContact is contactid

    ClientComboBox:
    Control Source: clientid
    Row Source: SELECT tblClients.clientID, tblClients.clientName, tblClients.cAddress1
    FROM tblClients;
    Bound column: 1

    ContactComboBox:
    Control Source: contactid
    Row Source: SELECT tblContact.contactID, tblContact.contactName
    FROM tblContact;
    Bound column: 1

    So does that change your answer???

    Thanks again.

  4. #4
    Join Date
    Sep 2002
    Location
    california
    Posts
    31

    multiple combo boxes

    Well I have discovered why I can't change the contact or client. The underlying query is not updateable.

    So now I've got the client combo box working....but right now the contact combo box starts out with the correct contact but I need to be able to choice from the specific clients contacts.

    Thanks.
    Debbie

  5. #5
    Join Date
    Oct 2001
    Location
    Chicago
    Posts
    440
    se my previous remarks about updating the contact combo box during the after event of the client combo box. The query that you need to use must include a join on the client table to the contacts so that you can filter them out.

  6. #6
    Join Date
    Sep 2002
    Location
    california
    Posts
    31

    Talking multiple combo boxes

    Thanks sooo much, it works.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •