Getting "Invalid Outside Procedure" on same code on two different forms! So confused.

[cherold]

I am beyond perplexed right now.



I have a database called Albums. I have a module that contains a Public Function called AddNewArtist which is designed to add names to a table.

I have a form called LPs, and if someone enters an unrecognized name in the Artist field the I use the NotInList event to call AddNewArtist. Like this:

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


    AddNewArtist NewData, Response, 5
    
End Sub

This works just fine.

I have another form called Instrumentals. It also has an Artist field. I use the NotInList event once again to call AddNew Artist. It looks like this:

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


AddNewArtist NewData, Response, 6


End Sub

When this is triggered, I get, "Compile error: Invalid Outside Procedure."



As you can see, the two calls are identical except the third argument, which is just an integer. I even tried using "5" in the second one as a Hail Mary pass, but as expected it made no difference.

Can anyone shed light on this puzzling dilemma?

[June7]

Post the code for AddNewArtist. What value is passed by the Response variable?

If you want to provide db for analysis, follow instructions at bottom of my post.

[cherold]

I'm attaching the database. Also, the original call is also failing now. Which is especially weird because after I got the error with the new call, I tested the original and it worked fine. But the next time I entered an unknown name I got the same Invalid Outside Procedure error!

The Function I'm calling is this one:

Public Function AddNewArtist(NewData As String, Response As Integer, Instrument As Integer)
    Dim strTmp As String
    
    'Get confirmation that this is not just a spelling error.
    strTmp = "Add '" & NewData & "' as a new performer?"
    If MsgBox(strTmp, vbYesNo + vbDefaultButton2 + vbQuestion, "Not in list") = vbYes Then
    
        'Append the NewData as a record in the Performers table.
        strTmp = "INSERT INTO Artists ( LastName, FirstName, BandName, OriginalName, Instrument_id ) VALUES ('" & getLastname(NewData) & "','" & getFirstname(NewData) & "','" & getBandname(NewData) & "','" & NewData & "'," & Instrument & " )"
        
        
        DBEngine(0)(0).Execute strTmp, dbFailOnError
        
        
        'Notify Access about the new record, so it requeries the combo.
        Response = acDataErrAdded
    End If


End Function

[June7]

First, you are calling a Function not a Sub. Function call must be on the right side of an expression or called from a query, like:

x = MyFunction(arg1, arg2, arg3)

If there is no need to return a result to the calling procedure or it is not called within a query, then make the function a sub.

Public Sub AddNewArtist(NewData As String, Response As Integer, Instrument As Integer)
...
End Sub

Second, setting Response = acDataErrAdded in this procedure probably does not do what you expect. It passes nothing back to the calling procedure, for that the function would have to:

AddNewArtist = something

Setting the Response variable should probably be done in the calling procedure.

Response = AddNewArtist(NewData, 5)

Public Function AddNewArtist(NewData As String, Instrument As Integer)
...
AddNewArtist = acDataErrAdded
End Function

http://www.fontstuff.com/access/acctut20.htm

There may be other issues but haven't done any testing.

Step debug your code. Refer to link at bottom of my post for debugging guidelines.

[ItsMe]

Maybe you pasted End Function or End Sub somewhere in a way that placed it within a procedure twice. Or you have Private Sub, Public function, etc twice.

End Function
End Function

[cherold]

This function was simply found online, so I only have a superficial understanding of it. Nevertheless, it was working until today.

I tried making a few changes based on your suggestions, making it a Sub or keeping it as a function that returns acDataErrAdded, but I got the same compile error.

I don't know anything about the compiler, but the code stops and throws the error right when it hits the sub that calls the function, so would it really matter what the function contained at that point? If I just made it an empty function would that actually have an effect on how the code runs before it even calls that function?

[June7]

Okay, the issue is related to the line at top of module:

Compare Database

should be

Option Compare Database

However, I revise my earlier statement about calling function and I am totally surprised. I always thought a function had to be in an expression.

I tried testing your procedure but the function has other runtime errors you will have to deal with.

[cherold]

Thank you so much, that fixed the problem. I don't even know what Option Compare Database is or what it's for; I don't think I put it in there so it may be something Access inserted automatically for unknown reasons.

Thanks again. I really appreciate your taking the time to help me.

[June7]

Access does put that line in there. Search web for info on what it is for.

Most experienced developers recommend adding a line in the header:

Option Explicit