Results 1 to 9 of 9
  1. #1
    New Lounger
    Join Date
    Feb 2003
    Location
    Griffith (Near Chicago), Indiana, USA
    Posts
    24
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Union query/macro/report help (Access 2000)

    I'm using a union query (COMBOBOX) within a form to pull up all my salesreps ( including "ALL"). This works fine. I have a macro to pull up a report and only the value I choose via the box. I've set the macro up to show that tableinfo=box info. When I click the macro I'm getting no results. I know i'm missing a step somewhere... Any suggestions?

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

    Re: Union query/macro/report help (Access 2000)

    If you refer to the combo box on the form, by default you are referring to its first column. Depending on the way you have set up the combo box, this column may or not be visible - perhaps you show the name of a sales rep, while the unique ID is hidden in the first column. You must keep this in mind when specifying the where-condition for opening the report. The where-condition should look like

    [FieldName] = [Forms]![frmFormName]![cboComboBoxName]

    with the appropriate names substituted:
    FieldName is the name of the field in the record source of the report that corresponds to the first column of the combo box.
    frmFormName is the name of the form.
    cboComboBox is the name of the combo box.

    If your combo box also contains an entry "ALL", you should check for that in your macro.

    PS: I never use macros; I find VBA code a lot easier to read, to debug and to maintain.

  3. #3
    New Lounger
    Join Date
    Feb 2003
    Location
    Griffith (Near Chicago), Indiana, USA
    Posts
    24
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Union query/macro/report help (Access 2000)

    I tried that... using the Name and the ID number, both as primary key and not primary key.
    I tried to use visual basic, I'm really unfamiliar with it though, (more familiar with macros) and I put code in but instead of bringing up a report it printed it with no info. This is what that code looked like.
    stDocName = "RPTTEST"
    stLinkCriteria = "[SrepID]=" & Me![SalesrepBOX]
    DoCmd.OPENREPORT stDocName, ,acpreview , stLinkCriteria

    I'd appreciate any help that you could give....
    Thanks
    Tracy

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

    Re: Union query/macro/report help (Access 2000)

    Seems strange.

    Open the form module in the Visual Basic Editor.
    Insert an empty line immediately above the DoCmd.OpenReport line and type MsgBox stLinkCriteria
    Switch back to Access and open the form.
    Try to bring up a report. You should see a message box with the value of stLinkCriteria. What does it say?

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

    Re: Union query/macro/report help (Access 2000)

    Is your sales rep ID a number or a string? If it's a string then you'll need to change one line to this:

    <pre>stLinkCriteria = "[SrepID]='" & Me![SalesrepBOX] & "'"</pre>

    Charlotte

  6. #6
    Platinum Lounger
    Join Date
    Dec 2001
    Location
    Melbourne, Australia
    Posts
    4,594
    Thanks
    0
    Thanked 27 Times in 27 Posts

    Re: Union query/macro/report help (Access 2000)

    Charlotte, will that work, you have a space after the first single quote and before the second single quote. <img src=/S/shrug.gif border=0 alt=shrug width=39 height=15>
    I know you have done that for readability, but we normally put Chr(34) instead of the single quote.

    So the statement should read:

    stLinkCriteria = "[SrepID]=" & Chr(34) & Me![SalesrepBOX] & Chr(34)

    Pat <img src=/S/smile.gif border=0 alt=smile width=15 height=15>

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

    Re: Union query/macro/report help (Access 2000)

    Actually, I don't have any spaces in there. I used the <!t>[Pre]<!/t> tags, which puts the code in courier font and gives it that appearance because the quotes take up the same amount of space even though the single quote character is narrower.
    Charlotte

  8. #8
    Platinum Lounger
    Join Date
    Dec 2001
    Location
    Melbourne, Australia
    Posts
    4,594
    Thanks
    0
    Thanked 27 Times in 27 Posts

    Re: Union query/macro/report help (Access 2000)

    The illusion is the problem here.
    Pat <img src=/S/smile.gif border=0 alt=smile width=15 height=15>

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

    Re: Union query/macro/report help (Access 2000)

    If you drag your mouse over those quotes, you'll see that there aren't any spaces there. Aren't you old enough to remember typewriters, Pat? <img src=/S/granny.gif border=0 alt=granny width=20 height=20> It wasn't until Windows and TrueType fonts that it became hard to read single quotes surrounded by double quotes. <img src=/S/grin.gif border=0 alt=grin width=15 height=15>
    Charlotte

Posting Permissions

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