If you simply want to create a form to display the fields of each record in a single text box then the simplest way is to create a form where all the fields are included. Suppose the following are two such records:
example record 1
Field1 : "DR001"
Field2 : "21/09/03"
Field3 : "Mary had a little lamb"
example record 2
Field1 : "DR002"
Field2 : "29/09/03"
Field3 : "It's fleece was white as snow."
Insert a new textbox control onto the form and set it's ControlSource to:
=[Field1] & [Field2] & [Field3]
Resize the new textbox control to enable all the information to be seen and set the visible property for the original fields to
No.
Variations to the above :
ControlSource to:
=[Field1] & " " & [Field2] & " " & [Field3] (this adds spaces between items)
ControlSource to:
=[Field1] & Chr(13) & Chr(10) & [Field2] & Chr(13) & Chr(10) & [Field3]
(this adds carriage returns between each item)
If you want to add a field to the table and modify the records by combining the data from Field1, Field2 and Field3 into a new Field4 then:
Open the table in design view.
Add a new field to the table structure by filling in the necessary details on the next empty line.
Save the table.
Now you have two easy choices:
1. Create an Update Query and run it. (Save it if you think you'll need it again.)
Select the Query tab on the database window.
Click New and select Design View.
Click Close on the Show Table dialogue without selecting a table.
Now an empty query design grid is visible with the View button (top left button on the form's toolbar) showing
SQL.
Click the SQL button and the SQL editor will open with the word
SELECT; highlighted. Replace this with the following code modified to suite your table name and field names.
UPDATE [Your Tablename] SET [Your Tablename].Field4 = [Your Tablename].Field1 & " " & [Your Tablename].Field2 & " " & [Your Tablename].Field3 ;
When you've done that click the View button (the one that showed SQL before). This will display the data in the table fields that you have selected without actually updating the table - if all's well.
The view button is now showing the blue set square symbol for Design View. Click this and study the information that now appears in the design grid.
The design grid is the fastest way to create a query normally but in this case I suggested typing directly into the SQL editor because it avoided me having to explain how to use the design grid which is explained quite well enough in the Access help files.
Assuming the query is constructed correctly the Run button (middle of the toolbar) with big fat
! mark will execute the query and modify the data.
Needless to say you do not
have to create a fourth field. You could just as easily append the contents of Fields 1, 2 and 3 into either of the second fields. Assuming that Field 1 is a numeric ID field it precludes combining text into it from other fields.
alternatively...
2. This choice involves creating a clone of the recordset or table and using the Edit and Update methods to modify the data in each record one by one. In execution it's just as quick but requires coding a sub routine in a module.
Private Sub Update_MyTable()
Dim dbs as Database, rst as Recordset
Set dbs = Currentdb
Set rst = dbs.OpenRecordset("MyTablename"
Rst.MoveFirst
With Rst
Do until Rst.EOF
.Append
!Field4 = Field1 & Field2 & Field3
.Update
.MoveNext
Loop
End with
Set Rst = Nothing
End Sub