concattanate values from different tables in a form

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I want to join together severl field values from three different tables, i
have tried using the following code as a guess (i have loned my books out
doh) could someone please correct me.

Me.File_name = [csr_tbl].[CSR_Number] & " " & [csr_tbl].[CSR_Name] & " " &
[csr_option_tbl].[Option_Name] & "Planning" & Me.Drawing_Status
 
You can't simply reference a table and field name like you have here because
Access won't know which record's values to pull in. Presumably you csr_table
table has several records. You could use the DLookup function to return the
values from this table, but it will have to be based on some criteria. For
example, if this table has a primary key field named Id, you might have a
statement like this:

Me.File_name = DLookup("[CSR_Number] & [csr_Name]" , "csr_table", "Id = " &
Me.Id) & DLookup("[Option_Name]", "csr_option_tbl", "Id = " & Me.Id) &
"Planning" & Me.Drawing_Status

Another approach would be to create a query which receives the ID as a
parameter and joins the two tables. You could then use the DLookup against
the query.

Barry
 
It appears that you are trying to assign the value of a form control.
However, you can't simply make it equal to three entire fields in three
different tables. First, you have to be able to link the three tables via
some type of key. In other words, is there an ID field that is common to all
three tables and your form? This will allow you to return (hopefully) one
record instead of all the records. Let's say you have a control on your form
called Me.RecordID, and it exists in all three tables. The easy way to do
this is to include the three tables in the query that drives your form. The
query would show the three tables linked to the existing dataset via RecordID.
Hopefully these are all one-to-one relationships and not a one-to-many
relationships. Now, you can create an output in your query like this:
FileName: [csr_tbl].[CSR_Number] & " " & [csr_tbl].[CSR_Name] & " " &
[csr_option_tbl].[Option_Name] & "Planning" & [OriginalData].Drawing_Status.
Then add the new field you created, FileName, to your form.
I want to join together severl field values from three different tables, i
have tried using the following code as a guess (i have loned my books out
doh) could someone please correct me.

Me.File_name = [csr_tbl].[CSR_Number] & " " & [csr_tbl].[CSR_Name] & " " &
[csr_option_tbl].[Option_Name] & "Planning" & Me.Drawing_Status
Click to expand...
 
Back
Top