autofill domain name

[132433]

Helle people,

A new dutch man, with terribel englisch....

I have a form for my customers where i also fill in the email adresses, know i don't wan't to fill in the hole email adres each time.

I have seen a acces database before that gives a list when you typed the @ sign in the email adres field.

I know that the builder used a tbldomain for listing al the domains that the putin the database. and each time you tab to the field emailsadres you begin to type

example.. markrutten@ and when you type the @ there popup a list of the table [tbldomain] en you begin to type for example Hotmail.com

but you can hit the entrebutton afther the letters hot because the match of Hotmail.com is already in the field.

I hope I make my question a little bit clear??

 

[missinglinq]

Here's a step-by-step for doing this.

You’ll need to create

• A Table for Domains with a single field;
• Name the field DomainName
• Name the Table tblDomains
• Enter all pertinent Domain Names in the table

Now, you'll need to

• Create a Combobox
• Place it to the immediate Right of the EmailAddress textbox and delete its attached Label
• Select "I want the combobox to lookup values in a table…"
• Select the tblDomains as the RowSource
• Move the DomainName field to the right-hand box to include it in the Combobox
• Sort on the DomainName Field
• Size the Column to show all of the Domain Names
• Select "Remember the value for later use"
• Name the Combobox cboDomains

Now, use this code, replacing EmailAddress, in all code, with the actual name of your Textbox that holds the email address.

This code ensures that the user includes the @ Symbol in the address before leaving the textbox

Private Sub EmailAddress_BeforeUpdate(Cancel As Integer)
 
 If InStr(Me.EmailAddress, "@") = 0 Then
  MsgBox "The Email Address Must Include the  @  Symbol!"
  Cancel = True
 End If

End Sub

When the @ symbol is entered in the address Textbox the Focus automatically moves to the Combobox

Private Sub EmailAddress_Change()
 
 If Right(Me.EmailAddress.Text, 1) = "@" Then
  cboDomains.SetFocus
 End If

End Sub

As the user begins to type in the domain name, Access will go to the first matching selection. Alternatively, the user can simply scroll down the list. When a Domain is selected from the Combobox, it is added to the data in the email address textbox. If the user makes a mistake they can select another Domain. The code will remove everything after the @ symbol and add the new domain name, as it did before. Note that it will not add a Domain unless the @ Symbol is present in the Email Address textbox

Private Sub cboDomains_AfterUpdate()
 
 Dim AT As Integer
 
 If Right(Me.EmailAddress, 1) = "@" Then
  Me.EmailAddress = Me.EmailAddress & Me.cboDomains
 Else
   AT = InStr(Me.EmailAddress, "@")
    If AT > 0 Then
     Me.EmailAddress = Left(Me.EmailAddress, AT) & Me.cboDomains
    End If
  End If
 
Me.cboDomains = Null

End Sub

This code is simply to insure that when you move to a Record the Combobox isn't the first Control to get the Focus. You need to replace AnyTextbox with the name of any Control you want, on your Form, except cboDomains.

Private Sub Form_Current()
 AnyTextbox.SetFocus
End Sub

This last bit of code is optional; it's not needed, however, for the rest of the code to work.

If you want the Combobox to 'drop down the list' when the Control gets the Focus, you can add this code

Private Sub cboDomains_GotFocus()
 Me.cboDomains.Dropdown
End Sub

You should be set, now! You

• Enter the email address, including the @ Symbol
• Focus automatically moves to the Combobox
• You select the Domain Name
• The Domain Name is added to the rest of the email address.

Linq ;0)>

 

[132433]

Hy There,

It work's olmost fine.

But when I put in a new domain, that isn't in the tabel yet, how can I make shure That he will save it in the list.

 

[missinglinq]

You would use the Not in List Property of the Combobox. Here's an excellent tutorial on the subject:

http://www.databasedev.co.uk/not_in_list.html

Linq ;0)>

 

[132433]

Thanks for the tip,

With some help of Google and a dutch template i have made this code for the cbo and it works fine...

Private Sub cboEmailDomein_NotInList(NewData As String, Response As Integer)
If MsgBox("Deze soort bestaat niet." & vbLf & vbLf & "Toevoegen?", vbYesNo, "Onbekend") = vbYes Then
    Response = acDataErrAdded
    DoCmd.RunSQL "INSERT INTO tblEmailDomein(Domeinnaam) VALUES('" & NewData & "')"
Else
    Response = acDataErrContinue
    Me.cboEmailDomein.Undo
End If
End Sub

Now it's just a matter of fine tunning the layout of it all.

greetzs Martijn

 

[132433]

Oké,

I was ready I thought, but in mij form I have two email fields the other one is for the Partner.
Must I now paste the hole code again in the VBA with the field names from the other field??

The Above is solved!!

Amn I Like Access 2013...

 

[missinglinq]

You can use the single Combobox you've already created, with the change of code in one event and with the addition of two additional events.

First off, replace the previous cboDomains_AfterUpdate code with this:

Private Sub cboDomains_AfterUpdate()
 
 Dim AT As Integer
 
 If Screen.PreviousControl.Name = "EmailAddress" Then
  If Right(Me.EmailAddress, 1) = "@" Then
   Me.EmailAddress = Me.EmailAddress & Me.cboDomains
  Else
   AT = InStr(Me.EmailAddress, "@")
    If AT > 0 Then
     Me.EmailAddress = Left(Me.EmailAddress, AT) & Me.cboDomains
    End If
  End If
 End If

If Screen.PreviousControl.Name = "EmailAddress2" Then
  If Right(Me.EmailAddress2, 1) = "@" Then
   Me.EmailAddress2 = Me.EmailAddress2 & Me.cboDomains
  Else
   AT = InStr(Me.EmailAddress2, "@")
    If AT > 0 Then
     Me.EmailAddress2 = Left(Me.EmailAddress2, AT) & Me.cboDomains
    End If
  End If
 End If

Me.cboDomains = Null

End Sub

You'll need to replace every instance of EmailAddress2 with the actual name of the second email address Textbox.

Then add these two event codes, once again replacing every instance of EmailAddress2 with the actual name of the second email address Textbox.

Private Sub EmailAddress2_BeforeUpdate(Cancel As Integer)
 
 If InStr(Me.EmailAddress2, "@") = 0 Then
  MsgBox "The Second Email Address Must Include the  @  Symbol!"
  Cancel = True
 End If

End Sub

Private Sub EmailAddress2_Change()
 
 If Right(Me.EmailAddress2.Text, 1) = "@" Then
  cboDomains.SetFocus
 End If

End Sub

If the user should have to change a Domain Name, after the fact, i.e. at some later date, having found that a mistake was made previously, they will need to

• Click into the appropriate email address textbox
• Then select the new Domain Name from the Combobox.

This allows Access to know which field the Combobox is supposed to be adding the Domain Name to.

Linq ;0)>