Results 1 to 10 of 10
  1. #1
    5 Star Lounger
    Join Date
    Jul 2001
    Location
    NJ, USA
    Posts
    617
    Thanks
    0
    Thanked 0 Times in 0 Posts

    combo box as a locator (Access XP)

    I have a combo box being used to locate specific members. When I have two members with the same last name I can't get it to go to the second person. The Row Source reads SELECT qryMembership.LastName, qryMembership.FirstName, qryMembership.MembershipID FROM qryMembership; there is one bound column. Column count is 3. The event procedure code reads as follows

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

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

    Thank you for helping me work on my bugs.

    Fay

  2. #2
    4 Star Lounger
    Join Date
    Aug 2002
    Location
    Dallas, Texas, USA
    Posts
    594
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: combo box as a locator (Access XP)

    Okay, what you need to do is change your combobox's 'bound' column to your ID field. Then filter your form based on the ID, not the name.

  3. #3
    5 Star Lounger
    Join Date
    Jul 2001
    Location
    NJ, USA
    Posts
    617
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: combo box as a locator (Access XP)

    Thanks for the response. If I change the bound column to 3 which was the location of the ID on the SQL grid it didn't work. But if I replace the combo box and make sure the ID is first on the grid it works. Go figure. Did I misunderstand the Bound column option below the Row Source?

    Again thanks for the help.

    Fay

  4. #4
    Super Moderator
    Join Date
    Aug 2001
    Location
    Evergreen, CO, USA
    Posts
    6,623
    Thanks
    3
    Thanked 60 Times in 60 Posts

    Re: combo box as a locator (Access XP)

    FYI, the Bound column selects the value that is linked to whatever the DataSource of the combo is, while the RowSource determines the source of the drop-down list the combo box displays. One of the very powerful tools in Access. I also wanted to note that we've seen some problems in using the Recordset.Clone method with AccessXP and ADO. For some reason, it doesn't always seem to work reliably, though that's the code the combo box wizard generates when you tell it to create a combo for record navigation purposes. We've migrated to applying a filter from the combo box, which seems very reliable, and often is quicker. Hope this helps.
    Wendell

  5. #5
    5 Star Lounger
    Join Date
    Jan 2001
    Location
    austin, Texas, USA
    Posts
    1,029
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: combo box as a locator (Access XP)

    I remembered you saying there have been some problems using the stock <font color=448800>Recordset.Clone</font color=448800> code when running the Select a Record on a Form wizard when placing a drop-down box. I happen to use this method frequently and was wondering if you could give me more detail on the issue of applying a filter from the combo box.

    I am currently working on a project that uses the standard method for finding a record based on the combo box and don't have any problems with that directly, but I also have a button set to Duplicate Record and, if you don't select the record using the combo box you get a very basic run-time error that I can't seem to trap out and use a less obscure warning. The run time error is <font color=red>'Invalid use of Null'.</font color=red> I want to replace that with a msgbox saying 'Please select a record from the drop down box before duplicating a record' but the Access VB error msgbox always take precedence.

    So -- actually, replacing the combo box may not help but I am curious as to why you avoid it and would like more details on your substitution.

    And, as to the error on the duplicate record routine, I am using the canned Access coding which likes to call menu selections -- is that a bad idea? Is there a better way to do it? I get confused by the acCmd and DoCmd and RunCommand options at any rate. But, I would like to not use the kind of code the Wizard likes to use, such as <font color=448800>DoCmd.DoMenuItem acFormBar, acEditMenu, 8, , acMenuVer70</font color=448800> -- it would seem to me that using custom menus or hiding/removing standard ones would cause such code not to work. It just *looks* wrong.

    Hope this isn't too confusing.

  6. #6
    Super Moderator
    Join Date
    Aug 2001
    Location
    Evergreen, CO, USA
    Posts
    6,623
    Thanks
    3
    Thanked 60 Times in 60 Posts

    Re: combo box as a locator (Access XP)

    First of all, the Recordset.Clone only seems to be a problem with databases developed on XP that run on Access 2000 systems. I believe it has something to do with XP defaulting to ADO when it builds the code, and A2K gets confused, but that's only a theory. In any event we found that it didn't always display the correct record, though this may have been fixed in the latest SP as we haven't tested it.

    To fix the duplicate record problem, why not disable the button on Form Open, and then enable it in code once the combo box is changed. The method you are using to duplciate the record works and doesn't need any complex code, which doing it in DAO or ADO would require - and those menu items should still work, even if the menu is not visible.

    As to the filter process in lieu of Recordset.Clone, one of the problems with the .Clone approach is that the full recordset is still there, and people can use PageUp and PageDn to move to a record which is different from that shown in the combo box. What we do is create a filter in code based on the contents of the combo box, and apply that to the form. It does typically require that you have a hidden text box on the form which contains the key for the record. Then anytime the combo box value changes, we remove the filter, we modify the filter, and then reapply it. Thus there is always only one record in the datasource, and things can't get out of sync. If you would like a sample of the code involved, it may take a day or so, but I will try to create one.
    Wendell

  7. #7
    5 Star Lounger
    Join Date
    Jan 2001
    Location
    austin, Texas, USA
    Posts
    1,029
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: combo box as a locator (Access XP)

    thanks for the response. I went ahead and replaced the <font color=blue>DoCmd</font color=blue> with <font color=blue>RunCommand</font color=blue> code and that works also. I kind of like RunCommand because you can tell from looking at it what you are trying to do, rather than calling a menu item by number. Guess it's just philosophical. I did notice a line in the VB Help that states that RunCommand replaced DoCmd after Access 97, which implies that DoCmd is, so to speak, deprecated. That doesn't eliminate the error you get when trying to duplicate the form data before selecting a record, which was the original problem. You suggestion to disable the button is interesting; I might want to have the form select a record on open as well -- say the first one, to reinforce that one navigates to other records thru the drop-down box. That would be even more intuitive, perhaps.

    On the subject of paging up and down on a selected record, I just noticed before getting your reply that this occurs when the Mouse Wheel or Page Down is used and definately don't want this to occur. I have a sub-field that needs to be tied to a specific record and don't want users to accidentally scroll into a new record on the subfield. I have it set to cycle in current record in Properties but can't find anything to answer to the Wheel situation. There is a way to detect PageUp or PageDown and do an event -- maybe automatically return to the 'actual' record and delete the one just created. It would be better to disable the key totally but help says key events can't be cancelled. Any suggestions?

    I realize this thread is getting off-topic; if you want to move it to something like Subform/Form Navigation Control issues... that's kind of what I am blathering about. I'm using A2K SR-1 on Win2KPro

  8. #8
    Plutonium Lounger
    Join Date
    Dec 2000
    Location
    Sacramento, California, USA
    Posts
    16,775
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: combo box as a locator (Access XP)

    Actually RunCommand replaced DoMenuItem. RunCommand is a method of the DoCmd object and the Application object.
    Charlotte

  9. #9
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: combo box as a locator (Access XP)

    If you want to disable keys, you can do the following:

    Set the KeyPreview property of the form to True - this will cause all key events to be handled by the form before being passed on to the event handler of the contol involved.

    In the KeyDown event handler of the form, set KeyCode to 0 for all key codes you want to be ignored. For example:

    Private Sub Form_KeyDown(KeyCode As Integer, Shift As Integer)
    Select Case KeyCode
    Case vbKeyPageUp, vbKeyPageDown
    KeyCode = 0
    End Select
    End Sub

  10. #10
    3 Star Lounger
    Join Date
    Jun 2001
    Location
    Salem, Oregon, USA
    Posts
    219
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: combo box as a locator (Access XP)

    > If I change the bound column to 3 which was the location of the ID on the SQL grid it didn't work. But if I replace the combo box and make sure the ID is first on the grid it works.

    Remember that referring to columns in a combo box is zero-based. IOW, if you have columns LastName, FirstName, and PersonID, bound column of 3, then to use your FindFirst you would look for PersonID matching, "Me![Combo46].column(2)," which is your bound third (not 2nd) column.

    thx
    Pat

Posting Permissions

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