Concatenate two text fields and then add to a table

[Andrew Wilkins]

Hi.

I have a form which allows the user to add records to my database. All of
the records are shown as text boxes on the form. Two of the fields are
'FirstName' and 'LastName'. Each field has a corresponding textbox on the
form (called 'txtFirstName' and 'txtLastName') so the user can type in the
names. I have also set up a third textbox which contains the expression
'=txtFirstName + " " + txtLastName'. This concatenates the contents of the
two text boxes. The trouble is I want this concatenated string to be added
into my table into the field 'FullName'.

Is this possible? How would it be done? I am an Access novice and so I have
no idea!

 

[Andrew Wilkins]

Thanks. The reason I wanted to insert the concatenated text into a field in
my table is that, on another form I want to list each record's full name in a
value list box. I can't think of any other way of getting each person's full
name listed unless there is a field containing their full name.

Hi.

I have a form which allows the user to add records to my database. All of
the records are shown as text boxes on the form. Two of the fields are
'FirstName' and 'LastName'. Each field has a corresponding textbox on the
form (called 'txtFirstName' and 'txtLastName') so the user can type in the
names. I have also set up a third textbox which contains the expression
'=txtFirstName + " " + txtLastName'. This concatenates the contents of the
two text boxes. The trouble is I want this concatenated string to be added
into my table into the field 'FullName'.

Is this possible? How would it be done? I am an Access novice and so I have
no idea!

There is no need to store the FullName in your table.
As long as you have stored the FirstName and LastName fields (as you
are now doing), anytime you need to show the Full Nmae, simply
concatenate it in an UNBOUND text control.
=[FirstName] & " " & [LastName]

Note that the ampersand & is the concatenation symbol.
The plus + can be used but it has special characteristics that differ
from the ampersand.

 

[KARL DEWEY]

You can concatenated text at any time including in a form's value list box.

Just use this in source -- SELECT [FName] & " " & [LName], [OtherData] FROM
xxx

Thanks. The reason I wanted to insert the concatenated text into a field in
my table is that, on another form I want to list each record's full name in a
value list box. I can't think of any other way of getting each person's full
name listed unless there is a field containing their full name.

Hi.

I have a form which allows the user to add records to my database. All of
the records are shown as text boxes on the form. Two of the fields are
'FirstName' and 'LastName'. Each field has a corresponding textbox on the
form (called 'txtFirstName' and 'txtLastName') so the user can type in the
names. I have also set up a third textbox which contains the expression
'=txtFirstName + " " + txtLastName'. This concatenates the contents of the
two text boxes. The trouble is I want this concatenated string to be added
into my table into the field 'FullName'.

Is this possible? How would it be done? I am an Access novice and so I have
no idea!

There is no need to store the FullName in your table.
As long as you have stored the FirstName and LastName fields (as you
are now doing), anytime you need to show the Full Nmae, simply
concatenate it in an UNBOUND text control.
=[FirstName] & " " & [LastName]

Note that the ampersand & is the concatenation symbol.
The plus + can be used but it has special characteristics that differ
from the ampersand.

 

[Andrew Wilkins]

The value list box gets it's data from a second textbox on the form. This
textbox, called txtSurname, contains the following VB code in the OnChange
event:
----
Private Sub txtSurname_Change()


Dim con, rs
Dim strSQL
Dim i


For i = 0 To (lstNames.ListCount - 1)
lstNames.RemoveItem 0
Next i


Set con = Application.CurrentProject.Connection
strSQL = "SELECT [PatientName] FROM [TBL_Patients] WHERE
[PatientSurname] like '" & txtSurname.Text & "%';"


Set rs = CreateObject("ADODB.Recordset")
rs.Open strSQL, con, 1 ' 1 = adOpenKeyset


If (rs.EOF) Then
rs.Close
Set rs = Nothing
Set con = Nothing
Exit Sub
End If


While (Not (rs.EOF))
lstNames.AddItem rs![PatientName]
rs.MoveNext
Wend


' Close the recordset and the database.

rs.Close
Set rs = Nothing
Set con = Nothing


End Sub
----
The VB code line 'lstNames.AddItem rs![PatientName]' is where the
information is sent to the value list box. How can I change this line to be
in SQL so I can then concatenate it? Thanks!

