Results 1 to 6 of 6
  1. #1
    Join Date
    Aug 2004
    Posts
    54

    Unanswered: Adding form fields to reports query

    Hi there,

    I'm trying to add a form field to a Query so I can use it on a report.

    I tried using =Forms!MachineDetail!txtServiceTotal to the Control Source of an unbound textbox on the report, but since it doesn't reference anything in particular, it comes up with !Name.

    I also tried putting Forms!MachineDetail!txtServiceTotal in the SQL statement of the report recordsource, but I get an Enter Parameter box when I try to preview it.

    Thanks!

  2. #2
    Join Date
    Feb 2004
    Location
    CT,USA
    Posts
    250
    The natural flow of data is first in tables, then picked off in queries which can be used for forms and reports. If you really wanna do what you ask, you must have the form opened which has the desired value. Then I'd want to know how is the form field getting filled? Perhaps another query?

    For example I have used the following successfully:
    Reports!rptFileList2.txtListingTitle = Forms!frmMultiReport.txtChartTitle
    It makes an unbound text field on the report receive the Form's unbound text field "chartTitle" which was set by particular update actions on the source form. Note: the form, and report were open simultaneously.
    Last edited by poliarci; 08-30-04 at 18:30.

  3. #3
    Join Date
    Dec 2003
    Location
    Dallas, TX
    Posts
    1,004

    Question

    Quote Originally Posted by Calfuzz
    Hi there,

    I'm trying to add a form field to a Query so I can use it on a report.

    I tried using =Forms!MachineDetail!txtServiceTotal to the Control Source of an unbound textbox on the report, but since it doesn't reference anything in particular, it comes up with !Name.

    I also tried putting Forms!MachineDetail!txtServiceTotal in the SQL statement of the report recordsource, but I get an Enter Parameter box when I try to preview it.

    Thanks!
    Hi Calfuzz,

    Is txtServiceTotal a field in the MachineDetail table? Is it a calculated field? If it is simply a field in your table then in your query make sure you have the table included and DoubleClick that field to include it in your query. From there into your form/report to manipulate as you choose. If in fact it is a calculated field as it kinda sounds like, don't do that at table level, instead at either query/form/report level.
    So first, what is this txtServiceTotal located???
    Also, take note of what poliarci is saying as well.

    Bud

  4. #4
    Join Date
    Aug 2004
    Posts
    54

    yar

    The way I have it set up right now, there's a page on the main form that deals with service costs for the machines.

    The service costs are in separate tables, for example:

    LaserATLM(LaserID, LaserPartCost, ModelID)
    Ballbar(BallbarID, BallbarCost, ModelID)

    It's set up that way rather than in one table in case the service department changes their install costs depending on which model they're installing a laser on, etc.

    The service costs tab on the main form is presently all unbound, because I haven't figured out the best way to use it. It pulls the install and warranty costs from the machines by using DLookups in VB, or if it's one particular manufacturer, it calculates a percentage of the machine price (which is calculated from machine base price, options, etc...)

    The rest of the form is a series of checkboxes (Who pays? Customer, Manufacturer, or Us) and if our company is chosen, the amount of the set rate shows up in a textbox on the right via DLookups.

    Those are added up at the bottom in TxtServiceTotal.

    So right now, none of the information for a particular machine is related to the service page of the form.

    The other pages are pretty much alright, but I'm not sure how to deal with this one.

    You said not to use calculated fields on a form, but rather on a query... I imagine there's more of a problem on how my tables are set up. Can you let me know if this gives you more insight into my problem?

    Thanks much!

  5. #5
    Join Date
    Dec 2003
    Location
    Dallas, TX
    Posts
    1,004
    Quote Originally Posted by Calfuzz
    If in fact it is a calculated field as it kinda sounds like, don't do that at table level, instead at either query/form/report level.
    Hi Calfuzz, just checked the board while here at work. There was a mis-read. See above that I stated the opposite of your last reply. Query/Forms/Reports are in fact where you should do the calculated field. Might get to borrow some time while here at work to look at your db, but will have to see.
    talk later

    Bud

  6. #6
    Join Date
    Aug 2004
    Posts
    54
    Hi,

    Thanks Bud, I did catch that.

    I know about the very strict rule never to do calculated fields in tables, but where *to* use them is deciding to be relatively tricky

    Thanks for your help!

Posting Permissions

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