Results 1 to 11 of 11
  1. #1
    Join Date
    Aug 2012
    Posts
    70

    Unanswered: How do I convert a form into a report?

    I am making a database for some coursework.

    It is also complete (this is only a prototype).

    The final thing I need to do which I can't easily do is create reports for bookings which will act as invoices for a customer.

    The problem is that the only way I have seen how to do this so far is to create 2 queries for each booking and have a final report-subreport report output of these query. Automating this for every customer is proving difficult (I have little DB knowledge). It would also give my DB a massive file size if I saved 2 queries and 2 reports (report and sub-report) for each booking.


    What would actually be perfect would be able to just print the form I have which displays each booking, however as this is schoolwork I would lose marks as I am not creating reports. (See BookingsFullDetails form in the DB attached).

    How can I convert the form into a report easily?
    Attached Files Attached Files

  2. #2
    Join Date
    Aug 2012
    Posts
    70
    I think I have found a way to do it in theory, I just don't know how to do it.

    I want to put a button on the form in BookingsFullDetails saying "view Report".
    When this is clicked I want this to happen:

    1. Open query CurrentBooking (I renamed the Booking1Header to this) in design view
    2. Open query CurrentBookingSUB (I renamed Booking1Content to this) in design view
    3. change the criteria of Booking_ID to that of the form the button was pressed on both forms.
    4. Save both forms
    5. Open CurrentBooking Report (I renamedBooking1 to this).


    As the underlying queries of this report have been changed, the report will be updated too, the user will then be able to choose themselves whether to save or not and even if they don't save, they can re-create the report again by clicking that button again.


    I have a feeling this can be done via a macro but I don't know how to specify to change the criteria of the queries to the Booking_ID of the record the button was beside.

    If somebody can tell me how to do this then I would be extremely grateful!

  3. #3
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    In the Access world you dont' do what you are proposing

    create your invoice report
    once you are happy with it then add a button to the form, which opens the invoice report
    you limit the inovice to print by setting the appropriate value for the report. if you look at the code behind the button it will probably use the openreport macro
    http://msdn.microsoft.com/en-us/libr...ffice.12).aspx

    set a 'where' condition for the macro (4th parameter).
    say your columnname was called InvoiceNo and the invoice you wanted to print in a control called mycontrol then the 'Where' condition is
    Code:
    'InvoiceNo = ' & mycontrol.value
    if the Invocie number was alphanumeric then you'd need to encapsualte the alphanumeric value with speech marks to tell the Access runtime where the string value starts and stops
    Code:
    'InvoiceNo = "' & mycontrol.value & '"'
    I'd rather be riding on the Tiger 800 or the Norton

  4. #4
    Join Date
    Aug 2012
    Posts
    70
    ok, that seems really helpful, thank you.



    So...

    I need to make a button on the form which opens the report and then add a while statement to it using the event builder.

    The value I want to be the same across the form and report is the value of a field named Booking_ID. Therefore how do I put this in the code?

    Is it:

    Code:
    'Booking_ID = ' & mycontrol.value
    What do I change "mycontrol" to in that code?


    I am sorry for the basic questions, I am new to databases as you have probably been able to tell

  5. #5
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    what ever the name of the control holding that value., I don't know your application therefore there is absolutely no way I can know what you have named the control
    I'd rather be riding on the Tiger 800 or the Norton

  6. #6
    Join Date
    Aug 2012
    Posts
    70
    I don't understand, what is a control?
    Can you give me an example of what a typical control is?


    Sorry about the stupid questions.

  7. #7
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    A control is a container on a form or report
    A text box's a control
    As Is a list of combo box. In fact anything you place on a form or report is a control. Lines are a control but they don't take values.
    I'd rather be riding on the Tiger 800 or the Norton

  8. #8
    Join Date
    Aug 2012
    Posts
    70
    This is my form. Will a button be the control?

    https://www.dbforums.com/attachment....1&d=1352572375


    Or, do I need to place a text box on my form saying Booking_ID and then the control would be a textbox (I assume you have to somehow specify which text box) .

    The only reason I don't have Booking_ID already on there is that I have been told never to show users primary/foreign keys as it means nothing to them
    Attached Thumbnails Attached Thumbnails Untitled-1.png  

  9. #9
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    a button is a control, but it doesnt' store data
    the biutton control, I'd suggest is where youpakce your code that calls the report (calls the openreport Macro. in fact a button wizard will create the code you need to open the report (but it won't add the extra you need to print the current invoice. thats why you need to add the 'where' condition referred to in the 3rd post. and thats why I provided a link to the openreport macro help stuff.
    I'd rather be riding on the Tiger 800 or the Norton

  10. #10
    Join Date
    Aug 2012
    Posts
    70
    So what exactly do I need to put in the where condition?
    I think I understand everything else, it is just that.

  11. #11
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    Examine the code behind your command button.
    Click the button.
    Right click to pukkuo the properties
    Select the build code option
    You should see the vba code window
    Look fir ge knackered click event. There should be openreport macro
    Set the value of the th property to be
    The name of the invoice number column and the value

    As said before
    If the name of the column in the table is InvoiceNo
    The name if ghetto control on the form holding the Invoice number is also called InvoiceNo ten And the invoice number is a string then the 4th parameter of openreport is
    'InvoiceNo = "' & InvoiceNo.value & '"'.
    If the invoice number is numeric then
    'InvoiceNo = ' & InvoiceNo.value
    Read the helpfile for openreport
    Read internet pages on openreport
    Try to get it working
    If you have problems by all means comeback. But at least make an effort before throwing your hands up in horror.
    I'd rather be riding on the Tiger 800 or the Norton

Posting Permissions

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