Results 1 to 11 of 11
  1. #1
    New Lounger
    Join Date
    Jun 2001
    Posts
    15
    Thanks
    1
    Thanked 0 Times in 0 Posts

    Get rpt with data from form (2000 SR-1)

    I am building a PC Inventory database. I have a form that begins by first requesting the PC Alias in a popup window and then the form is displayed (with 3 subforms).

    I want to take the information on the form and subforms and print it on an existing report. So, I set up a button and the onClick event procedure looks like this:

    Private Sub btnRptSoftwareByPCAlias_Click()
    On Error GoTo Err_btnRptSoftwareByPCAlias_Click

    Dim stDocName As String

    stDocName = "Software by PC Alias"
    DoCmd.OpenReport stDocName, acPreview, , "[MainDatabase].[PC Alias] Like [FormEntryMaintenanceAllInOne].[PC Alias]"


    Exit_btnRptSoftwareByPCAlias_Click:
    Exit Sub

    Err_btnRptSoftwareByPCAlias_Click:
    MsgBox Err.Description
    Resume Exit_btnRptSoftwareByPCAlias_Click

    End Sub


    When clicking the report button, I get a popup window that reads:

    Enter Parameter Value

    FormEntryMaintenanceAllInOne.PC Alias

    ...and I must re-enter the data to continue. Beyond that, the report is great.

    What am I doing wrong? How do I avoid the second popup window?

    Thanks in advance,
    Robert

  2. #2
    Gold Lounger
    Join Date
    Jun 2001
    Location
    Crystal Beach, FL, Florida, USA
    Posts
    3,436
    Thanks
    1
    Thanked 34 Times in 34 Posts

    Re: Get rpt with data from form (2000 SR-1)

    In part, you wrote:
    DoCmd.OpenReport stDocName, acPreview, , "[MainDatabase].[PC Alias] Like [FormEntryMaintenanceAllInOne].[PC Alias]"


    You problem is that you included the reference to the form control within the quotes, so it is not being evaluated. You need to do it this way:

    DoCmd.OpenReport stDocName, acPreview, , "[MainDatabase].[PC Alias] Like " & [FormEntryMaintenanceAllInOne].[PC Alias]

    I do have one other comment/question. Why are you using "Like"? Does your [PC Alias] field include wild cards? If you are specifically using the value within [PC Alias] than you should be using = rather than like; it is faster.
    Mark Liquorman
    See my website for Tips & Downloads and for my Liquorman Utilities.

  3. #3
    New Lounger
    Join Date
    Jun 2001
    Posts
    15
    Thanks
    1
    Thanked 0 Times in 0 Posts

    Re: Get rpt with data from form (2000 SR-1)

    Mark,

    I tried it (using =, thanks) and received an error box:

    Microsoft Access can't find the field '|' referred to in your expression.

    I don't see that character anywhere in the procedure. What now?

    I'm slowly learning this by myself that's why I ask such basic questions and often have the wrong syntax.

    Thanks,
    Robert

  4. #4
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Get rpt with data from form (2000 SR-1)

    When referring to a control on a form, it's better to use "!" instead of "."

    The following instructions should both work:

    DoCmd.OpenReport stDocName, acPreview, , & _
    "[MainDatabase].[PC Alias] = Forms![FormEntryMaintenanceAllInOne]![PC Alias]"

    or

    DoCmd.OpenReport stDocName, acPreview, , & _
    "[MainDatabase].[PC Alias] = " & [PC Alias]

    I don't know whether

    [MainDatabase].

    is necessary; you can probably omit it, but it depends on the record source of the report.

    In the first instruction, the reference to [PC Alias] is within the quotes, so it will be evaluated by the report when it is opened. Therefore, you must state explicitly where [PC Alias] comes from.

    In the second one, the reference is outside the quotes, so it will be evaluated by the form, before issuing the OpenReport command. Since [PC Alias] is on the form itself (I presume), just the control name is sufficient.

  5. #5
    New Lounger
    Join Date
    Jun 2001
    Posts
    15
    Thanks
    1
    Thanked 0 Times in 0 Posts

    Re: Get rpt with data from form (2000 SR-1)

    Thanks, Hans.

    The first instruction that you gave me worked but only after I put it on one line of code, removing the "&_" characters. Weird.

    Thanks, again.

    Robert

  6. #6
    Gold Lounger
    Join Date
    Jun 2001
    Location
    Crystal Beach, FL, Florida, USA
    Posts
    3,436
    Thanks
    1
    Thanked 34 Times in 34 Posts

    Re: Get rpt with data from form (2000 SR-1)

    I don't know where the "|" came from either, unless it was in your data? If your field is text, then you need to put quotes around the value you are checking against. For example, you WHERE clause in an OpenReport method would be something like this:

    "FieldName = " & chr(34) & txtControlName & chr(34)

    I find using chr(34) is easier to read than consecutive single and double quotes like this: "FieldName = '" & txtControlName & "'".
    Mark Liquorman
    See my website for Tips & Downloads and for my Liquorman Utilities.

  7. #7
    Gold Lounger
    Join Date
    Jun 2001
    Location
    Crystal Beach, FL, Florida, USA
    Posts
    3,436
    Thanks
    1
    Thanked 34 Times in 34 Posts

    Re: Get rpt with data from form (2000 SR-1)

    You wrote:
    >>When referring to a control on a form, it's better to use "!" instead of "."<<

    Why? If you use ".", then the compiler can detect errors, while errors with "!" are on detected at runtime.
    Mark Liquorman
    See my website for Tips & Downloads and for my Liquorman Utilities.

  8. #8
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Get rpt with data from form (2000 SR-1)

    This may be ignorance on my side. In my Access 97 (Dutch version) help, Microsoft states that "." should be used for items defined in Access itself, such as properties, and "!" for user-defined items, such as controls:

    frmMyForm.AllowAdditions = True

    frmMyForm!txtMyField = "John Doe"

  9. #9
    Plutonium Lounger
    Join Date
    Dec 2000
    Location
    Sacramento, California, USA
    Posts
    16,775
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Get rpt with data from form (2000 SR-1)

    The dot operator also denotes members of a collection, and the controls are members of the form's controls collection, so even in A97, the dot operator works in referencing controls. A lot of people use it because intellisense drops down the control name. On the other hand, typing control names in lower case with the bang operator will clue you when you complete the line because a misspelled on non-existent control won't change its case.

    I've seen arguments boths ways.
    Charlotte

  10. #10
    Gold Lounger
    Join Date
    Jun 2001
    Location
    Crystal Beach, FL, Florida, USA
    Posts
    3,436
    Thanks
    1
    Thanked 34 Times in 34 Posts

    Re: Get rpt with data from form (2000 SR-1)

    Access handles controls differently in Access97 (and beyond) than it did in Access2 (and maybe Help didn't catch up). Starting with A97, controls were also considered properties of the form, so you could use the "." instead of the "!". As I mentioned, this makes it easier to detect errors as they are now caught in the compiler.
    Mark Liquorman
    See my website for Tips & Downloads and for my Liquorman Utilities.

  11. #11
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Get rpt with data from form (2000 SR-1)

    Mark and Charlotte,

    Thank you. I'll keep it in mind - it's always nice to learn something!

    Regards,
    Hans

Posting Permissions

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