Re: Requerying ComboBox in NotInList event

On Mar 5, 5:48 pm, Salad <o...@xxxxxxxxxxx> wrote:
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.- Hide quoted text -

- Show quoted text -

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.
.

Relevant Pages

  • 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: Need help with looping through records to email report
    ... Dim rst As DAO.Recordset ... Dim strEmail As String, strSubject As String, strMsgBody As String ... 'Insert inline error handling to handle a cancelled report ...     Dim dbs As DAO.Database ...
    (microsoft.public.access.forms)
  • Re: Copy to Visible Cells only;Modify Code
    ... CommandButton1 with caption "Find Source Workbook" ... Private Sub CommandButton1_Click ... Dim wbSource As Workbook ...     Application.AutomationSecurity _ ...
    (microsoft.public.excel.programming)
  • Re: Mail Macro
    ... Dim sh As Worksheet ... Dim TempFilePath As String ... Dim SourceWB As Workbook ...     Dim sh As Worksheet ...
    (microsoft.public.excel.programming)
  • Re: Dynsmic Charts
    ... construct one of those ranges, if you don't know it ahead of time, you need ...   Dim rDataX As Range ... period on a chart. ...
    (microsoft.public.excel.programming)