Email address inserted from non related table (1 Viewer)

[Marlene]

I have a table that holds company information for the user - eg farm name, company name, manager, phone number and email address etc. Its simply used to provide headers for reports, so that the same generic database can be used on several farms. There is only one record in this table tblfarmdetails
The second table tblorder is for orders and receipts - I currently have code that allows the user to send an order request by email to the manager, and to also send notice of receipt of goods through to the manager.

I would like the code to get the email address from the unrelated table.
Can I reference this field in the vba?

Private Sub cmdemailorder_Click()

Dim orderdate As String
Dim stafford As String
Dim item As String
Dim itemamnt As String


'**create variables for Outlook
Dim objOutlook As Outlook.Application
Dim objEmail As Outlook.MailItem


orderdate = Me!orderdate
stafford = Me!Name
item = Me!item
itemamnt = Me!itemamnt


Set objOutlook = CreateObject("Outlook.application")
Set objEmail = objOutlook.CreateItem(olMailItem)


With objEmail
.To = email address here stored in tblfarmdetails!email
.Subject = "Order Request"
.Body = stafford & " would like you to order " & item & ". Amount: " & itemamnt
.Display
End With


Set objEmail = Nothing

End Sub

 

[plog]

Yes, just use a Dlookup:
http://www.techonthenet.com/access/functions/domain/dlookup.php

 

[Marlene]

Can you use a dlookup when there is no relationship, and you are just retrieving the only record in the unrelated table?

 

[pbaldy]

plog appears to be offline. Since your table has a single record, a DLookup() without a criteria will return your value, yes.

 

[Marlene]

Thankyou, I will try it.

 

[Marlene]

I can't seem to get the right syntax - I've created a text box in the orders form to retrieve the email address via the dlookup
=DLookUp([tblfarmdetails]![emailadmin],[tblfarmdetails]) but it returns #Name error. What am I doing wrong?

 

[pbaldy]

Did you notice in plog's link that the table and field names had quotes around them? Without a criteria, it would look like:

DLookup("FieldName" , "TableName")

 

[Marlene]

Paul
I didn't notice but I should have - the little things make the difference. I used the expression builder to enter the values... thank you to you both

 

[pbaldy]

Plog was happy to help and I was happy to fill in.