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

    Combo box (Access03)

    I use the wizard to create a combo box to locate records, the default coding is below

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

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

    My problem is that one of the department's name is Women's Pavilion. You smart ones probably already see the problem. The combo box works on all other departments by not this one because of the '. I know it is that because I remove just the 's and it works properly. I figured I need more quotes but I haven't figured it out. Any help would be appreciated. I can't rename the department.

    Thanks. Fay

  2. #2
    Platinum Lounger
    Join Date
    Feb 2002
    Location
    A Magic Forest in Deepest, Darkest Kent
    Posts
    5,681
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Combo box (Access03)

    Fay

    Have you tried ` (left hand inverted comma) it has worked for me in the past

    On a UK keyboard it is just left of the number 1 in the top row

    Women`s Pavilion
    Jerry

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

    Re: Combo box (Access03)

    That sort of issue is pretty common when using combo boxes to locate specific records. What we usually do to avoid that kind of thing is to make the combo box multi-column and store a numeric pointer to the departments. That probably means changing your table structure, which may not be possible, but I would certainly look at it. Otherwise you solve the problem of having an apostrophe in the field and then somebody puts in a quote (or an * or # or another special character), and the combo box fails on that.

    Another option is to apply a filter based on the combo box selection instead of using FindFirst, but that can be an issue where special characters are involved as well. However it is a bit less sensitive as you simply set the filter string to a value and then apply the filter.
    Wendell

  4. #4
    Platinum Lounger
    Join Date
    Nov 2001
    Location
    Vienna, Wien, Austria
    Posts
    5,009
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Combo box (Access03)

    Something that works for me is inserting a Standard Module in the VB Editor and pasting in the following code:

    Function FixQuotes(strText As String) As String
    FixQuotes = Chr$(34) & "" & strText & "" & Chr$(34)
    End Function

    Then rephrasing Me![Combo30] as FixQuotes(Me![Combo30]). Hopefully, it may work for you as well.
    Gre

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

    Re: Combo box (Access03)

    Fay, here's another routine that might be of help. It can be extended to handle other inconvenient characters as well. You would call this by passing in the value you need to wrap, and the routine figures out the type of value it is and adds the appropriate delimiters.

    <pre>Public Function SetVarKey(ByVal varKeyID As Variant)
    On Error GoTo SetVarKey_Err
    Dim varResult As Variant
    Const SINGLE_QUOTE As String = "'"
    Const DOUBLE_QUOTE As String = """"
    Select Case varType(varKeyID)
    ' Set the Key ID variable appropriately
    Case vbString
    If InStr(varKeyID, SINGLE_QUOTE) > 0 Then
    varResult = DOUBLE_QUOTE & varKeyID & DOUBLE_QUOTE
    Else
    varResult = SINGLE_QUOTE & varKeyID & SINGLE_QUOTE
    End If
    Case vbDate
    varResult = "#" & varKeyID & "#"
    Case Else
    varResult = varKeyID
    End Select

    SetVarKey_Exit:
    SetVarKey = varResult
    Exit Function
    SetVarKey_Err:
    MsgBox "Error: " & Err.Number & vbCrLf & Err.Description & vbCrLf _
    & "Proc Name: SetVarKey", _
    vbCritical + vbOKOnly, "Error Encountered"
    Resume SetVarKey_Exit
    End Function</pre>

    Charlotte

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

    Re: Combo box (Access03)

    Hello Charlotte thank you for coming back to me with this. I put the code you provided in the module then inserted it into the combo click event

    Private Sub Combo34_AfterUpdate()
    ' Find the record that matches the control.
    Dim rs As Object
    SetVarKey
    Set rs = Me.Recordset.Clone
    rs.FindFirst "[Department] = '" & Me![Combo34] & "'"
    If Not rs.EOF Then Me.Bookmark = rs.Bookmark
    End Sub

    Since I got an error message Argument not optional. I guess I put it in the wrong spot. I don't understand your line "You would call this by passing in the value you need to wrap." Could you explain further?

    Thank you. Fay

  7. #7
    Platinum Lounger
    Join Date
    Dec 2001
    Location
    Melbourne, Australia
    Posts
    4,594
    Thanks
    0
    Thanked 27 Times in 27 Posts

    Re: Combo box (Access03)

    I think what Charlotte means is to use it like:

    rs.FindFirst "[Department] = " & SetVarKey(Me![Combo34])

  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 (Access03)

    Got it in one, Pat. Sorry I wasn't clear enough in my instructions.
    Charlotte

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

    Re: Combo box (Access03)

    Nothing to be sorry about, you don't know how much I appreciate everyone's help. Because I am once again treading where I haven't been before.

    Okay I made the change as directed by Pat. The function breaks at the first Else with a compile error: Else without If.

    Thank you.

    Fay

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

    Re: Combo box (Access03)

    Nothing to be sorry about, you don't know how much I appreciate everyone's help. It is more my limited abilities. Because I am once again treading where I haven't been before.

    Okay I made the change as directed by Pat. The function breaks at the first Else with a compile error: Else without If.

    Thank you.

    Fay

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

    Re: Combo box (Access03)

    Without seeing the code you are running, I have no idea what is wrong. The code I posted certainly doesn't have an else without an if, so we need to see your code to determine what went haywire. It's possible you have an extra end if in there somewhere. <img src=/S/scratch.gif border=0 alt=scratch width=25 height=29>
    Charlotte

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

    Re: Combo box (Access03)

    I went back to your original code that I copied into the module and proofed it to what I have. When I copied it for some reason it was pasted in as one line. When I repaired it I by mistake left the varResult = DOUBLE_QUOTE on the same line as the Then.

    Bottom line is it works now. I apologize that I didn't catch it earlier when I put it in.

    Thank you. Fay

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

    Re: Combo box (Access03)

    Sorry about that. I used <!t>[Pre]<!/t> tags to preserve the formatting of the code I pasted in, and the way the Lounge code is written, you can't copy code in those tags directly into a code window because everything comes out in one long line. It's safest when copying code to paste it into something like Notepad, which preserves the line breaks, and from there copy and paste it into your code window.
    Charlotte

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

    Re: Combo box (Access03)

    This is one of the reasons I love this lounge. You learn something with each and every post.

    Have a great and safe New Years.

    Fay

Posting Permissions

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