Solved

Faxing a report using winfax pro 10

Posted on 2004-10-03
65
848 Views
Last Modified: 2012-06-27
I currently have winfax 10
i have  table "Customers" that have fields called "faxNo"(field for fax number), "FaxSent"( by default has "0" )
Now my query, "QueryForFaxReport", i have select all in table "Customers" where FaxSent = "0"
And My Report, "FaxReport" is based off of the query (a page per customer)

Now what im trying to do is fax the report to each number in the "faxNo" field of the report  (each page, of respective faxNo)
using winfax pro 10

Now Once the FAx go though and is successful!, then a "1" is placed in "Customer.FaxSent" ,  If not success, Then "Customer.FaxSent" =" 2" , .......are you getting this?:)

please post code that will help me do this with winfax and access
total points for this job is 600 points! 500 now and 100 posted for you

after this question is answer m i will immediately post another question , concerning this fuction. and will give you the link
0
Comment
Question by:Sam Cohen
  • 31
  • 31
  • 2
65 Comments
 
LVL 49

Expert Comment

by:Ryan Chong
ID: 12215021
See:

http://www.symantec.com/winfax/sdk.html

Download the Application SDK sample files and Documentation there..

Also at:
http://www.granite.ab.ca/accswfxp.htm

try see how to track the fax sending status, from there update your "Customer.FaxSent" status accordingly.
0
 
LVL 27

Expert Comment

by:jjafferr
ID: 12215301
Hi ryancys,



Make a command button on FaxForm Form, call this command button Command0, On Click of this button, paste the code below,
I have assumed the following:
CustomerID is the unique customer ID for each customer
faxNo is the Customer Fax Number
CustomerName is your Customer Name
CompanyName is your Customer Company Name


Private Sub Command0_Click()

'Lets get the Recordset for the Records
    Dim rc As Long: Dim nErrCode As Long: Dim wfSend As Object
    Dim db As dao.Database
    Dim rs As dao.Recordset

    Set db = CurrentDb
    Set rs = db.OpenRecordset("select * from Customers") 'if you have a condition/filter, put it here
    rs.MoveFirst

'Lets send one Record at a time
    Do Until rs.EOF

'Please note that I assumed CustomerID is the unique field to identify each Record
'In your Form where start this, I called it FaxForm, make a field called CustomerID
'In FaxReport, in the CustomerID field properties, in the Record Source write this:=Forms![FaxForm]![CustomerID]
'Now we will print the FaxReport to "C:\CustomerFaxReport.rtf"

        Forms![FaxForm]![CustomerID]= rs!CustomerID
        DoCmd.OutputTo acOutputTable, "FaxReport", acFormatRTF, "C:\CustomerFaxReport.rtf", false

'WinFax starts from here
+++++



Set wfSend = CreateObject("WinFax.SDKSend")
'Your info
wfSend.SetNumber ("123456")
wfSend.SetTo ("YourName")
wfSend.SetCompany ("YourCompany")

wfSend.SetType (0)

wfSend.AddRecipient
wfSend.SetNumber (rs!faxNo)         '("123456")
wfSend.SetTo (rs!CustomerName)      '("To your Customer Name")
wfSend.SetCompany (rs!CompanyName)  '("To your Customer Company Name")

'rc = wfSend.AddRecipient()
'If rc = 1 Then
'MsgBox "add recipient error " & wfSend.GetLastError()
'End If

'cover page
''wfSend.SetUseCover (1)
''wfSend.SetQuickCover (1)
''wfSend.SetCoverText ("sample VB code that uses winfax sdk to send a fax")

'wfSend.SetSubject ("If you have a subject")
wfSend.SetResolution (1): wfSend.SetDeleteAfterSend (1): wfSend.ShowCallProgess (1)

'attach a file. an image file or a file that can be printed.
rc = wfSend.AddAttachmentFile("C:\CustomerFaxReport.rtf")
'rc = wfSend.AddAttachmentFile("\\Server01\clients\0450-2002\Fax-Specs.doc")
'rc = wfSend.AddAttachmentFile("D:\Work\Attachements\E-274-01\Gate.tif")
'rc = wfSend.AddAttachmentFile("D:\Work\Attachements\E-592-01myD\Cook Specs.pdf")

' and away it goes!, OR an Error occures
rs.Edit
rc = wfSend.Send(0)
If rc = 1 Then
 'nErrCode = wfSend.GetLastError()
 'MsgBox "send failed. Error code: " & nErrCode
 rs!FaxSent="2"
else
 rs!FaxSent="1"
End If
 rs.Update

'get rid of the fax send object.
Set wfSend = Nothing


+++++
'WinFax Ends here

        If Not rs.EOF Then rs.MoveNext
    Loop
    rs.Close
    Set rs = Nothing
    Set db = Nothing

End sub




I hope this helps,

jaffer
0
 
LVL 27

Expert Comment

by:jjafferr
ID: 12215307
OOoooopps
The Hi should go to both of you
all4artz and ryancys
0
 
LVL 49

Expert Comment

by:Ryan Chong
ID: 12215322
8-)
0
 
LVL 8

Author Comment

by:Sam Cohen
ID: 12215510
I will try your code JJafferr
ryancys , i was up studying the sdk stuff....will come back to it later
Good morning from Atlanta ! Hi to the both of your!!!
0
 
LVL 8

Author Comment

by:Sam Cohen
ID: 12252088
Hey Jaffer

I have try your code
But i would like to do the following:

1)Instead of a button , could this function be used in as a macro? and how?

2)On this part of the code:
------->
wfSend.AddRecipient
wfSend.SetNumber (rs!faxNo)         '("123456")
wfSend.SetTo (rs!CustomerName)      '("To your Customer Name")
wfSend.SetCompany (rs!CompanyName)  '("To your Customer Company Name")
------->
 i get an error because FaxNo in the rs is NULL, so can you implement it where ,if null then
do error message and SentMailInstead="1" then continue with rest

3)So again, i have 3 reports, CoverPage(coverRpt), MainPage(FaxReport), ImagePage(imageRpt) (image is either embedded or linked),
i have 3 record in the table , so in accord i have 3 pages of records in my FaxReport
and i have 3 different coverpages that goes with the records
and 3 different pages of images---Note each record has a whole page .
so in each report(s) i have a different faxNo in on each page ---All reports are linked by the ID
so when i click on the button with the Macro function , i would like the Winfax to fax CoverRpt,FAxReport,and ImageRpt  (based on the ID) to the FaxNo that is associated with that ID, not all the pages and ID's to one fax number.
Just 1 of the cover , 1 of the main, and 1 of the image - all associated with the ID
How can i impliment you code to do just that?
0
 
LVL 27

Expert Comment

by:jjafferr
ID: 12255951
Hi all4artz

Please note that this code will work if Form FaxForm is open. it can be open in the background too.

1-
Although I don't see a difference in putting the code as a module OR as it it exists on a command button, because at the end you will still need a command button.

Never the less, here is how to do it with a module. Because we are not sending any variables to the Sub, the sub can be the same except for the the name.
Change:
Private Sub Command0_Click()

to:
Publice Sub SendmyFax(DummyVariant)

You should call it in the following way from a Form OR a Macro:
call SendmyFax(DummyVariant)

2-
Previously, when an error happend, you wanted rs!FaxSent="2",
but here is what you want now,
Assuming SentMailInstead is a field in the Customers Table:

 if isnull(rs!faxNo) then rs!SentMailInstead="1" :If Not rs.EOF Then rs.MoveNext
 if isnull(rs!CustomerName) then rs!SentMailInstead="1" :If Not rs.EOF Then rs.MoveNext
 if isnull(rs!CompanyName) then rs!SentMailInstead="1" :If Not rs.EOF Then rs.MoveNext

3-
> i have 3 record in the table , so in accord i have 3 pages of records in my FaxReport
The Report will print any number of Records, as long as they all share the same CustomerID.

