Access VBA

css navigation by Css3Menu.com

Last Name First

In our application, some reports needed the name broken in component parts and others strung together. A number of Functions gave us what was needed each time.
Public Function LastNameFirst(sID As Long) As String
	'Based on BorrowerID and BorrowerType, return name; Last Name first
	'Person ID passed as ‘sID’ to ‘DVid’
	Dim theName 	As String
	Dim Borrows 	As Recordset
	Set Borrows = CurrentDb.OpenRecordset("BorrowerInfo", dbOpenDynaset, dbSeeChanges)
	Do Until Borrows.EOF
		With Borrows
		If !DVid = sID Then
			theName = !LastName
			If Len(!Prefix) > 0 Then     'If there is a prefix before a number, capture it
		theName = theName & ", " & !Prefix & Chr(32) & !FirstNameMI & Chr(32) & !Suffix
			Else		'Else ignore the Prefix field
		theName = theName & ", " & !FirstNameMI & Chr(32) & !Suffix
			End If
			Exit Do		'If you have everything you came for exit the Loop
		End If
		End With
	Borrows.MoveNext
	Loop
	LastNameFirst = theName		'Data from search 
	Borrows.Close
End Function

Using a RecordSet gave us a signficant time advantage over a bunch of DLookUps.

© 2009-2017

Updated:  04/06/2017 20:32
This page added:  17 May 2009