Concatenating data

  • Thread starter Thread starter HOW1
  • Start date Start date
H

HOW1

Hi,

I wish to concatentate the data from 5 fields and display results in another
field. I need this to take place when the exiting the 5th field while adding
a new record to the table. Can someone give me the visual basic code to
program this function.
Please note that I am just a beginner therefore I would require the full
code to enter into the command.

Thanks in advance.
 
HOW1 said:
Hi,

I wish to concatentate the data from 5 fields and display results in
another field. I need this to take place when the exiting the 5th
field while adding a new record to the table. Can someone give me the
visual basic code to program this function.
Please note that I am just a beginner therefore I would require the
full code to enter into the command.

Thanks in advance.
Click to expand...

You cannot do this while entering directly in a table (something you shouldn't
be doing anyway). More importantly you should not store the result of such a
calculation in any table.

Create a SELECT query and add the concatenation expression as a calculated field
in the query. Then just use the query any place you would otherwise have used
the table and the concatenated value will be available with no code required at
all.
 
Rick,

Thanks for that.

I have 3 tables. Table 1 & 2 have a one to one relationship and table 2 & 3
have a many to many relationship. I was trying to convert this to a one to
one relationship by concatenating the data to simplify the relationships
between the tables. However it looks like I may need to use many to many
relationship between tables 2 & 3 afterall. I have read the instruction on
how to do this. However is seems a little complicated for me.
It talks about using junction tables. Can you help advise on the
relationships I would need to create between my 3 tables.

Thanks again.
 
You might want to list the names of your three tables and the primary index
of each. Here's the code anyway.

Dim dbs As ADODB.Connection, strSQL As String, strFiveFields As String
strFiveFields = Me.Fields1 & Me.Fields2 & Me.Fields3 & Me.Fields4 &
Me.Fields5
If Len(Trim(strFiveFields)) > 0 then
strSQL = "INSERT INTO NewTableName (FieldName) Values ( '" & strFiveFields
& "')"
Set dbs = CurrentProject.Connection
dbs.Execute strSQL ' run the insertquery
Set dbs = Nothing
End If
 
It would help if you describe your database's structure and purpose. A
many-to-many relationship requires three tables (including the junction
table), and a one-to-one is for specialized purposes. A one-to-one
relationship means that for each record in one table there is no more than
one corresponding record in the other table. An example of a many-to-many
relationship is courses and students. Each student can be in many courses,
and each course is attended by many students.
 
Back
Top