Results 1 to 11 of 11

Thread: combobox

  1. #1
    Join Date
    Feb 2005
    Location
    Northern Ireland
    Posts
    24

    Wink Unanswered: combobox

    I'm having a bit of trouble with the following code:

    Dim rs As Object

    Set rs = Me.Recordset.Clone
    rs.FindFirst "[LNAME] = '" & Me![Combo66] & "'"
    If Not rs.EOF Then Me.Bookmark = rs.Bookmark

    It's in the after update in a combobox on my form. It works OK in that I can select a staff name (LNAME) either by typing the first few letters or scrolling to the name. However, if there are 10 Smiths it will only go to the first one.

    How can I include the first name? I've tried using and but I can't get it to work.

    Thanks.

  2. #2
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    providuing you have a unique identifier for the employee then you can use your exisitng approach witha few modifications

    first off add the unique identifier to the rowsource of the combobox, ensure the element is invisible (you will need to adjust the number of columns and column widths

    place your code to do the find firt as aprt of the combo boxes on click event

    change the parameter to

    rs.FindFirst "[myuniqueidentifier] = " & Me![Combo66].column(x)

    where
    myuniqueidentifier is your primary key eg "employeeID"
    Column(x) where x is the number the unique identifier appears in your query minus 1 (combo box rowsources start from base 0 not 1 (so the first column is 0 not 1)
    ***HEATLT Warning****
    the above is air code - thj approach should work but its not tested or verified you may need to do some tweaking
    HTH

  3. #3
    Join Date
    Apr 2004
    Location
    outside the rim
    Posts
    1,011
    First, save yourself some memory down the road - Dim rs as a Recordset instead of an Object.

    If the First name is included in the combo box's pull down list, you can get that value using the Column and ListIndex properties (Access help file has a good example).

    But, for what you want to do, you should be using a KEY field. Presumably, your source data has a Key Field (set to Autonumber or something), which is a unique ID for each record (regardless of how many Smiths you have). If you set the key field as the bound column and set the width to 0 of that column, the combo box will display the next column (your last name field probably).

    Use the key field value for the rs clone and you're all set.

    If you don't have a key field setup, make that your next task.

    have fun
    have fun!

    Todd S.
    click to email


    Independent Development Consultant
    Biz db & reports - SQL, Access, Crystal, Seradex and more
    Small, custom, unique programs - Office, VBA, .NET


    _________________________________________________
    Favorite message from Windows:
    There was an error displaying the previous error message

    Sadly, there was no error number to look up ...

  4. #4
    Join Date
    Feb 2005
    Location
    Northern Ireland
    Posts
    24

    Wink

    Quote Originally Posted by tcace
    But, for what you want to do, you should be using a KEY field. Presumably, your source data has a Key Field (set to Autonumber or something), which is a unique ID for each record (regardless of how many Smiths you have). If you set the key field as the bound column and set the width to 0 of that column, the combo box will display the next column (your last name field probably).

    Use the key field value for the rs clone and you're all set.

    If you don't have a key field setup, make that your next task.

    have fun
    I did try that but I can ony search on the by number in staff_id.

  5. #5
    Join Date
    Feb 2005
    Location
    Northern Ireland
    Posts
    24

    Wink

    Quote Originally Posted by tmarsh
    I did try that but I can ony search on the by number in staff_id.
    Should have read:

    I did try that but I can ony search by number in staff_id.

  6. #6
    Join Date
    Apr 2004
    Location
    outside the rim
    Posts
    1,011
    Do you mean your combo box doesn't show the first name?

    In the source query for the combo, select 3 fields: Key, LName, FName.

    In the combo properties, set the number of columns to 3 and the column width to 0,1.5,1.5 (you may want to adjust the 2nd and 3rd value, but make the 1st value 0. Set the Bound COlumn to 1.

    This will include the First name in the drop down. Type "Sm" for Smith and then use the down arrow to select the one you want. After the focus moves to another control, however, only the last name will remain visible. Often, I'll use 2 fields: Key and Expr1:[LName] & ", " & [FName], that way the value the remains visible is both last and first name.

    For the form to go to the correct Smith, you will need to either use the key field, or search for a matching Last name AND First name. Convention says: use the key field.

  7. #7
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    I think the fundamental concept to grasp is that you can retrieve information from the selected row other than the displayed value. The combo box allows the user to identify the required record (by using inforamtion familar to the user in this case surname & forenames) and the application uses different values (primary key such as an EmployeeID).

    You can have multiple elements of data attached to a combo box, but only show the familar information to the user. I have seen forms using the combo box as a lookup table, extracting different elements for use as required.

  8. #8
    Join Date
    Apr 2004
    Location
    outside the rim
    Posts
    1,011
    Quote Originally Posted by tcace
    you can get that value using the Column and ListIndex properties (Access help file has a good example).
    Quote Originally Posted by healdem
    You can have multiple elements of data attached to a combo box, but only show the familar information to the user. I have seen forms using the combo box as a lookup table, extracting different elements for use as required.
    The data behind combo and list boxes is 2 dimensional, just like a query or table. Each "row" in them is akin to a Record in a table. Each "column" in them is akin to a Field in a table.

    The Column property holds the value in each column. This allows you to get the value from any column in a combo or list box.

    The ListIndex property refers to the current selected row (read as "Record") in the combo or list box.

    The two together allow you get a specific piece of data from anmywhere in the combo or list box grid of data.

    A example of how to this specific operation is contained in the Access help file. Search for the Column property.

    Have fun!
    have fun!

    Todd S.
    click to email


    Independent Development Consultant
    Biz db & reports - SQL, Access, Crystal, Seradex and more
    Small, custom, unique programs - Office, VBA, .NET


    _________________________________________________
    Favorite message from Windows:
    There was an error displaying the previous error message

    Sadly, there was no error number to look up ...

  9. #9
    Join Date
    Feb 2005
    Location
    Northern Ireland
    Posts
    24
    Quote Originally Posted by tcace
    Do you mean your combo box doesn't show the first name?

    In the source query for the combo, select 3 fields: Key, LName, FName.

    In the combo properties, set the number of columns to 3 and the column width to 0,1.5,1.5 (you may want to adjust the 2nd and 3rd value, but make the 1st value 0. Set the Bound COlumn to 1.

    This will include the First name in the drop down. Type "Sm" for Smith and then use the down arrow to select the one you want. After the focus moves to another control, however, only the last name will remain visible. Often, I'll use 2 fields: Key and Expr1:[LName] & ", " & [FName], that way the value the remains visible is both last and first name.

    For the form to go to the correct Smith, you will need to either use the key field, or search for a matching Last name AND First name. Convention says: use the key field.
    Maybe I'm looking at this the wrong way!! My form contains staff details, name, dept etc. and details of training done are in a subform. I originally had the staff_id showing on the form so I thought I'd change it to a combobox and use it to search. I couldn't get it to work, it is bound to staff_id in a query. So, I used a text box and this code:

    Private Sub Combo66_AfterUpdate()
    ' Find the record that matches the control.
    Dim rs As Object

    Set rs = Me.Recordset.Clone
    rs.FindFirst "[LNAME] = '" & Me![Combo66] & "'"
    If Not rs.EOF Then Me.Bookmark = rs.Bookmark
    End Sub

    allows me to scroll or type and get a name EXCEPT it still has the problem mentioned above, it won't go to fname and lname.

    I've tried all of the suggested code but can't get it to work. If I use the staff_id then when I type a name nothing happens, if I type a number that works but of course I want to search on name.

  10. #10
    Join Date
    Apr 2004
    Location
    outside the rim
    Posts
    1,011
    If you have your combo include both the staff_id and the LName, set it to a column width of 2, bound column = 1 and make the row widths such that the first number is 0, it will work like you want (it will search by staff_id but display the name and allow you to type the name)

    Also, if you are using the combo to select a name and then the form is supposed to jump to that name, make the combo unbound (delete the value in the recordsource property).

    You can do this using the wizard - select both the key and the name, one of the screens in the wizard has an option "Hide Key".

    Secondly, review my earlier post about combining the last name and first name together. When you click the elipses (the 3 dots to the right side of the field) for the row source property of the combo, it opens us a QBE grid (a query designer). Copy and paste the following into one of the fields:
    Staff:[LName] & ", " & [FName]

    This will give you a combo box that displays the full name, allow you to type the first few characters of the full name, yet find the record based on staff_id, so that you when you select Mark Smith, you get the Mark Smith, not Aaron Smith .

    have fun
    have fun!

    Todd S.
    click to email


    Independent Development Consultant
    Biz db & reports - SQL, Access, Crystal, Seradex and more
    Small, custom, unique programs - Office, VBA, .NET


    _________________________________________________
    Favorite message from Windows:
    There was an error displaying the previous error message

    Sadly, there was no error number to look up ...

  11. #11
    Join Date
    Feb 2005
    Location
    Northern Ireland
    Posts
    24

    Thumbs up

    Yes, it works indeed! I don't know what I was doing! Thanks to everyone that replied.

Posting Permissions

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