Results 1 to 11 of 11
  1. #1
    Join Date
    Jul 2004
    Posts
    156

    Unanswered: Recordsets & auto-selecting

    Hi, everyone:

    The situation is this: I have someone who is entering cost information based by employee number into a form, where the employee number is selected from a combo box based on the employee info table. The combo box lists the employees alphabetically so that they can choose the name of the employee without knowing their employee number. However, this combo box is bound to the employee number field in the cost information table. The employee information table's primary key is the employee number.


    My goal is this: I need to be able to select any name from the list, which is already implemented. Once they've entered the cost information based on that employee and move to a new record, I need to be able to automatically select the very next employee in the list ALPHABETICALLY. I know this is probably a lot of code, but I'm not very familiar with manipulating recordsets. If someone could just point me in the right direction, I would GREATLY appreciate it.

    THANKS!
    DocX

    The teachings of God's Begotten: 2 John 1:9

  2. #2
    Join Date
    Jun 2002
    Location
    Saudi Arabia / Philippines
    Posts
    126
    Hmm! My first thought was to suggest MoveNext but of cource this will not work since you want to move to the next row in the combo box.

    Well, there is a read-only property of your combo box called Column that may help if your combo box is based on a query that is already sorted alphabetically (and I assume it is). The column property has two arguments: index (or relative column) and row. Unfortunately these two arguments cannot be read directly; they are normally passed as arguments to the property get. So you will have to determine the row number of the current record by using a Do-Loop or For-Next until you find a match on the Employee Number (your unique key). Once you have the row number equivalent to the current record it is easy to increment it and get the Employee Number of the next row that, because your query is alphabetical, will be the next emplyee in an alphabetical listing.

    Yes, it's some coding effort but if tightly written should not be overwhelming. You must take into account end-conditions (e.g. end of list).

    If you want some sample code then ask and I will see what I can do.
    Rod

    fe_rod@hotmail.com

  3. #3
    Join Date
    Jul 2004
    Posts
    156
    Here is something that I've recently come up with since working on it. I'm trying to tap into the ListIndex property of the combo box, which will let you see what item is currently selected. However, I believe this is a read-only attribute since it gives me the "Hey, stupid, you're using the ListIndex property incorrectly" error. [What? Your VBA doesn't give you the "Hey, stupid" part?] If I follow this route then I'll have to find some read/write propery that'll let me set the combo box to a certain value related to the ListIndex property.

    With Me
    lngChosen = .cboEmployeeNo.ListIndex
    lngChosen = lngChosen + 1
    [After I've created the new record, then...]
    .cboEmployeeNo.ListIndex = lngChosen
    End With

    With your solution, Rod, I haven't put it into play yet, but I think I see what you're saying. Basically take the value of the Employee No of the field, then search through the combo box's item data by the column/row process until it equals it and then set the Column property equal to that? Is the Column property writable? I believe you're suggesting that since you mention incrementing it. By the way, your e-mail address--Iron Rod?

    Thanks!
    Last edited by DocX; 07-24-04 at 08:28.
    DocX

    The teachings of God's Begotten: 2 John 1:9

  4. #4
    Join Date
    Dec 2002
    Location
    Préverenges, Switzerland
    Posts
    3,740
    try this:

    myCombo = myCombo.Column(0, myCombo.ListIndex + 1)

    note that the "0" in ".Column(0,"
    is the zero-based index of the BOUND column... adjust to suit your bound column.

    and maybe add some code if you don't want to fall off the end of the list into null.

    izy
    currently using SS 2008R2

  5. #5
    Join Date
    Jul 2004
    Posts
    156
    izy....

    You are my friggin' HERO, man!!! It makes me feel so stupid after I see how easy it is. Man, I was thinking I might have to deal with recordsets from the query that the combo box is based on, blah blah blah. And this is the reason that forums like this exist--to not fry the brains of the less-experienced.

    A question pops into my mind, though, about future variations of something like this. What if you didn't have the combo box with the query that's already got things in alphabetical order? I mean, you could fix up the query just like it is for the combo box, but you'd have to load that query through code, right? And THEN use recordset navigation?

    But, guys, I appreciate all the help!
    DocX

    The teachings of God's Begotten: 2 John 1:9

  6. #6
    Join Date
    Jul 2004
    Posts
    156
    Here's what I've got, including the code to prevent null's evil things.
    Code:
    With Me
        lngChosen = .cboEmployeeNo.ListIndex
        lngChosen = lngChosen + 1
    End With
    
    'Code to add new record goes here
    
    With Me
        lngListCount = .cboEmployeeNo.ListCount - 1
        If lngChosen <= lngListCount Then
            .cboEmployeeNo = .cboEmployeeNo.Column(0, lngChosen)
        Else
            .cboEmployeeNo = .cboEmployeeNo.Column(0, 0)
        End If
    End With
    Do you guys see any possible errors with this? Thanks!
    DocX

    The teachings of God's Begotten: 2 John 1:9

  7. #7
    Join Date
    Dec 2002
    Location
    Préverenges, Switzerland
    Posts
    3,740
    a couple of thoughts.

    your:
    Else
    .cboEmployeeNo = .cboEmployeeNo.Column(0, 0)

    zaps back to the first row. this may or may not make sense depending on the result you are looking for. an alternative might be simply to go to Null. far as i know it's undocumented (so it may not be reliable), but
    .Column(0, myCombo.ListIndex + 1)
    when you are on the last entry of the combo cycles the combo to Null.

    and...

    instead of your recordset proposal, consider setting up SQL (with an appropriate ORDER BY clause) and switching the combo.rowsource in code.

    easy compared with all the hassle of populating a combo from a recordset using a callback function.

    izy
    currently using SS 2008R2

  8. #8
    Join Date
    Jul 2004
    Posts
    156
    At the end of the list, I wanted to cycle back to the first name in the list for the pure fact of just having consistency and looping back to the first. I didn't want to take any chances with null. It can be a scary thing, that null.

    As for the other questions, I was proposing that there was no combo box, just an empty field that would have to pull information from a table in alphabetical order when you added a new record. I don't see why I would do this, as of yet, since any information I'll be putting in like this would more than likely have to have a combo box.
    DocX

    The teachings of God's Begotten: 2 John 1:9

  9. #9
    Join Date
    Jul 2004
    Posts
    156
    Well, I've run into another problem.

    After it adds the new record, the code changes the combo box to the next number, right? Well, the problem with that is that it dirties the record and if you close the window, that record's added to the table. See the problem? I'm thinking about having some code for the form's OnClose property to "clean" the record so that it won't cause a problem. Is there a real quick way to un-dirty a record?
    DocX

    The teachings of God's Begotten: 2 John 1:9

  10. #10
    Join Date
    Dec 2002
    Location
    Préverenges, Switzerland
    Posts
    3,740
    don't know (i use unbound forms = no dirty records).

    a guess: can you capture the .listindex; movenext; and then mess with the combo?

    izy
    currently using SS 2008R2

  11. #11
    Join Date
    Jul 2004
    Posts
    156
    Wouldn't that still dirty the combo box causing it to save the record on close?

    After looking over it a bit, I realized that I'd want to clean the record anyway on close because I have a button that they have to push to add that record. If they just want to close the form without adding the record, even after changing different fields, which would dirty it, I'd want to clear the record.

    It seems as though there's no easy way to delete records if you don't use the menu command method. Is this true? I usually just do the preset command button option to delete records. But I don't want the confirmation message to pop up as they close the form. Any suggestions?
    DocX

    The teachings of God's Begotten: 2 John 1:9

Posting Permissions

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