I have a combobox whose rowsource is a union query. This query
displays a person's name in "lastname, firstname" format and in
"firstname lastname" format. The query results look like this:

Mouse, Mickey
Mickey Mouse

When a person is added, the querys' underlying recordset is updated in
the NotInList event. I can't figure out how to refresh the combobox
to display the new person. I get the standard error message that the
item is not in the list. Here's my code:

....<in the NotInList event>...
With rst
.AddNew
!FirstName = strFirstName
!MiddleInitial = strMiddleInitial
!LastName = strLastName
.Update
End With

Response = acDataErrAdded

I have to refresh the query somehow so that it displays in the combo
box. If I add the code:

Me.ComboBox.Requery

I get the standard error message that it must be saved.

Maybe there's a better way to do this? Thanks for any help or advice.

On Tue, 4 Mar 2008 11:09:22 -0800 (PST), EManning
<manning_news (AT) hotmail (DOT) com> wrote:

Your code, minus the Requery, looks correct. Perhaps you have On Error
Resume Next and you are suppressing errors?

-Tom.

The way it works for me is typing:

ComboBox.Requery

I don't use the me. part because im running the code in the form
already.

Instead of using the requery on the combo box, try requerying the
From.

CurrentForm.Requery

- GL


On Mar 5, 1:37*am, Tom van Stiphout <no.spam.tom7... (AT) cox (DOT) net> wrote:

On Mar 4, 11:37*pm, Tom van Stiphout <no.spam.tom7... (AT) cox (DOT) net> wrote:
No, no error suppression. But I did discover something a little
strange. Like I said yesterday, the combobox displays a name in two
formats: "LastName, FirstName MiddleInitial" and "FirstName
MiddleInitial LastName". I would like the user to be able to enter a
new name in either format. If I enter a new name like "Duck, Donald
D", then the event works fine. But if I enter "Donald D Duck", then I
get the error message that the item is not in the list. I have coding
to parse thru the new name and separate the name into last, first, and
middle. Here's the code:

Private Sub cboParticipant_NotInList(NewData As String, Response As
Integer)
On Error GoTo cboParticipant_NotInList_Error

.....<other coding>...

lngCommaFound = InStr(1, NewData, ",")
If lngCommaFound <> 0 Then
' A comma was found, so assume that the first word entered
is the last name. Beyond the comma is the first
' name and middle initial.
strLastName = Mid(NewData, 1, lngCommaFound - 1)

lngBlankFound = InStr(lngCommaFound + 2, NewData, " ")
strFirstName = Mid(NewData, lngCommaFound + 2,
lngBlankFound - (lngCommaFound + 2))
strMiddleInitial = Mid(NewData, lngBlankFound + 1)
Else
' No comma found, assume first name entered first. Assume
no middle initial. If it's there then it will
' be put into the last name field and the user will have to
separate it out in the call log.
lngBlankFound = InStr(1, NewData, " ")
strFirstName = Mid(NewData, 1, lngBlankFound)
strLastName = Mid(NewData, lngBlankFound + 1)
End If

With rst
.AddNew
!FirstName = strFirstName
!MiddleInitial = strMiddleInitial
!LastName = strLastName
.Update
End With

Response = acDataErrAdded

It's the "Else..." coding that is apparently causing the error. I've
followed it thru with debug, the variables are being stored correctly,
the recordset update and acDataErrAdded is being done. I did notice
that the entry does get added to the recordset after the error
displays and if I close the form and reenter then the entry is in the
combobox. So it appears to be a refresh issue but any attempt to
refresh causes an error.

Thanks for your reply, Tom.

On Mar 5, 8:57*am, Guillermo_Lopez <g.lo... (AT) iesdr (DOT) com> wrote:
Any attempt to requery the combobox gives me an error that the field
must be saved, whether or not the "Me." is used. The form is unbound
so requerying it wouldn't do any good. Thanks for replying.

EManning wrote:
Here's some code I wrote for 2 comboxes. I didn't have a recordsource
for the form using the code for Combo0. I added a recordsource for the
code in Combo3.

'Combo0_NotInList works just fine.
Private Sub Combo0_NotInList(NewData As String, Response As Integer)
Dim rst As Recordset
Set rst = CurrentDb.OpenRecordset("Table1", dbOpenDynaset)

If MsgBox("Make this a new entry?", vbYesNo, "Confirm Add") = vbYes
Then
rst.AddNew
rst!TextF = NewData
rst.Update
Response = acDataErrAdded
Else
Response = acDataErrContinue
End If
rst.Close
Set rst = Nothing
End Sub

'Combo3_NotInList works just fine.
'Add record by entering firstname space lastname
'I use a query for the combo's rowsource. The combo has 2 columns; ID
'and Fullname. Fullname is a concatenation of first and last names.
'The SQL is
'SELECT DISTINCTROW Table1.ID, [FirstName] & " " & [LastName] AS
FullName FROM Table1;

Private Sub Combo3_NotInList(NewData As String, Response As Integer)
Dim rst As Recordset
Set rst = CurrentDb.OpenRecordset("Table1", dbOpenDynaset)
Dim sF As Variant
Dim sL As Variant
Dim iP As String
iP = InStr(NewData, " ")
If iP > 0 Then
sF = Left(NewData, iP - 1)
sL = Mid(NewData, iP + 1)
Else
sF = NewData
End If

