First and Last name in Form - what is the best way to handle this?

[jonathanchye]

Hi all,

(MS Access 2010)

I have a linked table (tblGlobalAddressList) in my current database which the company's global address list. The fields in tblGlobalAddressList includes First Name, Last Name, Department, ContactNo.

I have created a form in my current DB and I want to pull First and Last names from tblGlobalAddressList. How I currently do it now is to create two combo boxes and link them to First and Last Name fields in tblGlobalAddressList. Reason I do this is because users can first select their last name and then then second combo box will narrow down their first name to further enhance data integrity.

However, I've encountered a problem here. I want to store both the First and Last name as a SINGLE field in the table my form is linked too.

I can't find a way to do this at the moment. Also, is it actually easier to first run a query and then use SQL to combine (Union?) First and Last name and just use a Text box in my form instead of 2 combo boxes?

 

[Uncle Gizmo]

Created a combo box on your form and bind it to the field where you want the first name last name stored.

Go in to the combo box properties and paste the following into the combo box row source:

SELECT [First Name] & " " & [Last Name] AS FirstLast, Department, ContactNo FROM tblGlobalAddressList;

Set the combo box property “Limit To List” to equal 1

Set the combo box property “Bound Column” to equal 1

Set the combo box property “Column Count” to equal 3

 

[missinglinq]

Uncle G's approach is the correct one! When stored in a Table the name should always be stored as two fields, then concatenated when necessary, as his SELECT statement shows. Trust me, it is always easier to concatenate data together than it is to parse it into it's various components!

Linq ;0)>

 

[Wiz47]

Uncle G's approach is the correct one! When stored in a Table the name should always be stored as two fields, then concatenated when necessary, as his SELECT statement shows. Trust me, it is always easier to concatenate data together than it is to parse it into it's various components!

Linq ;0)>

Truer words were never spoken. [First Name] & " " & [Last Name] is always the better approach.

 

[missinglinq]

Teach him to fish, and he'll sit on the dock for hours and drink beer."

That's a slight misquote! I believe it should be

"Teach him to fish, and he'll sit on the dock for hours, drink beer, and tell lies!"

 

[jonathanchye]

EDIT: Never mind I found the problem
EDIT2: Can you clarity a little bit about the "AS" part. Why is the first property "FirstLast"? Does this correspond to the Control Source on the main form I want to save the field in? Or just a new name for the combined fields?

Thanks a lot mate!

 

[Uncle Gizmo]

SELECT [First Name] & " " & [Last Name] AS FirstLast, Department, ContactNo FROM tblGlobalAddressList;

The “AS” part of the SQL string is not significant when used in the combo box environment because combo boxes use the “column index” to determine where the column is displayed in the combo box.

However when you use SQL strings in code, in functions and the like, then it is necessary to provide a name for the concatenation of the first name, last name field so that you can refer to it later in the code.

I have included an example below to demonstrate this.

The function “fSumPayments” sums the donations which are recorded in the field “PaymentAmount” which is part of the table “tblDonations”.

Notice that this summing part of the SQL string is given the name “AS SumOfPaymentAmount” and notice later that this value is returned to the function in this line here:

fSumPayments = rs.Fields("SumOfPaymentAmount")

'==========================================================================================
'==========================================================================================
'CODE BELOW FROM:
'http://my.safaribooksonline.com/0596009240/accesshks-CHP-8-SECT-4
'==========================================================================================
    
Public Function fSumPayments(lngConID As Long) As Currency
'http://www.oreilly.com/pub/h/3323
Dim strSQL As String
Dim strSQL1 As String
Dim strSQL2 As String
Dim strSQL3 As String
Dim strSQL4 As String
Dim strSQL5 As String

strSQL1 = "SELECT Sum(PaymentAmount) AS SumOfPaymentAmount "
strSQL2 = "FROM tblDonations "
strSQL3 = "GROUP BY ContID "
strSQL4 = "HAVING (((ContID)="
'430
strSQL5 = "))"
  
  strSQL = strSQL1 & strSQL2 & strSQL3 & strSQL4 & lngConID & strSQL5
  Dim conn As ADODB.Connection
  Set conn = CurrentProject.Connection
  Dim rs As New ADODB.Recordset
  rs.Open strSQL, conn, adOpenKeyset, adLockOptimistic
  'MsgBox rs.Fields("SumOfPaymentAmount")
  fSumPayments = rs.Fields("SumOfPaymentAmount")
  rs.Close
  Set rs = Nothing
  Set conn = Nothing
End Function

 

[Uncle Gizmo]

You can find more information in the book:

Access Hacks
By: Ken Bluttman
Publisher: O'Reilly Media, Inc.

Chapter 8. Programming > Substitute Domain Aggregate Functions for SQL Aggregat... - Pg. 254

Which is available online from Safari Books online here: http://bit.ly/gsq4ej