1. Computer problem? Tech Support Guy is completely free -- paid for by advertisers and donations. Click here to join today! If you're new to Tech Support Guy, we highly recommend that you visit our Guide for New Members.

Solved: Access 2010 MVL field Errors

Discussion in 'Business Applications' started by rekeene, Jan 15, 2015.

Thread Status:
Not open for further replies.
Advertisement
  1. rekeene

    rekeene Thread Starter

    Joined:
    Dec 2, 2014
    Messages:
    49
    Good day,

    I'm seeking assistance with glitches in my MultiValue Lookup Boxes:

    Scenario No. 1:

    The MVL field is sorted in alphabetical order, last name first.

    Example of names in MVL:
    1. Michael, Christopher
    2. Michael, Jones

    When I search the MVL field for Michael, Jones it returns the record for Michael, Christopher. Even if I manually scroll the MVL field and click on Michael, Jones, I’m still presented with Michael, Christopher’s record. It appears the MVL field is only searching/looking at the first occurrence of Michael.


    Scenario No. 2:

    The MVL field cannot locate records with an apostrophe in the name. For example: Jn’Marie. Do I need to omit the apostrophe?

    Thanks for your assistance.
     
  2. OBP

    OBP

    Joined:
    Mar 8, 2005
    Messages:
    19,895
    I assume that this is a "Find" Combo for locating records.
    The first problem is due to the data structure, as you have guessed it is only using the first column of I assume 2 columns.
    Does the table/query that the form is based on have an Indexed Key Field?
    if so include the key field in the Combo Row Source as the first Column and hide it by setting it's width to 0cm.

    The second problem is very old and goes back to the first versions of Access and is caused by the VBA code that does the matching of the records, the single apostrophe confuses the VBA which itself normally uses apostrophes in it's code.
    You need to change them in the code to the double apostrophe, the Inverted Comma "
     
  3. rekeene

    rekeene Thread Starter

    Joined:
    Dec 2, 2014
    Messages:
    49
    Hi OPB,

    I was able to fix find combo box glitch. Not successful on the second as I don't know where to make the adjustments to the code. I created the search/find field using the wizard. Thanks.
     
  4. OBP

    OBP

    Joined:
    Mar 8, 2005
    Messages:
    19,895
    To fix the second problem it will need some changes.
    In the Combo's Property List under "Event" what does it say in the "After Update" event, does it say "[Event Procedure]" or "[Embedded Macro]"?
    If it says [Embedded Macro] it will need to be changed to an [Event Procedure] using the drop down arrow that appears when you click on the [Embedded Macro].
    Once it says [Event Procedure] you can click on the 3 small dots on the right hand side and it will open the Visual Basic Editor at the correct procedure, which will be blank.
    I can supply the code to place in there if you can tell me the exact name of the fields that you are searching on.
     
  5. rekeene

    rekeene Thread Starter

    Joined:
    Dec 2, 2014
    Messages:
    49
    The name of the search field is "Client Name" created in the Expression Builder, using the First and Last name fields. Thanks much!
     
  6. OBP

    OBP

    Joined:
    Mar 8, 2005
    Messages:
    19,895
    Is that the actual names of the fields?
    First Name
    and
    last Name?

    With Visual Basic you have to be precise.
     
  7. rekeene

    rekeene Thread Starter

    Joined:
    Dec 2, 2014
    Messages:
    49
    Actually:
    FirstName
    and
    LastName
     
  8. OBP

    OBP

    Joined:
    Mar 8, 2005
    Messages:
    19,895
    Did you include the Key Indexed field in the combo as I suggested, if so what is it's name?
     
  9. rekeene

    rekeene Thread Starter

    Joined:
    Dec 2, 2014
    Messages:
    49
    It's called ChildID.

    Thanks.
     
  10. OBP

    OBP

    Joined:
    Mar 8, 2005
    Messages:
    19,895
    The VBA code is as follows

    Dim rs As Object

    Set rs = Me.Recordset.Clone
    rs.FindFirst "[ChildID] = " & Me![Combo name goes here] '
    If Not rs.EOF Then Me.Bookmark = rs.Bookmark


    Note that where I have "Combo name goes here" you need to change that to the name of your combo, which I forgot to ask for.
     
  11. rekeene

    rekeene Thread Starter

    Joined:
    Dec 2, 2014
    Messages:
    49
    Hi,

    This is the code I'm using:

    Private Sub ClientSearch_AfterUpdate()
    Dim rs As Object

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

    End Sub

    However, I'm receiving the attached error.
     

    Attached Files:

  12. OBP

    OBP

    Joined:
    Mar 8, 2005
    Messages:
    19,895
    Can you try removing the ' from the end of the line please.
     
  13. rekeene

    rekeene Thread Starter

    Joined:
    Dec 2, 2014
    Messages:
    49
    I did, and got the attached error. :(
     

    Attached Files:

  14. OBP

    OBP

    Joined:
    Mar 8, 2005
    Messages:
    19,895
    That suggests that your first Column in the Combo is not actually ChildID.
    Is ChildID the first column?
     
  15. OBP

    OBP

    Joined:
    Mar 8, 2005
    Messages:
    19,895
    Are you able to post a copy of your database with some dummy data (personal data is not allowed on the Forum) in it for me to work with?
    It would need to be Zipped.
     
  16. Sponsor

As Seen On
As Seen On...

Welcome to Tech Support Guy!

Are you looking for the solution to your computer problem? Join our site today to ask your question. This site is completely free -- paid for by advertisers and donations.

If you're not already familiar with forums, watch our Welcome Guide to get started.

Join over 733,556 other people just like you!

Loading...
Thread Status:
Not open for further replies.

Short URL to this thread: https://techguy.org/1141238

  1. This site uses cookies to help personalise content, tailor your experience and to keep you logged in if you register.
    By continuing to use this site, you are consenting to our use of cookies.
    Dismiss Notice