Results 1 to 4 of 4
  1. #1
    Join Date
    Jan 2005
    Posts
    3

    Question Unanswered: Combine fields into one combo box?

    I have a form that is accessing a "Client Info" ACCESS 97 database.

    The client names are broken out into two fields "LNAME" and "FNAME".

    I need to have one combo box that displays "LNAME, FNAME" for selection. Then OnUpdate the form will bring back other fields from the same record onto the form.

    Attached photo for visual...

    Thanks for listening!!

  2. #2
    Join Date
    Jan 2005
    Posts
    3

    screenshot

    forgot the screenshot ..
    Attached Thumbnails Attached Thumbnails help.gif  

  3. #3
    Join Date
    Nov 2003
    Posts
    1,487
    For your ComboBox, you will need to make the SQL statement located within the RowSource property look something like what is shown below:

    SELECT ClientNames.ClientID, ClientNames.LNAME & ", " & ClientNames.FNAME, ClientNames.ADDRESS, ClientNames.CITY, ClientNames.STATE, ClientNames.POSTCODE, ClientNames.TELEPHONE FROM ClientNames;

    Set the ColumnCount property to: 8
    Set the ColumnWidths property to: 0";1";0";0";0";0";0"0"
    Set the ListWidth property to whatever you like to provide the desire effect.
    Set the BoundColumn property to: 2

    Now in the OnClick event for your ComboBox, enter this code into the module:
    Code:
    If Not IsNull(Me.myFormsClientComboBoxFieldName) Then
       Me.myFormsClientNameField = Me.myFormsClientComboBoxFieldName.Column(1)
       Me.myFormsClientAddressField = Me.myFormsClientComboBoxFieldName.Column(2)
       Me.myFormsClientCityField = Me.myFormsClientComboBoxFieldName.Column(3)
       Me.myFormsClientStateField = Me.myFormsClientComboBoxFieldName.Column(4)
       Me.myFormsClientPostCodeField = Me.myFormsClientComboBoxFieldName.Column(5)
       Me.myFormsClientTelephoneField = Me.myFormsClientComboBoxFieldName.Column(6)
    End If
    You may need to play with the Column numbers a bit but I think you can get the idea. You can enhance the code later once you get everything providing the effect you want.


  4. #4
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    ...and this is a neat twist you can put on the lookup SQL:

    Code:
    SELECT	ClientNames.ClientID,
    	ClientNames.LNAME & ", " & ClientNames.FNAME FullName,
    	.
    	.
    	.
    	1 as SortOrder
    FROM	ClientNames
    UNION
    SELECT	ClientNames.ClientID,
    	ClientNames.FNAME & " " & ClientNames.LNAME FullName,
    	.
    	.
    	.
    	2 as SortOrder
    FROM	ClientNames
    order by SortOrder,
    	FullName;
    With this, each record appears in the list twice, once by First Name and once by Last Name, allowing your user to navigate to the record by typing either one.
    If it's not practically useful, then it's practically useless.

    blindman
    www.chess.com: "sqlblindman"
    www.LobsterShot.blogspot.com

Posting Permissions

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