Hi Everyone.

I'm having a problem with one form recognizing a public function and another form in the same database kicking out with an error when I try to call the same function. I'm using Access 2002. The code is as follows: Private Sub cmdFind_Click()
On Error GoTo Err_cmdFind_Click

Dim rst As New ADODB.Recordset
Dim cnn As ADODB.Connection
Dim ReturnedAddress As String
Dim cmd As New ADODB.Command
Dim StrSql As String
Set cnn = CurrentProject.Connection

DoCmd.SetWarnings False

If Me!cboNumYears < 0 Or Me!cboNumYears > 16 Then
MsgBox "Please enter an inspection age threshold"
GoTo Exit_cmdFind_Click
End If

If Me!chkExcludeClassI = False And Me!chkPrintClassIOnly = False Then
DoCmd.OpenQuery "qryBFAddInspectedDevicesToScheduleTemp"
Else
End If

If Me!chkExcludeClassI = True And Me!chkPrintClassIOnly = False Then
DoCmd.OpenQuery ""
Else
End If

If Me!chkExcludeClassI = False And Me!chkPrintClassIOnly = True Then
DoCmd.OpenQuery ""
Else
End If

rst.Open "SELECT * FROM tblBFScheduleTempInspections", cnn

Do Until rst.EOF
Call DisplayAddress(rst!MailedToName1, rst!MailedToName2, _
rst!MailedToAddress1, rst!MailedToAddress2, rst!MailedToCity, _
rst!MailedToState, rst!MailedToZip, ReturnedAddress)

StrSql = "Update tblBFScheduleTemp Set DisplayAddress = '" & ReturnedAddress & "' Where BFID = " & rst!BFID & " and RecordType = '" & rst!RecordType & "'"
cmd.ActiveConnection = cnn
cmd.CommandText = StrSql
cmd.Execute

rst.MoveNext
Loop

rst.Close

Me.Requery

Exit_cmdFind_Click:
DoCmd.SetWarnings True
Exit Sub

Err_cmdFind_Click:
MsgBox Err.Description
Resume Exit_cmdFind_Click

End Sub

When VB hits "Call DisplayAddress" portion of code in the second form it returns an error "Compile Error - Invalid use of property" and if I right-click and try to select "Definition" to see the Public Function DisplayAddress it returns the message "Cannot jump to DisplayAddress because it is in the library 'Z:\Design\WODesign\WODesign.mdb' which is not currently referenced"

'Z:\Design\WODesign\WODesign.mdb' is the database I am working in. DisplayAddress is a Public Function contained in a Module within the same database.

Here is DisplayAddress: Public Function DisplayAddress(Name1 As String, Name2 As String, _
Address1 As String, Address2 As String, City As String, State As String, _
Zip As String, ReturnedAddress)

Dim Line1 As String
Dim Line2 As String
Dim Line3 As String
Dim Line4 As String
Dim Line5 As String

If Trim(Name2) = "" Or IsNull(Name2) Then
Line1 = ""
Line2 = Trim(Name1) & vbCrLf
Else
Line1 = Trim(Name1) & vbCrLf
Line2 = Trim(Name2) & vbCrLf
End If

If Trim(Address1) = "" Or IsNull(Address1) Then
Line3 = Trim(Address2) & vbCrLf
Else
Line3 = Trim(Address1) & vbCrLf
End If

If Trim(Address2) = "" Or IsNull(Address2) Then
Line4 = Trim(City) & ", " & Trim(State) & " " & Trim(Zip)
Else
If Trim(Address1) = "" Or IsNull(Address1) Then
Line4 = Trim(City) & ", " & Trim(State) & " " & Trim(Zip)
Else
Line4 = Trim(Address2) & vbCrLf
End If
End If

If Trim(Address2) = "" Or IsNull(Address2) Then
Line5 = ""
Else
If Trim(Address1) = "" Or IsNull(Address1) Then
Line5 = ""
Else
Line5 = Trim(City) & ", " & Trim(State) & " " & Trim(Zip)
End If
End If

