Postcard printing based on different options.

[jinalpatel]

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

Private Sub cmdPreview_Click()
Select Case Me.Frame0.Value
 
Case 1
 
DoCmd.OpenReport "rptPostCard", acViewPreview, "qry_CustomerTypeOption"
 
Case 2
 
DoCmd.OpenReport "rptPostCard", acViewPreview, "qry_OrderNumberOption"
 
End Select
End Sub

Please help.

thanks

[ChipR]

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 :(

[NeoPa]

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.

[jinalpatel]

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

Private Sub Report_Open(Cancel As Integer)
 
Select Case [Forms]![frmPostcardPrinting].Frame0.Value
 
 
Case 1
Me.RecordSource = "qry_CustomerTypeOption"
 
Case 2
Me.RecordSource = "qry_OrderNumberOption"
 
Case 3
Me.RecordSource = "qry_LoggedByOption"
 
Case 4
Me.RecordSource = "qryLocation"
 
End Select
 
 
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

[jinalpatel]

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

[NeoPa]

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.

[jinalpatel]

@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

[NeoPa]

@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 :

Call CurrentDB.QuerDefs("qryAddRecs").Execute

[jinalpatel]

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

Option Compare Database
 
Private Sub Report_Open(Cancel As Integer)
 
Select Case [Forms]![frmPostcardPrinting].Frame0.Value
 
Case 1
Me.RecordSource = "qry_CustomerTypeOption"
 
Case 2
Me.RecordSource = "qry_OrderNumberOption"
 
Case 3
Me.RecordSource = "qry_LoggedDateOption"
 
Case 4
Me.RecordSource = "qry_Location"
 
End Select
 
 
End Sub
Private Sub Report_NoData(Cancel As Integer)
 
    ' Display message to user.
    MsgBox "There are no records to report", vbExclamation, "No Records"
 
    ' Close the report.
    Cancel = True
 
End Sub
 

Here is my code for main form that has all the options

Option Compare Database
 
Private Sub cmdCancel_Click()
 
    'Clear all the controls in the Form Header section.
    For Each ctl In Me.Controls
         Select Case ctl.ControlType
        Case acTextBox
            ctl.Value = Null
            Case acComboBox
            ctl.Value = Null
        End Select
    Next
 
'     Remove the form's filter.
'    Me.Filter = "(False)"
'    Me.FilterOn = True
 
End Sub
 
Private Sub cmdPreview_Click()
On Error GoTo Err_cmdPreview_Click
 
Select Case Me.Frame0.Value
Case 1
DoCmd.OpenReport "rptCustType", acViewPreview
 
Case 2
DoCmd.OpenReport "rptCustType", acViewPreview
 
Case 3
DoCmd.OpenReport "rptCustType", acViewPreview
 
Case 4
DoCmd.OpenReport "rptCustType", acViewPreview
 
End Select
 
Exit_cmdPreview_Click:
    Exit Sub
 
Err_cmdPreview_Click:
 
    MsgBox "Cannot print postcards as there are no records"
    'MsgBox Err.Description
    Resume Exit_cmdPreview_Click
 
 
End Sub
 
Private Sub cmdPrint_Click()
On Error GoTo Err_cmdPrint_Click
    Dim rstPrint As ADODB.Recordset
    Dim strSQL As String
    Dim conDb As ADODB.Connection
    Dim response As VbMsgBoxResult
 
    Dim stDocName As String
 
    stDocName = "rptCustType"
 
 
Select Case Me.Frame0.Value
 
Case 1
 
   MsgBox "Do you want to print the labels?Once you click the OK, you will not be able to cancel the process", vbOKCancel
 
    If vbOK Then
 
            'Printing report with the following command
             DoCmd.OpenReport stDocName, acNormal
 
 
            'As soon as the report
             Set conDb = CurrentProject.Connection
            '    Debug.Print
    strSQL = "Update dbo_SeedlingOrder " & _
    "set dbo_SeedlingOrder.PostCardPrintDate = Date()" & _
    "where dbo_seedlingOrder.ShippingChoiceGenId <>3" & _
    "and dbo_SeedlingOrder.CustomerTypeGenId=" & Me.cmbCustType & _
    "and dbo_SeedlingOrder.LocationGenId=" & Me.cmbLocationName & _
    "and dbo_SeedlingOrder.PaidInd = true" & _
    "and dbo_SeedlingOrder.PostCardPrintDate is null"
 
 
                    MsgBox "PostcardPrintDate is updated to today's date"
                    conDb.Execute strSQL
                    conDb.Close
                    Set conDb = Nothing
    Else
 
            DoCmd.CancelEvent
 
    End If
 
 
Case 2
 
    MsgBox "Do you want to print the labels?Once you click the OK, you will not be able to cancel the process", vbOKCancel
 
    If vbOK Then
 
            'Printing report with the following command
             DoCmd.OpenReport stDocName, acNormal
 
 
            'As soon as the report
             Set conDb = CurrentProject.Connection
            '    Debug.Print
    strSQL = "Update dbo_SeedlingOrder " & _
    "set dbo_SeedlingOrder.PostCardPrintDate = Date()" & _
    "where dbo_SeedlingOrder.OrderNumberPostLottery>=" & Me.txtBeginOrdNo & _
    "and dbo_SeedlingOrder.OrderNumberPostLottery<=" & Me.txtEndingOrdNo & _
    "and dbo_SeedlingOrder.PaidInd = true" & _
    "and dbo_SeedlingOrder.PostCardPrintDate is null"
 
 
 
                    MsgBox "PostcardPrintDate is updated to today's date"
                    conDb.Execute strSQL
                    conDb.Close
                    Set conDb = Nothing
    Else
            DoCmd.CancelEvent
 
   End If
 
'Loggeddate option
Case 3
   MsgBox "Do you want to print the labels?Once you click the OK, you will not be able to cancel the process", vbOKCancel
 
    If vbOK Then
 
            'Printing report with the following command
             DoCmd.OpenReport stDocName, acNormal
 
 
            'As soon as the report
             Set conDb = CurrentProject.Connection
            '    Debug.Print
strSQL = "Update dbo_OrderPayment Inner join dbo_SeedlingOrder" & _
"on dbo_OrderPayment.OrderGenId = dbo_SeedlingOrder.OrderGenId" & _
"inner join dbo_ref_Location on dbo_SeedlingOrder.LocationGenId = dbo_ref_LocationGenId" & _
"set dbo_SeedlingOrder.PostCardPrintDate = Date()" & _
"where dbo_SeedlingOrder.PostCardPrintDate is null" & _
"and dbo_OrderPayment.OrderPaymentDate>=" & Me.txtBeginDate & _
"and dbo_OrderPayment.OrderPaymentDate<=" & Me.txtEndDate & _
"and dbo_ref_Location.LocationName=" & Me.cmbLocationName3 & _
"and dbo_SeedlingOrder.PaidInd = true"
 
                    MsgBox "PostcardPrintDate is updated to today's date"
                    conDb.Execute strSQL
                    conDb.Close
                    Set conDb = Nothing
    Else
            DoCmd.CancelEvent
    End If
 
Case 4
   MsgBox "Do you want to print the labels?Once you click the OK, you will not be able to cancel the process", vbOKCancel
 
    If vbOK Then
 
            'Printing report with the following command
             DoCmd.OpenReport stDocName, acNormal
 
 
            'As soon as the report
             Set conDb = CurrentProject.Connection
            '    Debug.Print
                    strSQL = "Update dbo_SeedlingOrder " & _
                    "set dbo_SeedlingOrder.PostCardPrintDate = Date()" & _
                    "where dbo_seedlingOrder.ShippingChoiceGenId <>3" & _
                    "and dbo_SeedlingOrder.CustomerTypeGenId <>1" & _
                    "and dbo_SeedlingOrder.PostCardPrintDate is null" & _
                    "and dbo_SeedlingOrder.LocationGenId=" & Me.cmbLocationName1 & _
                    "and dbo_SeedlingOrder.PaidInd = true"
 
                    MsgBox "PostcardPrintDate is updated to today's date"
                    conDb.Execute strSQL
                    conDb.Close
                    Set conDb = Nothing
    Else
            DoCmd.CancelEvent
    End If
 
 
End Select
 
Exit_cmdPrint_Click:
    Exit Sub
 
Err_cmdPrint_Click:
    MsgBox Err.Description
    Resume Exit_cmdPrint_Click
End Sub
 
Private Sub Form_Load()
        cmbCustType.Enabled = True
        cmbLocationName.Enabled = True
        txtBeginOrdNo.Enabled = False
        txtEndingOrdNo.Enabled = False
        txtBeginDate.Enabled = False
        txtEndDate.Enabled = False
        cmbLocationName1.Enabled = False
        cmbLocationName3.Enabled = False
 
End Sub
 
 
Private Sub Form_Open(Cancel As Integer)
End Sub
 
Private Sub Frame0_Afterupdate()
Select Case Me.Frame0.Value
 
Case 1
        cmbCustType.Enabled = True
        cmbLocationName.Enabled = True
        txtBeginOrdNo.Enabled = False
        txtEndingOrdNo.Enabled = False
        txtBeginDate.Enabled = False
        txtEndDate.Enabled = False
        cmbLocationName1.Enabled = False
        cmbLocationName3.Enabled = False
 
Case 2
        cmbCustType.Enabled = False
        cmbLocationName.Enabled = False
        txtBeginOrdNo.Enabled = True
        txtEndingOrdNo.Enabled = True
        txtBeginDate.Enabled = False
        txtEndDate.Enabled = False
        cmbLocationName1.Enabled = False
        cmbLocationName3.Enabled = False
 
Case 3
        cmbCustType.Enabled = False
        cmbLocationName.Enabled = False
        txtBeginOrdNo.Enabled = False
        txtEndingOrdNo.Enabled = False
        txtBeginDate.Enabled = True
        txtEndDate.Enabled = True
        cmbLocationName1.Enabled = False
        cmbLocationName3.Enabled = True
 
Case 4
 
        cmbCustType.Enabled = False
        cmbLocationName.Enabled = False
        txtBeginOrdNo.Enabled = False
        txtEndingOrdNo.Enabled = False
        txtBeginDate.Enabled = False
        txtEndDate.Enabled = False
        cmbLocationName1.Enabled = True
        cmbLocationName3.Enabled = False
 
End Select
 
End Sub
 
 
 
Private Sub cmdQuit_Click()
On Error GoTo Err_cmdQuit_Click
 
 
    DoCmd.Quit
 
Exit_cmdQuit_Click:
    Exit Sub
 
Err_cmdQuit_Click:
    MsgBox Err.Description
    Resume Exit_cmdQuit_Click
 
End Sub
 

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.

[jinalpatel]

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.

[NeoPa]

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. Select Case ctl.ControlType
   Case acTextBox
       ctl.Value = Null
       Case acComboBox
       ctl.Value = Null
   End Select

   Can, more clearly be written as :
   

   Select Case ctl.ControlType
   Case acTextBox, acComboBox
       ctl = Null
   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.

[jinalpatel]

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