>i have 3 reports, CoverPage(coverRpt), MainPage(FaxReport), ImagePage(imageRpt)
Print all 3 Reports (we printed One earlier, so now make them 3 as shows):
DoCmd.OutputTo acOutputTable, "coverRpt", acFormatRTF, "C:\CustomercoverRpt.rtf", false
DoCmd.OutputTo acOutputTable, "FaxReport", acFormatRTF, "C:\CustomerFaxReport.rtf", false
DoCmd.OutputTo acOutputTable, "imageRpt", acFormatRTF, "C:\CustomerimageRpt.rtf", false

Then attach the 3 Reports:
rc = wfSend.AddAttachmentFile("C:\CustomercoverRpt.rtf")
rc = wfSend.AddAttachmentFile("C:\CustomerFaxReport.rtf")
rc = wfSend.AddAttachmentFile("C:\CustomerimageRpt.rtf")

Keeping in mind:
In The Report, in the CustomerID field properties, in the Record Source write this:=Forms![FaxForm]![CustomerID]
So that Records are sorted based on CustomerID.

If you only want to send an image with the Report, then, instead of the printing the Image:
Give the image name and path, like this
rc = wfSend.AddAttachmentFile("D:\Work\Attachements\E-274-01\Gate.tif")


Good luck

jaffer
0
 
LVL 8

Author Comment

by:Sam Cohen
ID: 12267604
ok i think its working but i keep getting an ms visual basic error:" the macro in this project are disabled..."
how do i enable it?

.. If the works ... then winfax should fax 3 pages of each recip. by its CustomerID , right??
0
 
LVL 27

Expert Comment

by:jjafferr
ID: 12272158
I am not not sure about this, it must be office 2003,
In the Tools > Options > security, set the security setting to low,

Yes, the 3 reports should be Printed and faxed.

jaffer
0
 
LVL 8

Author Comment

by:Sam Cohen
ID: 12274330
ok im running into a couple of problems , maybe you can clear up

1) this section---->
Set rs = db.OpenRecordset("select * from Customers") 'if you have a condition..........
----------------------------------------------->
How can have based on my filters or selection, have if there is none that is = to my openrecordset query then  label2  says "There are no Records to be faxed at this time" ,onLoad?


2)Where exactly should i put this code youve gave me earlier:

if isnull(rs!faxNo) then rs!SentMailInstead="1" :If Not rs.EOF Then rs.MoveNext
 if isnull(rs!CustomerName) then rs!SentMailInstead="1" :If Not rs.EOF Then rs.MoveNext
 if isnull(rs!CompanyName) then rs!SentMailInstead="1" :If Not rs.EOF Then rs.MoveNext

as you know i would like it to  initate during the wfsend.setnumber(rs!faxNo) because that is where it is highlighted yellow during the error.


3)Whats is the point of this part :
'Your info
wfSend.SetNumber ("123456")
wfSend.SetTo ("YourName")
wfSend.SetCompany ("YourCompany")

when i set in my info it sends me a fax also, but i dont want a fax sent to me
just to the (faxNo) , and when i disable this part of the code, it still tries to send a fax to unknown...how can i just send it to the rs!faxNO(s) or if there is a reason for this part?

4)is there a way that i add whats in the Image field of my table
(sort of like a link C:\ .....) to the
'rc = wfSend.AddAttachmentFile("D:\Work\Attachements\E-274-01\Gate.tif")
part. In other words in my imagefax field of one ID i have "C:\Fax\Attachments\Image.tif" so how can i say
'rc = wfSend.AddAttachmentFile(rs!imageFax)
does this sounds right?
but if there is a better way to fax images as a attachment automatic along like the reports you gave let me know. My image field is "ImageFAx"

If you can simply answer this question, then all will be done and i will reward =)
0
 
LVL 27

Expert Comment

by:jjafferr
ID: 12295691
Hi all4artz

1.
We will Count the number of Records that exist in your Table based on the CompanyName you select in your Form, the syntax is:
HowManyRecords=DCount("[CustomerID]","Customers","[CompanyName]=Forms![FaxForm]![CompanyName]")
If HowManyRecords<1 then Exit sub

You can put this code before calling the module (see full code below).

2.
I made a slight change, (see full code below).

3.
It shouldn't send you the fax, unless you put your numbers in wfSend.AddRecipient.
This is nothing but to identify you on the Recipient's fax machine and page.

BUT
It might be printing a Confirmation report for you.
This you can control from the winfax preferences, and set the Report (or something like that) to false.

4.
rc = wfSend.AddAttachmentFile(rs!imageFax)
is correct, and it is added to the full code, (see full code below).

+++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++

Form:
From the Form, you can call module, like this:
HowManyRecords=DCount("[CustomerID]","Customers","[CompanyName]=Forms![FaxForm]![CompanyName]")
If HowManyRecords<1 then Exit sub
call SendmyFax(DummyVariant)


Module:
Paste the following a module and save it with any name you wish.



Publice Sub SendmyFax(DummyVariant)

'Lets get the Recordset for the Records
    Dim rc As Long: Dim nErrCode As Long: Dim wfSend As Object
    Dim db As dao.Database
    Dim rs As dao.Recordset

    Set db = CurrentDb
    Set rs = db.OpenRecordset("select * from Customers") 'if you have a condition/filter, put it here
    rs.MoveFirst

'Lets send one Record at a time
    Do Until rs.EOF

 if isnull(rs!faxNo) then rs!SentMailInstead="1" :Goto CheckNextRecord
 if isnull(rs!CustomerName) then rs!SentMailInstead="1" :Goto CheckNextRecord
 if isnull(rs!CompanyName) then rs!SentMailInstead="1" :Goto CheckNextRecord


'Please note that I assumed CustomerID is the unique field to identify each Record
'In your Form where start this, I called it FaxForm, make a field called CustomerID
'In FaxReport, in the CustomerID field properties, in the Record Source write this:=Forms![FaxForm]![CustomerID]
'Now we will print the FaxReport to "C:\CustomerFaxReport.rtf"

        Forms![FaxForm]![CustomerID]= rs!CustomerID
        DoCmd.OutputTo acOutputTable, "coverRpt", acFormatRTF, "C:\CustomercoverRpt.rtf", false
        DoCmd.OutputTo acOutputTable, "FaxReport", acFormatRTF, "C:\CustomerFaxReport.rtf", false
        DoCmd.OutputTo acOutputTable, "imageRpt", acFormatRTF, "C:\CustomerimageRpt.rtf", false


'WinFax starts from here
'+++++


Set wfSend = CreateObject("WinFax.SDKSend")
'Your info
wfSend.SetNumber ("123456")
wfSend.SetTo ("YourName")
wfSend.SetCompany ("YourCompany")

wfSend.SetType (0)

wfSend.AddRecipient
wfSend.SetNumber (rs!faxNo)         '("123456")
wfSend.SetTo (rs!CustomerName)      '("To your Customer Name")
wfSend.SetCompany (rs!CompanyName)  '("To your Customer Company Name")

'rc = wfSend.AddRecipient()
'If rc = 1 Then
'MsgBox "add recipient error " & wfSend.GetLastError()
'End If

'cover page
''wfSend.SetUseCover (1)
''wfSend.SetQuickCover (1)
''wfSend.SetCoverText ("sample VB code that uses winfax sdk to send a fax")

'wfSend.SetSubject ("If you have a subject")
wfSend.SetResolution (1): wfSend.SetDeleteAfterSend (1): wfSend.ShowCallProgess (1)

'attach a file. an image file or a file that can be printed.
rc = wfSend.AddAttachmentFile("C:\CustomercoverRpt.rtf")
rc = wfSend.AddAttachmentFile("C:\CustomerFaxReport.rtf")
rc = wfSend.AddAttachmentFile("C:\CustomerimageRpt.rtf")
rc = wfSend.AddAttachmentFile(rs!imageFax)


