Results 1 to 3 of 3
  1. #1
    Join Date
    Jul 2007
    Posts
    72

    Unanswered: Module (public function) has lost touch with db??

    I have a On Not In List event written as:

    Code:
    Private Sub cboTalentname_NotInList(NewData As String, Response As Integer)
    DoCmd.RunCommand acCmdUndo
    DoCmd.RunCommand acCmdUndo
    intAns = AddNewToList(NewData, "tblTalent", "TALENTID", "Talent", "Talents")
    Response = acDataErrContinue
    
    End Sub
    AddNewToList() is saved as a Module and used to work properly. I have made changes to my database but I have triple checked the names of the fields I want it to add the new item to but I am getting
    "Compile error" "Variable not defined" referring to intAns

    Does this mean that it is not connecting properly to the Module because this is where it should find out that intAns is AddNewToList()


    Code of Module:

    Code:
    Public Function AddNewToList(NewData As String, stTable As String, stTableID As String, stFieldName As String, strPlural As String)
    
    On Error GoTo Err_AddNewToList
    
        'Adds a new record to a drop down box list and
    
        'selects that records in the source drop down box that called this code
       
    addData:
    
        'Declare variables and set source objects
    
        Dim strSQL As String, intStoreNewData As Long, objSource As Control
    
        Dim strMessage as string, intNewItem as long
    
        Set objSource = Screen.ActiveControl
    
       
        strMessage = "'" & NewData & "' is not in the current list of " & strPlural & ". " & Chr(13) & Chr(13) & _
    
                     "Do you want to add it to the list of " & strPlural & "? " & Chr(13) & _
    
                     "(Please check the new entry is correct before proceeding)."
    
       
            strSQL = "INSERT INTO [" & stTable & "] (" & stFieldName & ") SELECT '" & NewData & "';"
    
        
        ' Display message box asking if user wants to add a new item
    
        intNewItem = MsgBox(strMessage, vbYesNo + vbQuestion + vbDefaultButton2)
    
        'if yes
    
            '1. Run SQL statement to add to item to table
    
            '2. Recover new items record ID
    
            '3. Requery source combo box to refresh the combo box drop down list
    
            '4. Write the new items recordID to the combo box to display new record in box
    
    
        If intNewItem = vbYes Then
    
            CurrentDb.Execute strSQL
    
            intStoreNewData = DMax(stTableID, stTable, "[" & stFieldName & "] = '" & NewData & "'")
    
            objSource.Requery
    
            objSource = intStoreNewData
    
    
        End If
    
        'move cursor out of and then back to the combo box to close the combo box list, but retain focus on combo box
    
        SendKeys "+{TAB}{TAB}"
    
    
    Exit_AddNewToList:
    
        Exit Function
    
    
    Err_AddNewToList:
    
        MsgBox err.Description
    
        Resume Exit_AddNewToList
    
    End Function

  2. #2
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    You haven't declared intAns. Is it a private member variable, or alternatively have you added "Option Explicit" to the top of the form code module?
    Testimonial:
    pootle flump
    ur codings are working excelent.

  3. #3
    Join Date
    Jul 2007
    Posts
    72
    Okay,

    I added the Option Explicit to the top of the Module AddNewToList (I think it was there before and in redoing things I inadvertently removed it) but this didn't fix the problem.
    Then I added Dim intAns As Integer (which I'm sure I wasn't using before as I still have my word document of handy cody things that I took it from) but this did fix the problem.


    Code:
    Private Sub cboTalentname_NotInList(NewData As String, Response As Integer)
    Dim intAns As Integer
    DoCmd.RunCommand acCmdUndo
    DoCmd.RunCommand acCmdUndo
    intAns = AddNewToList(NewData, "tblTalent", "TALENTID", "Talent", "Talents")
    Response = acDataErrContinue
    
    End Sub
    Thanks Mr Flump, onwards and upwards

Posting Permissions

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