ReturnedAddress = Line1 & Line2 & Line3 & Line4 & Line5

End Function

Does anyone know what would cause the db to behave this way?

Well for one you shouldn't have Returned Address in the input parameters of the function. You should be setting

DisplayAddress = Line1 & Line2 & Line3 & Line4 & Line5

instead of ReturnedAddress.

To follow up on my last post - you would want your function to be:


Public Function DisplayAddress(Name1 As String, Name2 As String, _
Address1 As String, Address2 As String, City As String, State As String, _
Zip As String) As String

Dim Line1 As String
Dim Line2 As String
Dim Line3 As String
Dim Line4 As String
Dim Line5 As String

If Trim(Name2) = "" Or IsNull(Name2) Then
Line1 = ""
Line2 = Trim(Name1) & vbCrLf
Else
Line1 = Trim(Name1) & vbCrLf
Line2 = Trim(Name2) & vbCrLf
End If

If Trim(Address1) = "" Or IsNull(Address1) Then
Line3 = Trim(Address2) & vbCrLf
Else
Line3 = Trim(Address1) & vbCrLf
End If

If Trim(Address2) = "" Or IsNull(Address2) Then
Line4 = Trim(City) & ", " & Trim(State) & " " & Trim(Zip)
Else
If Trim(Address1) = "" Or IsNull(Address1) Then
Line4 = Trim(City) & ", " & Trim(State) & " " & Trim(Zip)
Else
Line4 = Trim(Address2) & vbCrLf
End If
End If

If Trim(Address2) = "" Or IsNull(Address2) Then
Line5 = ""
Else
If Trim(Address1) = "" Or IsNull(Address1) Then
Line5 = ""
Else
Line5 = Trim(City) & ", " & Trim(State) & " " & Trim(Zip)
End If
End If

DisplayAddress = Line1 & Line2 & Line3 & Line4 & Line5

End Function


And if you have a field called DisplayAddress, you want to change the function name (maybe to ReturnedAddress) because in your SQL statement you are asking to update DisplayAddress (which is currently the name of the function) and Access probably doesn't like that.

Monk,

Your function should return something:


Public Function DisplayAddress(Name1 As String, Name2 As String, _
Address1 As String, Address2 As String, City As String, State As String, _
Zip As String, ReturnedAddress) As String





StrSql = "Update tblBFScheduleTemp " & _
"Set DisplayAddress = '" & DisplayAddress(rst!MailedToName1, rst!MailedToName2, _
rst!MailedToAddress1, rst!MailedToAddress2, rst!MailedToCity, _
rst!MailedToState, rst!MailedToZip) & "' " & _
"Where BFID = " & rst!BFID & " and " & _
" RecordType = '" & rst!RecordType & "'"


Wayne

Wow!

You're quick Bob.

Wayne

Monk,

Your function should return something:


Public Function DisplayAddress(Name1 As String, Name2 As String, _
Address1 As String, Address2 As String, City As String, State As String, _
Zip As String, ReturnedAddress) As String


The red part above should not be there though.

And to follow up on Wayne's comment about it should return something, for functions that return values (and in reality functions should return values but due to the way Access works you may be forced to use a function to do the work of a sub if you need it available in a query or controlsource of a control). So, you would call it by setting something equal to it. If you are calling a sub then you can use Call xxxx But you really don't even have to use the explicit Call keyword.

Oops ... thought I took that out first.

See ya,
Wayne

Wow! Thanks Bob and Wayne, those replies were incredibly fast!

I updated my code (sorry I didn't understand Public Functions that well, I don't use them that often) and the following code now works with the first form. The updated code is as follows:
Private Sub cmdFind_Click()
On Error GoTo Err_cmdFind_Click

Dim rst As New ADODB.Recordset
Dim cnn As ADODB.Connection
Dim cmd As New ADODB.Command
Dim StrSql As String
Set cnn = CurrentProject.Connection

DoCmd.SetWarnings False

