Macro or Module

[jamesmor]

Here's my delema,

I currently have a table that I pull orders out of, now every month I have to pull out each sales reps accounts and send them to the reps, I currently have a macro to do this, but I have about 55 reps and have to do a lot of changing when someone comes or someone goes.

I have started to write a module to do this for me, but am running into a few hurdles, one being how to get the query that I call to change the name of the table that it creates, and then how to send said table to the correct rep.

Any help/Advice would be greatly appreciated

 

[The_Doc_Man]

Your best bet is a module-based activity. A little VBA never hurt anyone.

If you do this no more often than monthly, you might try to dynamically build a query based on your saleman table (assuming you have one). If not, there is still a way. You might need to build one fixed query that gives you some summary info, like say a count of records for each (GROUP BY) salesman for the current month as defined (through SELECT with a WHERE clause) as greater than a DateAdd of minus one (-1) month to the Date() function, e.g. This will give you one record for every salesperson AND you can see if that person has any records by examining the count field.

Then you would write code that opened the count query solely for the purpose of pulling names that had something to send. For each name in the recordset, build a query that will select for only that salesman's records. After all, you already have the name at that point. You can create an SQL query through code. Not that hard.

Now you can export the query (not a separate table - wastes space...). Anything you can do with a table you can do with a query - expect occupy the same amount of space. Compared to most tables, queries are TINY. But you can export to a spreadsheet, export to text, export to Word, export to whatever else you have available in your referernces library.

As to changing the name of the table to match the salesman, you might need to have a "translator module" that looks at the names - or maybe if you have a salesman table, you don't WANT the names - you want the salesman's internal ID number appended to the name of the query. And when you create the query, you have to give it a name anyway, so just append the ID number to the name through concatenation and, at worst, the CStr() function to convert the ID number to a string.

Now, if the ID holds special characters, remove them first 'cause if you don't, you will confuse Access terribly. But otherwise, this should not be a hard thing to do. If the ID is not a good idea, you need to parse the name character by character. Just convert O'Rourke to ORourke, and Torres-Garcia to TorresGarcia and so on. AND you need to check for duplicate names 'cause you'd hate for John Q Smith (#1) to get John Q Smith (#2) data and vice versa.

 

[sfreeman@co.mer]

That's good, here's another way to send automatically via e-mail

With 52 reps you may want to 'click a button' and e-mail every rep all of their orders for the period. This does not create any permanent tables. Pretty basic really, but will grab all orders for each rep, and send to each rep as an e-mail attachment.

Option Compare Database
Option Explicit

'------------------------------------------------------------
' Orders
'
'------------------------------------------------------------
Function Orders()
On Error GoTo Orders_Err

Dim db As Database
Dim rst As DAO.Recordset
Dim rstRep As DAO.Recordset
Dim strRep As String
Dim strEMail As String
Dim qryDef As DAO.QueryDef

Set db = CurrentDb

Set rstRep = db.OpenRecordset("SELECT DISTINCT tblOrders.Name FROM tblOrders ORDER BY tblOrders.Name;", dbOpenDynaset)

On Error Resume Next
DoCmd.DeleteObject acQuery, "qryTmp"
On Error GoTo Orders_Err

With rstRep
Do While Not .EOF
strRep = .Fields(0)
strEMail = strRep & "@mydomain.com"
Set qryDef = db.CreateQueryDef("qryTmp", "SELECT tblOrders.OrderData FROM tblOrders WHERE tblOrders.Name = """ & strRep & """ ORDER BY tblOrders.Name;")
DoCmd.SendObject acSendQuery, "qryTmp", "MS-DOSText(*.txt)", strEMail, "", "", "Monthly Orders", "Dear " & strRep & ", here are your orders for this month.", False, ""
DoCmd.DeleteObject acQuery, "qryTmp"
.MoveNext
Loop
End With

Orders_Exit:
Exit Function

Orders_Err:
MsgBox Error$

Resume Orders_Exit

End Function

[and set all your 'Sets' = Nothing]

 

[jamesmor]

Thanks Guys! This helps Immensly, I can do VB code easy enough, the problem I was having was figuring out how to make it "Access Friendly", just the small differences in language.

one last problem, the file that I pull the orders out of has other reps in it that I don't need/want, I'm assuming I would want to load the people that I want to pull into an array and do a for loop, would this be correct? or is there a better way?

 

[jamesmor]

question

I've been trying to use the code above to send out my reps customers, but access keeps throwing errors about user-defined data types, is there something else I need to install to make access use this code correctly?