' and away it goes!, OR an Error occures
rs.Edit
rc = wfSend.Send(0)
If rc = 1 Then
 'nErrCode = wfSend.GetLastError()
 'MsgBox "send failed. Error code: " & nErrCode
 rs!FaxSent="2"
else
 rs!FaxSent="1"
End If
 rs.Update

'get rid of the fax send object.
Set wfSend = Nothing


'+++++
'WinFax Ends here
CheckNextRecord:
        If Not rs.EOF Then rs.MoveNext
    Loop
    rs.Close
    Set rs = Nothing
    Set db = Nothing

End sub
0
 
LVL 27

Expert Comment

by:jjafferr
ID: 12296121
Sorry, I missed this part:
>label2  says "There are no Records to be faxed at this time" ,onLoad?

yes you can do that too, but I would use On Current, this way you can be updated when you change the Records too:

HowManyRecords=DCount("[CustomerID]","Customers","[CompanyName]=Forms![FaxForm]![CompanyName]")
 If HowManyRecords<1 then
  [label2]="There are no Records to be faxed at this time"
  Exit sub
 endif

OR
you can replace this code to the earlier code (the full code, above)
0
 
LVL 8

Author Comment

by:Sam Cohen
ID: 12297593
ok Jaffer,
The code you gave makes each recipent get the same fax, but that not what i wanted
this is what i would like to have wanted:
http://www.experts-exchange.com/Databases/MS_Access/Q_21164768.html
please go to the link i have posted it in greater detail .

can you edit the code with the info from link

if you answer on both , i will give all points
0
 
LVL 8

Author Comment

by:Sam Cohen
ID: 12303060
This is something like what i am looking for example below:
what the code above does is when i click a button it converts my report of one of the records(which is just the custID, name, address, Rep and message in a Report ) into RTF and saves it into a temp folder then attaching to fax, but sends the same Reports to the all recipents by their faxNo
but because each record has its own info to be fax how can i send its own info to its own fax #.

In other words , lets say that i have in my table fields: CustomerID, FName, LName,Address, PhNo, FaxToRecip, FaxNo,
and Message

 Cust ID  | Fname  | LName |ADDRESS  |  PhoneNo    | Fax To Rep     | Fax No.          | Fax Message
_________________________________________________________________________________________
450085   | John     | Doe     | 123 hill st | 4049999999 |Jane Parker     | 7708985858    | this is a test message A <--
450085   | John     | Doe     | 123 hill st | 4049999999 |Jane Parker     | 7708985858    | this is a test message A <--
236524   | JAne     | Doe     | 222 park s| 5054545258 | Tom Tom       | 6065656000   | this is a test message B
450085   | John     | Doe     | 123 hill st  | 4049999999 |  George Ban   | 6786767777   | this is a test message D <--
880225   | Jon       | Wrin    | 44 Oak st  | 4049999999 |Johnny B Good |  7708985858  | this is a test message C

***Notice the "<--" John Doe has 3 records 2 are the same but the 1 has a different Rep and a different Fax # and a different message. but the same CustID (sometimes customers have 2 or more different Reps but the same custID)

Now how can this be done with the code above: I want an automatic fax sequence to fax using winfax at the click of a button
I would like each record faxed to it own fax # with it own info
so: " 450085   John Doe   123 hill st  4049999999 This is a test message A " ---get faxed to Jane Parker at 7708985858
with 2 pages(because it only has 2 records of the same)
  " 236524  JAne Doe    222 park s   5054545258  this is a test message B" ----gets faxed to Tom Tom  at 6065656000
  " 450085  John Doe    123 hill st   4049999999  This is a test message D" ----gets faxed to George Ban at 6786767777  
and so on............doing all records

How can i do this please feel free to change or implement the code above to make it work...PLease help!!!!!!!!!!!!!
0
 
LVL 27

Expert Comment

by:jjafferr
ID: 12305719
Hi all4artz
Please don't mension the Carrot (points) as a weapon/tool, as we all try our best to help.


Now I can see the bigger picture, which will make some changes to the code.
It seems that CustomerID is NOT the right Unique field, because we have to look at the FaxNo when it is different,
NOR is the FaxNo, because,
Jon Wrin have the same FaxNo 7708985858 John Doe    
THE right UNIQUE field is a combination of CustomerID and FaxNo


I will summarize everything here once and for all, taking care of all the bits and pieces as we have gone so far, including the changes required NOW.
PLEASE read everything again, as I added the rest of the fields which should be added to the FormFax and ReportFax, without which, the code will NOT work.




Form:
From the Form, you can call module, like this:

Make a command button on FaxForm Form, call this command button Command0, On Click of this button, paste the code below,
I have assumed the following:
CustomerID is the customer ID for each customer
faxNo is the Customer Fax Number
CustomerName is your Customer Name
CompanyName is your Customer Company Name


Private Sub Command0_Click()

HowManyRecords=DCount("[CustomerID]","Customers","[CustomerID]=Forms![FaxForm]![CustomerID] and [FaxNo]=Forms![FaxForm]![FaxNo]")
 If HowManyRecords<1 then
  [label2]="There are no Records to be faxed at this time"
  Exit sub
 endif
 
call SendmyFax(DummyVariant)

End Sub

++++++++++++++++++++++++++++
Module:
Paste the following a module and save it with any name you wish.



Publice Sub SendmyFax(DummyVariant)

'Lets get the Recordset for the Records
    Dim rc As Long: Dim nErrCode As Long: Dim wfSend As Object
    Dim db As dao.Database
    Dim rs As dao.Recordset

    Set db = CurrentDb
    Set rs = db.OpenRecordset("select * from Customers") 'if you have a condition/filter, put it here
    rs.MoveFirst

'Lets send one Record at a time
    Do Until rs.EOF

 if isnull(rs!faxNo) then rs!SentMailInstead="1" :Goto CheckNextRecord
 if isnull(rs!CustomerName) then rs!SentMailInstead="1" :Goto CheckNextRecord
 if isnull(rs!CompanyName) then rs!SentMailInstead="1" :Goto CheckNextRecord

'*****Please note, you could use a query as the Record source for your ReportFax and use filter these fields in the Criteria of each field, BUT since we started in a different way, I am continuing that way.

'Please note that CustomerID and FaxNo are the combination for a unique Record
'In your Form where this starts, I called it FaxForm, make the following fields:
CustomerID
FaxNo
Fname
LName
ADDRESS
PhoneNo
FaxToRep
FaxMessage

'In FaxReport, in the properties of each of the fields above, write this in the Record Source:
=Forms![FaxForm]![CustomerID]
=Forms![FaxForm]![FaxNo]
=Forms![FaxForm]![Fname]
=Forms![FaxForm]![LName]
=Forms![FaxForm]![ADDRESS]
=Forms![FaxForm]![PhoneNo]
=Forms![FaxForm]![FaxToRep]
=Forms![FaxForm]![FaxMessage]

'Now we will print the FaxReport to "C:\CustomerFaxReport.rtf"

        Forms![FaxForm]![CustomerID]= rs!CustomerID
        Forms![FaxForm]![FaxNo]= rs!FaxNo
        Forms![FaxForm]![Fname]= rs!Fname
        Forms![FaxForm]![LName]= rs!LName
        Forms![FaxForm]![ADDRESS]= rs!ADDRESS
        Forms![FaxForm]![PhoneNo]= rs!PhoneNo
        Forms![FaxForm]![FaxToRep]= rs!FaxToRep
        Forms![FaxForm]![FaxMessage]= rs!FaxMessage

        DoCmd.OutputTo acOutputTable, "coverRpt", acFormatRTF, "C:\CustomercoverRpt.rtf", false
        DoCmd.OutputTo acOutputTable, "FaxReport", acFormatRTF, "C:\CustomerFaxReport.rtf", false
        DoCmd.OutputTo acOutputTable, "imageRpt", acFormatRTF, "C:\CustomerimageRpt.rtf", false