If Me!cboNumYears < 0 Or Me!cboNumYears > 16 Then
MsgBox "Please enter an inspection age threshold"
GoTo Exit_cmdFind_Click
End If

If Me!chkExcludeClassI = False And Me!chkPrintClassIOnly = False Then
DoCmd.OpenQuery "qryBFAddInspectedDevicesToScheduleTemp"
Else
End If

If Me!chkExcludeClassI = True And Me!chkPrintClassIOnly = False Then
DoCmd.OpenQuery ""
Else
End If

If Me!chkExcludeClassI = False And Me!chkPrintClassIOnly = True Then
DoCmd.OpenQuery ""
Else
End If

rst.Open "SELECT * FROM tblBFScheduleTempInspections", cnn

Do Until rst.EOF

StrSql = "Update tblBFScheduleTempInspections Set DisplayAddress = '" & _
DisplayAddress(rst!MailedToName1, rst!MailedToName2, _
rst!MailedToAddress1, rst!MailedToAddress2, rst!MailedToCity, _
rst!MailedToState, rst!MailedToZip) & "' Where BFID = " & rst!BFID & _
" and RecordType = '" & rst!RecordType & "'"
cmd.ActiveConnection = cnn
cmd.CommandText = StrSql
cmd.Execute

rst.MoveNext
Loop

rst.Close

Me.Requery

Exit_cmdFind_Click:
DoCmd.SetWarnings True
Exit Sub

Err_cmdFind_Click:
MsgBox Err.Description
Resume Exit_cmdFind_Click

End Sub


and the updated Public Function is as follows:
Public Function DisplayAddress(Name1 As String, Name2 As String, _
Address1 As String, Address2 As String, City As String, State As String, _
Zip As String) As String

Dim Line1 As String
Dim Line2 As String
Dim Line3 As String
Dim Line4 As String
Dim Line5 As String

If Trim(Name2) = "" Or IsNull(Name2) Then
Line1 = ""
Line2 = Trim(Name1) & vbCrLf
Else
Line1 = Trim(Name1) & vbCrLf
Line2 = Trim(Name2) & vbCrLf
End If

If Trim(Address1) = "" Or IsNull(Address1) Then
Line3 = Trim(Address2) & vbCrLf
Else
Line3 = Trim(Address1) & vbCrLf
End If

If Trim(Address2) = "" Or IsNull(Address2) Then
Line4 = Trim(City) & ", " & Trim(State) & " " & Trim(Zip)
Else
If Trim(Address1) = "" Or IsNull(Address1) Then
Line4 = Trim(City) & ", " & Trim(State) & " " & Trim(Zip)
Else
Line4 = Trim(Address2) & vbCrLf
End If
End If

If Trim(Address2) = "" Or IsNull(Address2) Then
Line5 = ""
Else
If Trim(Address1) = "" Or IsNull(Address1) Then
Line5 = ""
Else
Line5 = Trim(City) & ", " & Trim(State) & " " & Trim(Zip)
End If
End If

DisplayAddress = Line1 & Line2 & Line3 & Line4 & Line5

End Function


I am encountering a type mismatch on my second form (same code), which I can figure out when I have more time, but if I right-click and try to select "Definition" to see DisplayAddress it still returns the message "Cannot jump to DisplayAddress because it is in the library 'Z:\Design\WODesign\WODesign.mdb' which is not currently referenced"

'Z:\Design\WODesign\WODesign.mdb' is the database I am working in. DisplayAddress is a Public Function contained in a Module within the same database.

If I right-click and select "Definition" in the code on the first form it shows me the code for Public Function DisplayAddress. I think once I clear up the type mismatch I will probably get the same error message as before.

I've tried troubleshooting the "Type Mismatch" error and it IS being caused by the DisplayAddress in the following code: StrSql = "Update tblBFScheduleTempInspections Set DisplayAddress = '" & DisplayAddress(rst!MailedToName1, rst!MailedToName2, _
rst!MailedToAddress1, rst!MailedToAddress2, rst!MailedToCity, _
rst!MailedToState, rst!MailedToZip) & "' Where BFID = " & rst!BFID & " and RecordType = '" & rst!RecordType & "'"