If MsgBox("Not in list. Make this a new entry?", vbYesNo, "Confirm
Add") = vbYes Then
rst.AddNew
rst!FirstName = sF
rst!LastName = sL
rst.Update
Response = acDataErrAdded
Else
Response = acDataErrContinue
End If
rst.Close
Set rst = Nothing
End Sub

Fur
http://www.youtube.com/watch?v=BPv0qCg4so8

On Mar 5, 2:58*pm, Salad <o... (AT) vinegar (DOT) com> wrote:
Thanks for your reply, Salad. Here is my complete code for the
NotInList event. Unless I'm just overlooking it, it appears to be the
same as yours:

Private Sub cboParticipant_NotInList(NewData As String, Response As
Integer)
On Error GoTo cboParticipant_NotInList_Error

' Participant is not in database. Create a record for them in
tblParticipants.

Dim lngCommaFound As Long
Dim lngBlankFound As Long
Dim strLastName As String
Dim strFirstName As String
Dim strMiddleInitial As String

strMsg = "Do you wish to add them?"
If MsgBox(strMsg, vbYesNo, conQuote & NewData & conQuote & " not
in database.") = vbNo Then
Me.cboParticipant.Undo
Response = acDataErrContinue
Else
lngCommaFound = InStr(1, NewData, ",")
If lngCommaFound <> 0 Then
' A comma was found, so assume that the first word entered
is the last name. Beyond the comma is the first
' name and middle initial.
strLastName = Mid(NewData, 1, lngCommaFound - 1)

' lngBlankFound could be zero if something like "Duck,
Donald" and no middle initial was entered.
lngBlankFound = InStr(lngCommaFound + 2, NewData, " ")
If lngBlankFound = 0 Then
lngBlankFound = Len(NewData) + 1
End If

strFirstName = Mid(NewData, lngCommaFound + 2,
lngBlankFound - (lngCommaFound + 2))
strMiddleInitial = Mid(NewData, lngBlankFound + 1)
Else
' No comma found, assume first name entered first. Assume
no middle initial. If it's there then it will
' be put into the last name field and the user will have to
separate it out in the call log.
lngBlankFound = InStr(1, NewData, " ")
strFirstName = Mid(NewData, 1, lngBlankFound)
strLastName = Mid(NewData, lngBlankFound + 1)
strMiddleInitial = " "
End If

Set dbs = CurrentDb
Set rst = dbs.OpenRecordset("tblParticipants", dbOpenDynaset)

With rst
.AddNew
!FirstName = strFirstName
!MiddleInitial = strMiddleInitial
!LastName = strLastName
.Update
End With

Response = acDataErrAdded

rst.Close
dbs.Close
Set rst = Nothing
Set dbs = Nothing
End If

cboParticipant_NotInList_Resume:
Exit Sub

cboParticipant_NotInList_Error:
EmailErrorMsg "An error occured in frmStudiesDataEntry -
cboParticipant_NotInList" & vbCrLf & vbCrLf & _
Err.Number & " " & Err.Description
Resume cboParticipant_NotInList_Resume


Like I said before, entering something like "Duck, Donald D" works
just fine. But entering "Donald D Duck" causes an item-not-in-list
error. It's like the name must have the comma or it won't work.
"Donald D Duck" does get entered into the table but not without the
error display. Should I just ignore the error and go on? I'm open to
a better way of doing this if you have a suggestion.

EManning wrote:

Have you stepped thru the code starting at the Else statement? Are the
values getting stored to the variables correctly? Could lngBlankFound
be 0? Turn off your error trapping as well while debugging. Throwing
mud at wall, are the field sizes OK in the table?

You could make a name like
Blow, Joe A
into
Joe A Blow
and using Split()

If 2 elements are returned you have a first and last name. If 3, you
have a middle initial.

On Mar 5, 5:48*pm, Salad <o... (AT) vinegar (DOT) com> wrote:
Yep, followed thru with debug, all the values are stored correctly.
I've added code to handle lngBlankFound if zero. Field sizes are OK
in the table. It still seems to depend on the comma being in the new
entry. Is there a way to capture the value of combobox and reformat
it to a "lastname, firstname.." format before it hits the NotInList
event?

Thanks for your help, Salad, and anyone else who replies.

EManning wrote:

OK. I think I got it. I was using Spaces and my query source was
First & " " & Last. I should have used an example of yours.

When I changed it to look at commas and update I got errors as well.

What I did was change the bound column property from 1 (ID), to the
Fullname field 2. I then ported the code to my BeforeUpdate event and
updated.

In the AfterUpate event I put a Requery. I didn't bother to find the
name...I know you can figure that stuff out. Also, I only looked for
comma in name, not spaces.

I basically think it's the comma in the RowSource that was the problem.

Private Sub Combo6_BeforeUpdate(Cancel As Integer)
Dim rst As Recordset
Set rst = CurrentDb.OpenRecordset("Table9", dbOpenDynaset)
Dim sF As Variant
Dim sL As Variant
Dim sS As Variant

Dim iP As String
iP = InStr(Me.Combo6, ", ")
If iP > 0 Then
sF = Left(Me.Combo6, iP - 1)
sL = Mid(Me.Combo6, iP + 2)
Else
sF = Me.Combo6
End If
sS = "FirstName = '" & sF & "'"
If sL > "" Then
sS = sS & " And LastName = '" & sL & "'"
End If
rst.FindFirst sS
If rst.NoMatch Then
If MsgBox("Not in list. Make this a new entry?", vbYesNo,
"Confirm Add") = vbYes Then
rst.AddNew
rst!FirstName = sF
rst!LastName = sL
rst.Update
Else
Cancel = True
End If
End If
rst.Close
Set rst = Nothing

End Sub

Unholy
http://www.youtube.com/watch?v=kuS1Jl2Y6TU