'WinFax starts from here
'+++++


Set wfSend = CreateObject("WinFax.SDKSend")
'Your info
wfSend.SetNumber ("123456")
wfSend.SetTo ("YourName")
wfSend.SetCompany ("YourCompany")

wfSend.SetType (0)

wfSend.AddRecipient
wfSend.SetNumber (rs!faxNo)         '("123456")
wfSend.SetTo (rs!CustomerName)      '("To your Customer Name")
wfSend.SetCompany (rs!CompanyName)  '("To your Customer Company Name")

'rc = wfSend.AddRecipient()
'If rc = 1 Then
'MsgBox "add recipient error " & wfSend.GetLastError()
'End If

'cover page
''wfSend.SetUseCover (1)
''wfSend.SetQuickCover (1)
''wfSend.SetCoverText ("sample VB code that uses winfax sdk to send a fax")

'wfSend.SetSubject ("If you have a subject")
wfSend.SetResolution (1): wfSend.SetDeleteAfterSend (1): wfSend.ShowCallProgess (1)

'attach a file. an image file or a file that can be printed.
rc = wfSend.AddAttachmentFile("C:\CustomercoverRpt.rtf")
rc = wfSend.AddAttachmentFile("C:\CustomerFaxReport.rtf")
rc = wfSend.AddAttachmentFile("C:\CustomerimageRpt.rtf")
rc = wfSend.AddAttachmentFile(rs!imageFax)


' and away it goes!, OR an Error occures
rs.Edit
rc = wfSend.Send(0)
If rc = 1 Then
 'nErrCode = wfSend.GetLastError()
 'MsgBox "send failed. Error code: " & nErrCode
 rs!FaxSent="2"
else
 rs!FaxSent="1"
End If
 rs.Update

'get rid of the fax send object.
Set wfSend = Nothing


'+++++
'WinFax Ends here
CheckNextRecord:
        If Not rs.EOF Then rs.MoveNext
    Loop
    rs.Close
    Set rs = Nothing
    Set db = Nothing

End sub
0
 
LVL 8

Author Comment

by:Sam Cohen
ID: 12305751
Thanks JJaffer
I am up all night waiting for you...=)
didnt mean anything about the carrot point
you have been the only expert that has been helping me , and iam grateful!!

I will try it now ::: so dont go anywhere i will be right back
0
 
LVL 27

Expert Comment

by:jjafferr
ID: 12305831
Sorry, but I am on the other side of the world, our time now is 12 noon, and I will be around for the next 2 hours OR more if required.
0
 
LVL 27

Expert Comment

by:jjafferr
ID: 12306500
Knock Knock,
the two hours are gone, are you still working on it?
0
 
LVL 8

Author Comment

by:Sam Cohen
ID: 12316771
Hey Jaffer , I having a bit of probs:
Check this out:
My code:
--------------------------------------------------------------------------------

Option Compare Database
Dim rc As Long: Dim nErrCode As Long: Dim wfSend As Object
    Dim db As dao.Database
    Dim rs As dao.Recordset
   
   
 Private Sub Form_Open(Cancel As Integer)
'Lets get the Recordset for the Records

    Set db = CurrentDb
    Set rs = db.OpenRecordset("select * from CustomersTbl where [CustomersTbl].[SentFax]= 0 And CustomersTbl.[Sale complete]=no") 'if you have a condition/filter, put it here
   
   End Sub
   
Private Sub Command0_Click()

HowManyRecords = DCount("[CustID]", "CustomersTbl", "[CustID]=Forms![FaxForm]![CustID] and [FaxNo]=Forms![FaxForm]![FaxNo]")
 If HowManyRecords < 1 Then
  Me.Text13 = "There are no Records to be faxed at this time"
  Exit Sub
 End If
 
 rs.MoveFirst
 'Lets send one Record at a time
    Do Until rs.EOF
   
 
   If IsNull(rs!FaxNo) Then rs!Edit = rs!SendLetterInstead = "1": GoTo CheckNextRecord





'"Now we will print the FaxReport to "C:\Send_Fax.rtf"

               
        DoCmd.OutputTo acOutputReport, "Cover_Report", acFormatRTF, "C:\Cover_Report.rtf", False
        DoCmd.OutputTo acOutputReport, "Send_fax", acFormatRTF, "C:\Send_fax.rtf", False
       

'WinFax starts from here
'+++++


Set wfSend = CreateObject("WinFax.SDKSend")
'Your info
wfSend.SetNumber ("123456")
wfSend.SetTo ("YourName")
wfSend.SetCompany ("YourCompany")

wfSend.SetType (0)

wfSend.AddRecipient
wfSend.SetNumber (rs!FaxNo) '("123456")
wfSend.SetTo (rs!FName & " " & rs!LName)      '("To your Customer Name")
wfSend.SetCompany (rs!CustCo.)  '("To your Customer Company Name")

'rc = wfSend.AddRecipient()
'If rc = 1 Then
'MsgBox "add recipient error " & wfSend.GetLastError()
'End If

'cover page
''wfSend.SetUseCover (1)
''wfSend.SetQuickCover (1)
''wfSend.SetCoverText ("Cover page text goe here")

wfSend.SetSubject ("MD Survey")
wfSend.SetResolution (1): wfSend.SetDeleteAfterSend (1): wfSend.ShowCallProgess (1)

'attach a file. an image file or a file that can be printed.
rc = wfSend.AddAttachmentFile("C:\Cover_Report.rtf")
rc = wfSend.AddAttachmentFile("C:\Send_fax.rtf")
'rc = wfSend.AddAttachmentFile("C:\Attachments\" & (rs!CustID) & ".pdf")

' and away it goes!, OR an Error occures
rs.Edit
rc = wfSend.Send(0)
If rc = 1 Then
 'nErrCode = wfSend.GetLastError()
 'MsgBox "send failed. Error code: " & nErrCode
 rs!SentFax = "2"
 rs!SendLetterInstead = "1"
Else
 rs!SentFax = "1"
End If
 rs.Update
 
' -------------> to refresh faxform
'DoCmd.Close

'Dim stDocName As String
'Dim stLinkCriteria As String

    'stDocName = "FaxForm"
    'DoCmd.OpenForm stDocName, , , stLinkCriteria
   

'get rid of the fax send object.
Set wfSend = Nothing
  rs.Requery
'+++++
'WinFax Ends here
CheckNextRecord:
        If Not rs.EOF Then rs.MoveNext
    Loop
    rs.Close
    Set rs = Nothing
    Set db = Nothing
   
   
   


End Sub

--------------------------------------------------------
My Form
-------------
now the rec source for faxform is this query:
Select * from CustomersTbl where [CustomersTbl].[SentFax]= 0 And CustomersTbl.[Sale complete]=no

i have the following fields on faxform: CustID, FName, LName, Reps, FaxNo
so based on the query , it populates 3 records to choose from with the navigation arrows

My Reports
---------------
The rec source for my report Send_fax i have this:
SELECT * FROM CustomersTbl WHERE [CustomersTbl].[CustID]=[forms]![FaxForm]!CustID And [CustomersTbl].[FaxNo]=[forms]![FaxForm]![FaxNo];

I have to have the faxForm open in order for report to be viewed correctly

and a brief sample of my send_fax repot is
--------------------SAMPLE REPORT-------------------------------
FAX TO:                                            Customer Info:
[REP]                                             [=FName &" "& LName]
[FaxNo]                                          [Address]
[CUSTID]                                        [PhONENo]


Dear [Rep]

We would like to thank you for your recent sales margin you met. The product will be sent out to
your customer, [FName] &" "& [LName], Customer # [CUSTID] and will be sent out to
[Address]. They should receive it in 3-5 businesss days. If you have any questions,
please contact us. Thank again [Rep] and keep up the good work.