This is the code from the form that does work: StrSql = "Update tblBFScheduleTemp Set DisplayAddress = '" & DisplayAddress(rst!MailedToName1, rst!MailedToName2, _
rst!MailedToAddress1, rst!MailedToAddress2, rst!MailedToCity, _
rst!MailedToState, rst!MailedToZip) & "' Where BFID = " & rst!BFID & " and RecordType = '" & rst!RecordType & "'"


As you can see, the statement is the same except for a different table name. It's as if the form that does work CAN see the public function, and the other form CAN'T see it and therefore doesn't understand it.

Can anyone help?

You're passing all parameters as string, which is a bit suspicious, since you're passing values from a recordset. If any of these are Null, then you'll get errors - but that would probably be RT 94 - Invalid use of Null (or possibly some Byref thingie). Perhaps you should either pass as variant, or check for Null before passing the values?

Hi Roy,

You're definitely right about the risk of Nulls. I get the "Invalid Use of Nulls" when it happens to pass a Null.

I changed my code to pass the rst! values to string variables and then used those string variables in the DisplayAddress input values. The same thing happened.

You got me thinking about the fact that data could be a variable here. To rule out the data variable I copied the EXACT code from the form that works into the form that doesn't work. It doesn't work in the other form.

Code from the form that doesn't work: rst.Open "SELECT * FROM tblBFScheduleTemp", cnn

Do Until rst.EOF

StrSql = "Update tblBFScheduleTemp Set DisplayAddress = '" & DisplayAddress(rst!MailedToName1, rst!MailedToName2, _
rst!MailedToAddress1, rst!MailedToAddress2, rst!MailedToCity, _
rst!MailedToState, rst!MailedToZip) & "' Where BFID = " & rst!BFID & " and RecordType = '" & rst!RecordType & "'"
cmd.ActiveConnection = cnn
cmd.CommandText = StrSql
cmd.Execute

rst.MoveNext
Loop

rst.Close


Code from the form that works: rst.Open "SELECT * FROM tblBFScheduleTemp", cnn

Do Until rst.EOF

StrSql = "Update tblBFScheduleTemp Set DisplayAddress = '" & DisplayAddress(rst!MailedToName1, rst!MailedToName2, _
rst!MailedToAddress1, rst!MailedToAddress2, rst!MailedToCity, _
rst!MailedToState, rst!MailedToZip) & "' Where BFID = " & rst!BFID & " and RecordType = '" & rst!RecordType & "'"
cmd.ActiveConnection = cnn
cmd.CommandText = StrSql
cmd.Execute

rst.MoveNext
Loop


This has got to be some strange database problem. If I go to the code on the form that works and right-click on "DisplayAddress" and select "Definition" it brings me to the code of the Public Function DisplayAddress. If I go to the code on the form that does NOT work and do the same thing I get the message: "Cannot jump to DisplayAddress because it is in the library 'Z:\Design\WODesign\WODesign.mdb' which is not currently referenced"

'Z:\Design\WODesign\WODesign.mdb' is the database I am working in. DisplayAddress is a Public Function contained in a Module within the same database.

I've tried compacting and repairing. Can forms in the same db have different modules referenced?

This sounds like corruption. I'd try the /decompile option. Check out this step by step approach http://www.granite.ab.ca/access/decompile.htm, though, between step 2 and 3, I'd close the database again, then open it (while holding shift to avoid any code running) to compact in another instance than the one decompiling it.

I would say have you tried dealing with the nulls, as Roy initially suggested? In other words taking this:

Do Until rst.EOF

StrSql = "Update tblBFScheduleTempInspections Set DisplayAddress = '" & _
DisplayAddress(rst!MailedToName1, rst!MailedToName2, _
rst!MailedToAddress1, rst!MailedToAddress2, rst!MailedToCity, _
rst!MailedToState, rst!MailedToZip) & "' Where BFID = " & rst!BFID & _
" and RecordType = '" & rst!RecordType & "'"
cmd.ActiveConnection = cnn
cmd.CommandText = StrSql
cmd.Execute

