Results 1 to 6 of 6
  1. #1
    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. #2
    Join Date
    Oct 2003
    Location
    Canada
    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. #3
    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. #4
    Join Date
    Oct 2003
    Location
    Canada
    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. #5
    Join Date
    Mar 2004
    Posts
    4

    Email ID - more....

    Hi jmrSudbury,

    Thank you for your reply. Please see below the changes I made to the script:

    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. #6
    Join Date
    Oct 2003
    Location
    Canada
    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
  •