Thanks,
Management

P.S.[Fax_Message]
--------------SAMPLE REPORT----------------------------------------------------------

My Table
--------------
In my table let say i have 4 records:
Cust ID  | Fname  | LName |ADDRESS  |  PhoneNo    |Rep(who to fax to)| FaxNo.           | Fax Message
_________________________________________________________________________________________
450085   | John     | Doe     | 123 hill st | 404-999-9999 |Jane Parker     | 7708985858    | this is a test message A
450085   | John     | Doe     | 123 hill st | 404-999-9999 |Jane Parker     | 7708985858    | this is a test message A
236524   | Vera     | Mars   | 222 park s| 505-454-5258 | Tom Tom       | 6065656000   | this is a test message B
450085  | James   | Wrin   |  44 Oak st | 777-777-7777 |Johnny B Good |  7708985858  | this is a test message C

**Noted above : sometimes the SAME CUSTIDs can be with different Customers .And the reps and faxNo are different**
------This is what ive meant to before =) srry

Now the problems
---------------------------

1) when i click command
i might run in to 2 of the same CustID records witth sams rep and faxNo, well what i want to happen is
The form choose select Distinctive by CustId and Rep and FaxNo, so the Rep can get one fax dial up with 2 pages(1 for each of the 2 records) instead of that rep receiving 2 dialups  and a total of 4 pages.
That way Customer James Wrin's rec (even though the same CustID as John Doe(s))wont be sent to the rep, Jane Parker.
It will only be sent to rep Johnny B Good. So maybe a query can be added or change in this part:

Set rs = db.OpenRecordset("select * from CustomersTbl where [CustomersTbl].[SentFax]= 0 And CustomersTbl.[Sale complete]=no") 'if you have a condition/filter, put it here

-------

2)Is there any way to make FaxForm refresh after each record get sent successful (FaxSent=1) or unsuccessful(sentFax=2)That way  it will guarenteed
each records that belong to the rep get sent to that rep and no other rep---or something?
and faxForm fields should be clear after the last rec get sent
I have tried this:
'DoCmd.Close

'Dim stDocName As String
'Dim stLinkCriteria As String

    'stDocName = "FaxForm"
    'DoCmd.OpenForm stDocName, , , stLinkCriteria
It works somewhat , but it stops at the last record causing someone else to recieve the fax that dont belong to them.

------
3)Is there away i can receive the final status of winfax and post it in a db field? (e.g. busy, fax failed)

4)If all this work could an automation be added to so that it can do fax at a certain time (9am)?


Jaffer , i feel that i am almost there, if you can help me ,i will be willing to increase points!!!!!!!.......

0
 
LVL 27

Expert Comment

by:jjafferr
ID: 12319045
I wrote a few lines, but ie crashed on me and took away my request for a banana milk shake, if we ever meet ;o)

1-
I am not an SQL guy, so here is your SQL which I took from a query and trimmed a bit, the Group By (Query menu bar > View > Totals) will distinct:

SELECT CustID, Fname, Lname, Address, PhoneNo, Rep, FaxNo, Fax_Message, SentFax, [Sale complete]
FROM Customers
GROUP BY CustID, Fname, Lname, Address, PhoneNo, Rep, FaxNo, Fax_Message, SentFax, [Sale complete]
HAVING (((SentFax)=0) AND (([Sale complete])="no"));

2-
Put this in the begining, like this:
 rs.MoveFirst
 'Lets send one Record at a time
    Do Until rs.EOF
me.CustID=0: me.Fname="": me.Lname="": me.Address="": me.PhoneNo=0: me.Rep="": me.FaxNo=0: me.Fax_Message=""
Refresh

3-
I thought you are already doing that in:
rs!SentFax = "2"

But you can set WinFax to try sending the fax a few times (from the winFax preferences),
other than that, sorry I don't know.

4-
This will work if your FaxForm is open a few minutes before 9am.

In the Form properties, set the Timer Interval to 50000 (which equals to 5 minutes),
so every 5 minutes Access will check your On Timer [Event Procedure], and if it matchs the request, Access will Execute your code (see below),
The On Timer place this code:

Private Sub Form_Timer()
'Excute the command on 9am

    Dim TheTime As Date
    TheTime = Format(Now(), "hh:mm")

    Select Case TheTime
        Case #9:00:00 AM#
            'Either put your Code here
            'OR call sub Command0

    Case Else
            'do nothing
    End Select
End Sub



jaffer
0
 
LVL 8

Author Comment

by:Sam Cohen
ID: 12320999
well jaffer for some stange reason i keep getting one record left in the form

see when i click command it seem to get all records sent but one last one(shown in my nav arrows)
i think the problem lies here

Do Until rs.EOF

see because i would have to click the last one
can with Do the loop until all is done then field ion form will be blank
showing  Me.Text13 = "There are no Records to be faxed at this time"
0
 
LVL 27

Expert Comment

by:jjafferr
ID: 12323137
rs.count
should tell you the number of records in your recordset, try it
0
 
LVL 8

Author Comment

by:Sam Cohen
ID: 12326235
I think i got it!!!!
i need sometype of wait or hold in the code to give the code a brief moment to save the report files and then continue,
because obviously the code is moving faster than the save process because for same strange reason i keep getting the last record's info faxed to the first 4 records....And check this out if i stop the loop in the code and do each one manually ,,it works! but if i allow the loop to do it i have this problem...s how about so kind of BUSY in the background type of thing telling th code to wait a moment like maybe 5 secs then proceed...  
0
 
LVL 27

Expert Comment

by:jjafferr
ID: 12326349
You are very smart, in ONE question you took all my gadjets :o(

here is the code to pause the code execution for 5 seconds, while allowing the Events to continue working:

    'pause 5 seconds
    PauseTime = 5    ' Set pause duration.
    Start = Timer      ' Set start time.
    Do While Timer < Start + PauseTime
        DoEvents         ' Yield to other processes.
    Loop
0
 
LVL 8

Author Comment

by:Sam Cohen
ID: 12327819
well you might have answer 2 question ,, thats 700 point bub
0
 
LVL 8

Author Comment

by:Sam Cohen
ID: 12327833
where would i put if i want to hold so attachment can save?
0
 
LVL 27

Expert Comment

by:jjafferr
ID: 12328255
You put it after Printing the Reports, and before you go further (try a 10 second pause too), like this:

        DoCmd.OutputTo acOutputReport, "Cover_Report", acFormatRTF, "C:\Cover_Report.rtf", False
        DoCmd.OutputTo acOutputReport, "Send_fax", acFormatRTF, "C:\Send_fax.rtf", False
       
    'pause 5 seconds
    PauseTime = 5    ' Set pause duration.
    Start = Timer      ' Set start time.
    Do While Timer < Start + PauseTime
        DoEvents         ' Yield to other processes.
    Loop

'WinFax starts from here
'+++++
0
 
LVL 8

Author Comment

by:Sam Cohen
ID: 12331411
ok here is what im encountering,


1)
because of this OpenRecordst:

Set rs = db.OpenRecordset("select * from CustomersTbl where [CustomersTbl].[SentFax]= 0 And CustomersTbl.[Sale complete]=no") 'if you have a condition/filter, put it here

this cause my faxes to go throught alright just one problem if a rep has (2) records he will recieve 2 calls with a total of 4 faxes ---but what he should be recieving is 1 call of a total of 2 faxes(because of the 2 records)

so then i have try thisGroup By sql you gave me:
SELECT CustID, Fname, Lname, Address, PhoneNo, Rep, FaxNo, Fax_Message, SentFax, [Sale complete]
FROM Customers GROUP BY CustID, Fname, Lname, Address, PhoneNo, Rep, FaxNo, Fax_Message, SentFax, [Sale complete] HAVING (((SentFax)=0) AND (([Sale complete])="no"));

