Thread: email id creation (access 2000)

1. Registered User
Join Date
Mar 2004
Posts
4

Unanswered: email id creation (access 2000)

I am asked to create a simple db for email id creation. First, Middle and Last names to be entered. The first alphabet of the first name, the first alphabet of the Middle name and the first alphabet of the last name should be combined and added to 2004. e.g. John E Doe would have email id of JED2004. I wanted to know how I could automate this by putting in a formula to pick these first alphabets from the name and add the year. Also if another JED2004 exists in the db I would like it to show the duplication and increment the year by 1 to JED2005. I would appreciate all help and suggestions on this. I am a self learner in access and all help is appreciated. All dos and don't would be welcome too.
Thank you

2. Registered User
Join Date
Oct 2003
Location
Posts
574
You would have to start out with a table that contains all current email id's. Then use a form that has 2 text boxes. The top one is where the user enters the person's name. Make a button that will calculate the email id and put it into the second box. Another button called Accept could save the name and email id into the table.

Private Sub cmdCalc_Click()
Dim cnt As Integer, stID As String
DoCmd.RunCommand acCmdSaveRecord
stID = left(toptextboxname, 1)
cnt = InStr(1, toptextboxname, " ") + 1
If cnt <> 1 Then
stID = stID & Mid(toptextboxname, cnt, 1)
cnt = InStr(cnt, toptextboxname, " ") + 1
If cnt <> 1 Then 'in case a middle name/initial is not given/available
stID = stID & Mid(toptextboxname, cnt, 1)
End If
End If
For cnt = 0 To 1000
If DCount("EmailId", "YourTableName", "EmailID = '" & stID & Year(Date) + cnt & "' ") = 0 Then Exit For
Next cnt
Bottomtextboxname = stID & Year(Date) + cnt
End Sub

3. Registered User
Join Date
Mar 2004
Posts
4

question and thank you

Hi jmrSudbury,

Thank you for your help on this. I did the following:

First created a table (named Table1) with one field called EmailId and added current email ids to the table.

Then created a form with 2 text boxes, top one was named Name and the bottom one was labelled EmailId. Created 2 buttons, the first one was Save button created from the Button Command wizard. For the second button I created the button then right clicked and selected Build Event. Then selected code builder from the choices. When I came to the VB screen instered your codes there. Changed all the toptextboxname to Name and YourTableName to Table1. Saved it compiled it. When I compiled there came a message saying that "Compiler error - Sub or Function not defined" for the line

stID = stID & Mid(Name, cnt, 1)
***
Also the below lines are in red:
*** If cnt <> 1 Then 'in case a middle name/initial is not given/available
*******
*** End If

If DCount("EmailId", "Table1", "EmailID = '" & stID & Year(Date) + cnt &
"' ") = 0 Then Exit For Next cnt

4. Registered User
Join Date
Oct 2003
Location
Posts
574
It worked fine here in Access 2003. Check your references (Tools -> References) for any that say Missing and uncheck them. As well, rename your textbox from Name to a non-reserved word, or put name in brackets thus: [Name]

If that does not work, then try hardcoding some numbers just for a test:

dim stid2
stID2 = Mid([New_Name], 2, 1)

stid2 should get the second character in the name field. If you still get a compile error, then you can try the same with left and rights:

stid2 = left(right([NewName],len([New_Name]-2)),1)

5. Registered User
Join Date
Mar 2004
Posts
4

Email ID - more....

Hi jmrSudbury,

Option Compare Database
Option Explicit

Private Sub cmdCalc_Click()
Dim cnt As Integer, stID As String
DoCmd.RunCommand acCmdSaveRecord
stID = Left([User], 1)
cnt = InStr(1, [User], " ") + 1
If cnt <> 1 Then
stID = stID & Mid([User], cnt, 1)
cnt = InStr(cnt, [User], " ") + 1
If cnt <> 1 Then 'in case a middle name/initial is not given/available
stID = stID & Mid([User], cnt, 1)
End If
End If
For cnt = 0 To 1000
If DCount("emailID", "Table1", "emailID = '" & stID & Year(Date) + cnt &
"' ") = 0 Then Exit For
Next cnt
email = stID & Year(Date) + cnt

End Sub

The problem appears to be in this line which appears in red:
If DCount("emailID", "Table1", "emailID = '" & stID & Year(Date) + cnt &
"' ") = 0 Then Exit For

And when I compile it says syntax error. So I guess it would be something silly which we can fix. My table has one field named emailID and table is saved as Table1. The form has 2 text boxes, User and email respectively. I created a button and selected build event to insert code. I use a Mac machine and Access is used through Citrix Client. Access ver. is 2002. Please advise as to how I can make this work. Thank you for all your help
Last edited by serendb; 05-10-04 at 04:54.

6. Registered User
Join Date
Oct 2003
Location
Posts
574
Like I said, it worked for me as I posted it. You could make sure that you do not need a line continuation:

If DCount("emailID", "Table1", "emailID = '" & stID & Year(Date) + cnt &
"' ") = 0 Then Exit For

to be change to:

If DCount("emailID", "Table1", "emailID = '" & stID & Year(Date) + cnt & _
"' ") = 0 Then Exit For

If that does not fix it, try hard coding things like replacing year(date) with 2004 until it works:

If DCount("emailID", "Table1", "emailID = '" & stID & 2004 + cnt & "' ") = 0 Then Exit For

Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts
•