Need to select email based on postcode entry, plz help

sneapdogg

Registered User.
Local time
Today, 05:06
Joined
Aug 25, 2010
Messages
11
Hi guys,

I have a database to record daily issues to be resolved for my team.
I have a form where a customers postcode is entered and from this entry i want access to automatically fill in the corresponding email for the team that will deal with it so I can just copy and paste it straight into an email, save me looking up the postcode in my little book every time.

Upto now i have added a table with 2 columns, 1 with all UK postcodes (first 2 letters only) and the other with the email address for the team that deals with the post code.

Not sure if this is best way to do it but please tell me if another ways works better.

Anyway, what i want it when a postcode is entered, the corresponding email shows up next to it. all it needs to match is the first two letters of a postcode.

Any help on this would be great.

Thanks

-SneapDogg-
 
Your two basic options are using DLookup() and opening a recordset. DLookup() is probably simpler. More info on syntax here:

http://www.mvps.org/access/general/gen0018.htm

You'll also want to use the Left() function to grab the first 2 characters.
 
I'm quite new at this, can you take a look at the screen shots of my table and my form and explain in a little more detail how to do this.

When i type a postcode in to the postcode box on the form, i want it to show the email for that postcode from the table in the bottom box of the form.

Thanks.

ps. sorry screenshots are b/w but these old ass pc's at wrk dont give me option to save as jpg or any kind of compressed file type had to save as bmp and use monochrome to fit it in 100kb limit.
 
Last edited:
In response to your PM, you would use the after update event of the textbox the user enters the postcode into, lookup the email address and put it in the email textbox:

Me.EmailTextboxName = DLookup("MailBox", "TableName", "Postcode = '" & Left(Me.PostcodeTextboxName, 2) & "'")
 
In response to your PM, you would use the after update event of the textbox the user enters the postcode into, lookup the email address and put it in the email textbox:

Me.EmailTextboxName = DLookup("MailBox", "TableName", "Postcode = '" & Left(Me.PostcodeTextboxName, 2) & "'")


Done the following as suggested:
=Me.email=DLookUp("Mailbox","PAV emails","postcode = '" & Left(Me.postcode,2) & "'")

but when I type a postcode in I get the following error:

The object doesnt contain the Automation Object 'Me.'.

Not sure why i'm getting this, can anyone point me to the answer?

-SneapDogg-
 
Last edited:
Moved it to the VBA module but getting syntax error and its highlighting the following.


Private Sub postcode_AfterUpdate()

=Me.email=DLookUp("Mailbox","PAV emails","postcode = '" & Left(Me.postcode,2) & "'")

End Sub


now i'm lost.
 
Code:
Private Sub postcode_AfterUpdate()

Me.email=DLookUp("Mailbox","PAV emails","postcode = '" & Left(Me.postcode,2) & "'")

End Sub

Remove the equal sign at the beginning.

I assume that the control on your form is called email.

JR
 

Users who are viewing this thread

Back
Top Bottom