By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
435,538 Members | 2,225 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 435,538 IT Pros & Developers. It's quick & easy.

Postcard printing based on different options.

jinalpatel
P: 68
Hello

I am a novice to Access/VBA and this is driving me nuts. Any help would be appreciated.

I have to create a little application which can print postcards(4"X6") based on following options:
Customer Type
OrderNumber
LoggedDate
Location

There are three cmd buttons Print, Preview and Cancel.

I created one report which has general look and feel of postcard(exmple return address on left hand upper corner and To address in the middle)

I developed four queries based on 4 different options.
qry_CustomerTypeOption
qry_OrderNumberOption
qryLoggedDtOption
qryLocationOption

When I assign one query to report's recordsource it works fine

My problem:

I want to change the recordsource of the report to the different queries as per the options selected. I don't know how to do it. I tried with the

Expand|Select|Wrap|Line Numbers
  1. Private Sub cmdPreview_Click()
  2. Select Case Me.Frame0.Value
  3.  
  4. Case 1
  5.  
  6. DoCmd.OpenReport "rptPostCard", acViewPreview, "qry_CustomerTypeOption"
  7.  
  8. Case 2
  9.  
  10. DoCmd.OpenReport "rptPostCard", acViewPreview, "qry_OrderNumberOption"
  11.  
  12. End Select
  13. End Sub
Please help.

thanks
Dec 9 '08 #1
Share this Question
Share on Google+
11 Replies


Expert 100+
P: 1,287
You can make a separate report for each case, or you should be able to do:

docmd.openreport ...

case 1
Reports![rptPostCard].RecordSource = "qry_CustomerTypeOption"
case 2
Reports![rptPostCard].RecordSource = "qry_OrderNumberOption"

