Rabu, 28 Februari 2018

[AccessDevelopers] File - Monthly_Notices.txt

 


Monthly notices:

Hi Kids!:

Don't forget to check out our "Links" section at the website for helpful sites. Also take a peek at books that others have found worthwhile in our books database under the 'Database' link of the main AccessDevelopers page. Feel free to add any books or links that you have found useful.

__._,_.___
Reply via web post • Reply to sender • Reply to group • Start a New Topic • Messages in this topic (164)
Have you tried the highest rated email app?
With 4.5 stars in iTunes, the Yahoo Mail app is the highest rated email app on the market. What are you waiting for? Now you can access all your inboxes (Gmail, Outlook, AOL and more) in one place. Never delete an email again with 1000GB of free cloud storage.

Please zip all files prior to uploading to Files section.

.

__,_._,___
Diposting oleh di Tidak ada komentar:

Re: [MS_AccessPros] Re: Copy Paste

 

Bill - go to rogersaccesslibrary.com (I think that's right) and look for AD Tejpal's folders. I'm sure he has a sample database for putting a report in an email body.

Regards,
Bill Mosca

------ Original message------
From: 'Bill Singer' Bill.Singer@at-group.net [MS_Access_Professionals]
Date: Wed, Feb 28, 2018 1:41 PM
Cc:
Subject:RE: [MS_AccessPros] Re: Copy Paste

 

Bill,

Thanks for the reply.  This is more than enough for me.  I think this is a bit over my head and more than I need.

 

At this point I create a report and turn it into a .pdf and attach it to an email.

 

Thanks for your time.

 

Bill

 

Bill Singer

Office 365

Minnesota  

 

From: MS_Access_Professionals@yahoogroups.com [mailto:MS_Access_Professionals@yahoogroups.com]
Sent: Monday, February 26, 2018 4:25 PM
To: MS_Access_Professionals@yahoogroups.com
Subject: [MS_AccessPros] Re: Copy Paste

 

 

Bill - Sorry this has gone unanswered. I thought I posted a reply the day you posted. While this is not an exact how-to I hope it is enough for you to get started.

 

 

I use a table called EmailTemplate with 3 fields:

NoticeName text(50)

HTMLTemplate Memo

SubjectLine Text(50)

 

I wrote the HTML to be used as the body of the email and put bracketed placeholders for each field. HTML is below.

 

Then I open a recordset for the template I want. That should return only the one record that has the template you want. Then use Replace() to put the strings where they belong. In this case the only placeholder is [TFirstName].

 

    Dim db As DAO.Database

    Dim rsTemplate As DAO.Recordset

    Dim strSQL As String

    Dim strHTLMTemplate As String        

    

    Set db = CurrentDb

    strSQL = "SELECT NoticeName, HTMLTemplate, SubjectLine " _

             & "FROM EmailTemplate " _

             & "WHERE NoticeName = '" & conNoticeName & "'"

    

    Set rsTemplate = db.OpenRecordset(strSQL, dbOpenDynaset, dbSeeChanges)

    'Put the HTML into a string variable

    strHTLMTemplate = rsTemplate!HTMLTemplate

    strHTLMTemplate = Replace(strHTLMTemplate, "[TFirstName]", strToFName)

 

 

From there, you can put strHTMLTemplate into the body of your email. See my code sample for emailing at http://www.thatlldoit.com/Pages/codesamples.aspx 

    

 

 



 

<html>

<head>

  <title></title>

</head>

<BODY>

<P>Welcome aboard, [TFirstName].</P>

<P>You now have an account in the Application Master System and will need to take a few steps to install it.</P>

<P>Click this link to install the Application Master System: <a 

href="file:///\\SAN01\Department\IS\AppMasterSystem\AppMasterSystem_FEUpdate.vbs">Application Master System Installer</a></p>

<p>The installer will download all the files, put a shortcut on your desktop and 

open the application. The shortcut looks like this: </p>

<p><img border=0 alt="" align=left 

<p>&nbsp;</p>

<p>&nbsp;</p>

<p>&nbsp;</p>

<p>The Installation will open a small window telling you the application is loading. If you get any prompts to over-write or copy & save click yes.</p>

<p>Depending on your MS Access version and settings you may get warnings or error messages when the application opens. If you get any of these please call me so I can remote to your computer and correct the settings. This should only take a minute or two.</p>

</BODY>

</html>

 

Regards,

Bill Mosca, Founder - MS_Access_Professionals

My nothing-to-do-with-Access blog

 


---In MS_Access_Professionals@yahoogroups.com, <Bill.Singer@at-group.net> wrote :

In MS Access Office 365 I have a form that contains about 9 pieces of information that my customers frequently request.  I created a small report in hopes of doing a COPY -PASTE and putting the information in an email.  That would be fast and easy.

The COPY-PASTE idea did not go well.  The underlying query was pasted into my report.

 

I could export as a PDF but I would really like to just PASTE the information into an email.

 

Any thoughts

 

Bill Singer

Andover, MN

 

 

 

 

__._,_.___
Posted by: wrmosca <wrmosca@comcast.net>
Reply via web post • Reply to sender • Reply to group • Start a New Topic • Messages in this topic (4)
Have you tried the highest rated email app?
With 4.5 stars in iTunes, the Yahoo Mail app is the highest rated email app on the market. What are you waiting for? Now you can access all your inboxes (Gmail, Outlook, AOL and more) in one place. Never delete an email again with 1000GB of free cloud storage.

.

__,_._,___
Diposting oleh di Tidak ada komentar:

RE: [MS_AccessPros] Re: Copy Paste

 

Bill,

Thanks for the reply.  This is more than enough for me.  I think this is a bit over my head and more than I need.

 

At this point I create a report and turn it into a .pdf and attach it to an email.

 

Thanks for your time.

 

Bill

 

Bill Singer

Office 365

Minnesota  

 

From: MS_Access_Professionals@yahoogroups.com [mailto:MS_Access_Professionals@yahoogroups.com]
Sent: Monday, February 26, 2018 4:25 PM
To: MS_Access_Professionals@yahoogroups.com
Subject: [MS_AccessPros] Re: Copy Paste

 

 

Bill - Sorry this has gone unanswered. I thought I posted a reply the day you posted. While this is not an exact how-to I hope it is enough for you to get started.

 

 

I use a table called EmailTemplate with 3 fields:

NoticeName text(50)

HTMLTemplate Memo

SubjectLine Text(50)

 

I wrote the HTML to be used as the body of the email and put bracketed placeholders for each field. HTML is below.

 

Then I open a recordset for the template I want. That should return only the one record that has the template you want. Then use Replace() to put the strings where they belong. In this case the only placeholder is [TFirstName].

 

    Dim db As DAO.Database

    Dim rsTemplate As DAO.Recordset

    Dim strSQL As String

    Dim strHTLMTemplate As String        

    

    Set db = CurrentDb

    strSQL = "SELECT NoticeName, HTMLTemplate, SubjectLine " _

             & "FROM EmailTemplate " _

             & "WHERE NoticeName = '" & conNoticeName & "'"

    

    Set rsTemplate = db.OpenRecordset(strSQL, dbOpenDynaset, dbSeeChanges)

    'Put the HTML into a string variable

    strHTLMTemplate = rsTemplate!HTMLTemplate

    strHTLMTemplate = Replace(strHTLMTemplate, "[TFirstName]", strToFName)

 

 

From there, you can put strHTMLTemplate into the body of your email. See my code sample for emailing at http://www.thatlldoit.com/Pages/codesamples.aspx 

    

 

 



 

<html>

<head>

  <title></title>

</head>

<BODY>

<P>Welcome aboard, [TFirstName].</P>

<P>You now have an account in the Application Master System and will need to take a few steps to install it.</P>

<P>Click this link to install the Application Master System: <a 

href="file:///\\SAN01\Department\IS\AppMasterSystem\AppMasterSystem_FEUpdate.vbs">Application Master System Installer</a></p>

<p>The installer will download all the files, put a shortcut on your desktop and 

open the application. The shortcut looks like this: </p>

<p><img border=0 alt="" align=left 

<p>&nbsp;</p>

<p>&nbsp;</p>

<p>&nbsp;</p>

<p>The Installation will open a small window telling you the application is loading. If you get any prompts to over-write or copy & save click yes.</p>

<p>Depending on your MS Access version and settings you may get warnings or error messages when the application opens. If you get any of these please call me so I can remote to your computer and correct the settings. This should only take a minute or two.</p>

</BODY>

</html>

 

Regards,

Bill Mosca, Founder - MS_Access_Professionals

My nothing-to-do-with-Access blog

 


---In MS_Access_Professionals@yahoogroups.com, <Bill.Singer@at-group.net> wrote :

In MS Access Office 365 I have a form that contains about 9 pieces of information that my customers frequently request.  I created a small report in hopes of doing a COPY -PASTE and putting the information in an email.  That would be fast and easy.

The COPY-PASTE idea did not go well.  The underlying query was pasted into my report.

 

I could export as a PDF but I would really like to just PASTE the information into an email.

 

Any thoughts

 

Bill Singer

Andover, MN

 

 

 

 

__._,_.___
Posted by: "Bill Singer" <Bill.Singer@at-group.net>
Reply via web post • Reply to sender • Reply to group • Start a New Topic • Messages in this topic (3)
Have you tried the highest rated email app?
With 4.5 stars in iTunes, the Yahoo Mail app is the highest rated email app on the market. What are you waiting for? Now you can access all your inboxes (Gmail, Outlook, AOL and more) in one place. Never delete an email again with 1000GB of free cloud storage.

.

__,_._,___
Diposting oleh di Tidak ada komentar:

Senin, 26 Februari 2018

Re: [MS_AccessPros] Converting to Access SQL

 

Perfect. Thank you again. 

With Warm Regards,
 
Arthur D. Lorenzini
IT System Manager
Cheyenne River Housing Authority
Wk.(605)964-4265  Ext. 130
Fax (605)964-1070

"Valar Dohaeris"






From: "Duane Hookom duanehookom@hotmail.com [MS_Access_Professionals]" <MS_Access_Professionals@yahoogroups.com>
To: "MS_Access_Professionals@yahoogroups.com" <MS_Access_Professionals@yahoogroups.com>
Sent: Monday, February 26, 2018 2:35 PM
Subject: Re: [MS_AccessPros] Converting to Access SQL

 
Hi Art,

The method that I would use is VBA in the after update of the combo box:

Dim strSQL as String
Dim strWhere as String
Select Case Me.cmbChooseCommunity
    Case 0
    Me.sfrmTenantList.Form..RecordSource = "qryTenantListing"
  case Else
    Me.sfrmTenantList.Form.RecordSource = "SELECT * FROM qryTenantListing WHERE CommunityID = " & Me.cmbChooseCommunity
End Select

This assumes CommunityID is numeric and you don't have a link master/child set up.

Regards,
Duane Hookom

From: MS_Access_Professionals@yahoogroups.com <MS_Access_Professionals@yahoogroups.com> on behalf of Art Lorenzini dbalorenzini@yahoo.com [MS_Access_Professionals] <MS_Access_Professionals@yahoogroups.com>
Sent: Monday, February 26, 2018 1:46 PM
To: MS_Access_Professionals@yahoogroups.com
Subject: Re: [MS_AccessPros] Converting to Access SQL
 


Yes there was a entry Link Master/Child properties. Master = cmbChooseCommunity and Child = CommunityID. I removed the link an the All started to work but nothing else did. Here is the record source for cmbChooseComunity:

SELECT tlkpCommunity.CommunityID, tlkpCommunity.CommunityName FROM tlkpCommunity UNION SELECT 0,"All" FROM tlkpCommunity ORDER BY CommunityName; 

So since I removed the Master Link I am going to have to write a query for each community and put in my case statement?

With Warm Regards,
 
Arthur D. Lorenzini
IT System Manager
Cheyenne River Housing Authority
Wk.(605)964-4265  Ext. 130
Fax (605)964-1070

"Valar Dohaeris"






From: "Duane Hookom duanehookom@hotmail.com [MS_Access_Professionals]" <MS_Access_Professionals@yahoogroups.com>
To: "MS_Access_Professionals@yahoogroups.com" <MS_Access_Professionals@yahoogroups.com>
Sent: Monday, February 26, 2018 1:17 PM
Subject: Re: [MS_AccessPros] Converting to Access SQL

 
Hi Art,

Are there any fields/expressions in the Link Master/Child properties of the subform?

I don't see any reference in the RecordSource to the cmbChooseCommunity control. 

Regards,
Duane


From: MS_Access_Professionals@yahoogroups.com <MS_Access_Professionals@yahoogroups.com> on behalf of Art Lorenzini dbalorenzini@yahoo.com [MS_Access_Professionals] <MS_Access_Professionals@yahoogroups.com>
Sent: Monday, February 26, 2018 12:27 PM
To: MS_Access_Professionals@yahoogroups.com
Subject: Re: [MS_AccessPros] Converting to Access SQL
 


I am having an issue with  my cmbChooseCommunity which is supposed to filter sfrmTenantList; The subform using a working version qryTenantListing you see below:

 
Private Sub cmbChooseCommunity_AfterUpdate()

Select Case Me.cmbChooseCommunity
   
    Case 0
      Forms![frmTenantListingDashboard]![sfrmTenantList].Form.RecordSource = "qryTenantListing"
      Forms![frmTenantListingDashboard]![sfrmTenantList].Form.Requery
 
   End Select
 End Sub
Working version of qryTenantListing:

SELECT B4602200.NAME, tblUnit.UnitCode, tblUnit.BedroomCount, tblUnit.CurrentStatus, tblProject.[Project Number], tblUnit.UnitStatus, tblUnit.ClassificationType, tblUnit.UnitHousingType, tlkpCommunity.CommunityName, B4602200.BSSI_Tenant_Lease_Status, B4602200.CUSTNMBR, B4602200.BSSI_Lease_Execution_Dat AS Move_In_Date, Trim([ADDRESS1]) & ", " & Trim([CITY]) & ", " & Trim([STATE]) & " " & Trim([Zip]) AS TenantAddress, tblUnit.UnitID, tblUnit.CommunityID
FROM ((B4602200 INNER JOIN (tblUnit INNER JOIN tblProject ON tblUnit.ProjectID = tblProject.ProjectID) ON B4602200.LOCNCODE = tblUnit.UnitCode) INNER JOIN tlkpCommunity ON tblUnit.CommunityID = tlkpCommunity.CommunityID) INNER JOIN RM00101 ON B4602200.CUSTNMBR = RM00101.CUSTNMBR
WHERE (((tblUnit.CurrentStatus)="CRHA Use") AND ((B4602200.BSSI_Tenant_Lease_Status)=2))
ORDER BY tblUnit.UnitCode;


When I Change my Combo box to the "All" item, it runs the code but the subform remains empty. But if I run the query it runs fine...


If I select another community is the list, it filters the subform fine.


With Warm Regards,
 
Arthur D. Lorenzini
IT System Manager
Cheyenne River Housing Authority
Wk.(605)964-4265  Ext. 130
Fax (605)964-1070

"Valar Dohaeris"






From: "wrmosca wrmosca@comcast.net [MS_Access_Professionals]" <MS_Access_Professionals@yahoogroups.com>
To: "dbalorenzini@yahoo.com [MS_Access_Professionals]" <MS_Access_Professionals@yahoogroups.com>
Sent: Sunday, February 25, 2018 11:48 AM
Subject: Re: [MS_AccessPros] Converting to Access SQL

 
Art- your company is paying this consultant. Make him convert it...or look up case blocks in T-SQL and Iff statements in Access. You'll find you can convert it yourself.

Grow your skills!

Regards,
Bill Mosca

------ Original message------
From: dbalorenzini@yahoo.com [MS_Access_Professionals]
Date: Sun, Feb 25, 2018 8:36 AM
Cc:
Subject:[MS_AccessPros] Converting to Access SQL

 
We have a technician from a vendor we work with that was supposed to aide in a creation of a report. I sent him my version of the code and he sent back this?
SELECT
        case when B4602200.BSSI_Tenant_Lease_Status =  2
     then B4602200.NAME 
     else 'Vacant'
     end as 'Name'
       ,case when B4602200.BSSI_Tenant_Lease_Status =  2
     then B4602200.CUSTNMBR 
     else 'Vacant'
     end as 'Customer Number'
       ,case when B4602200.BSSI_Tenant_Lease_Status = 2
     then 'Active Lease'
     else 'No Active Lease'
     end as 'Lease Status'
       ,case when B4602200.BSSI_Tenant_Lease_Status =  2
     then B4602200.BSSI_Lease_Execution_Dat
     else 'Vacant'
     end as 'Move In Date'
       ,tblUnit.UnitCode
       ,tblUnit.BedroomCount
       ,tblUnit.CurrentStatus
       ,tblProject.[Project Number]
       ,tblUnit.UnitStatus
       ,tblUnit.ClassificationType
       ,tblUnit.UnitHousingType
       ,tlkpCommunity.CommunityName
       ,Trim([ADDRESS1]) & ", " & Trim([CITY]) & ", " & Trim([STATE]) & " " & Trim([Zip]) AS TenantAddress
       ,tblUnit.UnitID
       ,tblUnit.CommunityID
FROM ((B4602200 INNER JOIN (tblUnit INNER JOIN tblProject ON tblUnit.ProjectID = tblProject.ProjectID)
              ON B4602200.LOCNCODE = tblUnit.UnitCode)
              INNER JOIN tlkpCommunity ON tblUnit.CommunityID = tlkpCommunity.CommunityID)
              INNER JOIN RM00101 ON B4602200.CUSTNMBR = RM00101.CUSTNMBR
WHERE tblUnit.CurrentStatus = 'CRHA Use'
ORDER BY tblUnit.UnitCode

I am hoping you could help me decipher it. I need help on the case statements. I believe he is writing T-SQL against a SQL Server but I did tell him we were working with an access backend.

Thank you 

Art Lorenzini
Sioux Falls, sd












__._,_.___
Posted by: Art Lorenzini <dbalorenzini@yahoo.com>
Reply via web post • Reply to sender • Reply to group • Start a New Topic • Messages in this topic (8)
Have you tried the highest rated email app?
With 4.5 stars in iTunes, the Yahoo Mail app is the highest rated email app on the market. What are you waiting for? Now you can access all your inboxes (Gmail, Outlook, AOL and more) in one place. Never delete an email again with 1000GB of free cloud storage.

.

__,_._,___
Diposting oleh di Tidak ada komentar:
Langganan: Postingan (Atom)