Page 1 of 2 12 LastLast
Results 1 to 15 of 16
  1. #1
    Join Date
    Dec 2004
    Posts
    46

    Unanswered: A very unusual print single form question.

    Main Form
    Case_number
    Name
    Address, etc.

    Subform linked via Case_number to the main form
    Contains Name
    Address
    Check box saying "include in report"

    I know how to preview the current form using the various codes available

    Problem is that I struggle to include the "include in report" checked data into this report. Either I have to print it all or print a single record.

    Code on my button is as below:

    Private Sub Command67_Click()

    Dim strReportName As String
    Dim strCriteria As String

    strReportName = "Transaction summary"
    strCriteria = "[Case_number]=" & Me![Case_number]
    DoCmd.OpenReport strReportName, acViewPreview, , strCriteria

    End Sub

    It uses "Case_number" to make it print off, but the "include_in_report" (which is a written "yes" "no" field isnt in this main form but in a subform called dadcheck donor subform.

    Any help is much appreciated

    davinder.sangha@Sassco.co.uk

  2. #2
    Join Date
    Sep 2004
    Location
    Tampa, FL
    Posts
    520
    So if I am interpreting what you are saying correctly you want the user to choose which record to include in the report?

    The way that comes to my mind is sloppy and most likely wrong but, it would work.

    If a real developer saw my current project they would probally gouge out thier own eyes.

    Anyway, maybe a table (temp?) that when the user clicks on "Include in Report" the case number is added to this table. When it is time to print the button queries the records that have the case number in the new table, prints the form then clears the new tables data.

    seems to me would work in a single user environment. Usure about a multiple user one.
    Take my answer with a grain of salt, no others replied so there you go.

    That's my 2 Cents
    Darasen

  3. #3
    Join Date
    Dec 2004
    Posts
    46

    Coded button to print the single report

    Hi Thanks for the response.

    I think I know what the problem is. Basically it's based around the small bit of code that one uses to print out a single report from a form. I created a button which sends the user to the preview of the report on the correct page.

    Private Sub cmdPrintRecord_Click()

    Dim strReportName As String
    Dim strCriteria As String

    strReportName = "rptPrintRecord"
    strCriteria = "[Case_number]=" & Me![Case_number]
    DoCmd.OpenReport strReportName, acViewPreview, , strCriteria

    End Sub

    Problem is that the form by itself simply previews the current record. But the form also contains the data in a subform which is the key info I need.

    Example:

    Main DB
    Name
    Address
    Payment details

    Sub DB
    Name
    Address
    Sample details
    Include in report

    What i want is to be able to customise a report so that when someone clicks Include in report for one of the records in the sub DB, then it's included in the details.

    I've actually done it, but the problem is that i won't appear when I click the coded button, because the filter changes to Case_number. I can't filter the Include_in_report because it says that It can't find the field (probably because it's in the subform).

    My theory is that if there is a way of modifying the below code for the button:

    strCriteria = "[Case_number]=" & Me![Case_number]

    so that I could also add [include_in_report] = 'yes' in, then it would print off the single report and also include the sub DB details for that record which only have Include in report clicked.

    I've got to be honest. I am a very below par programmer. I tend to learn from sites like these, but I only know the very basics.

    Help is really appreciated.

    I'm not sure how to do the TEMP table thing that you've mentioned.

    Cheers

  4. #4
    Join Date
    Nov 2004
    Location
    Bangor, ME USA
    Posts
    44
    Not that I know what I'm talking aboout but have you tried something to the effect of

    strCriteria = "[Case_number]=" & Me![Case_number] & " AND " & [forms]![Subformname].[Case_Number] ="& Me![Case_number] & " AND " & [forms]![Subformname].[Include_In_Report] = "YES"

    My logic is that you have not told the code the value is on the subform not the main form, maybe that is why it can't find it. the above (in my logic) would force a matching of main form and subform case numbers as well as only those with the same case number that you have checked off to include in your report.

    HTH
    Kevin

  5. #5
    Join Date
    Dec 2004
    Posts
    46
    Private Sub Command76_Click()

    Dim strReportName As String
    Dim strCriteria As String

    strReportName = "Transaction Summary"
    strCriteria = "[Case_number]=" & Me![Case_number] & " AND " & [Forms]![dadcheck_donors_details_subform].[Case_number] = "& Me![Case_number] & " And " & [forms]![dadcheck_donors_details_subform].[include_in_report] = "yes"
    DoCmd.OpenReport strReportName, acViewPreview, , strCriteria


    End Sub

    The line gets highlighted and then I get a syntax error

    Is there any other way to do it?

    Basically what we have is a paternity testing database

    The clients details are entered into the main db while it has linked subforms with the persons getting the tests done.

    Now for example, there are three people being tested, John, Amy and Michael. Amy and Michael are living elsewhere and their tests are seperate while John lives elsewhere as well

    So I need to generate two letters, one for John saying "enclosed is a sampling kit, etc."

    And another for Amy and Michael saying "enclosed are two sampling kits"

    It sounds simple and I've managed to sort out the report form etc, but I want to be able to click a button from the main DB form and invoke all the fields in the current subform which have the "include_in_report" selected as yes (this is a Lookup Yes No field).

    The idea at the top of this message seems to be in the right direction, but it keeps giving syntax errors.

  6. #6
    Join Date
    Dec 2004
    Posts
    46
    ALso ive noticed that when invoked from the button, the Filter on the report is over ridden.

    The standard one I put on the report is the following

    ([Include_in_report]='Yes') AND ([sub_case_number]=2)

    Obviously it only brings up the case numbered 2, but it's in the general idea of how to solve this.

    Does anyone know the code which will transform

    ([Include_in_report]='Yes') AND ([sub_case_number]=2) ---with 2 being replaced by the currently viewed case.

    Into the code to add into a button?



    Note I have renamed the case_number in the subform to sub_case_number after trying all the previous solutions.

  7. #7
    Join Date
    Dec 2003
    Location
    Dallas, TX
    Posts
    1,004
    Hi davindersangha and "Welcome to the Forum"

    For the sake of saving you grief, is there any way you could make a copy of your database (removing pertinent data and replace with dummy data), ZIP it down and then POST it here for someone to look at and analyze for you? That would save a lot of time and might get your result the first time.

    just a thought
    have a nice one,
    BUD

  8. #8
    Join Date
    Dec 2003
    Location
    Dallas, TX
    Posts
    1,004
    Hi again,

    Not totally sure if this is your situation but take a look at my three posted images and see if they represent what you are trying to do.
    Image1: I have a form for Vendors and SubForm with PO's I placed to a Vendor. There are several PO's to each particular Vendor,

    Image2: HOWEVER, when I wish to print only certain PO's or Lines for that Vendor, I click the CheckBox that just says PRINT.

    Image3: THEN the Report shows up with ONLY the ones that I selected to show with the CheckBox.

    Like I said, I might not understand your situation clearly and am trying to. So let me know it this is along the lines of what you're trying to achieve.

    have a nice one,
    BUD
    Attached Thumbnails Attached Thumbnails bobnz1of3.bmp   bobnz2of3.bmp   bobnz3of3.bmp  

  9. #9
    Join Date
    Dec 2004
    Posts
    46
    Didnt realise you could do that!!!

    I've stripped the database down and now it includes only the basic button on the main form which previews a report showing all the items (even though they are unchecked).

    SO heres what I wanna do:

    Open up dadcheck client details.
    In the subform I want to three "include in report" to say "yes" in two fields and one to say "no"

    So for example:
    Arjan = yes
    Jaspal = no
    Paolo = yes
    Davinder yes

    Then click the "print transaction summary" button which will take you to the preview of the report. As you can see, the report shows all four names even though one has been unchecked.

    I'm sure it's a simple problem to solve.
    For example if I click the button to go to the report and then view the report in design mode, the filter has changed to:

    ([Case_number]=1)

    Obviously, it's picking up some code from the code attached to the button to make this change. The code on the button is:


    Private Sub Command67_Click()

    Dim strReportName As String
    Dim strCriteria As String

    strReportName = "Transaction Summary"
    strCriteria = "[Case_number]=" & Me![Case_number]
    DoCmd.OpenReport strReportName, acViewPreview, , strCriteria

    End Sub

    Any help and advice would be appreciated. I'm new to this sort of stuff, but by god, im enjoying trying to resolve this problem.
    Attached Files Attached Files

  10. #10
    Join Date
    Dec 2004
    Posts
    46
    I think that's it!

    From what I see, you have a main form and then sub forms with a True False question.

    I then see on the report that the true items only appear.

    Very similar to mine.

    I have client details (the payer)
    Then sub people who are taking the test.
    Then a Yes No question

    On mine the Yes ones and no ones appear when i envoke the form using a button.

  11. #11
    Join Date
    Dec 2004
    Posts
    46

    Problem solved

    DONE IT

    The basic code remains the same for the Report.

    I created a seperate Query same as the one used already, but added the ='Yes' into the criteria section.

    So although the button overides any criteria put to the report, the query used by the form can only print the ones which say Yes.

    Thanks for all the help. I wouldn't have got the idea without all the suggestions posted.

  12. #12
    Join Date
    Dec 2003
    Location
    Dallas, TX
    Posts
    1,004
    Quote Originally Posted by davindersangha
    DONE IT

    The basic code remains the same for the Report.

    I created a seperate Query same as the one used already, but added the ='Yes' into the criteria section.

    So although the button overides any criteria put to the report, the query used by the form can only print the ones which say Yes.

    Thanks for all the help. I wouldn't have got the idea without all the suggestions posted.
    Just glad to be of some help. I knew it kinda sounded like mine in a way so thought I'd toss it at ya. Glad you have it all resolved now. Hope you have a nice day too.
    BUD

  13. #13
    Join Date
    Sep 2004
    Location
    Tampa, FL
    Posts
    520
    OK my way tanked, but it seems to have generated some interest in the post .


    If Bud or some one could post the resolved sample it might be neat to see the solution.
    Darasen

  14. #14
    Join Date
    Dec 2004
    Posts
    46
    The basic command to create a button to preview the current form in a report is straightforward. I got the code from various sites. The only changes I made were adding my report name "Transaction summary" and a field in the current form "Case_number" It can be any field, but from what I understand is that if its not a number field, then you have to add some quotes on the end of th strCriteria command.

    Private Sub Command67_Click()

    Dim strReportName As String
    Dim strCriteria As String

    strReportName = "Transaction Summary"
    strCriteria = "[Case_number]=" & Me![Case_number]
    DoCmd.OpenReport strReportName, acViewPreview, , strCriteria

    End Sub

    What this does is invoke the report. The report singles out Case_number in a filter and makes the current record the filter. Problem is that this overides any filter you put into a report.

    All did to resolve this was to go into the query for that report and put the filter directly into the query. In my case it was Include_in_report=Yes. And it worked. I had to make a copy of the query as the Include_in_report command would then go across the board in my query if I used it in another form, etc.

  15. #15
    Join Date
    Nov 2002
    Posts
    154

    Wink A solution?

    I have a feeling that this should work: can you just include in the data source for the report the include_in_report field (ie the query underlying it) and put under the criteria for that field 'YES'? That way only records with 'YES' filled out would ever show up in your report, and you wouldn't even need to have that field show up on your report. I hope that this is helpful.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •