How to adjacent field from a record

[DaveM]

I have a form (named WO Request) that utilizes a combo box (named SITE) to
show a list of phyiscal sites the operator can pick from. This combo is
bound to a table (named Site). The table has two fields, one is SiteID, and
SiteName.

My problem is that when the operator has selected the proper site in the
combo box and other related fields they select a "submit" command button.
The code on the event click of the command button extracts the contents of
the forms various fields and places them into variables that i place into a
email body. All field contents make it over to the email body properly
except for this combo box content shows up in the email body as a number (4
for example) which is actually the SiteID number from the Site table, not the
site's name which is shown in the combo box of the form. What i need is the
corresponding SiteName.

I believe i need a SQL statement somehow to get this correct but i'm not
having much luck with this part. Any help would be appreciated.

Thanks

DaveM

 

[Douglas J. Steele]

If the combo box contains both the SiteID and the SiteName, you can refer to
the value of SiteName for the currently selected item as
Me!MyComboBox.Column(1)

Note that the Column collection starts numbering at 0, so Column(1) refers
to the value in the second column.

 

[DaveM]

Douglas, thanks for this however the combo box only contains the Site's Name
(not the ID) and in my code i have Me.mycombobox.value which as i noted
returns the SiteID number from the table the box is bound to. Sorry for not
being clearer before.

Does that help clarify?

Thx in advance
DaveM

 

[John W. Vinson]

My problem is that when the operator has selected the proper site in the
combo box and other related fields they select a "submit" command button.
The code on the event click of the command button extracts the contents of
the forms various fields and places them into variables that i place into a
email body. All field contents make it over to the email body properly
except for this combo box content shows up in the email body as a number (4
for example) which is actually the SiteID number from the Site table, not the
site's name which is shown in the combo box of the form. What i need is the
corresponding SiteName.

I believe i need a SQL statement somehow to get this correct but i'm not
having much luck with this part. Any help would be appreciated.

Just create a Query joining your table to the site table on the SiteID to get
the site name.

Perhaps you could post your actual code... it should be easy enough to adapt
it, but we can't fix it without seeing it!

 

[Steve]

In your code where you need to get the site name, use this expression:

DLookup("[SiteName]","TblSite","[SiteID] = " & Forms![WO Request]!Site)

Steve
(e-mail address removed)

 

[Daryl S]

DaveM -

Try Douglas' solution anyway. The combobox may only display the name, but
if it is bound to the table, then the SiteID is there, but hidden - you know
it is there because that is the .value. Use .column(1) to see the name.

 

[DaveM]

good idea daryl...i should have tried that first. My appoligies. I did
adjust the column property to (1) and poof, there it was. Thank you to all
who replied.