and i get the following error " cannot update --- database is read only" and this part of code is highlighted: "rs!Edit"

My Db is not set on read only i have check , because also i put back the first code and it works

2)
in the wfsend.send  code part, why is it that is a line is busy or if fax is cancelled or in no answer
wfsend.send still = (0)? when its supposed to = (1)
what could be the problem, or if theres a solution?
isnt the fax failing if it does any of thoses things?

3)
is this code you gave me:
Set wfSend = CreateObject("WinFax.SDKSend")  
'Your info
wfSend.SetNumber ("123456")    <--------------------------------this part
wfSend.SetTo ("YourName")       <--------------------------------this part
wfSend.SetCompany ("YourCompany")   <------------------------------- this parts

wfSend.SetType (0)

wfSend.AddRecipient
wfSend.SetNumber (rs!FaxNo) '("123456")
wfSend.SetTo (rs!FName & " " & rs!LName)      '("To your Customer Name")
wfSend.SetCompany (rs!CustCo.)  '("To your Customer Company Name")

the part where i put my info  is allowing me to receive to receive the same faxes....can i stop this? as i dont need any faxes that im trying to send away.


These are truly the only stumps in my way ......please help


0
 
LVL 27

Expert Comment

by:jjafferr
ID: 12331451

1-
With Grouping you cannot update the Table.
Let me think of this point for a minute and see how best to handle it.
We might have to get the Record values in code and send them to the Report, and when a duplicate is found, to skip it.

2-
wfsend.send(0) is an instruction to send the fax, but it will NOT hold any further value in it.
it is rs!FaxSent that should hold the values of 1 or 2.

I don't really know how WinFax handles this, but
Since wfsend.send(0) is sent from the code, I think its the WinFax application (not the code) that handles further errors,
as the code will have to loop through the rest of the Records.
Maybe what yo can do, is to pause it after wfsend.send(0) for 5-10 seconds and then check if the results are OK.

3-
Try commenting these lines, like this:

'wfSend.SetNumber ("123456")    <--------------------------------this part
'wfSend.SetTo ("YourName")       <--------------------------------this part
'wfSend.SetCompany ("YourCompany")   <------------------------------- this parts

and if it didn't work, comment the next line too
'wfSend.SetType (0)

0
 
LVL 8

Author Comment

by:Sam Cohen
ID: 12331473
1)ill wait for this if you can get it =)

2)wfsend.send(0)<----- i dont have/see this any where on the code , but here " rc= wfsend.send(0)"

3) now it doesnt send to myfax# "9999999999" , but it still try to sending something to fax# "1"
i wish it would be there , but all well
0
 
LVL 27

Expert Comment

by:jjafferr
ID: 12331482

2-
Yes it is the same,

Good,
So the only problem that exists so far is #1, Right?
0
 
LVL 8

Author Comment

by:Sam Cohen
ID: 12331499
Well i tried putting here(look below) and failing the fax purposely and still gives me faxSent=1...ughhh!

'attach a file. an image file or a file that can be printed.
rc = wfSend.AddAttachmentFile("C:\Cover_Report.rtf")
rc = wfSend.AddAttachmentFile("C:\Send_fax.rtf")
'rc = wfSend.AddAttachmentFile("C:\Attachments\" & (rs!CustID) & ".pdf")
'-------------------

'pause 90 seconds
    PauseTime = 90   ' Set pause duration.
    Start = Timer      ' Set start time.
    Do While Timer < Start + PauseTime                           <----------i tried putting it here
        DoEvents         ' Yield to other processes.
    Loop
 '------------------------

' and away it goes!, OR an Error occures
rs.Edit
rc = wfSend.Send(0)
If rc = 1 Then
 'nErrCode = wfSend.GetLastError()
 'MsgBox "send failed. Error code: " & nErrCode
 rs!SentFax = "2"
 rs!SendLetterInstead = "1"
Else
 rs!SentFax = "1"
End If
 rs.Update



And yes problem #1) still exists----------
0
Get up to 2TB FREE CLOUD per backup license!

An exclusive Black Friday offer just for Expert Exchange audience! Buy any of our top-rated backup solutions & get up to 2TB free cloud per system! Perform local & cloud backup in the same step, and restore instantly—anytime, anywhere. Grab this deal now before it disappears!

 
LVL 27

Expert Comment

by:jjafferr
ID: 12331673
Remove the Pause,

Lets do these trials,
Do ONE trial at a time, if it didn't work, then set it back to original value and do the next one:

1-
instead of rc = wfSend.Send(0)
try rc = wfSend.Send(1)

2-
rc1=wfSend.GetLastError()

3-
rc2=wfSend.IsError()
'if rc2=1 then there is an error
0
 
LVL 27

Expert Comment

by:jjafferr
ID: 12332963
Ok, this is the idea:
When we read a Record, we will read its CustId and Rep and FaxNo,
then comapre these values to an array (which will have the default values of Zero),
if these values are unique, then we will save them in the array,
if they are not unique (which means, its repeated) then we will move to the next Record.
Please follow to know where we will place this code.
+++++

 rs.MoveFirst  '<== Notice this line in the existing code

'Let count how many Records exist in this Query
    Qcount=Dcount("[CustId]","CustomersTbl","[SentFax]= 0 And [Sale complete]='no'")

    Dim TCustId(Qcount), TRep(Qcount), TFaxNo(Qcount)

'Lets initialize all values to Zero, a combination value that will never exist in the Records
    for j=1 to Qcount
      TCustId(j)="0": TRep(j)="0": TFaxNo(j)=0
    next j


 'Lets send one Record at a time
    Do Until rs.EOF      '<== Notice this line in the existing code
  If IsNull(rs!FaxNo) Then rs!Edit = rs!SendLetterInstead = "1": GoTo CheckNextRecord     '<== Notice this line in the existing code

    for j=1 to Qcount

      if TCustId(j)=rs!CustId and TRep(j)=rs!Rep and TFaxNo(j)=rs!FaxNo then
          GoTo CheckNextRecord
      else
         TCustId(j)=rs!CustId and TRep(j)=rs!Rep and TFaxNo(j)=rs!FaxNo
      endif

    next j
 
+++++  

For item #2, I got the items I asked you to try from the WinFax SDK I had,
I think you should download the SDK from http://www.symantec.com/winfax/sdk.html  as ryancys have advised earlier,
there you will find a few commands that you might try with your code, besides trying what I gave you.

jaffer
0
 
LVL 27

Expert Comment

by:jjafferr
ID: 12345168
Hey all, what's happening?
0
 
LVL 8

Author Comment

by:Sam Cohen
ID: 12470139
error

"constant expression required"
0
 
LVL 27

Expert Comment

by:jjafferr
ID: 12470705
Where, which line?
0
 
LVL 8

Author Comment

by:Sam Cohen
ID: 12470855
This one..Qcount is highlighted:
'Let count how many Records exist in this Query
    Qcount=Dcount("[CustId]","CustomersTbl","[SentFax]= 0 And [Sale complete]='no'")
0
 
LVL 27

Expert Comment

by:jjafferr
ID: 12470931
what is the Data Type for SentFax? is Number OR Ttext?
What I gave is for Number,
but is SentFax is Text, then the code should be:
Qcount=Dcount("[CustId]","CustomersTbl","[SentFax]= '0' And [Sale complete]='no'")
0
 
LVL 8

Author Comment

by:Sam Cohen
ID: 12471069
sentfax is a number
not a text
0
 
LVL 27

Expert Comment

by:jjafferr
ID: 12471163
I think it has to do with the way you declared Qcount, it has nothing to do with the code.
The following lines will clarify this.

I got this form:
http://www.experts-exchange.com/Databases/MS_Access/Q_10240440.html


when you declare a fixed length string variable, you have to use a constant as the length.