You can concatenated text at any time including in a form's value list box.

Just use this in source -- SELECT [FName] & " " & [LName], [OtherData] FROM
xxx

Thanks. The reason I wanted to insert the concatenated text into a field in
my table is that, on another form I want to list each record's full name in a
value list box. I can't think of any other way of getting each person's full
name listed unless there is a field containing their full name.

On Fri, 10 Feb 2006 14:42:09 -0800, Andrew Wilkins wrote:

Hi.

I have a form which allows the user to add records to my database. All of
the records are shown as text boxes on the form. Two of the fields are
'FirstName' and 'LastName'. Each field has a corresponding textbox on the
form (called 'txtFirstName' and 'txtLastName') so the user can type in the
names. I have also set up a third textbox which contains the expression
'=txtFirstName + " " + txtLastName'. This concatenates the contents of the
two text boxes. The trouble is I want this concatenated string to be added
into my table into the field 'FullName'.

Is this possible? How would it be done? I am an Access novice and so I have
no idea!

There is no need to store the FullName in your table.
As long as you have stored the FirstName and LastName fields (as you
are now doing), anytime you need to show the Full Nmae, simply
concatenate it in an UNBOUND text control.
=[FirstName] & " " & [LastName]

Note that the ampersand & is the concatenation symbol.
The plus + can be used but it has special characteristics that differ
from the ampersand.

 

[KARL DEWEY]

Data is not SENT. Data is sourced (pulled).
Open the form in design view and click on menu VIEW - Properties. Click on
the listbox and edit the RowSource.
You might use ---
SELECT [PatientName] FROM [TBL_Patients]

The value list box gets it's data from a second textbox on the form. This
textbox, called txtSurname, contains the following VB code in the OnChange
event:
----
Private Sub txtSurname_Change()


Dim con, rs
Dim strSQL
Dim i


For i = 0 To (lstNames.ListCount - 1)
lstNames.RemoveItem 0
Next i


Set con = Application.CurrentProject.Connection
strSQL = "SELECT [PatientName] FROM [TBL_Patients] WHERE
[PatientSurname] like '" & txtSurname.Text & "%';"


Set rs = CreateObject("ADODB.Recordset")
rs.Open strSQL, con, 1 ' 1 = adOpenKeyset


If (rs.EOF) Then
rs.Close
Set rs = Nothing
Set con = Nothing
Exit Sub
End If


While (Not (rs.EOF))
lstNames.AddItem rs![PatientName]
rs.MoveNext
Wend


' Close the recordset and the database.

rs.Close
Set rs = Nothing
Set con = Nothing


End Sub
----
The VB code line 'lstNames.AddItem rs![PatientName]' is where the
information is sent to the value list box. How can I change this line to be
in SQL so I can then concatenate it? Thanks!

You can concatenated text at any time including in a form's value list box.

Just use this in source -- SELECT [FName] & " " & [LName], [OtherData] FROM
xxx

Thanks. The reason I wanted to insert the concatenated text into a field in
my table is that, on another form I want to list each record's full name in a
value list box. I can't think of any other way of getting each person's full
name listed unless there is a field containing their full name.

:

On Fri, 10 Feb 2006 14:42:09 -0800, Andrew Wilkins wrote:

Hi.

I have a form which allows the user to add records to my database. All of
the records are shown as text boxes on the form. Two of the fields are
'FirstName' and 'LastName'. Each field has a corresponding textbox on the
form (called 'txtFirstName' and 'txtLastName') so the user can type in the
names. I have also set up a third textbox which contains the expression
'=txtFirstName + " " + txtLastName'. This concatenates the contents of the
two text boxes. The trouble is I want this concatenated string to be added
into my table into the field 'FullName'.

Is this possible? How would it be done? I am an Access novice and so I have
no idea!

There is no need to store the FullName in your table.
As long as you have stored the FirstName and LastName fields (as you
are now doing), anytime you need to show the Full Nmae, simply
concatenate it in an UNBOUND text control.
=[FirstName] & " " & [LastName]

Note that the ampersand & is the concatenation symbol.
The plus + can be used but it has special characteristics that differ
from the ampersand.