Results 1 to 6 of 6
  1. #1
    Join Date
    Apr 2002
    Location
    New Zealand
    Posts
    19

    Unanswered: Print a report based on on screen form - VBA Help.

    Hi
    I can printscreen a form, but what I have not been able to do yet is print the report version of the form I have open on the screen.
    I Have a purchase order form which when I click the print button i want it to call up and print the report for the current screen version. The screen version mirrors the report version except that some text boxes are a fixed size while those on the report version are allowed to resize and inlarge to word rap. The report is based on a query.
    Is it possible to do an acSelection type of VBA for the report.
    What I currently get is the entire Purchase order range when all I want is the current ordernumber.
    Thanking you in advance
    Regards
    bill

  2. #2
    Join Date
    Jun 2002
    Location
    Saudi Arabia / Philippines
    Posts
    126
    Bill, hi.

    "Word rap?" Is this the new music that's all the rage?

    Seriously if all the data that appears on your purchase order form is contained in the underlying bound table/query then simply use the DoCmd statement with the OpenReport action and dynamically insert the current PO number in the optional Where clause of the action. I asume PO number is unique.

    If however there is unbound data on your form that has to appear on the printed version then you are into the game of creating a temporary table with fields for all the data you need, populating it with the data from your form, and then displaying or printing the form from the temporary table.

  3. #3
    Join Date
    Apr 2002
    Location
    New Zealand
    Posts
    19
    Thanks, Rod

    PO = Purchase order and it is unique.
    Could you run by the temperary feild aspect please.

    Can the DoCmd statement be reused by many people if it is used across a network, will a dialogue box appear for the user to select the current open form. Please teach me to suck eggs on this one.
    Cheers
    Much Appreciated
    Regards
    bill

  4. #4
    Join Date
    Jun 2002
    Location
    Saudi Arabia / Philippines
    Posts
    126
    Bill, hi again.

    By coincidence I have just written a very basic and simple example of this for another poster. If you send me your email address I can send the mdb file (270Kb) to you - unfortunately dbForums does not allow me to attach files otherwise I could send it now.

    In the meantime (and I see from your other posts that you are familar with getting into VBA coding) let me try to explain what I mean. Access Reports (and Forms for that matter) can only be bound to Tables or Queries. I'm sure that somewhere someone has written some clever stuff whereby Forms and Reports can be bound to arrays or user defined data types. (If they are reading this will they please identify themselves to one of the cabin crew.)

    So the problem is how to get unbound data from a Form onto a Report. You can open a Report from a Form but as far as I am aware you cannot pass parameter values to that report. So as a Report has to be based on a Table, we create a Table definition with a field for each and every datum that we want on the report. We then design the Report bound to the Table definition (Access Wizards are indispensible here).

    Now all that we need to do from the Form is to poke our values into the Table and invoke the Report. Well sort of because first we should clear any existing values from the table. I maintain that the best way to do this is to delete the Table definition and then rebuild it. This also has the advantages of: eliminating any definition changes a meddling user may have made in the meantime; placing the Table structure in the VBA code where it can be easily modified when the users change their minds.

    The example I have uses the DAO (Data Access Objects) library v3.6. The thoroughbred professionals will probably scoff and say I should be using Active X libraries instead. But then I understand DAO and have worked with it, and why learn Active X when it might change again due to all this .Net rubbish that Microsoft is pushing.

    So in summary I would solve the requirement by:

    1 Delete Table from TableDefs if it exists
    2 Create new Tabledef
    3 Create new Fields
    4 Append Fields to TableDef
    5 Append TableDef to TableDefs collection
    6 Open Table as RecordSet
    7 AddNew row using the values on the Form
    8 Close RecordSet
    9 Invoke Report

    It's not quite as bad as it appears from the above.

  5. #5
    Join Date
    Jun 2002
    Location
    Saudi Arabia / Philippines
    Posts
    126
    Bill

    Sorry, I did not comment on your DoCmd question. DoCmd invokes the available Access actions so I guess that it is available to each and every user.

    Rod

  6. #6
    Join Date
    Apr 2002
    Location
    New Zealand
    Posts
    19
    Thanks so much Rod,
    You are a gentleman and scollar.
    I am indebited to your valued assistance.
    I would appreciate the Mdb File
    My e-mail ddress is wapfu@xtra.co.nz
    Regards
    Bill

Posting Permissions

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