Page 1 of 2 12 LastLast
Results 1 to 15 of 23
  1. #1
    Join Date
    Aug 2006
    Posts
    559

    Unanswered: No IDs, MUST have words

    Ok, so on my forms, I've made it so that an actual text/word value I have in tables be viewable on the form AFTER passing a numeric value on the form.

    What I need to do is have this same TEXT value on the reports which as of right now is just passing the numeric value.

    I thought of trying to do a similar thing like in the forms for "Record Source" on a new text box, but it didn't work, just gave me a #NAME? value.

    Any ideas?

    **EDIT**

    I wonder if I use the same method and just put a listbox on the report? Would that work? **I don't want to start messing up the reports as I have them set the way I want them and don't want un-necessary items in the code and whatnot**
    Last edited by Grafixx01; 09-02-08 at 19:18.

  2. #2
    Join Date
    Dec 2004
    Location
    Madison, WI
    Posts
    3,926
    Not 100% sure what you mean here but to pass a value from a form to a report...

    1. Make sure the form is open (either minimized or visible = false) If you make it invisibile, make sure to make it visible in the OnClose event of the report. If you make the form minimized, in the OnClose event of the report I usually like to "restore" the form.

    2. For the Sourceobject of the field on the report, you could then (as long as the form is open), set it to: = Forms!MyFormName!MyFieldName.

    ex:
    Forms!MyFormName.visible = false
    Docmd.OpenReport "MyReportName", acviewpreview
    and then in the OnClose event of the report....
    Forms!MyFormName.visible = true

    or
    Forms!MyFormName.Minimize
    Docmd.OpenReport "MyReportName", acviewpreview
    and then in the OnClose event of the report...
    Forms!MyFormName.setfocus
    docmd.restore

    OR

    1. You can always pass the value to the field on the report (leaving the sourceobject blank of that field on the report.)

    Dim SomeVariable as variant
    SomeVariable = me!MyFieldName (or SomeVariable = Forms!MyFormName!MyFieldName)
    DoCmd.Close acForm, "MyFormName"
    Docmd.OpenReport "MyReportName", acviewpreview
    Reports!MyReportName!MyUnboundFieldName = SomeVariable

    (Note - in the above last example, you must first store the value on the form to SomeVariable and then you can close the form and pass that value to the report field.)

    I may have totally read you wrong though on what you're trying to do. If so, could you maybe please re-explain?

    You may also be able to use the Dlookup command in the sourceobject of the field on the report (although I tend to shy away from ever using the dlookup command.)
    or
    Not sure if the recordsource of your report is unbound or how you've got this setup but maybe you could somehow get the field you want displayed on the report into the recordsource query for the report.

    There are also some other options but again, I'm not quite sure I fully understand the problem from your explanation.
    Last edited by pkstormy; 09-02-08 at 20:32.
    Expert Database Programming
    MSAccess since 1.0, SQL Server since 6.5, Visual Basic (5.0, 6.0)

  3. #3
    Join Date
    Aug 2006
    Posts
    559
    Paul,

    I think you got it with the first suggestion but I'll try it and post back if it works or not. I probably didn't explain it well enough. In the field that I have like "Squad" on my form, you can actually see the squad name, or like the listboxes that have "Country" in them, you can see the country name.

    What I need, is when this report prints, rather than saying like "37" for country is says "Spain" if that was the 37th country in the list. And the like for all the other fields.

    I think that your method of hiding the form / making it not-visible, is the easiest way and I'll try that and let everyone know if it works or not.

  4. #4
    Join Date
    Dec 2004
    Location
    Madison, WI
    Posts
    3,926
    You may also want to link the relational table which shows "Squad" (similar to linking the table for the listboxes to show "Country") in the recordsource query for the report. Then, like the listbox situation, just drag down the text field which would show "Squad" versus the ID. This might be the ideal method to do this versus actually passing the value to the report field.

    In regards to making the form "invisible", this is usually what I like to do (versus minimizing the form). But after the report closes (ie. OnClose event), I then set the form to visible again. I do it this way since most of my forms are popup/modals (with the caption bar removed.) If you try to minimize a form which is set to modal = yes, you can sort of "lock" the mdb where the report doesn't show, the form is minimized (but doesn't really show minimized but "dissappears"), and it doesn't really let you do anything unless you can somehow re-show the form again (so in essence, minimizing a modal = Yes form is never a good idea.)

    But I might go with adding the relational table into the recordsource query of the report and then dragging down the appropriate text type field to show on the report. This would be preferred versus passing the text value to the report but either way works.

    Let me know what you decide to do and how it works out for you.
    Expert Database Programming
    MSAccess since 1.0, SQL Server since 6.5, Visual Basic (5.0, 6.0)

  5. #5
    Join Date
    Aug 2006
    Posts
    559
    Quote Originally Posted by pkstormy
    You may also want to link the relational table which shows "Squad" (similar to linking the table for the listboxes to show "Country") in the recordsource query for the report. Then, like the listbox situation, just drag down the text field which would show "Squad" versus the ID. This might be the ideal method to do this versus actually passing the value to the report field.

    In regards to making the form "invisible", this is usually what I like to do (versus minimizing the form). But after the report closes (ie. OnClose event), I then set the form to visible again. I do it this way since most of my forms are popup/modals (with the caption bar removed.) If you try to minimize a form which is set to modal = yes, you can sort of "lock" the mdb where the report doesn't show, the form is minimized (but doesn't really show minimized but "dissappears"), and it doesn't really let you do anything unless you can somehow re-show the form again (so in essence, minimizing a modal = Yes form is never a good idea.)

    But I might go with adding the relational table into the recordsource query of the report and then dragging down the appropriate text type field to show on the report. This would be preferred versus passing the text value to the report but either way works.

    Let me know what you decide to do and how it works out for you.
    Alrighty, now I'm confuxed! I thought that I had a small incling on what I was going to do but now I have no clue.

    **EDIT**

    I wonder, would a DLookup work in what I'm trying to do? Would that be easier? I'm trying to figure out the easiest and quickest way to accomplish this task. I'll attach the DB so everyone will be able to figure out / or try to figure out what I'm talking about.
    Last edited by Grafixx01; 09-03-08 at 12:21.

  6. #6
    Join Date
    Aug 2006
    Posts
    559
    If I put the code below, on the Command Button to print:

    Code:
    Private Sub Command107_Click()
    On Error GoTo Err_cmdPrintCurrent_Click
    'this is the print command button to print the current task with all data on the form
    
        Dim stDocName As String
        Me.Refresh 'print current task on rptTask report with all data
        stDocName = "rptIndividual" 'print report
        DoCmd.OpenReport stDocName, acNormal
        'DoCmd.Minimize acForm, "frmIndividual", acSaveYes (THIS IS THE EDITED CODE TO MINIMIZE THE FORM WHILE PRINTING)
        DoCmd.Close acForm, "frmIndividual", acSaveYes (I'D TAKE THIS OUT)
        stDocName = "frmMainMenu" 'open frmMainMenu upon correct login being entered
        DoCmd.OpenForm stDocName, , , stLinkCriteria
        
    Exit_cmdPrintCurrent_Click:
        Exit Sub
        
        
        
    Err_cmdPrintCurrent_Click:
        MsgBox Err.Description
        Resume Exit_cmdPrintCurrent_Click
    End Sub
    And then do the SourceObject code as you stated.

    Rather than reopening the form, can I just do a
    Code:
    DoCmd.Close acForm, "frmIndividual", acSaveYes (I'D TAKE THIS OUT)
        stDocName = "frmMainMenu" 'open frmMainMenu upon correct login being entered
        DoCmd.OpenForm stDocName, , , stLinkCriteria
    in the "OnClose" event of the report and have it return the user to the Main Menu?

  7. #7
    Join Date
    Aug 2006
    Posts
    559
    Well that didn't work. It gave me an error of "Wrong number of arguments or Invalid property"

    **EDIT**

    I am also looking at your second recommendation of "passing the value" but haven't got a clue on how to do that because I think that the

    Dim SomeVariable as Variant

    Would have to be for EACH field that I have on the form with a TEXT value hiding an ID value? Then, since the report essentially prints the contents of the listboxes, I haven't got a clue on how that'd be done.
    Attached Files Attached Files
    Last edited by Grafixx01; 09-03-08 at 12:34.

  8. #8
    Join Date
    Nov 2007
    Location
    Adelaide, South Australia
    Posts
    4,049
    Edit: Referring to post 5

    Link doesn't work.

    If you had an idea to try before post 4, then do it and see how you go!

    I don't think I fully understand the problem yet... but if it's what I think, then the best way to do what you want is to have the related tables associated with the IDs in the report's underlying query:

    The report gets it's numeric criteria from the form.
    The report's query relates the numeric criteria's table in

    In other words:

    You have a form with a numeric value 37 for a country -- perhaps cboCountry.
    Your main query for the report has a table with a 37 in a foreign key, perhaps CountryID.
    You add the COUNTRY table to the main query, linking the foreign key field (with the 37 in it) to the CountryID (primary key) of the COUNTRY table.
    Include the CountryName field in the main query.
    Should be good to go.

    Owner and Manager of
    CypherBYTE, Microsoft Access Development Specialists.
    Microsoft Access MCP.
    And all around nice guy!


    "Heck it's something understood by accountants ... so it can't be 'that' difficult..." -- Healdem
    "...teach a man to code and he'll be frustrated for life! " -- georgev

  9. #9
    Join Date
    Aug 2006
    Posts
    559
    Quote Originally Posted by StarTrekker
    Edit: Referring to post 5

    Link doesn't work.

    If you had an idea to try before post 4, then do it and see how you go!

    I don't think I fully understand the problem yet... but if it's what I think, then the best way to do what you want is to have the related tables associated with the IDs in the report's underlying query:

    The report gets it's numeric criteria from the form.
    The report's query relates the numeric criteria's table in

    In other words:

    You have a form with a numeric value 37 for a country -- perhaps cboCountry.
    Your main query for the report has a table with a 37 in a foreign key, perhaps CountryID.
    You add the COUNTRY table to the main query, linking the foreign key field (with the 37 in it) to the CountryID (primary key) of the COUNTRY table.
    Include the CountryName field in the main query.
    Should be good to go.

    Link restored...

  10. #10
    Join Date
    Aug 2006
    Posts
    559
    Alright, got it to minimize the form and print the report. However, still haven't been able to get the TEXT instead of words but working on that now.

    The thing also is that when the report finishes printing, what I want it to do (since all values will be passed) is to close the frmIndividual and return to the main menu.

    Would it happen if I just 'uncomment-out' the "DoCmd.Close acForm "frmIndividual" acSaveYes portion of the code?

    **Edit**

    Just answered my own question. Sorry guys and gals. Now I've gotta figure out the Text, no ID fields and I'll be straight for a little while. So this is what perplexes me now...

    If the information that I need in the report is contained within a query to populate a listbox, how would I get the information into the the report? IE: Gender (Male / Female) rather than 1 or 2 ; Actual State Name (California, New York, etc) rather than 4, 19, etc??
    Last edited by Grafixx01; 09-03-08 at 14:27.

  11. #11
    Join Date
    Feb 2004
    Location
    Chicago, IL
    Posts
    1,312
    Do you have a table that has something like:

    GenderID-----Gender
    1-------------Male
    2-------------Female


    StateID-------StateName
    1-------------California
    2-------------Illinois
    .
    .
    .
    20------------Idaho


    Assume you do, and assume you have a table called tblGender and tblState. Edit your query (the one feeding the report) and add the two tables. Then create joins between the tables by clicking on the Gender field in your query and dragging it to the GenderID field. I line should appear. Then add the Gender field to you query results. Do the same with the StateID field (drag it to the State field in the query), then add the StateName Field to the query.

  12. #12
    Join Date
    Aug 2006
    Posts
    559
    Quote Originally Posted by DCKunkle
    Do you have a table that has something like:

    GenderID-----Gender
    1-------------Male
    2-------------Female


    StateID-------StateName
    1-------------California
    2-------------Illinois
    .
    .
    .
    20------------Idaho


    Assume you do, and assume you have a table called tblGender and tblState. Edit your query (the one feeding the report) and add the two tables. Then create joins between the tables by clicking on the Gender field in your query and dragging it to the GenderID field. I line should appear. Then add the Gender field to you query results. Do the same with the StateID field (drag it to the State field in the query), then add the StateName Field to the query.
    I do have this, as you described above, but wouldn't I have to add almost every table that is in the db for that to happen?

  13. #13
    Join Date
    Feb 2004
    Location
    Chicago, IL
    Posts
    1,312
    How many lookup tables do you have?

  14. #14
    Join Date
    Aug 2006
    Posts
    559
    Hmm...Good question... Have no idea. I think just like 5.

    **EDIT**

    I wonder if a Dlookup would work easier / better?

    Like:

    =DLookup ("Gender", "tblGender", "GenderID=" & Dlookup ("Gender", "tblIndividual", "GenderID=" & IndID))
    Last edited by Grafixx01; 09-03-08 at 15:37.

  15. #15
    Join Date
    Dec 2004
    Location
    Madison, WI
    Posts
    3,926
    See the attached and the rptIndividual form (see the recordsource of this form) - this shows how you would show the Ethinicity - the concept is the same for any other relational type fields where you need to show the text value from the relational table.

    Also notice how I configured the Print Individual code behind this button on the frmIndividual (and also look at the OnClose event of the rptIndividual report.)

    Hope this helps.
    Attached Files Attached Files
    Expert Database Programming
    MSAccess since 1.0, SQL Server since 6.5, Visual Basic (5.0, 6.0)

Posting Permissions

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