Complicated?

[Oli]

Hi

OK what I am trying to do is as follows:

I have a form (frm_termination) and this gets its information from
(tbl_termination).

The company I a writing this form bascially has 3 suppliers. If the field
(number) value is prefixed with one thing then when a command button is
pressed an email needs to go one supplier, and if the field is prefixed with
another thing then to another supplier etc.

The way I see to do this is create another table with 3 columns (ID , prefix
, email) and when my button is pressed it compares the first 7 digits (the
bit we are interested in) with the value 'prefix' in my new table. When a
match is found - it sends an email to the corresponding address.

The only question is, where do I start with something like this?

Many thanks in advnce and thanks for being so patient!

Oli

 

[Dirk Goldgar]

Hi

OK what I am trying to do is as follows:

I have a form (frm_termination) and this gets its information from
(tbl_termination).

The company I a writing this form bascially has 3 suppliers. If the
field (number) value is prefixed with one thing then when a command
button is pressed an email needs to go one supplier, and if the field
is prefixed with another thing then to another supplier etc.

The way I see to do this is create another table with 3 columns (ID ,
prefix , email) and when my button is pressed it compares the first 7
digits (the bit we are interested in) with the value 'prefix' in my
new table. When a match is found - it sends an email to the
corresponding address.

The only question is, where do I start with something like this?

Many thanks in advnce and thanks for being so patient!

Oli

Okay, suppose you've created the table you describe and called it, let's
say, "PrefixEmails". Then your command button's Click event procedure
might look something this:

'----- start of example code -----
Private Sub cmdEmailSupplier_Click()

Dim strEmail As String

strEmail = vbNullString & _
DLookup("Email", "PrefixEmails", _
"Prefix='" & Left(Me!SomeField, 7) & "'")

' Note: "SomeField" is the name of the field containing
' the prefix as the first 7 characters. I'm assuming that
' this is stored as a text field in the PrefixEmails table.

If Len(strEmail) = 0 Then
strEmail = _
InputBox("No e-mail address is on file. Please enter it:")
End If

If Len(strEmail) > 0 Then

DoCmd.SendObject acSendNoObject, _
To:=strEmail, _
Subject:="Your Subject", _
MessageText:="Your message text", _
EditMessage:=True

End If

End Sub
'----- end of example code -----

 

[Rebecca Riordan]

Well, I'm obliged to start by telling you that you're making life difficult
for yourself. It will be far easier if you have a "send to" field that
identifies the supplier explicitly. And it sounds like the "numbers" you're
using aren't "numbers" at all, but some kind of numeric code. (You only
have a number if you can add two values. Obviously adding two suppliers
isn't sensible, so they're not numbers.) At the very least, you should
consider storing this field as a string rather than a number.

That said, you often don't have complete control over these things, so if
you can't change your schema, what you'll need to do is use the LEFT
function (possibly having first converted the "number" to a string) to
extract the leftmost characters, which you then search for in the table.
You can do this either in code or by calling a query.

HTH

--
Rebecca Riordan, MVP

Designing Relational Database Systems
Microsoft SQL Server 2000 Programming Step by Step
Microsoft ADO.NET Step by Step

http://www.microsoft.com/mspress

Blessed are they who can laugh at themselves,
for they shall never cease to be amused...

 

[Oli]

Thanks Dirk - one last thing, in the MessageText - how do I insert a new
line?

TIA
Oli

 

[Dirk Goldgar]

Thanks Dirk - one last thing, in the MessageText - how do I insert a
new line?

MessageText:="This is the first line." & vbCrLf & _
"This is the second line."