NotInList Event + Formatting = Problems.

[funger]

Hi!
I have a dropdown that holds a list of dates. I want this list to all be formatted the same: mm/dd/yyyy.

The problem arises when someone enters m-d-yyyy... Because the NewData value in the NotInList event does not follow the format of the data in the list, the code crashes.

Can anyone give any ideas? Thanks in advance!

-Jim

 

[apkohn]

I had a similar issues, in that I wanted to select a record from a drop down list. The numbering convention is for 4 digit numbers (ie 0001, 0002 etc..), but want users to be able to type 1, 2 3 etc... as well.

As such, this is what I did:
Private Sub cmbGoTo_NotInList(NewData As String, Response As Integer)
If IsNumeric(NewData) Then 'Check that the new data is a number, and not garbage text
If DCount("*", "tblTableName","[ActionID] = " & NewData) Then 'check that a matching record exists
'Change the format, to that intended
cmbGoTo.Text = Format(NewData, "0000&quot
Response = acDataErrContinue
End If
End If
End Sub

Now, from the above, if NewData isn't numeric, then, the acDataErrContinue isn't hit, and so the "Not In List" error box still appears.

You could modify the above to change IsNumeric to IsDate, and the format statement to format(NewData,"mm/dd/yyyy&quot. Have a play around, and I hope it works.