Combo box (Access03)

Topic

New Reply

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

Reply | Quote

Replies

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

Reply | Quote

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.

Reply | Quote

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.

Reply | Quote

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.

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

Reply | Quote

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

Reply | Quote

I think what Charlotte means is to use it like:

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

Reply | Quote

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

Reply | Quote

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

Reply | Quote

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

Reply | Quote

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.

Reply | Quote

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

Reply | Quote

Sorry about that. I used [Pre] 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.

Reply | Quote

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

Reply | Quote