How do I make a function return all results from a loop?

[Niklas Östergren]

Hi!

I´m trying to loop throug a recordset to see if a persons street where the
person live allready exist. And if so display the autonumbered primary key
for that/these person that live on the same street. But the function that I
have so far only return the primary key for the last person in the
recordset.

Any idéa would be highly appreciated.

TIA!
// Niklas


Her´s the code:
===================================================
Public Function CheckDoubleAdress(strStreet As String) As Long
'****************************************************************
' Description: Check if adress data allready is registrated.
'
' Author: Niklas Östergren
' Date: 2004-11-07
' Returns: 0 if adress does NOT exist else return PersonID for
' person which is registrated on same street.
'******************************************************************
On Error GoTo Error_Handler

Dim db As DAO.Database
Dim rec As DAO.Recordset ' Holding recordset to search in.
Dim strSQL As String ' Holding SQL-string to search in.
Dim intCounter As Integer ' Holding number of persons that have same
adress
Dim strMatches As String ' Holding matched records
Dim strPersonName As String ' Holding persons complete name that live on the
same street

Set db = Currentdb()

' If strStreet don´t have any value then return 0 and exit function.
If strStreet & "" = "" Then
CheckDoubleAdress = 0
GoTo Exit_Procedure
End If

' If we get here then we know that strStreet have a valid value.
strSQL = "SELECT tblPerson.*, tblPersonAddress.* FROM tblPersonAddress "
_
& "RIGHT JOIN tblPerson ON tblPersonAddress.PersonAddressID =
tblPerson.fkPersonAddressID " _
& "WHERE tblPersonAddress.Street = " & "'" & strStreet & "'"

' Open up the recordset for selected street
Set rec = db.OpenRecordset(strSQL, dbOpenDynaset)

' Check if any record. If not return 0 else look up
' the persons that have this street registrated
If rec.RecordCount = 0 Then

CheckDoubleAdress = 0
GoTo Exit_Procedure

' Yes the street is registrated in db
Else

rec.MoveLast
intCounter = rec.RecordCount
rec.MoveFirst

' Loop throug all record and get persons complete name and DoB
Do Until rec.EOF
strPersonName = rec!FirstName & " " & rec!LastName & "
(född: " & rec!DoB & ")"
strMatches = strMatches & Chr$(10) & strPersonName

' Return persons ID-number
CheckDoubleAdress = rec!PersonID

rec.MoveNext
Loop

' Display the persons that have same adress
MsgBox "Följande " & intCounter & " person/er är registrerade på
samma gatuadress: " & vbCrLf _
& Chr$(10) & strMatches & vbCrLf & vbCrLf, vbInformation +
vbOKOnly, "Dubbletter"

End If

' Clean up
rec.Close
Set rec = Nothing

Exit_Procedure:
Exit Function

Error_Handler:
MsgBox "Ett fel har uppstått i programmet " & vbCrLf _
& "Vänligen kontakta administratören och ge dem denna
informationen: " & vbCrLf & vbCrLf _
& "Error Number " & Err.Number & ", " & Err.Description & ",
(Procedur: CheckDoubleAdress)", vbCritical, "Medlemsregister"
ErrorLog
Resume Exit_Procedure
Resume

End Function
==========================================================

 

[Niklas Östergren]

Hi again!

If I use this code:
=========================================
' Loop throug all record and get persons complete name and DoB
Do Until rec.EOF
strPersonName = rec!FirstName & " " & rec!LastName & "
(född: " & rec!DoB & ")"
strMatches = strMatches & Chr$(10) & strPersonName

' Return persons ID-number
CheckDoubleAdress = rec!PersonID

rec.MoveNext
Loop
=======================================

Then will function < CheckDoubleAdress > return only PersonID for the last
record, like this:
4

But if I use this code:
=====================================
' Return persons ID-number
CheckDoubleAdress = CheckDoubleAdress & rec!PersonID
========================================

Then I get PersonID for all records but not seperated with a line feed so it
looks like this:
247

The result (247) am I going to use to look up these records in a form. But
would I realy be able to do this when I get this return. Shouldn´t I get a
result like this to be able to use each PersonID seperatly:
2
4
7

If so, how do I get that from the function listed (CheckDoubleAdress is dim
as Long)

TIA!
// Niklas

 

[Niklas Östergren]

If I use < Debug.Print rec!PersonID > instead of < CheckDoubleAdress =
rec!PersonID > then I get the result I want.

Is it not possible to have the function to return the result within a loop?

TIA!
// Niklas

 

[Douglas J. Steele]

Where are you showing the results of the function? It's entirely possible
that Chr$(10) (Linefeed) isn't sufficient, and that you need both Carriage
Return and Linefeed.

See whether

strMatches = strMatches & vbCrLf & strPersonName

or

strMatches = strMatches & Chr$(13) & Chr$(10) & strPersonName


work any better

 

[Niklas Östergren]

Douglas

Well I have been out for a while and have been thinking of this and come to
the conclution that I can aproche it in another way.

What I was thinkin, at the beginning, was that I needed the PersonID
(primary key) as a return of the function to be abel to open up a form with
the record source set to be just the selected PersonID. That´s not true so
what I´ll do instead is that I´ll change the result to be of type boolean
instead and resturn True/False and then make selection depending on that
respons. If < True > then open up form with recordsource of same
Select-string as I used in the function. Which will show exatly the records
I´d like to display to the user.

If < False > then I´ll don´t do anything since it´s only when the street
adress allready is registrated that there might be a risk of that the user
is trying to registrate a person that allready is in the db. Offcourse I do
have other check´s as well this is only one of them.

I run the function in lost focus event of control txtStreet in the
registration form.

Do you have another way to approache this little problem where I actually
have use for the PersonID returned by the function?

Thanks for taking time!

// Niklas

 

[Douglas J. Steele]

Sorry: I don't really understand what you're asking.

However, I did notice the problem with your original function.

Your function is declared as:

Public Function CheckDoubleAdress(strStreet As String) As Long

That means all it can return is a single Long Integer value.

If you want a series of numbers to be returned, one per row, change that to

Public Function CheckDoubleAdress(strStreet As String) As String

 

[Niklas Östergren]

Hmm, maby I´m not explaining myself good enough. That doesn´t realy matter
since I work this around. But what I was trying to do was to get the running
primary key (PersonID) for all records in the recordset which matched the
criteria of having the same street. And since the primari key is of datatype
long I thought that I also had to make the returned value from the function
to datatype long.

Obviously that doesn´t work if I want more than a sigle value returned. So
one way is, I realice now when you explain to me, to return the values as
data type string. And I guess to convert it to integer or long before used
somewhere else.

Anyhow I was looked a little bit in my thought earlyer today but now I know
how to do. So if you need to dig closer into this since it´s no longer a
problem for me.

Thanks for helping out though I apreciate it a lot!

// Niklas