rst.MoveNext
Loop

and changing it to this:

Do Until rst.EOF

StrSql = "Update tblBFScheduleTempInspections Set DisplayAddress = '" & _
DisplayAddress(Nz((rst!MailedToName1,""), Nz((rst!MailedToName2,""), _
Nz((rst!MailedToAddress1,""), Nz((rst!MailedToAddress2,""), Nz((rst!MailedToCity,""), _
Nz((rst!MailedToState,""), Nz((rst!MailedToZip,"")) & "' Where BFID = " & rst!BFID & _
" and RecordType = '" & rst!RecordType & "'"
cmd.ActiveConnection = cnn
cmd.CommandText = StrSql
cmd.Execute

rst.MoveNext
Loop

Bob,

I did take care of the nulls, although in a different manner (I like yours much better). I even copied the exact code from the form that works so both forms would be using the same data. I think I've ruled out that it could be any sort of data problem at this point, but let me know if I've missed something.

Roy,

I did a decompile according to the instructions you linked to, Thanks! I have never done that and it took a while to clean up some of my old code. The db size decreased from 12 MB to 10 MB. I still am having the same problem.

Just for fun I created a new blank form and stuck a button on it. I copied the code from the form that does NOT work into the button and guess what?

It works!

So, I stuck a new button on the form where the code doesn't work, and copied the code from the other button that does NOT work and guess what?

It doesn't work.

The problem is definitely form specific.

I have had a similar problem in the past regarding code referencing other forms. I'll have code in one form that references a field in a different form (such as Forms!frmBilling!txtDate). Sometimes I can have the exact same code in a different form and it will tell me it can't find Forms!frmBilling!txtDate even though I can open frmBilling and I have the exact same code in another form and it finds it fine. I have had to rebuild the offending form from scratch to get it to work.

I think the same thing is happening here, just with the Class Modules and not the Forms.

There has GOT to be a better way than that.

I did take care of the nulls, although in a different manner (I like yours much better). I even copied the exact code from the form that works so both forms would be using the same data. I think I've ruled out that it could be any sort of data problem at this point, but let me know if I've missed something.

HOW did you try to "take care of the nulls?" Because, I don't think there are many, if any, other ways to do it so it would work, in this case. I guess you could have assigned a string variable the recordset field by using NZ first like strName = Nz(rst!Name,"") and then using strName in the code calling the function. But, in some way using NZ would be required.

I created a bunch of string variables

Dim Name1 As String
Dim Name2 As String
etc.

and then after opening the recordset I had a bunch of statements that forced a Null String in the new variables if the value was Null.

If IsNull(rst!Name1) Then "" Else Name1 = rst!Name1
If IsNull(rst!Name2) Then "" Else Name2 = rst!Name2
etc.

Then I passed the new variables to DisplayAddress instead of the rst! values.

I have never used NZ before, but I can see it's much easier.

I don't think that it would work correctly the way you showed (and you also said it didn't work), but the way I showed should work fine as it captures and replaces nulls for any current record.

Are you saying that you are using code residing in form a's class module from form b?

If so, you will probably need to qualify it, for instance through the forms collection (which will require the form to be open)

Forms!NameOfForm.DisplayAddress(....

Or better, place the code in a standard module (in VBE - Insert | Module)

Else, there must be something else going on. Would anything else be named DisplayAddress on the form?

<grin>her's another way of ensuring a you pass a string, without any functions

MyFunction(rs.fields("SomeField").value & "")

just concatenate with ZLS</grin>

Roy,

That was it!

There is a field in the table called DisplayAddress (both tables, that's why it didn't work with the other table either). Not a problem in itself, but when the form was created it carried the name of the field into the name of the textbox when the table field was placed on the form. I renamed the textbox DisplayAddress to txtDisplayAddress and the code worked perfectly.

Thanks for all your help

Memo to me - take care of the little things......