Not in List and Trailing space 1

[dbarnard]

Hi,


I'm using Access 2007 and I have a combo box which uses the 'Not in List' event to check to make sure the entry is in another table, and if not asks the user if they want to Add the record. The code then does an INSERT INTO.... to add the new record, but...

Although when entering the data into the table any trailing spaces are removed by Access, when 'Not is list' triggers it uses the data entered WITH the space to check. If the record IS in the table (without the space) it thinks it's 'Not in List'. This means that when the INSERT INTO tries to write the record it fails due to a duplicate record.

Is there any way I can get it to trim the trailing space before checking it is not in the list?

Thanks
Dean

 

[aperez54]

Have you tried the TRIM function? Spaces drive me nuts too.

 

[LisaWay]

Dean,

This should help you:

Private Sub MyCombo_NotInList(NewData As String, Response As Integer)
    If NewData <> Trim(NewData) Then
        [MyCombo] = Trim(NewData)
        Response = acDataErrContinue
    End If
End Sub

Regards,
Lisa

 

[dbarnard]

Thanks for the tips.

Lisa, I had tried something similar to what you've put above, but my problem is that once the space has got trimmed I need a way of getting the 'Not in List' event to trigger again if the 'trimmed' entry is not in the list.
At the moment, after the code above runs with an entry not in the list (and with a space at the end) the combo box dropdown appears (without the new entry in it as I'd expect), but on pressing enter it allows the new value and continues to the next field even though it hasn't written the combo box data to the other table.

Any idea's?

Thanks
Dean

 

[LisaWay]

The following code forces a recheck if the value entered has trailing spaces:

Private Sub MyCombo_NotInList(NewData As String, Response As Integer)
    If NewData <> RTrim(NewData) Then
        Response = acDataErrContinue
        [MyCombo] = Null
        SendKeys RTrim(NewData) & "{Tab}"
    Else
        ' Add the value to the table, then ...
        Response = acDataErrAdded
    End If
End Sub

Note: I changed "Trim" to "RTrim" to avoid trimming leading spaces.

Regards,
Lisa

 

[dbarnard]

Lisa,

Thanks for the bit of code. I'd bodged it another way with a DLookup, so I'll change it to the above.

Cheers
Dean