Re: Requerying ComboBox in NotInList event

EManning wrote:

On Mar 5, 2:58 pm, Salad <o...@xxxxxxxxxxx> wrote:

EManning wrote:

On Mar 5, 8:57 am, Guillermo_Lopez <g.lo...@xxxxxxxxx> wrote:

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...@xxxxxxx> wrote:

On Tue, 4 Mar 2008 11:09:22 -0800 (PST), EManning

<manning_n...@xxxxxxxxxxx> wrote:

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

-Tom.

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.- Hide quoted text -

- Show quoted text -- Hide quoted text -

- Show quoted text -

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.

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

Furhttp://www.youtube.com/watch?v=BPv0qCg4so8- Hide quoted text -

- Show quoted text -


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.


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.

.

Relevant Pages

  • Re: Requerying ComboBox in NotInList event
    ... Dim rst As Recordset ... Set rst = CurrentDb.OpenRecordset ... Dim lngBlankFound As Long ... It's like the name must have the comma or it won't work. ...
    (comp.databases.ms-access)
  • Re: Requerying ComboBox in NotInList event
    ... Any attempt to requery the combobox gives me an error that the field ... Response As Integer) ... Dim rst As Recordset ... Set rst = CurrentDb.OpenRecordset ...
    (comp.databases.ms-access)
  • Re: Dbl-Click to add Choice to Combo Box
    ... > Thanks Steve! ... > Dim strsql As String, ... I would move "Response = acDataErrAdded" to be the next line after ... Set rst = db.OpenRecordset ...
    (microsoft.public.access.forms)
  • RE: Help With "NotInList"
    ... account number, it lets me add it but the rest of the fields fill on the form ... Dim rs As DAO.Recordset ... Response As Integer) ... Set rst = Me.RecordsetClone ...
    (microsoft.public.access.formscoding)
  • disable default message (NotInList)
    ... my problem is the default access message still appear if i click NO ... Response As Integer) ... Dim rst As DAO.Recordset ... Set rst = db.OpenRecordset ...
    (microsoft.public.access.forms)