Hi Guys,
I have an aceess database created in access 2000 but works perfectly fine in 2007/2010. We have a button that makes changes to a table and sends the report(s) to a printer. What i am trying to achieve is to add a second button to send the report as an attachment so it can be emailed, and to call another database where the email address is stored.
The current button calls a database to get the address information and automatically sends it to the printer. So i thought i would just copy this button and amend it to my needs, as it will be doing 90% of the same functions.
The button code for that one is as follows:
I am reasonable at databases but when it comes to VB i am pretty novice. What are the lines that i need to change in order to achieve my needs?
Many thanks!
I have an aceess database created in access 2000 but works perfectly fine in 2007/2010. We have a button that makes changes to a table and sends the report(s) to a printer. What i am trying to achieve is to add a second button to send the report as an attachment so it can be emailed, and to call another database where the email address is stored.
The current button calls a database to get the address information and automatically sends it to the printer. So i thought i would just copy this button and amend it to my needs, as it will be doing 90% of the same functions.
The button code for that one is as follows:
Code:
Private Sub Button73_Click()
On Error GoTo Err_Button73_Click
Dim MyDB As Database, myset As Recordset, mytable As Recordset, myseen As Recordset
Dim Myworks As Workspace, Mymem As Recordset, Myprints As Recordset, Mypicture As Recordset
Set Myworks = DBEngine.Workspaces(0)
Set MyDB = Myworks.Databases(0)
Set myset = MyDB.OpenRecordset("Matchmaker", DB_OPEN_DYNASET)
Set mytable = MyDB.OpenRecordset("MatchesFound", DB_OPEN_DYNASET)
Set myseen = MyDB.OpenRecordset("Memberseen", DB_OPEN_DYNASET)
Set Mymem = MyDB.OpenRecordset("Activemembers", DB_OPEN_DYNASET)
Set Myprints = MyDB.OpenRecordset("ProfilePrint", DB_OPEN_DYNASET)
Set Mypicture = MyDB.OpenRecordset("MemPhoto", DB_OPEN_DYNASET)
Set MyNewprints = MyDB.OpenRecordset("NewProfilePrint", DB_OPEN_DYNASET)
Response = MsgBox("Are you Sure you want to Complete this profile match?", 4)
If Response = 6 Then GoTo Doem
Exit Sub
Doem:
If Myprints.RecordCount <> 0 Then
Myprints.MoveFirst
Do Until Myprints.EOF
Myprints.Edit
Myprints.Delete
Myprints.MoveNext
Loop
End If
If MyNewprints.RecordCount <> 0 Then
MyNewprints.MoveFirst
Do Until MyNewprints.EOF
MyNewprints.Edit
MyNewprints.Delete
MyNewprints.MoveNext
Loop
End If
Myworks.BeginTrans
mytable.MoveFirst ' Membership File
Myprints.AddNew
MyNewprints.AddNew
Mymem.MoveFirst
Do Until Mymem.EOF
If Mymem!SocialNo = mytable!MastSocialNo Then
Myprints!Addlin1 = Mymem!Addlin1
Myprints!Addlin2 = Mymem!Addlin2
Myprints!Addlin3 = Mymem!Addlin3
Myprints!Addlin4 = Mymem!Addlin4
Myprints!Postcode = Mymem!Postcode
MyNewprints!Addlin1 = Mymem!Addlin1
MyNewprints!Addlin2 = Mymem!Addlin2
MyNewprints!Addlin3 = Mymem!Addlin3
MyNewprints!Addlin4 = Mymem!Addlin4
MyNewprints!Postcode = Mymem!Postcode
GoTo otherdets
End If
Mymem.MoveNext
Loop
otherdets:
myset.MoveFirst
Do Until myset.EOF
If myset!SocialNo = mytable!MastSocialNo Then
Myprints!SocialNo1 = myset!SocialNo
Myprints!SocialNo2 = mytable!SlavSocialNo
Myprints!Memtype = myset!Memtype
Myprints!Firstname = myset!Firstname
Myprints!Surname = myset!Surname
Myprints!Contactphone = myset!Contactphone
Myprints!Nationality = myset!Nationality
Myprints![Marital Status] = myset![Marital Status]
Myprints!YearofBirth = myset!YearofBirth
Myprints!Occupation = myset!Occupation
Myprints!Children = myset!Children
Myprints!Height = myset!Height
Myprints!Weight = myset!Weight
Myprints!Build = myset!Build
Myprints!Hair = myset!Hair
Myprints!Eyes = myset!Eyes
Myprints!Accommodation = myset!Accommodation
Myprints![Live with] = myset![Live with]
Myprints!Smoke = myset!Smoke
Myprints![Drink text] = myset![Drink text]
Myprints!Drive = myset!Drive
Myprints![Own Car] = myset![Own Car]
Myprints!Newspapers = myset!Newspapers
Myprints!County = myset!County
Myprints!Classical = myset!Classical
Myprints![Country and Western] = myset![Country and Western]
Myprints!Folk = myset!Folk
Myprints!Jazz = myset!Jazz
Myprints!Opera = myset!Opera
Myprints!Pop = myset!Pop
Myprints!Rock = myset!Rock
Myprints![Winter Sports] = myset![Winter Sports]
Myprints![Water Sports] = myset![Water Sports]
Myprints!Sailing = myset!Sailing
Myprints![Summer Sports] = myset![Summer Sports]
Myprints![Team Games] = myset![Team Games]
Myprints![Watching Sports] = myset![Watching Sports]
Myprints!Reading = myset!Reading
Myprints!DIY = myset!DIY
Myprints!Gardening = myset!Gardening
Myprints!Television = myset!Television
Myprints!Films = myset!Films
Myprints!Photography = myset!Photography
Myprints!Bridge = myset!Bridge
Myprints!Chess = myset!Chess
Myprints!Cooking = myset!Cooking
Myprints!Cinema = myset!Cinema
Myprints!Discos = myset!Discos
Myprints!Pubs = myset!Pubs
Myprints!Concerts = myset!Concerts
Myprints![Night Clubs] = myset![Night Clubs]
Myprints!Restaurants = myset!Restaurants
Myprints![Dinner Parties] = myset![Dinner Parties]
Myprints!Parties = myset!Parties
Myprints![Wine Bars] = myset![Wine Bars]
Myprints![Additional Information] = myset![Additional Information]
Myprints!Affectionate = myset!Affectionate
Myprints!Romantic = myset!Romantic
Myprints!Confident = myset!Confident
Myprints!Gregarious = myset!Gregarious
Myprints!Serious = myset!Serious
Myprints!Tolerant = myset!Tolerant
Myprints!Sentimental = myset!Sentimental
Myprints!Considerate = myset!Considerate
Myprints!Humorous = myset!Humorous
Myprints!Strongwilled = myset!Strongwilled
Myprints!Shy = myset!Shy
Myprints!Intellectual = myset!Intellectual
Myprints!Predictable = myset!Predictable
Myprints!Adventurous = myset!Adventurous
Myprints!Sensitive = myset!Sensitive
Myprints!Extrovert = myset!Extrovert
Myprints!Ambitious = myset!Ambitious
Myprints!Reserved = myset!Reserved
Myprints!Dependable = myset!Dependable
Myprints!Practical = myset!Practical
Myprints!Fashionconscious = myset!Fashionconscious
Myprints.Update
MyNewprints!SocialNo1 = myset!SocialNo
MyNewprints!SocialNo2 = mytable!SlavSocialNo
MyNewprints!Memtype = myset!Memtype
MyNewprints!Firstname = myset!Firstname
MyNewprints!Surname = myset!Surname
MyNewprints!Contactphone = myset!Contactphone
MyNewprints!Nationality = myset!Nationality
MyNewprints![Marital Status] = myset![Marital Status]
MyNewprints!YearofBirth = myset!YearofBirth
MyNewprints!Personality = myset!Personality
MyNewprints!IPartner = myset!IPartner
MyNewprints!Holiday = myset!Holiday
MyNewprints!Interests = myset!Interests
MyNewprints!FavMusic = myset!FavMusic
MyNewprints!FavBooks = myset!FavBooks
MyNewprints!FavFilms = myset!FavFilms
MyNewprints!FavSports = myset!FavSports
MyNewprints!Faveven = myset!Faveven
MyNewprints!Happiness = myset!Happiness
MyNewprints!Introducing = myset!Introducing
Mypicture.MoveFirst
Do Until Mypicture.EOF
If Mypicture!SocialNo = myset!SocialNo Then
MyNewprints!Myphoto = Mypicture!Photo
GoTo Reccomplete
Else
Mypicture.MoveNext
End If
Loop
Reccomplete:
MyNewprints.Update
GoTo othermemdets
End If
myset.MoveNext
Loop
othermemdets:
myset.MoveFirst
Do Until myset.EOF
If myset!SocialNo = mytable!MastSocialNo Then ' Update Date to send next profile.
myset.Edit ' Enable editing.
myset!Lastsentdate = Date
myset!Nextsenddate = (Date + myset!NoofDays)
myset.Update ' Save changes.
GoTo shutiters
Else
myset.MoveNext ' Find next occurrence.
End If
Loop ' End of loop.
shutiters:
Do Until mytable.EOF
myseen.AddNew
myseen!MastSocialNo = mytable!SlavSocialNo
myseen!SlavSocialNo = mytable!MastSocialNo
myseen!MATName = Forms![MembersFound]![Firstname]
myseen!MATSurname = Forms![MembersFound]![Surname]
myseen!MatchDate = mytable!MatchDate
myseen.Update
myseen.AddNew
myseen!MastSocialNo = mytable!MastSocialNo
myseen!SlavSocialNo = mytable!SlavSocialNo
myseen!MATName = mytable!Firstname
myseen!MATSurname = mytable!Surname
myseen!MatchDate = mytable!MatchDate
myseen.Update
' other matched member
If mytable!Memtype <> "Matchmaker" Then
myset.MoveFirst
Do Until myset.EOF
If myset!SocialNo = mytable!SlavSocialNo Then ' Update Date to send next profile.
myset.Edit ' Enable editing.
myset!Lastsentdate = Date
myset!Nextsenddate = (Date + myset!NoofDays)
myset.Update ' Save changes.
GoTo crtprt
Else
myset.MoveNext ' Find next occurrence.
End If
Loop ' End of loop.
End If
crtprt:
Myprints.AddNew
MyNewprints.AddNew
Mymem.MoveFirst
Do Until Mymem.EOF
If Mymem!SocialNo = mytable!SlavSocialNo Then
Myprints!Addlin1 = Mymem!Addlin1
Myprints!Addlin2 = Mymem!Addlin2
Myprints!Addlin3 = Mymem!Addlin3
Myprints!Addlin4 = Mymem!Addlin4
Myprints!Postcode = Mymem!Postcode
MyNewprints!Addlin1 = Mymem!Addlin1
MyNewprints!Addlin2 = Mymem!Addlin2
MyNewprints!Addlin3 = Mymem!Addlin3
MyNewprints!Addlin4 = Mymem!Addlin4
MyNewprints!Postcode = Mymem!Postcode
GoTo nextdets
End If
Mymem.MoveNext
Loop
nextdets:
myset.MoveFirst
Do Until myset.EOF
If myset!SocialNo = mytable!SlavSocialNo Then
Myprints!SocialNo1 = myset!SocialNo
Myprints!SocialNo2 = mytable!MastSocialNo
Myprints!Memtype = myset!Memtype
Myprints!Firstname = myset!Firstname
Myprints!Surname = myset!Surname
Myprints!Contactphone = myset!Contactphone
Myprints!Nationality = myset!Nationality
Myprints![Marital Status] = myset![Marital Status]
Myprints!YearofBirth = myset!YearofBirth
Myprints!Occupation = myset!Occupation
Myprints!Children = myset!Children
Myprints!Height = myset!Height
Myprints!Weight = myset!Weight
Myprints!Build = myset!Build
Myprints!Hair = myset!Hair
Myprints!Eyes = myset!Eyes
Myprints!Accommodation = myset!Accommodation
Myprints![Live with] = myset![Live with]
Myprints!Smoke = myset!Smoke
Myprints![Drink text] = myset![Drink text]
Myprints!Drive = myset!Drive
Myprints![Own Car] = myset![Own Car]
Myprints!Newspapers = myset!Newspapers
Myprints!County = myset!County
Myprints!Classical = myset!Classical
Myprints![Country and Western] = myset![Country and Western]
Myprints!Folk = myset!Folk
Myprints!Jazz = myset!Jazz
Myprints!Opera = myset!Opera
Myprints!Pop = myset!Pop
Myprints!Rock = myset!Rock
Myprints![Winter Sports] = myset![Winter Sports]
Myprints![Water Sports] = myset![Water Sports]
Myprints!Sailing = myset!Sailing
Myprints![Summer Sports] = myset![Summer Sports]
Myprints![Team Games] = myset![Team Games]
Myprints![Watching Sports] = myset![Watching Sports]
Myprints!Reading = myset!Reading
Myprints!DIY = myset!DIY
Myprints!Gardening = myset!Gardening
Myprints!Television = myset!Television
Myprints!Films = myset!Films
Myprints!Photography = myset!Photography
Myprints!Bridge = myset!Bridge
Myprints!Chess = myset!Chess
Myprints!Cooking = myset!Cooking
Myprints!Cinema = myset!Cinema
Myprints!Discos = myset!Discos
Myprints!Pubs = myset!Pubs
Myprints!Concerts = myset!Concerts
Myprints![Night Clubs] = myset![Night Clubs]
Myprints!Restaurants = myset!Restaurants
Myprints![Dinner Parties] = myset![Dinner Parties]
Myprints!Parties = myset!Parties
Myprints![Wine Bars] = myset![Wine Bars]
Myprints![Additional Information] = myset![Additional Information]
Myprints!Affectionate = myset!Affectionate
Myprints!Romantic = myset!Romantic
Myprints!Confident = myset!Confident
Myprints!Gregarious = myset!Gregarious
Myprints!Serious = myset!Serious
Myprints!Tolerant = myset!Tolerant
Myprints!Sentimental = myset!Sentimental
Myprints!Considerate = myset!Considerate
Myprints!Humorous = myset!Humorous
Myprints!Strongwilled = myset!Strongwilled
Myprints!Shy = myset!Shy
Myprints!Intellectual = myset!Intellectual
Myprints!Predictable = myset!Predictable
Myprints!Adventurous = myset!Adventurous
Myprints!Sensitive = myset!Sensitive
Myprints!Extrovert = myset!Extrovert
Myprints!Ambitious = myset!Ambitious
Myprints!Reserved = myset!Reserved
Myprints!Dependable = myset!Dependable
Myprints!Practical = myset!Practical
Myprints!Fashionconscious = myset!Fashionconscious
Myprints!Sendto = Forms![MembersFound]![Firstname] & " " & Forms![MembersFound]![Surname]
MyNewprints!SocialNo1 = myset!SocialNo
MyNewprints!SocialNo2 = mytable!SlavSocialNo
MyNewprints!Memtype = myset!Memtype
MyNewprints!Firstname = myset!Firstname
MyNewprints!Surname = myset!Surname
MyNewprints!Contactphone = myset!Contactphone
MyNewprints!Nationality = myset!Nationality
MyNewprints![Marital Status] = myset![Marital Status]
MyNewprints!YearofBirth = myset!YearofBirth
MyNewprints!Personality = myset!Personality
MyNewprints!IPartner = myset!IPartner
MyNewprints!Holiday = myset!Holiday
MyNewprints!Interests = myset!Interests
MyNewprints!FavMusic = myset!FavMusic
MyNewprints!FavBooks = myset!FavBooks
MyNewprints!FavFilms = myset!FavFilms
MyNewprints!FavSports = myset!FavSports
MyNewprints!Faveven = myset!Faveven
MyNewprints!Happiness = myset!Happiness
MyNewprints!Introducing = myset!Introducing
Mypicture.MoveFirst
Do Until Mypicture.EOF
If Mypicture!SocialNo = myset!SocialNo Then
MyNewprints!Myphoto = Mypicture!Photo
GoTo Reccomplete1
Else
Mypicture.MoveNext
End If
Loop
Reccomplete1:
GoTo memdets
End If
myset.MoveNext
Loop
memdets:
Myprints.Update
MyNewprints.Update
mytable.MoveNext
Loop
' Members seen file updated - now print profiles for posting
Myworks.CommitTrans
myset.Close
mytable.Close
myseen.Close
Mymem.Close
Myprints.Close
MyNewprints.Close
Mypicture.Close
Dim DocName As String
DocName = "NewProfilePrint1"
DoCmd.OpenReport DocName, A_NORMAL
DocName = "ProfilePrintAddress"
DoCmd.OpenReport DocName, A_NORMAL
Forms![Matchmaker].Refresh
DoCmd.Close
Exit_Button73_Click:
Exit Sub
Err_Button73_Click:
MsgBox Error$
Myworks.Rollback
Resume Exit_Button73_Click
End SubMany thanks!