Pull data from another form & place in email, using link criteria? 2

[Najemikon]

Hi,

I've finally got a link working from one of my forms for logging calls to produce an email, so an operator can forward details of a call he's taken straight to the relevant contact without retyping. It all works fine, apart from the subject line.

Basically, I'm using something like:

OutlookSubject = ContactName & " from " & CustomerName & " called " & DateTime

Works fine, except CustomerName is a link to another table; on the form itself it's a combo box where you can choose an entry from the customer table. The contents of the combo box is actually just the record number of the relevant link of course, but the standard Access combo wizard created a query in the background to display the name.

Now I'm trying to do it myself in VBA for this button & failing miserably! Obviously the result I get is a subject like "Fred from 231 called 20/10/04 09:32"

Can someone just point me in the right direction? Do I have to create a variable (to all intents, a new field) that can have the link criteria applied & how do I do that link?

Any help much appreciated!

 

[PJStephenson]

Have you tried Dlookup?
formula you need is something like
Dlookup("[CoName]","[CoTable]","[CoID] = " & CustomerName )
HTH
Peter

 

[Zion7]

Najemikon,
DLookup is good as Peter said, or is it possible to use the column property of the combobox?
It sounds like column 1 or Combo.Column(0) is your bound column, which holds Customer ID. Does Combo.Column(1), or Combo.Column(2), hold the actual name? If so...

OutlookSubject = ContactName & " from " & CustomerName.Column(1) & " called " & DateTime

...if it doesn't, you can make it so.

Private Sub Form_Load()
Dim SQL As String
SQL = _
"SELECT CustomerID, CustomerName " & _
"FROM CustomerTable"
With CustomerName
    .RowSource = SQL
    .BoundColumn = 1
    .ColumnCount = 2
    .ColumnWidths = "0 in;1 in"
    .RowSourceType = "Table/Query"
End With
End Sub

Either DLookup or this, will work.

Good Luck!

 

[Najemikon]

Thank you both very much! I had used Dlookup before, but couldn't get my head round it this time. That was just what I needed, Peter.

However, when I saw your post Zion, I went back to my original code & you're quite right; I just added .column(1) & it worked! I never knew it was so easy to get at the other columns. It seems so logical now!

Thanks again.