-
Using DLookup to validate combobox
I have a table, Counties, with one field, PropertyCounties. There is a combobox on the form, Orders, with the table Counties as its source. The limit to list is set to yes. When the user enters a value not on the list--let’s call it “ aLpha ”--the not on list event procedure takes over. The entry is run through a combobox cleanup function, changing it to “Alpha”. I then am trying to use DLookup to check to see if, in fact, “Alpha” is not already on the list. (Obviously, “ aLpha” would not have been recognized by Access.) When I run this code through the debugger, the DLookup is finding values in the Counties table, but they are apparently random valid values, e.g., “Beta”.
I want DLookup to tell me if there is already an entry in the Counties table (e.g., Alpha) that the combobox cleanup function returns after the user’s input (e.g., Alpha).
How do I do this?
Code:
Private Sub PropertyCounty_NotInList(NewData As String, _
Response As Integer)
Dim strNewCounty As String
Dim varTemp As Variant
varTemp = DLookup("[PropertyCounties]", "Counties", _
"[PropertyCounties]=" & (strNewCounty = ComboboxCleanUp(NewData)))
If strNewCounty = varTemp Then
Me.Undo
MsgBox "This county is already in the list.", _
vbOKOnly, "Duplicate County Found"
Exit Sub
ElseIf MsgBox("Add " & strNewCounty & " to list?", vbYesNo, "Add a new county?") = vbYes Then
Dim db As Database
Dim rst As DAO.Recordset
Set db = CurrentDb()
Set rst = db.OpenRecordset("Counties")
rst.AddNew
rst!PropertyCounties = strNewCounty
rst.Update
Response = acDataErrAdded
rst.Close
Me.PropertyCounty.Value = strNewCounty
Me.PropertyCounty.RowSource = Me.PropertyCounty.RowSource
'Sometimes the requery does not work so I use the line above
'for a forced requery
Response = acDataErrContinue
Else
Response = acDataErrContinue
Me.Undo
MsgBox "Use a county that is already in the list.", _
vbOKOnly, "A new county will not be added."
End If
End Sub
Public Function ComboboxCleanUp(ByVal strTemp As String) As String
'Used with all comboboxes on control
strTemp = LTrim(RTrim(strTemp))
'Leading and following spaces stripped
strTemp = StrConv(strTemp, vbProperCase)
'First letter capitalized
ComboboxCleanUp = strTemp
'Cleaned-up string replaces old string
End Function
Posting Permissions
- You may not post new threads
- You may not post replies
- You may not post attachments
- You may not edit your posts
-
Forum Rules
|
|