I have only used that with forms, not reports, but it should work. No time to try it beforehand sorry :(
Dec 9 '08 #2

NeoPa
Expert Mod 15k+
P: 31,494
You can change the RecordSource of a report from within the Report_Open() procedure. You may have problems unless the new query returns the same fields as the one it was designed with.

A more elegant solution (generally - depends on exactly what you're after) might be to design the report to handle all types, then simply call the same report with a different filter for each option.
Dec 9 '08 #3

jinalpatel
P: 68
After reading this post I tried this: same report with different record source each time using case statement in Report's On Open event like this
Expand|Select|Wrap|Line Numbers
  1. Private Sub Report_Open(Cancel As Integer)
  2.  
  3. Select Case [Forms]![frmPostcardPrinting].Frame0.Value
  4.  
  5.  
  6. Case 1
  7. Me.RecordSource = "qry_CustomerTypeOption"
  8.  
  9. Case 2
  10. Me.RecordSource = "qry_OrderNumberOption"
  11.  
  12. Case 3
  13. Me.RecordSource = "qry_LoggedByOption"
  14.  
  15. Case 4
  16. Me.RecordSource = "qryLocation"
  17.  
  18. End Select
  19.  
  20.  
  21. End Sub
Now what happens is it only works for the fisrt option(meaning it nicely displays the postcard (reoport) for CustomerType) For other options it shows #Error at all the textboxes on the report.
I am stuck here again.

NeoPa,

Yes each of my query returns same values like FirstName, LastName, Address, City,State and Zip.
You think that what I have mentioned above will not work? Because I have to have develop the queries .I can not use filters as that is the customer requirement.

thanks again for replying
Dec 9 '08 #4

jinalpatel
P: 68
Here I am again..
I got that working. All of my options are working.Don't know how? I just restarted whole application 3-4 times and it worked.
Thanks
Dec 10 '08 #5

NeoPa
Expert Mod 15k+
P: 31,494
Very pleased to hear it Jinal :)

Not least because I was struggling with the concept of a customer requesting you not use filters (like they would even know what one was).

...but if it's working that's all fine.
Dec 10 '08 #6

jinalpatel
P: 68
@NeoPa

I also wonder, but they have specified in the req. doc that I have to develop four queries.Don't know why?

I have one more question..
For each query I have one criteria "application must change SeedlingOrder:PostcardPrinting date to date postcards printed"
How do I do that?

I know I have to use insert query under cmdPrint_click() but how?
If you can give me some example, I would be thankful
Thanks again
Dec 10 '08 #7

NeoPa
Expert Mod 15k+
P: 31,494
@jinalpatel
Without access to all the details it's impossible for me to say why they have done that. It looks like they have tried to work out the implementation details for you too. This can sometimes be helpful, especially when the people specifying have a good understanding of the technology. Unfortunately, there are times when it can just add confusion.

Regardless of why that may be, that is the position you find yourself in so it must be handled, one way or another.
@jinalpatel
As I have no access to any of the items you refer to there is very little I can do here to help. Try rephrasing your question so that it makes some better sense.
@jinalpatel
If you define an Insert query (QueryDef) (The wizard can help get you started there if you need) called "qryAddRecs", then you can execute that query using code similar to the following :
Expand|Select|Wrap|Line Numbers
  1. Call CurrentDB.QuerDefs("qryAddRecs").Execute
Dec 11 '08 #8

jinalpatel
P: 68
I have following code segment for my report which is same for all options, meaning user can chose any of the option from CustType, OrdNo, LoggedDate and Location. As you know I have developed four queries based on each option criteria. Here is the code for Report
Expand|Select|Wrap|Line Numbers
  1. Option Compare Database
  2.  
  3. Private Sub Report_Open(Cancel As Integer)
  4.  
  5. Select Case [Forms]![frmPostcardPrinting].Frame0.Value
  6.  
  7. Case 1
  8. Me.RecordSource = "qry_CustomerTypeOption"
  9.  
  10. Case 2
  11. Me.RecordSource = "qry_OrderNumberOption"
  12.  
  13. Case 3
  14. Me.RecordSource = "qry_LoggedDateOption"
  15.  
  16. Case 4
  17. Me.RecordSource = "qry_Location"
  18.  
  19. End Select
  20.  
  21.  
  22. End Sub
  23. Private Sub Report_NoData(Cancel As Integer)
  24.  
  25.     ' Display message to user.
  26.     MsgBox "There are no records to report", vbExclamation, "No Records"
  27.  
  28.     ' Close the report.
  29.     Cancel = True
  30.  
  31. End Sub
  32.  

Here is my code for main form that has all the options
Expand|Select|Wrap|Line Numbers
  1. Option Compare Database
  2.  
  3. Private Sub cmdCancel_Click()
  4.  
  5.     'Clear all the controls in the Form Header section.
  6.     For Each ctl In Me.Controls
  7.          Select Case ctl.ControlType
  8.         Case acTextBox
  9.             ctl.Value = Null
  10.             Case acComboBox
  11.             ctl.Value = Null
  12.         End Select
  13.     Next
  14.  
  15. '     Remove the form's filter.
  16. '    Me.Filter = "(False)"
  17. '    Me.FilterOn = True
  18.  
  19. End Sub
  20.  
  21. Private Sub cmdPreview_Click()
  22. On Error GoTo Err_cmdPreview_Click
  23.  
  24. Select Case Me.Frame0.Value
  25. Case 1
  26. DoCmd.OpenReport "rptCustType", acViewPreview
  27.  
  28. Case 2
  29. DoCmd.OpenReport "rptCustType", acViewPreview
  30.  
  31. Case 3
  32. DoCmd.OpenReport "rptCustType", acViewPreview
  33.  
  34. Case 4
  35. DoCmd.OpenReport "rptCustType", acViewPreview
  36.  
  37. End Select
  38.  
  39. Exit_cmdPreview_Click:
  40.     Exit Sub
  41.  
  42. Err_cmdPreview_Click:
  43.  
  44.     MsgBox "Cannot print postcards as there are no records"
  45.     'MsgBox Err.Description
  46.     Resume Exit_cmdPreview_Click
  47.  
  48.  
  49. End Sub
  50.  
  51. Private Sub cmdPrint_Click()
  52. On Error GoTo Err_cmdPrint_Click
  53.     Dim rstPrint As ADODB.Recordset
  54.     Dim strSQL As String
  55.     Dim conDb As ADODB.Connection
  56.     Dim response As VbMsgBoxResult
  57.  
  58.     Dim stDocName As String
  59.  
  60.     stDocName = "rptCustType"
  61.  
  62.  
  63. Select Case Me.Frame0.Value
  64.  
  65. Case 1
  66.  
  67.    MsgBox "Do you want to print the labels?Once you click the OK, you will not be able to cancel the process", vbOKCancel
  68.  
  69.     If vbOK Then
  70.  
  71.             'Printing report with the following command
  72.              DoCmd.OpenReport stDocName, acNormal
  73.  
  74.  
  75.             'As soon as the report
  76.              Set conDb = CurrentProject.Connection
  77.             '    Debug.Print
  78.     strSQL = "Update dbo_SeedlingOrder " & _
  79.     "set dbo_SeedlingOrder.PostCardPrintDate = Date()" & _
  80.     "where dbo_seedlingOrder.ShippingChoiceGenId <>3" & _
  81.     "and dbo_SeedlingOrder.CustomerTypeGenId=" & Me.cmbCustType & _
  82.     "and dbo_SeedlingOrder.LocationGenId=" & Me.cmbLocationName & _
  83.     "and dbo_SeedlingOrder.PaidInd = true" & _
  84.     "and dbo_SeedlingOrder.PostCardPrintDate is null"
  85.  
  86.  
  87.                     MsgBox "PostcardPrintDate is updated to today's date"
  88.                     conDb.Execute strSQL
  89.                     conDb.Close
  90.                     Set conDb = Nothing
  91.     Else
  92.  
  93.             DoCmd.CancelEvent
  94.  
  95.     End If
  96.  
  97.  
  98. Case 2
  99.  
  100.     MsgBox "Do you want to print the labels?Once you click the OK, you will not be able to cancel the process", vbOKCancel
  101.  
  102.     If vbOK Then
  103.  
  104.             'Printing report with the following command
  105.              DoCmd.OpenReport stDocName, acNormal
  106.  
  107.  
  108.             'As soon as the report
  109.              Set conDb = CurrentProject.Connection
  110.             '    Debug.Print
  111.     strSQL = "Update dbo_SeedlingOrder " & _
  112.     "set dbo_SeedlingOrder.PostCardPrintDate = Date()" & _
  113.     "where dbo_SeedlingOrder.OrderNumberPostLottery>=" & Me.txtBeginOrdNo & _
  114.     "and dbo_SeedlingOrder.OrderNumberPostLottery<=" & Me.txtEndingOrdNo & _
  115.     "and dbo_SeedlingOrder.PaidInd = true" & _
  116.     "and dbo_SeedlingOrder.PostCardPrintDate is null"
  117.  
  118.  
  119.  
  120.                     MsgBox "PostcardPrintDate is updated to today's date"
  121.                     conDb.Execute strSQL
  122.                     conDb.Close
  123.                     Set conDb = Nothing
  124.     Else
  125.             DoCmd.CancelEvent
  126.  
  127.    End If
  128.  
  129. 'Loggeddate option
  130. Case 3
  131.    MsgBox "Do you want to print the labels?Once you click the OK, you will not be able to cancel the process", vbOKCancel
  132.  
  133.     If vbOK Then
  134.  
  135.             'Printing report with the following command
  136.              DoCmd.OpenReport stDocName, acNormal
  137.  
  138.  
  139.             'As soon as the report
  140.              Set conDb = CurrentProject.Connection
  141.             '    Debug.Print
  142. strSQL = "Update dbo_OrderPayment Inner join dbo_SeedlingOrder" & _
  143. "on dbo_OrderPayment.OrderGenId = dbo_SeedlingOrder.OrderGenId" & _
  144. "inner join dbo_ref_Location on dbo_SeedlingOrder.LocationGenId = dbo_ref_LocationGenId" & _
  145. "set dbo_SeedlingOrder.PostCardPrintDate = Date()" & _
  146. "where dbo_SeedlingOrder.PostCardPrintDate is null" & _
  147. "and dbo_OrderPayment.OrderPaymentDate>=" & Me.txtBeginDate & _
  148. "and dbo_OrderPayment.OrderPaymentDate<=" & Me.txtEndDate & _
  149. "and dbo_ref_Location.LocationName=" & Me.cmbLocationName3 & _
  150. "and dbo_SeedlingOrder.PaidInd = true"
  151.  
  152.                     MsgBox "PostcardPrintDate is updated to today's date"
  153.                     conDb.Execute strSQL
  154.                     conDb.Close
  155.                     Set conDb = Nothing
  156.     Else
  157.             DoCmd.CancelEvent
  158.     End If
  159.  
  160. Case 4
  161.    MsgBox "Do you want to print the labels?Once you click the OK, you will not be able to cancel the process", vbOKCancel
  162.  
  163.     If vbOK Then
  164.  
  165.             'Printing report with the following command
  166.              DoCmd.OpenReport stDocName, acNormal
  167.  
  168.  
  169.             'As soon as the report
  170.              Set conDb = CurrentProject.Connection
  171.             '    Debug.Print
  172.                     strSQL = "Update dbo_SeedlingOrder " & _
  173.                     "set dbo_SeedlingOrder.PostCardPrintDate = Date()" & _
  174.                     "where dbo_seedlingOrder.ShippingChoiceGenId <>3" & _
  175.                     "and dbo_SeedlingOrder.CustomerTypeGenId <>1" & _
  176.                     "and dbo_SeedlingOrder.PostCardPrintDate is null" & _
  177.                     "and dbo_SeedlingOrder.LocationGenId=" & Me.cmbLocationName1 & _
  178.                     "and dbo_SeedlingOrder.PaidInd = true"
  179.  
  180.                     MsgBox "PostcardPrintDate is updated to today's date"
  181.                     conDb.Execute strSQL
  182.                     conDb.Close
  183.                     Set conDb = Nothing
  184.     Else
  185.             DoCmd.CancelEvent
  186.     End If
  187.  
  188.  
  189. End Select
  190.  
  191. Exit_cmdPrint_Click:
  192.     Exit Sub
  193.  
  194. Err_cmdPrint_Click:
  195.     MsgBox Err.Description
  196.     Resume Exit_cmdPrint_Click
  197. End Sub
  198.  
  199. Private Sub Form_Load()
  200.         cmbCustType.Enabled = True
  201.         cmbLocationName.Enabled = True
  202.         txtBeginOrdNo.Enabled = False
  203.         txtEndingOrdNo.Enabled = False
  204.         txtBeginDate.Enabled = False
  205.         txtEndDate.Enabled = False
  206.         cmbLocationName1.Enabled = False
  207.         cmbLocationName3.Enabled = False
  208.  
  209. End Sub
  210.  
  211.  
  212. Private Sub Form_Open(Cancel As Integer)
  213. End Sub
  214.  
  215. Private Sub Frame0_Afterupdate()
  216. Select Case Me.Frame0.Value
  217.  
  218. Case 1
  219.         cmbCustType.Enabled = True
  220.         cmbLocationName.Enabled = True
  221.         txtBeginOrdNo.Enabled = False
  222.         txtEndingOrdNo.Enabled = False
  223.         txtBeginDate.Enabled = False
  224.         txtEndDate.Enabled = False
  225.         cmbLocationName1.Enabled = False
  226.         cmbLocationName3.Enabled = False
  227.  
  228. Case 2
  229.         cmbCustType.Enabled = False
  230.         cmbLocationName.Enabled = False
  231.         txtBeginOrdNo.Enabled = True
  232.         txtEndingOrdNo.Enabled = True
  233.         txtBeginDate.Enabled = False
  234.         txtEndDate.Enabled = False
  235.         cmbLocationName1.Enabled = False
  236.         cmbLocationName3.Enabled = False
  237.  
  238. Case 3
  239.         cmbCustType.Enabled = False
  240.         cmbLocationName.Enabled = False
  241.         txtBeginOrdNo.Enabled = False
  242.         txtEndingOrdNo.Enabled = False
  243.         txtBeginDate.Enabled = True
  244.         txtEndDate.Enabled = True
  245.         cmbLocationName1.Enabled = False
  246.         cmbLocationName3.Enabled = True
  247.  
  248. Case 4
  249.  
  250.         cmbCustType.Enabled = False
  251.         cmbLocationName.Enabled = False
  252.         txtBeginOrdNo.Enabled = False
  253.         txtEndingOrdNo.Enabled = False
  254.         txtBeginDate.Enabled = False
  255.         txtEndDate.Enabled = False
  256.         cmbLocationName1.Enabled = True
  257.         cmbLocationName3.Enabled = False
  258.  
  259. End Select
  260.  
  261. End Sub
  262.  
  263.  
  264.  
  265. Private Sub cmdQuit_Click()
  266. On Error GoTo Err_cmdQuit_Click
  267.  
  268.  
  269.     DoCmd.Quit
  270.  
  271. Exit_cmdQuit_Click:
  272.     Exit Sub
  273.  
  274. Err_cmdQuit_Click:
  275.     MsgBox Err.Description
  276.     Resume Exit_cmdQuit_Click
  277.  
  278. End Sub
  279.  
Here what I am doing is,
1) sending the appropriate query to the report to get opened.
2)Get the PostcardPrintDate updated to curerent date by docmd.runsql(Somehow I got that working)


My problem:
for both Print and Priview option same problem(written below)

It only works for first option. For other options it goes to Report_NoData() function and gives me message "There are no records to report" and "Cannot print the Postcards as there are no records"

I crosschecked by running the queries separately and it has data. It shows me number of records.

Also I did F9 at different places and found out that it does go to its correct case but than it goes to Report_NoData().

Please help me out of it. It is driving me nuts.
Dec 11 '08 #9

jinalpatel
P: 68
also,
I got the PostCardPrintDate field updated to current date. That means those update queries are right. if I comment docmd.openreport under cmdPrint command and than I run the app, I can successfully update the PostCardPrintDate field to the current date.
Please help me out of this.
Dec 11 '08 #10

NeoPa
Expert Mod 15k+
P: 31,494
Jinal,

Nothing immediately springs to mind that would explain your problem.

Let me just highlight a few things first in your code, then I will ask you to trace (Debugging in VBA) through the Report_Open() code to see how much of it is being processed and which route it takes.
  1. Check out Require Variable Declaration.
  2. Similar to the last one, Always compile code before posting with a request for help. There is little more annoying to someone willing to offer some of their spare time than finding a problem that is easily resolved simply by using the compiler. I doubt this will find your particular problem now, but it is definitely worth everyone making a note of. Always worth pointing out.
  3. In your cmdPreview_Click() procedure you use a Select ... Case statement where each of the Case statements has the same code in it.
  4. Code indentation should never be random. There is information in where the code starts. It is not mandatory, but it can impart information to the reader. Conversely, the incorrect positioning can actual send misleading information and can make the code harder to read and understand. This is not generally appreciated.
  5. Expand|Select|Wrap|Line Numbers
    1. Select Case ctl.ControlType
    2. Case acTextBox
    3.     ctl.Value = Null
    4.     Case acComboBox
    5.     ctl.Value = Null
    6. End Select
    Can, more clearly be written as :
    Expand|Select|Wrap|Line Numbers
    1. Select Case ctl.ControlType
    2. Case acTextBox, acComboBox
    3.     ctl = Null
    4. End Select
The most important point with respect to your current issue would certainly be to look at the tracing of the code to determine where it is going that you do not expect it to go. The other points are well worth looking at though.

PS. Sorry for the delayed response. I've been mainly unavailable for a while and have struggled to keep up with all my ongoing threads. Hopefully we can get back to sorting this out now.
Dec 16 '08 #11

jinalpatel
P: 68
Thanks NeoPa,

I figured out this problem.

my 2nd option was not working as I have made a silly typing mistake for typing name. In query I wrote txtEndOrderNo instead of txtEndOrdNo

my 3rd option was not working as I have made mistake in capturing the date values. I didn't write # in front and in the end of my date field.

my 4th option is little bit wierd as I had to add one table that tablle although does not contain any field of my interest but just the reference table. Anyhow by using F9 I figured it out. Today was project deadline and thank go I finished it on time.

Thanks very much for guiding me through all those problems.

I appreciate your help.


@NeoPa
Dec 16 '08 #12

Post your reply

Sign in to post your reply or Sign up for a free account.