List box for suggested Email IDs

[130975]

Hi Everyone,
I am here again with a problem. Lots of work has been done on my Email ID creation database, of course with TSDN help.
My problem is that, in my database I have two tables Student Master, which has the basic information like
Stud Code Text PK
First Name Text
Last Name Text
Class Number
Age Number

And another table Email ID table, which has
Stud Code FK
Email ID PK
Creation date Date/Time

I have a form based on the master table and a button which opens the Email form. The Stud Code is passed from the main form. In the Email form the Email ID field is checked for duplicates. The format for the email ID goes like this
[First Name].[Last Name]@abc.com. In case of duplication the system has to add 1 or 2 or 3 before @ sign like [First Name].[Last Name][e-mail]. What my problem is that I want a list box to show the available suggested (format) Email ID's in case of duplication, so that the user can choose form there. And also show which IDs are used by the system.
Thanks and Regards
130975

[nico5038]

For showing the existing Email ID's you can use a query with the criteria:
LIKE [First Name] & "." & [Last Name] & "%"

To get the needed number when you have duplicates use a Dcount() with the same LIKE in the WHERE section.

Need more assistance ?

Nic;o)

[130975]

Hi Nico;o

Thanks for quick reply. But the point is not clear to me, should I add a list box in the Email form? If so please give me the details of the list box properties. The two forms are connected with the Stud ID, the Email ID is created according to the name in that Stud ID. The List box should display the same name Email IDs. I am little bit confused. Kindly help.

Thanks and Regards
130975

[nico5038]

As stated, forshowing the existing Email ID's you can use a query with the criteria:
LIKE [First Name] & "." & [Last Name] & "%"
So define a listbox (or datasheet subform) on your email form. With this criteria Access will show matches when present.

When you need to issue an emailaddress you'll need to check or the combination exists, to get the needed number when you have duplicates use a Dcount() with the same LIKE in the WHERE section.
The DCount() will be Null when there's no match found so a test like

IF IsNull(Dcount("*","tblEmailAddresses","EmailAddress like '" & [First Name] & "." & [Last Name] & "%'") then
   Me.EmailAddress = [First Name] & "." & [Last Name] & "@yoursite.com'")
else
   Me.EmailAddress = [First Name] & "." & [Last Name] & Dcount("*","tblEmailAddresses","EmailAddress like '" & [First Name] & "." & [Last Name] & "%'") & "@yoursite.com"
endif
 

Nic;o)