Page 4 of 4 FirstFirst 1234
Results 46 to 54 of 54
  1. #46
    bradp1979 is offline Competent Performer
    Windows 7 64bit Access 2007
    Join Date
    Jul 2015
    Location
    San Francisco, CA
    Posts
    234

    You already described it for the listbox, but I'm not sure how to make the report only give the specified information. I've got the report created, but it's giving me everything as opposed to just those which apply to the current client. I don't see where I can enter SQL code for the report, nor do I see a filter option. I can do VBA, but would the same code work?

  2. #47
    June7's Avatar
    June7 is offline VIP
    Windows 7 64bit Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,825
    I showed method of opening report and applying filter. Except now reverse the criteria. And since Reference is a text type field, need apostrophe delimiters for the parameter.

    DoCmd.OpenReport "report name", acViewPreview, , "Reference='" & Me.ClientID & "'"
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

  3. #48
    bradp1979 is offline Competent Performer
    Windows 7 64bit Access 2007
    Join Date
    Jul 2015
    Location
    San Francisco, CA
    Posts
    234
    Awesome. That worked beautifully.

    Now I've just got to come up with how to fix the one issue I'm having with tblClient having business Entity names, and tblContact having individual First and last names. Right now, I'm getting the report showing exactly what I want, but it's only taking from tblClient, so all the individuals have blank EntityNames.

    I had written out the IIf statement, saying if tblClient.EntityName is null, then use tblContact.PrimaryFirstName & tblContactPrimaryLastName, but I don't know how to make the report do that.

  4. #49
    June7's Avatar
    June7 is offline VIP
    Windows 7 64bit Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,825
    Expression in query to construct a field. Then bind textbox to the field, same as for a natural field.

    Of course, this assumes tblContact is in the report RecordSource.
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

  5. #50
    bradp1979 is offline Competent Performer
    Windows 7 64bit Access 2007
    Join Date
    Jul 2015
    Location
    San Francisco, CA
    Posts
    234
    Unfortunately, since all the reference numbers are on tblClient, that is the record source. Is there any way to attach a second table to the report?

  6. #51
    bradp1979 is offline Competent Performer
    Windows 7 64bit Access 2007
    Join Date
    Jul 2015
    Location
    San Francisco, CA
    Posts
    234
    Alright, I did it. I recreated the report and took fields from both tables, now it works.

    Thank you for all your help.

  7. #52
    bradp1979 is offline Competent Performer
    Windows 7 64bit Access 2007
    Join Date
    Jul 2015
    Location
    San Francisco, CA
    Posts
    234
    Alright, last question on this thread: on frmClient I included txtCount below lboEvent, and it counts the total number of events the client has booked. I want that to be on the report that I created. How can I do that?

  8. #53
    June7's Avatar
    June7 is offline VIP
    Windows 7 64bit Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,825
    Was that using a domain aggregate function - DCount?
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

  9. #54
    bradp1979 is offline Competent Performer
    Windows 7 64bit Access 2007
    Join Date
    Jul 2015
    Location
    San Francisco, CA
    Posts
    234
    No, I didn't need to use DCount. The control source for the text box is =[lboEvent].
    [ListCount].

Page 4 of 4 FirstFirst 1234
Please reply to this thread with any new information or opinions.

Similar Threads

  1. Replies: 14
    Last Post: 07-13-2015, 12:47 PM
  2. Replies: 8
    Last Post: 10-03-2013, 08:11 AM
  3. Transform Column values in Table
    By Mnelson in forum Access
    Replies: 1
    Last Post: 06-11-2012, 03:06 PM
  4. 3 values in 1 Column - Get Count of Each Value
    By Ghoztrider in forum Queries
    Replies: 1
    Last Post: 01-09-2012, 03:26 PM
  5. Replies: 2
    Last Post: 11-18-2009, 06:49 PM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Other Forums: Microsoft Office Forums