Results 1 to 9 of 9

Thread: New Problem

  1. #1
    gdboling
    Guest

    New Problem

    Ok, here is what I am doing. I am making a lookup list for a name field so that you don't have to enter the name every time you need different data under the same name. Here is my code

    Option Compare Database

    Private Sub FigureName_NotInList(NewData As String, Response As Integer)

    Dim Db As DAO.Database
    Dim Rs As DAO.Recordset
    Dim Msg As String
    Dim CR As String

    CR = Chr$(13)


    ' Exit this subroutine if the combo box was cleared.
    If NewData = "" Then Exit Sub

    ' Confirm that the user wants to add the new Figure Name.
    Msg = "'" & NewData & "' is not in the list." & CR & CR
    Msg = Msg & "Do you want to add it?"
    If MsgBox(Msg, vbQuestion + vbYesNo) = vbNo Then
    ' If the user chose not to add a customer, set the Response
    ' argument to suppress an error message and undo changes.
    Response = acDataErrContinue
    ' Display a customized message.
    MsgBox "Please enter a different name."
    Else



    ' If the user chose to add a new Figure Name, open a recordset
    ' using the Lookup table.
    Set Db = CurrentDb
    Set Rs = Db.OpenRecordset("Lookup", dbOpenTable)
    ' Let code execution continue if a run-time error occurs.
    On Error Resume Next
    ' Create a new record.
    Rs.AddNew


    ' Assign the NewData argument to the CompanyName field.
    Rs![FigureName] = NewData
    ' Save the record.
    Rs.Update

    MsgBox "Name Added"

    End If
    Rs.Close
    End Sub

    Now, everything works, except, I still get an error saying the name is not in the list and then it doesn't let me go to the next record. The good news is that it adds it to my lookup list. Any ideas?

  2. #2
    2 Star Lounger
    Join Date
    Jan 2001
    Location
    Tennessee
    Posts
    194
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: New Problem

    have you tried requering the combo box at the end of the code
    me!combo1.requery

  3. #3
    gdboling
    Guest

    Re: New Problem

    Well, that took it out of the loop, but it still is giving me the Item not in List error and won't go to the next rocord.

  4. #4
    gdboling
    Guest

    Re: New Problem

    Also, when I try just to add directly into the Entry table without using the form I get that not in list message. I bet that has something to do with it because that is the final step in the process is to add it to that field in that record. Any way around that?

  5. #5
    2 Star Lounger
    Join Date
    Jan 2001
    Location
    Tennessee
    Posts
    194
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: New Problem

    the are no event procedures in a table i don't know why you would get this error entering data directly into a table

  6. #6
    gdboling
    Guest

    Re: New Problem

    Ok, I know. Now, does the limit to list option set to yes or no?? I think I got passed that error, I can enter data directly into the table, but I Still get the not in list error with the form. I'm going crazy. aaahhh!!!

    <img src=/S/bash.gif border=0 alt=bash width=35 height=39>

  7. #7
    2 Star Lounger
    Join Date
    Jan 2001
    Location
    Tennessee
    Posts
    194
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: New Problem

    set the limit to list property of the combo box to yes

  8. #8
    gdboling
    Guest

    Re: New Problem

    I did that, now why won't it let me continue on to the next record? Here is some more details.

    I have a table : Lookup
    Has one column with FigureName

    I have a table : Entry
    Has Two columns, ID and FigureName

    The Entry table has the combo box
    Row Source is Lookup
    Bound to 1 column (Lookups FigureName)
    Limit to List set to Yes

    On NotInPrivate Sub FigureName_NotInList(NewData As String, Response As Integer)


    Dim Db As DAO.Database
    Dim Rs As DAO.Recordset
    Dim Msg As String
    Dim CR As String

    CR = Chr$(13)


    ' Exit this subroutine if the combo box was cleared.
    If NewData = "" Then Exit Sub

    ' Confirm that the user wants to add the new Figure Name.
    Msg = "'" & NewData & "' is not in the list." & CR & CR
    Msg = Msg & "Do you want to add it?"
    If MsgBox(Msg, vbQuestion + vbYesNo) = vbNo Then
    ' If the user chose not to add a customer, set the Response
    ' argument to suppress an error message and undo changes.
    Response = acDataErrContinue
    ' Display a customized message.
    MsgBox "Please enter a different name."
    Else



    ' If the user chose to add a new Figure Name, open a recordset
    ' using the Lookup table.
    Set Db = CurrentDb
    Set Rs = Db.OpenRecordset("Lookup", dbOpenTable)
    ' Let code execution continue if a run-time error occurs.
    On Error Resume Next

    ' Create a new record.
    Rs.AddNew

    ' Assign the NewData argument to the FigureName field.
    Rs![FigureName] = NewData
    ' Save the record.
    Rs.Update

    MsgBox "Name Added"

    End If

    Rs.Close
    Me!Combo1.Requery
    End Sub

    I go into the form and enter a new name, it then prompts me if I would like to add that name. I click yes, it adds the name and then says "The text you entered isn't an item in the list". It still ads the name to the Lookup table, but not the Entry Table. It needs to be in both

  9. #9
    2 Star Lounger
    Join Date
    Jan 2001
    Location
    Tennessee
    Posts
    194
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: New Problem

    you code is only being applied to the lookup table as far as i can see
    you could try adding another record set for the entry table

Posting Permissions

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