Query Criteria and Variable in E-mail in VBA

[doupis]

Thanks for your help! We are trying to automate one of our price verification procedures. There are two forms to be filled out on Access. One (maybe two) come from our Contractor. They will input the Bill of Materials, Scrap Rates, Inventory Turns and Cost. This goes to a non-official Bill of Materials table. ​

The second form comes from our Supplier. They will input the the cost they charge the contractor for the given Bill of Materials. Access runs a report that will automatically compare the Contractor costs with the Supplier costs for each component on the Bill of Materials. If the component costs match, it will highlight the Contractor cost green, it they do not, it highlights red.​

If all numbers are green, I hit the "Approve" button on the report and there are three queries that need to be ran. An Update Query that changes all the statuses for the components from "Unapproved" to "Approved", an Append Query to add the Bill of Materials to our official table, and a Delete Query that removes the components from the unofficial Bill of Materials table. Once this is all done, an e-mail needs to be sent to Finance to roll costing for the SKU.​

I do not know the best way, but I currently am having getting the Code to do the following:​

1. Put the specific SKU in the e-mail, which is variable.
2. Change the Criteria of the Queries so they only run for the specific approved sku
3. Get the "Approve" and "Deny" buttons on the report to only function for the specific SKU.​

Currently all the SKUs that are in "Unapproved" status show on the report. Once I hit approve, it wants to approve all the SKUs, not just the one I'm looking at.​

Code so far: ​

Sub Command36_Click()
'''Current Issue: Need Query Criteria to be SKU specific''' 
'Update Query to change BOM status to "Approved" 
DoCmd.OpenQuery "(2302) BOM - 3PM Entry Query Approved", acViewNormal, acEdit

'Append Query to add BOM to official BOM table 
DoCmd.OpenQuery "(2300) BOM - 3PM Entry Query", acViewNormal, acEdit

'Delete Query to remove BOM from unofficial BOM table 
DoCmd.OpenQuery "(2301) BOM - 3PM Entry Query", acViewNormal, acEdit

'Send E-mail to Cost Analyst to Cost or Price Update 
''' Current issue: getting .Subject to have specific SKU'''

Set objOutlook = GetObject(, "Outlook.Application") Set objOutlook = CreateObject("Outlook.Application") 
Set objOutlookMsg = objOutlook.CreateItem(olMailItem)

With objOutlookMsg

.To = "email@email.com" .Subject = "TEST: Please cost " 
.Body = "This message was sent via Microsoft Access. Please delete this e-mail." 
.Display

End With

'Save and Close Report

'DoCmd.Save 
'DoCmd.Close

'Clean-Up Code

Set objOutlookMsg = Nothing 
Set objOutlook = Nothing 
Set objOutlookRecip = Nothing

End Sub

Any ideas here or that may be a better process, please let me know! Again, thanks!​

 

[spikepl]

What is your specific problem or problems?

BTW: Set objOutlook = GetObject(, "Outlook.Application") is redundant - delete that code.

For listing code do not use characters that can be part of code such as << but use the code tags. Go Advanced->select your code->click on #

 

[doupis]

What is your specific problem or problems?

Getting Access to understand it is comparing prices for a SKU so it can move the approved pricing to an official table, send an e-mail once it's approved/denied to the finance department/contractor and then loop the remaining SKUs.

One set of pricing comes from the contractor, the other set comes from the supplier. I need to verify that the contractor is charging what the supplier says the price is.

The following is code for an "Approve" command button on a report. When clicked, it starts a chain of queries to approve the pricing for ALL SKUs on the report, move the SKUs to an official table and delete the SKUs from a temporary table.

I need it to only approve one SKU at a time.

Sub Command36_Click()

    Dim Db As Database
    Set Db = CurrentDb
    Dim SKU As DAO.Recordset
    Dim EL As DAO.Recordset
    Set EL = Db.OpenRecordset("1501 E-Mail Notifications")
    Set FG = Db.OpenRecordset("1401 BOM - 3PM Entry")
    
    '''Current Issue: Need Query Criteria to be SKU specific'''
    'Update Query to change BOM status to "Approved"
        'DoCmd.OpenQuery "(2302) BOM - 3PM Entry Query Approved", acViewNormal, acEdit
    
    'Append Query to add BOM to official BOM table
        'DoCmd.OpenQuery "(2300) BOM - 3PM Entry Query", acViewNormal, acEdit
        
     'Delete Query to remove BOM from unofficial BOM table
        'DoCmd.OpenQuery "(2301) BOM - 3PM Entry Query", acViewNormal, acEdit
    
    'Send E-mail to Cost Analyst to Cost or Price Update
    '''   Current issue: getting .Subject to have specific SKU'''
        Set objOutlook = CreateObject("Outlook.Application")
        Set objOutlookMsg = objOutlook.CreateItem(olMailItem)
         With objOutlookMsg
             .To = EL![Finance E-Mail List]
             .CC = EL![Planning E-Mail List] & "; " & EL![Procurement E-Mail List]
             .Subject = "TEST: Please cost " & FG![PMS]
             .Body = "This message was sent via Microsoft Access.  Please delete this e-mail."
             .Display
        End With
    
    'Save and Close Report
        'DoCmd.Save
        'DoCmd.Close
    
    'Clean-Up Code
        Set objOutlookMsg = Nothing
        Set objOutlook = Nothing
        Set objOutlookRecip = Nothing
End Sub