Example1
Dim strTemp as String * 50
   'works with no problem

Example2
Const fldLength = 50
Dim strTemp as String * fldLength
   'works with no problem

example3
Dim fldLength as integer
fldLength = 50 (or the value of a control on a form)
Dim strTemp as String * fldLength
    'Compile Error
    'Constant Expression Required

This behavior also occurs when you declare the value in a private user-defined data type
++++++++++++++++++++++++++++++

in fact for testing purposes, try this, which I think will work and give you the reult:
msgbox Dcount("[CustId]","CustomersTbl","[SentFax]= 0 And [Sale complete]='no'")

but, this will give you the same error
Qcount=Dcount("[CustId]","CustomersTbl","[SentFax]= 0 And [Sale complete]='no'")
msgbox Qcount

but, this should work
dim Qcount as string
Qcount=Dcount("[CustId]","CustomersTbl","[SentFax]= 0 And [Sale complete]='no'")
msgbox Qcount

Please let me if this helped.

jaffer
0
 
LVL 8

Author Comment

by:Sam Cohen
ID: 12471191
OK jaffer
this is what need:
Set rs = db.OpenRecordset("SELECT DISTINCT  FaxNo, Rep, FROM CustomersTbl WHERE faxSent=0 AND [sales complete]=no")

Now this is the right recordset that i need set, but the only problem...is this part when it tries to, rs edit/update in this part:

rs.Edit
rc = wfSend.Send(0)
If rc = 1 Then
 'nErrCode = wfSend.GetLastError()
 'MsgBox "send failed. Error code: " & nErrCode
 rs!SentFax = "2"
 rs!SendLetterInstead = "1"
Else
 rs!SentFax = "1"
End If
 rs.Update

Why doent it update/edit with the current Open recordset Query i have above???
It just keep giving me a read only error.

Jaffer, that rs edit, is the only part that stopping me
0
 
LVL 27

Expert Comment

by:jjafferr
ID: 12471224
we already discussed this before,
SELECT DISTINCT is another word for Grouping.

So basically you have the individual Records for READ ONLY, not for writing.
Thats why we had to take the longer root.

going back to
'Constant Expression Required

did what I gave you work?
We are almost there.
0
 
LVL 8

Author Comment

by:Sam Cohen
ID: 12471290
>>>did what I gave you work?
Im working with it now..

Jaffer , I wouldnt be able to use Grouping because it gives me multiple fax to a single Rep.
when i use Distinct , it gives me what i need and that is one time fax to rep with all his customers info going through at that one time
0
 
LVL 27

Expert Comment

by:jjafferr
ID: 12471315
Please read my earlier comment too, when I posted the last solution:

Ok, this is the idea:
When we read a Record, we will read its CustId and Rep and FaxNo,
then comapre these values to an array (which will have the default values of Zero),
if these values are unique, then we will save them in the array,
if they are not unique (which means, its repeated) then we will move to the next Record.


so to avoid Distinct, we had to take the longer root.
0
 
LVL 8

Author Comment

by:Sam Cohen
ID: 12471476
ok ive tried it with the Group By function and i get thye same thing....
Do you by any change have and IM? because we are filling this thing up here
0
 
LVL 8

Author Comment

by:Sam Cohen
ID: 12471480
I keep getting that rs Edit ...read only error
0
 
LVL 27

Expert Comment

by:jjafferr
ID: 12471511
I think you are mixing issues here.
You cannot Update/Edit DISTINCT nor GROUP,
therefore I posted this messege for you, which I thought you were working on.

this is the last post which you should work on:

Ok, this is the idea:
When we read a Record, we will read its CustId and Rep and FaxNo,
then comapre these values to an array (which will have the default values of Zero),
if these values are unique, then we will save them in the array,
if they are not unique (which means, its repeated) then we will move to the next Record.
Please follow to know where we will place this code.
+++++

 rs.MoveFirst  '<== Notice this line in the existing code

'Let count how many Records exist in this Query
    Qcount=Dcount("[CustId]","CustomersTbl","[SentFax]= 0 And [Sale complete]='no'")

    Dim TCustId(Qcount), TRep(Qcount), TFaxNo(Qcount)

'Lets initialize all values to Zero, a combination value that will never exist in the Records
    for j=1 to Qcount
      TCustId(j)="0": TRep(j)="0": TFaxNo(j)=0
    next j


 'Lets send one Record at a time
    Do Until rs.EOF      '<== Notice this line in the existing code
  If IsNull(rs!FaxNo) Then rs!Edit = rs!SendLetterInstead = "1": GoTo CheckNextRecord     '<== Notice this line in the existing code

    for j=1 to Qcount

      if TCustId(j)=rs!CustId and TRep(j)=rs!Rep and TFaxNo(j)=rs!FaxNo then
          GoTo CheckNextRecord
      else
         TCustId(j)=rs!CustId and TRep(j)=rs!Rep and TFaxNo(j)=rs!FaxNo
      endif

    next j
 
+++++  

For item #2, I got the items I asked you to try from the WinFax SDK I had,
I think you should download the SDK from http://www.symantec.com/winfax/sdk.html  as ryancys have advised earlier,
there you will find a few commands that you might try with your code, besides trying what I gave you.

jaffer
0
 
LVL 8

Author Comment

by:Sam Cohen
ID: 12471578
Ive understand that part Jaffer , but what do i put here:

Set rs = db.OpenRecordset("SELECT.......")
as my record set?
0
 
LVL 27

Expert Comment

by:jjafferr
ID: 12471612
Use the same old one

Set rs = db.OpenRecordset("select * from CustomersTbl where [CustomersTbl].[SentFax]= 0 And CustomersTbl.[Sale complete]=no")


which will have the duplicate fax numbers, but the code I gave will NOT allow these faxes to go through, it will filter them further and only send one fax.
0
 
LVL 8

Author Comment

by:Sam Cohen
ID: 12471672
ok now im getting an error where im pointing the arrow below:

dim Qcount as string
Qcount=Dcount("[CustId]","CustomersTbl","[SentFax]= 0 And [Sale complete]='no'")
msgbox Qcount
-------->Dim TCustId(Qcount), TRep........
 Qcount is highlighted

error says
'Compile Error
    'Constant Expression Required
0
 
LVL 27

Expert Comment

by:jjafferr
ID: 12471767
Ok, I got it,

What is the Maximum number of Records you can have with duplicate Fax numbers?
if it is 500, then change the line to:

Dim TCustId(510), TRep(510), TFaxNo(510)

I like to add an additional 10 to be on the safe side,

so change this line with a maximum number that you will never exceed.
0
 
LVL 8

Author Comment

by:Sam Cohen
ID: 12471812
Data mismatch error
this is highlighted yellow:
Qcount=Dcount("[CustId]","CustomersTbl","[SentFax]= 0 And [Sale complete]='no'")
0
 
LVL 27

Expert Comment

by:jjafferr
ID: 12471825
change the dim to:

dim Qcount as double
OR
dim Qcount as integer
OR
you can remove the whole Dim line and try
0
 
LVL 8

Author Comment

by:Sam Cohen
ID: 12471888
I have changed
dim Qcount as string
to:

dim Qcount as double
-or-
dim Qcount as integer

but still gets the same error
0
 
LVL 8

Author Comment

by:Sam Cohen
ID: 12471945
even tried removingthe whole dim line and still same error
0
 
LVL 27

Expert Comment

by:jjafferr
ID: 12471969
checked on Data mismatch error
and all the following links accuse the field type, Number or Text and how to enclose them between Single and Double quotes

http://www.experts-exchange.com/Databases/MS_Access/Q_10181703.html
http://www.experts-exchange.com/Databases/MS_Access/Q_20653687.html
http://www.experts-exchange.com/Databases/MS_Access/Q_20939681.html

so please do the check again.
0
 
LVL 8

Author Comment

by:Sam Cohen
ID: 12472005
Ive think ive found it Jaffer!!!!
it is this:

......... [Sale complete]='no'")
ive change to Sale complete]=no

I am now testing it...=)
0
 
LVL 8

Author Comment

by:Sam Cohen
ID: 12472153
Jaffer it tried everyone on the records =(
I need it to be distinct in some way

Note: if a Rep has 3 customers then he gets fax one time with those 3 recs,
Not faxed 3 times and 3 rec on each
0
 
LVL 27

Expert Comment

by:jjafferr
ID: 12472320
Can I ask you to zip your mdb and email it to me, I will have a look at it.
It will be easier for both of us.
0
 
LVL 8

Author Comment

by:Sam Cohen
ID: 12472497
Jaffer , i can send it to you ,
I will explain the implements in the email

dont worry i know your email add, because its in your profile =)
0
 
LVL 8

Author Comment

by:Sam Cohen
ID: 12472520
but pretty much i need this type of distinction(below) or similar to work with the recent code you gave me

Set rs = db.OpenRecordset("SELECT DISTINCT  FaxNo, Rep, FROM CustomersTbl WHERE faxSent=0 AND [sales complete]=no")

0
 
LVL 27

Expert Comment

by:jjafferr
ID: 12472521
thanks
0
 
LVL 27

Accepted Solution

by:
jjafferr earned 500 total points
ID: 12528516
Hi there, and sorry for the delay.
your mdb caused some problems (because of the Cover_Report), but I managed to copy it to another mdb.

Field [survey complete] is a Yes/No,
so Yes means -1, and NO means 0.

Now the code will ONLY pick a unique Record and discard the repeated.
I didn't go to the Reports, I just worked on the code,
so the code will loop through all the Records, but will only allow 1 unique Record to pass.


Dim rc As Long: Dim nErrCode As Long: Dim wfSend As Object
    Dim db As DAO.Database
    Dim rs As DAO.Recordset
    Dim TCustId() As Variant: Dim TRepL() As Variant: Dim TRepF() As Variant: Dim TFaxNo() As Variant
    Dim HowManyRecords As Variant
   
    Set db = CurrentDb
    Set rs = db.OpenRecordset("select * from fax_letter_table where [fax_letter_table].[fax_status]= 0 And fax_letter_table.[survey complete]=0") 'if you have a condition/filter, put it here
     
     HowManyRecords = DCount("[unique]", "fax_letter_table", "[unique]=Forms![MdFaxForm]![unique] and [md_fax]=Forms![MdFaxForm]![md_fax]")
'     MsgBox HowManyRecords
 
 If HowManyRecords < 1 Then
  Me.Text13 = "There are no Records to be faxed at this time"
  Exit Sub
 End If
 
 rs.MoveFirst
     'Lets send one Record at a time
 
ReDim TCustId(HowManyRecords): ReDim TRepL(HowManyRecords): ReDim TRepF(HowManyRecords): ReDim TFaxNo(HowManyRecords)

'Lets initialize all values to Zero, a combination value that will never exist in the Records
    For j = 1 To Qcount
      TCustId(j) = "0": TRepL(j) = "0": TRepF(j) = "0": TFaxNo(j) = 0
    Next j

     While Not rs.EOF

  If IsNull(rs!md_fax) Then rs!Edit = rs!letter_status = "1": GoTo CheckNextRecord
 ' If IsNull(rs!FaxNo) Then rs!Edit = rs!SendLetterInstead = "1": GoTo CheckNextRecord     '<== Notice this line in the existing code

    For j = 1 To HowManyRecords
'MsgBox "1**" & rs!unique & " + " & rs!Last_name & " + " & rs!First_name & " + " & rs!md_fax
      If TCustId(j) = rs!unique And TRepL(j) = rs!Last_name And TRepF(j) = rs!First_name And TFaxNo(j) = rs!md_fax Then
          GoTo CheckNextRecord
      Else
         TCustId(j) = rs!unique And TRepL(j) = rs!Last_name And TRepF(j) = rs!First_name And TFaxNo(j) = rs!md_fax
      End If
    Next j

 
  MsgBox "2**" & rs!unique & " + " & rs!Last_name & " + " & rs!First_name & " + " & rs!md_fax
 
'  Exit Sub

'"Now we will print the FaxReport to "C:\CustomerFaxReport.rtf"

               
        DoCmd.OutputTo acOutputReport, "Cover_Report", acFormatRTF, "C:\Cover_Report.rtf", False
        DoCmd.OutputTo acOutputReport, "Send_fax", acFormatRTF, "C:\Send_fax.rtf", False
       

'WinFax starts from here
'+++++


Set wfSend = CreateObject("WinFax.SDKSend")
'Your info
wfSend.SetNumber ("123456")
wfSend.SetTo ("YourName")
wfSend.SetCompany ("YourCompany")

wfSend.SetType (0)

wfSend.AddRecipient
wfSend.SetNumber (rs!md_fax) '("123456")
wfSend.SetTo (rs!md_firstname & " " & rs!md_lastname)      '("To your Customer Name")
wfSend.SetCompany (rs!md_address1)  '("To your Customer Company Name")

'rc = wfSend.AddRecipient()
'If rc = 1 Then
'MsgBox "add recipient error " & wfSend.GetLastError()
'End If

'cover page
''wfSend.SetUseCover (1)
''wfSend.SetQuickCover (1)
''wfSend.SetCoverText ("sample VB code that uses winfax sdk to send a fax")

wfSend.SetSubject ("MD Survey")
wfSend.SetResolution (1): wfSend.SetDeleteAfterSend (1): wfSend.ShowCallProgess (1)

'attach a file. an image file or a file that can be printed.
rc = wfSend.AddAttachmentFile("C:\Cover_Report.rtf")
rc = wfSend.AddAttachmentFile("C:\Send_fax.rtf")
'rc = wfSend.AddAttachmentFile("S:\Shared\Pendragon SyncServer DB\consents\" & (rs!unique) & ".pdf")

' and away it goes!, OR an Error occures
rs.Edit
rc = wfSend.Send(0)
If rc = 1 Then
 'nErrCode = wfSend.GetLastError()
 'MsgBox "send failed. Error code: " & nErrCode
 rs!fax_status = "2"
 rs!letter_status = "1"
Else
 rs!fax_status = "1"
End If
 rs.Update

DoCmd.Close
 Dim stDocName As String
Dim stLinkCriteria As String

    stDocName = "MdFaxForm"
    DoCmd.OpenForm stDocName, , , stLinkCriteria



'get rid of the fax send object.
Set wfSend = Nothing
  rs.Requery
'+++++
'WinFax Ends here
CheckNextRecord:
        If Not rs.EOF Then rs.MoveNext
    Wend
    rs.Close
    Set rs = Nothing
    Set db = Nothing


Good Luck in your project,

jaffer
0

Featured Post

What Should I Do With This Threat Intelligence?

Are you wondering if you actually need threat intelligence? The answer is yes. We explain the basics for creating useful threat intelligence.

Join & Write a Comment

Most if not all databases provide tools to filter data; even simple mail-merge programs might offer basic filtering capabilities. This is so important that, although Access has many built-in features to help the user in this task, developers often n…
When you are entering numbers in a speadsheet, and don't remember what 6×7 is, you just type “=6*7" instead. It works in every cell! This is not so in Access. To enter the elusive 42 in a text box, you have to find a calculator, and then copy the re…
Familiarize people with the process of retrieving data from SQL Server using an Access pass-thru query. Microsoft Access is a very powerful client/server development tool. One of the ways that you can retrieve data from a SQL Server is by using a pa…
With Microsoft Access, learn how to start a database in different ways and produce different start-up actions allowing you to use a single database to perform multiple tasks. Specify a start-up form through options: Specify an Autoexec macro: Us…

759 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

21 Experts available now in Live!

Get 1:1 Help Now