Help with Code

  • Thread starter Thread starter SF
  • Start date Start date
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
 
Hi,

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