Help with Code

S

SF

I have a form to record proposals. Each proposal covers at least one
communes. I wrote a function to display name of communes in one field but
getting error when a proposal does not have Commune entry. I got "Error 5 -
Invalid procedure call or argument". I think this code run find if we can at
blank field to those proposal that did not have communes. The code is list
below


Public Function ListOfCommuneNames(ID As Long)
On Error GoTo Err_ListOfCommuneNames

Dim dbs As DAO.Database
Dim rst As DAO.Recordset
Dim I As Integer
Dim strSQL As String
Dim txtTemp As String

strSQL = "SELECT qryProjectCommunes.Cn_Commune_e FROM qryProjectCommunes"
strSQL = strSQL & " WHERE (((qryProjectCommunes.P_ObjectID) = " & ID & "))"
strSQL = strSQL & " ORDER BY qryProjectCommunes.Cn_Commune_e;"

Set dbs = CurrentDb
Set rst = dbs.OpenRecordset(strSQL, dbOpenDynaset)
'rs.Open SQL, CurrentProject.Connection, adOpenDynamic, adLockOptimistic
If rst.RecordCount > 0 Then
For I = 1 To rst.RecordCount
Txt = Txt & rst![Cn_Commune_e] & ", "
rst.MoveNext
Next I
End If
Set rst = Nothing
ListOfCommuneNames = Left(Txt, Len(Txt) - 2)

Exit_ListOfCommuneNames:
Exit Function

Err_ListOfCommuneNames:
Debug.Print "Error " & Err.Number & " - " & Err.Description
MsgBox Err.Number & " - " & Err.Description, vbOKOnly + vbInformation
Resume Exit_ListOfCommuneNames

End Function
 
S

Sreedhar

Hi,

I see that you've declared the variable "txtTemp" but your code uses "Txt".
Option Explicit ?
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top