Results 1 to 12 of 12
  1. #1
    3 Star Lounger
    Join Date
    Feb 2001
    Posts
    369
    Thanks
    2
    Thanked 1 Time in 1 Post

    Not in list problem (2000sp3)

    I have a combo box based on a table with fields fullname, firstname, and id, with column 1 (displayed) of [firstname] & ":- " & [fullname] for ease of finding people (in this environment, names are ONLY ever written as surname midname firstname, and many surnames are similar. Combobox is limited to list with a not in list event which brings up a form to add a new member, including generating new id and finding firstname. The problem I have is that new names are entered as "surname midname firstname", not "firstname:- surname midname firstname", in order to involve problems between autocomplete and diacritical marks (the font is not unicode and requires 2 characters for some letters, leading to duplication with autocomplete, which is necessary for normal use).

    The new record is added successfully, but despite adding a line to modify the contents of the combo box to the not in list code (below), on returning to the form I still receive the error message that the item entered is not in the list, occasionally accompanied by the nonbreaking error message in the immediate window "Microsoft access cannot perform this action at this time, please stop the code and try again. ". I have tried the following workarounds: basing the combo box on a union query of firstnames and fullnames, which was (predictably) a failure in every sense, changing the limittolist property in the not in list code (not allowed), and changing the combobox to have 2 columns containing firstname and fullname, which has the same problems as above.

    Is there any way to do what I want via the not in list event?

    A final workaround would be to have a command button to add new data, rather than use not in list, but I hate asking my data entry folks to do more work (especially when it comes to shifting between keyboard and mouse).

    Anyway, here's the not in list code:

    Private Sub Form_filler_NotInList(NewData As String, Response As Integer)
    Dim usrResponse As String
    usrResponse = MsgBox("The person you have entered is not in the list. Do you want to add them?", vbYesNo)
    If usrResponse = vbYes Then
    DoCmd.OpenForm "AddStaffName", , , , , acDialog, NewData & ":" & Me.Name 'Passes form name and argument separator as well as newdata to add member form
    If IsLoaded("AddStaffName") Then 'IsLoaded is user defined function to check if form loaded (add member form becomes invisible once its OK button clicked)
    Response = acDataErrAdded
    DoCmd.Close acForm, "AddStaffName", acSaveYes
    Me![Form filler] = FindFirstNameCCDB(NewData) & ":- " & NewData
    Else
    Response = acDataErrContinue
    End If
    Else
    Me![Form filler] = Null
    Me![Form filler].SetFocus
    End If

    Thanks for your thoughts

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Not in list problem (2000sp3)

    Why don't you just show the full name (firstname midname lastname) in the combo box? Adding firstname & ":- " before it doesn't provide any extra information. You won't have to set the value of [form filler] in that case, and the NotInList event will work correctly.

    If you insist on keeping it as it is, I'm afraid you will have to resort to your workaround (a command button). I don't think you can get the NotInList event to handle it.

    Note: using acSaveYes as third argument to DoCmd.Close is dangerous. It doesn't mean that the record is saved, but that the design of the object (the AddStaffName form in this case) will be saved. This either locks the database for exclusive use, or raises an error if more than one user is active. I suggest replacing acSaveYes by acSaveNo. The record will be saved automatically.

  3. #3
    3 Star Lounger
    Join Date
    Feb 2001
    Posts
    369
    Thanks
    2
    Thanked 1 Time in 1 Post

    Re: Not in list problem (2000sp3)

    Vietnamese names are never written that way in Vietnam, and it would cause much confusion (see original post), but due to the high frequency of certain surnames here (not to mention the fact that many people just write their firstname on the form) data entry is quicker and easier if autocomplete works with the first name.

    Thanks for the tip on the save constant - I usually change them all to no once I've finished developing a section, but I hadn't thought about the locking issues.

  4. #4
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Not in list problem (2000sp3)

    I don't know how to make the combo box work with data entry in a different form from the normal display, so unless somebody else comes up with a solution, I would recommend using a separate command button for adding a new person. You can assign an accelerator key to make it possible to invoke the command button from the keyboard.

  5. #5
    Plutonium Lounger
    Join Date
    Dec 2000
    Location
    Sacramento, California, USA
    Posts
    16,775
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Not in list problem (2000sp3)

    You're going to need to undo the combobox first in order to avoid getting trapped in the NotINList event.
    Charlotte

  6. #6
    3 Star Lounger
    Join Date
    Feb 2001
    Posts
    369
    Thanks
    2
    Thanked 1 Time in 1 Post

    Re: Not in list problem (2000sp3)

    Aaargh! This is driving me mad.

    So, choose command button option, and suddenly everything is weird. I can't programmatically shift the focus to the command button. I think Hans looked at this form ages ago trying to help me with weird focus events, and found that for some bizarre reason, some (not all) attempts to set focus on this tab of this form went via the first control on the tab, which needs code behind it to control the form, and that messes everything up. Knowing that this problem is bizarre, I take the easy way out and just add a flag to skip the code behind the first control. Now I don't get the cannot pass focus error, but the focus doesn't pass. Instead, I end up back at the combo box with the list popped down, and, this is the really bizarre thing, if I hit enter, the addnewstaff form opens! On a few occasions (usually when I've put a break point in and the focus has shifted to the VB editor and then back to the form, the focus passes to the button as intended, but activating button leads either to instant error message that new staff member wasn't added, or to running the code but not stopping at any breakpoints to allow me to work out what's going on, and also throwing up this odd, non breaking error: "Microsoft Access cannot complete this operation. You must stop the code and try again. 29068 ". I've tried undoing the combobox and setting it to null and trying to cancel the notinlist event (not sure if that's possible), but all to no avail.

    I've tried rebuilding the form by copying controls onto new form and copying code over, but it doesn't help. Have tried repairing database, to no avail, and even tried running the /decompile switch, which occasionally worked for me when access97 went weird, but I don't know if it does anything in 2000.

    I've attached the form in question, in case anyone's feeling really nice, though I've had to cut some subforms out to get it in under 100kb. The box in question is "Form filler" (label:- Ba
    Attached Files Attached Files

  7. #7
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Not in list problem (2000sp3)

    It's hard to test anything, since Sorted Table, the record source of the PhuocLongComplete form, isn't present in the database as posted. If I set the record source to Clinical_Case_Data for testing, I still get lots of errors (not just about the missing subforms)

  8. #8
    Plutonium Lounger
    Join Date
    Dec 2000
    Location
    Sacramento, California, USA
    Posts
    16,775
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Not in list problem (2000sp3)

    Sorry, Mark, but I never do it this way, so I'm having a hard time seeing what you want to accomplish. If I were doing it, I would add the name to the list in code then pop up the form with the name already filled in (and the record saved) and let them complete the rest of the information. Instead of using DoCmd.CancelEvent, I would ask if they want to add the person. If they did not want to, I would set Response = acDataErrContinue and then Me![form filler].Undo. If they wanted to add it, I would set Response = acDataErrAdded and execute SQL to add that record to the Staff table. Then I would popup a form and pass it the newly created record PK in the WhereCondition of the DoCmd.OpenForm so they could add the department, etc.
    Charlotte

  9. #9
    3 Star Lounger
    Join Date
    Feb 2001
    Posts
    369
    Thanks
    2
    Thanked 1 Time in 1 Post

    Re: Not in list problem (2000sp3)

    Sorry.

    I can't get it to less than 100k with all essentials present, so I'll send 2 messages. This one has the forms, and the next will have the modules and tables. Put them together and there should be no errors.
    Attached Files Attached Files

  10. #10
    3 Star Lounger
    Join Date
    Feb 2001
    Posts
    369
    Thanks
    2
    Thanked 1 Time in 1 Post

    Re: Not in list problem (2000sp3)

    part 2
    Attached Files Attached Files

  11. #11
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Not in list problem (2000sp3)

    I managed to get it working partly, but there are still problems. Clicking my way through 67 error messages in a row because tables are missing is not my idea of fun, so I'm putting this aside for the moment. I'll get back to it later.

  12. #12
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Not in list problem (2000sp3)

    I think I got it to work now.

    1. PhuocLongComplete form

    1a. Form_Filler_NotInList event
    Remove DoCmd.CancelEvent, it serves no purpose.

    1b. btnAddStaff_KeyPress event
    Remove the entire event handler, it interferes with the btnAddStaff_Click event.

    1c. btnAddStaff_Click event
    You must set the value of Form_Filler to the new staff ID. Requerying the combo box will fill in the name automatically. Complete code:

    Private Sub btnAddStaff_Click()
    Dim UsrResponse As Byte, strNewName As String
    strNewName = Me!txtTmpNameStore
    DoCmd.OpenForm "AddStaffName", , , , , acDialog, strNewName & ":" & Me.Name
    If IsLoaded("AddStaffName") Then
    Me.Form_filler.Requery
    Me.Form_filler = Forms!addstaffname!ID
    DoCmd.Close acForm, "AddStaffName", acSaveNo
    Else
    UsrResponse = MsgBox("You have not added the staff member. Are you sure you " & _
    "want to leave form filler blank?", vbYesNo)
    If UsrResponse = vbYes Then
    Me!txtTmpNameStore = Null
    Else
    Me.Form_filler = Null
    Me.Form_filler.SetFocus
    End If
    End If
    End Sub

    2. AddStaffName form

    2a. New ID control
    Add a text box bound to the ID field; set Visible to No. It is needed to set the value of Form_Filler.

    2b. cmdOK_Click event
    DoCmd.Save acForm, Me.Name is dangerous, it will lead to conflicts and database corruption in a multi-user environment. Replace it by an instruction to save the record. And remove one of the two occurrences of Me.Visible = False, it is superfluous. Complete code:

    Private Sub cmdOK_Click()
    On Error GoTo ERR1
    Me!FirstName = FindFirstNameCCDB(Me!txtStaffMember)
    RunCommand acCmdSaveRecord
    Me.Visible = False
    Exit Sub
    ERR1:
    Debug.Print Err.Description; " "; Err.Number
    Resume Next
    End Sub

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •