Re: Requerying ComboBox in NotInList event

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.



.

Relevant Pages

  • Re: 11-string recuerdos- where on the bell curve?
    ...   Yes, a couple of the ornaments in the first section did not come out ... which there is not room for improvement, slurs included, but I do not ... This is my recording of Recuerdos done on six string several years ... a heavier bass? ...
    (rec.music.classical.guitar)
  • Re: Export multiple versions of a query to separate Excel files
    ... If I try to run the code I can select the folder that I want to create ... Dim qdf As DAO.QueryDef ... Dim strSQL As String, strTemp As String, strMgr As String ...
    (microsoft.public.access.externaldata)
  • Re: Using String for new List name
    ...     for line in infile: ... and aclS represents "access control list String." ... My quest, if you will, is to create a list of syslog entries, each ... Since ACL rules can be written with a range of granularity, ...
    (comp.lang.python)
  • Re: Macro to delete rows until a row contains a certain text strin
    ... Dim rngStartCell As Range ... Dim strToFindStart As String ...     'Test if any rows between first and last identifiers ... starting cell to one row before the row containing "KM In". ...
    (microsoft.public.excel.programming)
  • Re: Excel Chart Macro Issue
    ... If the issue is string length, why would forming a large string at the end ... Sometimes this means setting up a summary range that the chart uses. ... Dim rDataX As Range ...   Dim wsData As Worksheet ...
    (microsoft.public.excel.programming)