Access gives a warning for NotInList Event with a splitted field

[Banana]

I'm banging my head against this one.

I have a combobox that allows users to select a volunteer's name from list. They may add a new volunteer by typing in first name and last name, which is the only thing we need to know about the volunteers, and the combobox get split into two corresponding fields for table tblVolunteer.

The code works *perfectly*, yet Access fires a default "You cannot add this to list" warning. I've tried several ways of juggling it to get it disabled or to requery the table (even though it isn't necessary) to no avail.

However, using RuralGuy's diagnostic tool as below

If IsNull(DLookup("FirstName", "tblVolunteers", _
            "[FirstName] = '" & NewData & "'")) Then
            MsgBox "[" & NewData & "] is NOT in the Table yet!"
        Else
            MsgBox "[" & NewData & "] IS in the Table!"
        End If

I can tell you that none of bajillion commands I've tried will actually ensure that the NewData is updated to the table actually does update it.

However, when I omit out the parts telling to split the NewData into two components, the code works perfectly and this time without any warning from Access. Put it back in, and it fires a warning. Either way, combobox has a new record added and automatically inserted.

Here is the code, san Rural Guy's diagnostic tool for clarity:

Private Sub cmbVolunteerID_NotInList(NewData As String, Response As Integer)

'The code is provided by SJ McAbney, from access-programmers.co.uk/forums
'It can be found at http://www.access-programmers.co.uk/forums/showthread.php?t=98091

' provide text constants to reduce text later and allow for faster execution
    ' due to added speed from the compilation of constants
    Const Message1 = "The data you have entered is not in the current selection."
    Const Message2 = "Would you like to add it?"
    Const Title = "Unknown entry."
    Const NL = vbCrLf & vbCrLf
    
    ' database and recordset object variables
    Dim db As DAO.Database
    Dim rs As DAO.Recordset
    Dim Splitter As Variant
    Splitter = Array(2, 1)
    ' show message box and evaluate if the user has selected Yes or No
    If MsgBox(Message1 & NL & Message2, vbQuestion + vbYesNo, Title) = vbYes Then
        ' open a connection to the current database
        Set db = CurrentDb
        Set rs = db.OpenRecordset("tblVolunteers")
        ' using the recordset object
            
        'creates an array of last name and first name
        Splitter = (Split([NewData], " "))
        
        With rs
            .AddNew ' prepare to add a new record
            .Fields("FirstName") = Splitter(0)
            .Fields("LastName") = Splitter(1) ' add unfound data into field
            .Update ' update the table
            .Close ' close the recordset object
        End With
        Response = acDataErrAdded ' confirm record added
    Else
        Me.cmbVolunteerID.Undo ' clear the entry in the combobox
        Response = acDataErrContinue ' confirm the record is not allowed
    End If

End Sub

Any suggestion would be greatly appreciated.

 

[bonekrusher]

In the properties for the combo box, is the "Limit to List" Yes or No?

It should be marked as "no"

Bones

 

[RuralGuy]

Actually BoneKrusher, if the LimitToList property is not set to yes, then the OnNotInList event never fires.

Banana, try commenting out the Splitter = Array(2, 1) line. It is not necessary anyway.

 

[bonekrusher]

Ahhh I stand corrected... Thx

 

[RuralGuy]

No problem Bones. I just wanted to keep the record straight.

 

[raskew]

BoneKrusher (when you turn 14 maybe you'll come up with a more appropriate moniker):

Do you read and write? No disrespect to RG intended, but do you see that the posting that you attribute to RG was actually posted by SJ McAbeney.

Bob

 

[Banana]

Banana, try commenting out the Splitter = Array(2, 1) line. It is not necessary anyway.

I'll give it a try when I get back to work this Monday. Thanks.


Also, I thought of something- could it be because the NewData is splitted, and stored as two fields in the multi-column combobox, when Access requery the list, it's looking for "Firstname Lastname" (e.g. the original NewData prior to splitting), not "FirstName | LastName" (the record with splitted fields), thus giving out a warning message? (Though it does somehow manage to get the newly added record automatically selected as expected....)

 

[RuralGuy]

That could definately be a problem. Why not add a field to the query that concantenates the two names with a space and let it look for that field?

 

[Banana]

or could I say something like

NewData= DMax("VolunteerID", "tblVolunteers", "VolunteerID")

just before the acDataErrAdded statement?

Also,

That could definately be a problem. Why not add a field to the query that concantenates the two names with a space and let it look for that field?

I'm not sure where that field would go; did you mean I'd make a new field in the table that contains both firstname/lastname?

 

[RuralGuy]

Not in the table but in a query of the table. The field would *only* exist in the query.

 

[Banana]

Success.

Making the recordsource of the combobox a contenated query of two names made Access STFU.

Funny though, the diagnostic tool says say it's not in the table. But I think it's probably me that got it wrong, not the tool.

Oh, I just realized- Is it possible to use dlookup to check the query stored in the record or do I have to create a standalone query to be able to use dlookup to check for data integrity after the data has been added?

 

[RuralGuy]

You can use DLookup() on your query with the concantenated.

 

[Banana]

I thought dlookup couldn't handle concantenated? a Sample code?

 

[RuralGuy]

I am not aware of any concatenation problem with DLookUp().
IsNull(DLookup("YourField", "YourQuery", "[YourField] = '" & NewData & "'"))

Where NewData is in the format of "FirstName LastName"

 

[Banana]

I assumed that I'd need a standalone query, that I cannot retrieve query I created in controlsource of the combobox. So I went and made me a query and it works great.

WRT concatenation, I read in other threads that you cannot write Dlookup("Field1" & " " "Field2", "Table", "Field1" & " " "Field2"=NewData.

Nonetheless, thanks for input!