Combo Box not limited to list 1

[raf]

I have a Customer Info Form. I use a combo box to go quicky to a particular customer. I want limit_to_list false so that if the customer is not there, I can get a new form and enter new data.

The combo box uses a hidden CustID field (an AutoNum) but displays the CustName. It will not let me set limit_to_list to false because the field used to look up the customer is hidden (width 0).

I tried to use column 2 (the name) as searching field but something worse happens! Here it is:

I have a customer Mom's Place. This crashes the VBA code behind the combo box. In fact, any string which uses an apostrophe causes a problem. I looked at the wizard generated code and it has something like

custName & " ' "

like it's putting in an apostrophe as a delimiter, which must be causing a problem when it comes to the apostrophe in "Mom's Place".

How can I make a string that includes an apostrophe, or is there another solution for my combo box problem?

Thanks

RAF

 

[Quehay]

Have a button/hyperlink on your form that links to the parent records form. If a value isn't on the list click the button and enter a new record (on the One side).

In the Form_Close Event have:

[tt] Forms!frm_YourForm.cboLookup.Requery[/tt]

The new One record will be in the box.

 

[RickSpr]

Limit to List is sort of misleading. When you set Limit to List to "No", it means that Access will automatically add new items to the list, without raising the Not in List event. When you set it to "Yes", it doesn't mean no items can be added, it means that Access will raise the Not in List event. You said you wanted to get a new form for adding customers that are not in the list. For that, you need to set Limit to List to "Yes". It sounds backward, I know, but that's how it works.

To fix your wizard-generated code, notice that the code puts one apostrophe before and one after the custName. If you change each of those to two quote (&quot marks, it will give you one quote mark in the string to replace each apostrophe. That solves the problem, provided non of your customer names contains a quote mark.

If you want to be able to handle both apostrophes and quote marks in the custName, replace "custName" in the wizard expression with "DblQuotes(custName)", and add the following procedure to the form module or any standard module:

    Public Function DblQuotes(s As String) As String
        Dim i As Integer, j As Integer, result As String
    
        result = s
        i = 0
        Do
            j = Instr(i + 1, result, """")
            If j = 0 Then Exit Do
            result = Left$(result, j) & Mid$(result, j)
            i = j + 1
        Loop
        DblQuotes = result
    End Function

This function replaces each single quote with a pair of quotes, which the SQL statement processor will then reduce back to single quotes as it executes. Rick Sprague

 

[Quehay]

Rick,

Thanks for that submission--that's a really useful function!
For clarity's sake:

Single quotation = '
Double quotation = "

Apostrophe is just a single quotation in a different semantic context. (It's the right side of a complementary pair of single quotes if you've got a more sophisticated font.)