Trim() and "On Not in List" Event Procedure

[chelm]

I've defined the following function as an event procedure in the On Not in List:

Private Sub cboLastname_NotInList(NewData As String, Response As Integer)
Dim rs As Recordset
Dim db As Database
If MsgBox(UCase(NewData) & " is not in the list of Authors." & vbNewLine & "Enter it as new Author?", vbYesNo + vbQuestion, "Confirm.") = vbYes Then
    Set db = CurrentDb
    Set rs = db.OpenRecordset("tblCitationAuthor")
    rs.AddNew
    rs!CitationAuthorLastName = NewData
    rs.Update
    Response = acDataErrAdded
    rs.Close
    Set db = Nothing
Else
    Response = acDataErrContinue
End If
End Sub

The problem is that it does not trim the string before comparing the not in list. Is there a way to trim before the even procedure runs? To clarify what I mean it currently evaluates "Bob" and "Bob " as two different strings, I'd like to use the trim function before the check on not in list.

Any advice is greatly appreciated.

 

[namliam]

NO... it simply works this way.

You have to trap "Bob " and see to make sure it really doesnt exist as the "trimmed version" either.

Also you SHOULD disambiguate your code for future compatibilty:
Dim rs As DAO.Recordset
Dim db As DAO.Database

Search for "disambiguate" on the forum to find more datails ...

 

[chelm]

I get that I need to check before the On Not in List event procedure, the issue is that I can't figure out how to do it. Everything I've tried doesn't work. (I'm sorry if I wasn't clear before.)

I'll look into the disambiguate, thanks for the tip.

 

[namliam]

You cannot check before the event is triggered. So you would have to look at the trimming inside the event itself.

