Page 1 of 2 12 LastLast
Results 1 to 15 of 20
  1. #1
    Join Date
    Dec 2002
    Location
    Préverenges, Switzerland
    Posts
    3,740

    Angry Unanswered: not in list - going round in circles!

    i have a query (editable) that pulls some fields for a bound form.

    one field holds the FK to a table tblXX. this field sits in a combo: first column bound & zero width, second column the user-friendly string.

    i'm trying to allow users to add to the list. this can't be done "native" by access with the zero width bound column (why mr. gates made this decision is unclear!)

    so i try the following code:

    Code:
    Private Sub cboXX_NotInList(NewData As String, Response As Integer)
        Response = acDataErrContinue
        Dim msg As String
        Dim strSQL As String
        msg = NewData & " not in the database!" & vbCrLf & vbCrLf
        msg = msg & "Create a new entry?"
        If MsgBox(msg, vbQuestion + vbOKCancel, NewData & " not in list!") = vbCancel Then
            cboXX = Null
        Else
            strSQL = "INSERT INTO tblXX (FieldYY) VALUES ('" & NewData & "');"
            DoCmd.RunSQL (strSQL)
            cboXX.Requery 'aaaaaarrrgggghhhhhhhhh!
        End If
    End Sub

    Frustrated = True
    Do While Frustrated

    cant requery the combo until i save the record
    cant save the record because the combo has no value
    cant give the combo a value because NewData is not in rowsource
    cant get NewData into the rowsource until i requery
    Loop

    izy
    currently using SS 2008R2

  2. #2
    Join Date
    Dec 2002
    Location
    Préverenges, Switzerland
    Posts
    3,740
    thanks to moretools i seem to have a solution.

    replace
    cboXX.Requery

    with
    response = acDataErrAdded

    ...and it seems OK


    izy
    currently using SS 2008R2

  3. #3
    Join Date
    Dec 2002
    Location
    Préverenges, Switzerland
    Posts
    3,740
    NOPE! still stuck.

    acDataErrAdded gets thru the error msgbox but it's still the same problem:

    until i save the record i cant requery the combo
    until i requery the combo i cant save the record

    loops thru the _NotInList() code on save, generating an error when the INSERT runs a second time with the same NewData (index no dupes).

    Hmmmmmm!
    i knew there was a reason i don't normally play with bound forms.

    izy
    currently using SS 2008R2

  4. #4
    Join Date
    Feb 2004
    Posts
    22
    Try the following replacement:
    ...
    ...
    strSQL = "INSERT INTO tblXX (FieldYY) VALUES ('" & NewData & "');"
    DoCmd.RunSQL (strSQL)
    cboXX.Undo
    Response = acDataErrAdded
    'or try cboXX.Requery followed by Response = acDataErrContinue
    ...
    ...

    Since the new data is already in the table, undoing the combobox might prevent the save record requirement.

    Jack

  5. #5
    Join Date
    Mar 2004
    Location
    belgium
    Posts
    290
    I think i met this solution in sample database included with access.
    it was a sample database concerning bookkeeping, the english word for it i don´t know.

    but when you enter a (wrong/new) item of the list, it asks you if you like to add a new item to that list

    it is a combo/list with input field.

    and it is also a property setting what does the trick.

    hope it helps, ask me anyway for more detailed information if i can find it :-)))

    emiel

  6. #6
    Join Date
    Dec 2002
    Location
    Préverenges, Switzerland
    Posts
    3,740
    thanks Jack,

    i don't like the look of the following code but it seems to work.

    Code:
    Private Sub cboXX_NotInList(NewData As String, Response As Integer)
        Response = acDataErrContinue
        Dim msg As String
        Dim strSQL As String
        msg = NewData & " not in the database!" & vbCrLf & vbCrLf
        msg = msg & "Create a new entry?"
        If MsgBox(msg, vbQuestion + vbOKCancel, NewData & " not in list!") = vbCancel Then
            cboXX = Null
        Else
            strSQL = "INSERT INTO tblXX (FieldYY) VALUES ('" & NewData & "');"
            DoCmd.RunSQL (strSQL)
            Response = acDataErrAdded
            cboXX.Undo
            SaveEdits                    'a sub() to do just that
            cboXX.Requery
            msgbox cboXX.column(theFriendlyStringColumn) 'is empty!
        End If
    End Sub
    to my amazement, this exits with cboXX set & saved correctly at NewData!

    izy

    CORRECTION: cboXX is unchanged on exit, but the record is correctly updated on the next SaveEdits when the user moves off the record.
    Last edited by izyrider; 03-22-04 at 04:41.
    currently using SS 2008R2

  7. #7
    Join Date
    Sep 2003
    Location
    MI
    Posts
    3,713
    Izy,

    I ran into the same thing ... How I handled this was to make a function that performed the combobox requery. All you do is make a CALL to the function ...

  8. #8
    Join Date
    Feb 2004
    Posts
    22
    Izy,
    You do need to explicitely requery the combo box.
    The acDataErrAdded does an AUTOMATIC requery!

    Jack

  9. #9
    Join Date
    Dec 2002
    Location
    Préverenges, Switzerland
    Posts
    3,740
    WHAT!!!!!!!!!!!!!!

    i'm checking that out now.

    izy
    currently using SS 2008R2

  10. #10
    Join Date
    Dec 2002
    Location
    Préverenges, Switzerland
    Posts
    3,740
    you are sooooooo right Jack!

    where do you find any documentation on this acDataErrXxxxx stuff?

    thanks a million. izy
    currently using SS 2008R2

  11. #11
    Join Date
    Dec 2002
    Location
    Préverenges, Switzerland
    Posts
    3,740
    ...and have you got any idea why cboXX.column(1) returns empty?

    izy
    currently using SS 2008R2

  12. #12
    Join Date
    Feb 2004
    Posts
    22
    Go to the Object Browser and enter NotInList.
    Click the Help button (?) for that entry and you should see definitions for the 3 constants that are used: acDataErrContinue, acDataErrAdded and acDataErrDisplay.

    The second column returns empty because of the Undo.
    Try leaving it out now that you no longer have to explicitely requery.

    Glad to help.

    Jack
    Last edited by jackb22; 03-22-04 at 12:47.

  13. #13
    Join Date
    Oct 2003
    Location
    Roanoke, Va
    Posts
    445

    Smile

    Originally posted by izyrider
    you are sooooooo right Jack!

    where do you find any documentation on this acDataErrXxxxx stuff?

    thanks a million. izy
    A little more info.

    http://msdn.microsoft.com/library/de...ce03022000.asp
    Gregg


    DAO, ADO, SQL, Automation and anything else I can pick up.

  14. #14
    Join Date
    Dec 2002
    Location
    Préverenges, Switzerland
    Posts
    3,740
    nope!

    removing the .undo loops back into the _NotInList() a second time, runs the INSERT a second time and dies with the index/no dupes.

    ...but it works PERFECTLY with the .undo (and no .requery)

    .column(1) is displayed exactly as i want in the combo - i just can't msgbox it, but i was only doing that for debugging.

    thanks a lot Jack - this was driving me nuts. izy
    currently using SS 2008R2

  15. #15
    Join Date
    Dec 2002
    Location
    Préverenges, Switzerland
    Posts
    3,740
    thanks Gregg - it's a little clearer from there.
    the page also includes code with a .undo equivalent

    izy
    currently using SS 2008R2

Posting Permissions

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