Concatenate strings from different tables

I hope someone is able to help me with this. I have three related
tables: Connections (tbl_CNX), Systems (tbl_SYS), and Components
(tbl_CMP). These tables have each been set up with a description
field. I have created a form that uses comboboxes to identify
interfaces by connections between components in different systems.
Everything is working great.

My problem is this: I need to grab the descriptions from each table
and concatenate the descriptions into one string. Here's an example:
tbl_CNX.Conn_Type = VDC
tbl_CNX.Conn_Description = "Voltage DC"
tbl_SYS.ID = 1
tbl_SYS.Description = "Engine Compartment"
tbl_SYS.ID = 2
tbl_SYS.Description = "Surveillance System"
tbl_CMP.ID = A
tbl_CMP.Description = "Generator"
tbl_CMP.ID = B
tbl_CMP.Description = "Power Converter"

Let's say there is a VDC connection between System 1 Component A and
System 2 Component B. The key on my form would look like this
VAC.1A.2B and my desired description would read "Voltage DC connection
from Engine Compartment Generator to Surveillance System Power
Converter".
Reference:: Sikh Philosophy Network http://www.sikhphilosophy.net/information-technology/12509-concatenate-strings-from-different-tables.html

I have tried to use SQL to accomplish this but have had difficulty
since i am not very experienced in that language. I would prefer to be
able to assign a string variable to the descriptions that match the
type and ID's but I'm not sure how to go about this either. If I could
Reference:: Sikh Philosophy Network http://www.sikhphilosophy.net/showthread.php?t=12509
get the descriptions into a series of string variables, I could easily
accomplish what I need. Thanks for your time and any help you can
provide.

 
Do share your immediate thoughts or reactions on this issue? We value your views! Login Now! or Sign Up Today! to share your views with us.. Gurfateh!

Still hoping for help. Just to further clarify, is there a way to
assign a value to a string variable based in VBA logic? For example:

Dim str_cnx as string
Dim strSQL as string

strSQL = "SELECT tbl_CNX.Conn_Description FROM tbl_CNX "
strSQL = strSQL & "WHERE tbl_CNX.Conn_Type = FORM_CNX.conn_type"

str_cnx = Docmd.RunSQL (strSQL)
Reference:: Sikh Philosophy Network http://www.sikhphilosophy.net/showthread.php?t=12509

I know this doesn't work, but this gives a pretty good explanation of
what I am trying to accomplish. Again, thanks for any help.
Reference:: Sikh Philosophy Network http://www.sikhphilosophy.net/showthread.php?t=12509

str_cnx = DLookup("[Conn_Description]", "[tbl_CNX]", _
"Conn_Type = " & Forms!FORM_CNX!conn_type)

This assumes that a) Conn_Type is a numeric field and b) form FORM_CNX is
open when you run this.

If Conn_Type is text, use:
Reference:: Sikh Philosophy Network http://www.sikhphilosophy.net/showthread.php?t=12509

str_cnx = DLookup("[Conn_Description]", "[tbl_CNX]", _
"Conn_Type = '" & Forms!FORM_CNX!conn_type & "'")
Reference:: Sikh Philosophy Network http://www.sikhphilosophy.net/showthread.php?t=12509

or

str_cnx = DLookup("[Conn_Description]", "[tbl_CNX]", _
"Conn_Type = " Chr$(34) & Forms!FORM_CNX!conn_type & Chr$(34))



--
Doug Steele, Microsoft Access MVP
http://I.Am/DougSteele
(no e-mails, please!)


"Glen T" wrote in message
news:1152102352.196891.62080@j8g2000cwa.googlegrou ps.com...
> Still hoping for help. Just to further clarify, is there a way to
> assign a value to a string variable based in VBA logic? For example:
>
> Dim str_cnx as string
> Dim strSQL as string
>
> strSQL = "SELECT tbl_CNX.Conn_Description FROM tbl_CNX "
> strSQL = strSQL & "WHERE tbl_CNX.Conn_Type = FORM_CNX.conn_type"
>
> str_cnx = Docmd.RunSQL (strSQL)
>
> I know this doesn't work, but this gives a pretty good explanation of
> what I am trying to accomplish. Again, thanks for any help.
>

Thank you Douglas


Douglas J Steele wrote:
> str_cnx = DLookup("[Conn_Description]", "[tbl_CNX]", _
> "Conn_Type = " & Forms!FORM_CNX!conn_type)
>
> This assumes that a) Conn_Type is a numeric field and b) form FORM_CNX is
> open when you run this.
>
> If Conn_Type is text, use:
>
> str_cnx = DLookup("[Conn_Description]", "[tbl_CNX]", _
> "Conn_Type = '" & Forms!FORM_CNX!conn_type & "'")
>
> or
>
> str_cnx = DLookup("[Conn_Description]", "[tbl_CNX]", _
> "Conn_Type = " Chr$(34) & Forms!FORM_CNX!conn_type & Chr$(34))
>
>
>
> --
> Doug Steele, Microsoft Access MVP
> http://I.Am/DougSteele
> (no e-mails, please!)
>
>
> "Glen T" wrote in message
> news:1152102352.196891.62080@j8g2000cwa.googlegrou ps.com...
> > Still hoping for help. Just to further clarify, is there a way to
> > assign a value to a string variable based in VBA logic? For example:
Reference:: Sikh Philosophy Network http://www.sikhphilosophy.net/showthread.php?t=12509
> >
> > Dim str_cnx as string
> > Dim strSQL as string
> >
> > strSQL = "SELECT tbl_CNX.Conn_Description FROM tbl_CNX "
> > strSQL = strSQL & "WHERE tbl_CNX.Conn_Type = FORM_CNX.conn_type"
> >
> > str_cnx = Docmd.RunSQL (strSQL)
> >
> > I know this doesn't work, but this gives a pretty good explanation of
Reference:: Sikh Philosophy Network http://www.sikhphilosophy.net/showthread.php?t=12509
> > what I am trying to accomplish. Again, thanks for any help.
> >