Set db = CurrentDb
Set rs = db.OpenRecordset("tblCitationAuthor")
rs.Findfirst "CitationAuthorLastName = """ & trim(NewData) """"
if rs.nomatch then ' Trimmed value not found

 

[chelm]

I'm pretty new to VBA so are you saying to do something like this:

Private Sub cboLastname_NotInList(NewData As String, Response As Integer)
Dim rs As Recordset
Dim db As Database
Set db = CurrentDb
Set rs = db.OpenRecordset("tblCitationAuthor")
rs.Findfirst "CitationAuthorLastName = """ & trim(NewData) """"
If rs.nomatch Then
    If MsgBox(UCase(NewData) & " is not in the list of Authors." & vbNewLine & "Enter it as new Author?", 	vbYesNo + vbQuestion, "Confirm.") = vbYes Then
    	Set db = CurrentDb
    	Set rs = db.OpenRecordset("tblCitationAuthor")
    	rs.AddNew
    	rs!CitationAuthorLastName = NewData
    	rs.Update
    	Response = acDataErrAdded
    	rs.Close
    	Set db = Nothing
    Else
    	Response = acDataErrContinue
    End If
Else

End If
End Sub

How then do I return the ID of the one already in the database?

 

[JBurlison]

Close i think this might work

Private Sub cboLastname_NotInList(NewData As String, Response As Integer)
Dim rs As Recordset
Dim db As Database
Set db = CurrentDb
Set rs = db.OpenRecordset("tblCitationAuthor")
rs.Findfirst "CitationAuthorLastName = """ & trim(NewData) """"
If rs.nomatch Then
   If MsgBox(UCase(NewData) & " is not in the list of Authors." & vbNewLine _
& "Enter it as new Author?", 	vbYesNo + vbQuestion, "Confirm.") = vbYes Then
    	Set db = CurrentDb
    	Set rs = db.OpenRecordset("tblCitationAuthor")
  With rs
    	rs.AddNew
    	rs.Fields("CitationAuthorLastName") = NewData
    	rs.Update
  End With
    	Response = acDataErrAdded
    	rs.Close
    	Set db = Nothing
    Else
    	Response = acDataErrContinue
    End If
Else

End If
End Sub

 

[chelm]

The code doesn't work, it still allows both "bob" and "bob "

 

[namliam]

Private Sub cboLastname_NotInList(NewData As String, Response As Integer)
Dim rs As Recordset
Dim db As Database
Set db = CurrentDb
Set rs = db.OpenRecordset("Select * from tblCitationAuthor")
rs.Findfirst "CitationAuthorLastName = """ & trim(NewData) & """"
If rs.nomatch Then
    If MsgBox(UCase(NewData) & " is not in the list of Authors." & vbNewLine & "Enter it as new Author?", 	vbYesNo + vbQuestion, "Confirm.") = vbYes Then
    	Set db = CurrentDb
    	Set rs = db.OpenRecordset("tblCitationAuthor")
    	rs.AddNew
    	rs!CitationAuthorLastName = NewData
    	rs.Update
    	Response = acDataErrAdded
    	rs.Close
    	Set db = Nothing
    Else
    	Response = acDataErrContinue
    End If
Else
msgbox trim(NewData) & " without spaces has been found!"
End If
End Sub

How then do I return the ID of the one already in the database?

Above code should work, tho I changed it a little bit with fixing one or two mistakes.

The recordset is a records, it contains all the Columns of your table.
rs!YourID should return your ID.
Why would you want to return the ID of the one in the DB? The user is not going to care about the ID is he?

 

[chelm]

Thanks for your help. The form I'm working with has many fields, so the code above is for one of many fields. What I meant by returning a value is that I need to set the field of the form equal to the trimmed value, or set it equal to the record that is already in the database. Currently the code pops up the custom message box you added "...without spaces has been found!". Then immediately pops up an error message: "The text you entered isn't an item in the list. Select an item from the list, or enter text that matches one of the listed items."

 

[chelm]

If you would like you can download an old version of this from:http://www.c-helm.com/access/
to see what I'm talking about. I modified the code to the above.
But if you copy and paste the above code and then enter a name like:
"Johnson"
and then
"Johnson "
You'll see what I mean. I also realized I have another issue with the code, maybe it's really the same issue. The issue is when you have a name like "Jackons-Smith" in the database and then you add "Jackson" later, for some reason after you tab through the form it goes back to "Jackson-Smith"? I think it maybe the same issue, the value in the field is not being set to any value when the function returns? I'm not really sure how VBA works, I'm a lot more fluent in C++ and PHP...

 

[namliam]

Adding "response = False" prevents the default message from popping up.

Adding "Me.cboLastname = rs.Key" should reset the box to the value that was found.

 

[chelm]

"Me.cboLastname = rs.Key" causes an error:

Compile Error: Method or data member not found.

 

[boblarson]

"Me.cboLastname = rs.Key" causes an error:

Compile Error: Method or data member not found.

I believe when he used rs.Key that you are supposed to substitute the key field name there, not use it explicitly.

 

[chelm]

Yikes, I can't believe I missed that... Thanks for the help.

 

[chelm]

Causing infinite loop?

So I modified the code above to the following, but I'm guessing I've messed something up. I thought it was working as it would allow me to insert new citations on dissertations that had other citations linked to them. However when I try to add a new citation to a dissertation that has none associated with it I get stuck in an infinite loop which pops up the message box asking if I would like to add it to the list.
The code:

Private Sub cboTitle_NotInList(NewData As String, Response As Integer)
Dim rs As Recordset
Dim db As Database
Set db = CurrentDb
Set rs = db.OpenRecordset("Select * from tblCitationTitles")
rs.FindFirst "CitationTitle = """ & Trim(NewData) & """"
If rs.NoMatch Then
    If MsgBox(UCase(NewData) & " is not in the list of Citation Titles." & vbNewLine & "Enter it as new Title?", vbYesNo + vbQuestion, "Confirm.") = vbYes Then
        Set db = CurrentDb
        Set rs = db.OpenRecordset("tblCitationTitles")
        rs.AddNew
        rs!CitationTitle = NewData
        Me.cboTitle = rs!CitationTitleID
        Me.Requery
        Me.cboLastname.SetFocus
        rs.Update
        Response = acDataErrAdded
        rs.Close
        Set db = Nothing
    Else
        Response = acDataErrContinue
    End If
Else
MsgBox Trim(NewData) & " has been found that does not have additional spaces.  Please select it from the list."
Response = False
Me.cboTitle = rs!CitationTitleID

End If

End Sub

When I hit no it puts me into debug and highlights the lines:

        Me.Requery
        Me.cboLastname.SetFocus

If I remove these two lines it will add the record, but then it jumps to the bottom of the form and I have to waste time navigating back to the section I'm working in.

 

[boblarson]

Why do you have this:

Response = False

in there? You should only have two Responses in there

1. with acDataErrAdded
and
2. with acDataErrContinue

Get rid of that third.

 

[chelm]

Setting it to false was suggested based on:

Thanks for your help. The form I'm working with has many fields, so the code above is for one of many fields. What I meant by returning a value is that I need to set the field of the form equal to the trimmed value, or set it equal to the record that is already in the database. Currently the code pops up the custom message box you added "...without spaces has been found!". Then immediately pops up an error message: "The text you entered isn't an item in the list. Select an item from the list, or enter text that matches one of the listed items."

Response:

Adding "response = False" prevents the default message from popping up.

 

[boblarson]

Namliam's response was slightly incorrect. The part that keeps the error from showing up is:acDataErrContinue and you already have that there. If it isn't working then you have it not coded correctly. In fact when I look at it, you are using a find first which you don't have to do. The combo already told you it was not in the list, so why are you trying to check again? Get rid of that If and the surrounding stuff and you should be good to go.

 

[chelm]

Removing the find returns me to the code I originally had. The problem with the origional code is that it doesn't trim on list comparison. It was allowing "bob", "bob " and " bob" as separate items in the list instead of trimming them. I origionally didn't think it was going to be a problem, but the kids working in the database have made hundreds of duplicates because of extra spaces. So what I need to do is find a way to trim and then check if it's in the list. The addition you are suggesting I remove is what was suggested to fix the problem.

You cannot check before the event is triggered. So you would have to look at the trimming inside the event itself.

Set db = CurrentDb
Set rs = db.OpenRecordset("tblCitationAuthor")
rs.Findfirst "CitationAuthorLastName = """ & trim(NewData) """"
if rs.nomatch then ' Trimmed value not found

My original code:

 Private Sub cboLastname_NotInList(NewData As String, Response As Integer)
Dim rs As Recordset
Dim db As Database
If MsgBox(UCase(NewData) & " is not in the list of Authors." & vbNewLine & "Enter it as new Author?", vbYesNo + vbQuestion, "Confirm.") = vbYes Then
    Set db = CurrentDb
    Set rs = db.OpenRecordset("tblCitationAuthor")
    rs.AddNew
    rs!CitationAuthorLastName = NewData
    rs.Update
    Response = acDataErrAdded
    rs.Close
    Set db = Nothing
Else
    Response = acDataErrContinue
End If
End Sub

The original code has 2 issues. The first is that it allows duplicate records because it doesn't trim. The second is that it if you add "bob" after "boblarson" is already in the database it will add "boblarson" but the field reverts to "bob" so you have to essentially enter it twice.

 

[boblarson]

Then you might be able to keep the same code but change the part where you have

Response = False

to

Response = acDataErrContinue