Page 2 of 4 FirstFirst 1234 LastLast
Results 16 to 30 of 54
  1. #16
    bradp1979 is offline Competent Performer
    Windows 7 64bit Access 2007
    Join Date
    Jul 2015
    Location
    San Francisco, CA
    Posts
    234
    I forgot that I had updated the FE. In this FE, the contact form has where I want the information.



    The Reference of 3396 is the Client ID. I did delente Client ID record 3396.
    Attached Files Attached Files

  2. #17
    June7's Avatar
    June7 is offline VIP
    Windows 7 64bit Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,822
    Which is the 'contact' form? - precise name please. As well as the control with the attempted expression.
    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. #18
    bradp1979 is offline Competent Performer
    Windows 7 64bit Access 2007
    Join Date
    Jul 2015
    Location
    San Francisco, CA
    Posts
    234
    It's frmClient. I don't have an attempted expression just yet.

    Attached is a picture of what's at the bottom of frmClient. In the top box are all of the past and upcoming events for this particular client.

    The "Clients referred" box shows the number of clients this particular client has referred.

    I want the lower box to show the details of the 14 referred clients. All of the information that I need is on two different tables: tblClient, and tblEvent. From tblClient, it will find all the clients that were referred by the current client, then on tblEvent, it will count the number of events the referred client has had.
    Attached Thumbnails Attached Thumbnails Client2.png  

  4. #19
    bradp1979 is offline Competent Performer
    Windows 7 64bit Access 2007
    Join Date
    Jul 2015
    Location
    San Francisco, CA
    Posts
    234
    Update: I created qryReferral to experiment with. Expresion 1 says IIf (IsNull([PrimaryFirstName] & [PrimaryLastName]),[EntityID]), and Expression 2 says DCount("*","tblClient","Reference = '" & [ClientID] & "'")

    However, I attach that query to the unbound text box at the bottom of frmClient, and I get the error message: Query must have one destination field.
    Attached Thumbnails Attached Thumbnails qryReferral.png  

  5. #20
    June7's Avatar
    June7 is offline VIP
    Windows 7 64bit Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,822
    Why are there two listboxes for qryClientEvent? The one named lboEvent seems to work.

    The DCount expression is also working, once the form is filtered to a client record.

    I NEVER use dynamic parameterized queries. I would put SQL statement directly in RowSource.

    frmClient is opening with two popup input prompts for tblClientReferral.ClientID - why?
    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.

  6. #21
    bradp1979 is offline Competent Performer
    Windows 7 64bit Access 2007
    Join Date
    Jul 2015
    Location
    San Francisco, CA
    Posts
    234
    I don't know why you're getting the two popup input prompts. I'm not getting them.

    I've changed qryReferral a bit, so that now the columns are: ClientID; EntityName; IIf(IsNull([EntityName]),[PrimaryFirstName] & [PrimaryLastName]); DCount("*","tblClient","Reference = '" & [ClientID] & "'"). - I don't know if the DCount expression is correct for this particular context. I used the same DCount expression as is the control source for the textbox that displays the total number of referred clients by the current client.

    I have 2 issues: In the IIf statement, I am telling it to take information from two different tables. What I need to tell it is that if EntityName on tblClient is null, then take PrimaryFirstName and PrimaryLastName from tblContact. I don't know the correct syntax to point it at two different tables.

    Secondly, the "Query must have one destination field."

    What listboxes are in qryClientEvent? lboEvent does work, it's visible when the client type is C (not V or G).

  7. #22
    bradp1979 is offline Competent Performer
    Windows 7 64bit Access 2007
    Join Date
    Jul 2015
    Location
    San Francisco, CA
    Posts
    234
    Ok, so I got the query to do something. As an experiment, I just have the following columns in the query: ClientID, EntityName, and IIf(IsNull([tblClient.EntityName]),[tblContact.PrimaryFirstName] & [tblContact.PrimaryLastName]). Of course, what that gives me in the textbox is every single client we have as opposed to just those referred by the current client.

  8. #23
    June7's Avatar
    June7 is offline VIP
    Windows 7 64bit Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,822
    An IIf expression in query can't refer to fields of tables that are not included in the query. Would have to build a query of those two tables and then use DLookup to pull data.

    I didn't say listboxes were IN query, I said there are two listboxes FOR qryClientEvent. Both have qryClientEvent as RowSource. What is List177 supposed to be for?
    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. #24
    bradp1979 is offline Competent Performer
    Windows 7 64bit Access 2007
    Join Date
    Jul 2015
    Location
    San Francisco, CA
    Posts
    234
    How do I see the listboxes for a query?

    I just realized why this is even coming up. Delete qryClientEvent as the control source for the second text box. I just put that there to see what would happen, and didn't delete it.

    I've created qryReferral and that is now the control form for the second text box.

    I understand now that I need to use DLookup to pull data, but I don't know how to do that in this particular case. I want it to pull only the data that pertains to the clients that were referred, and I don't know what syntax to use, or even where to put it.

    I'm uploading a new FE that has the query that I wrote.
    Attached Files Attached Files

  10. #25
    June7's Avatar
    June7 is offline VIP
    Windows 7 64bit Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,822
    Textbox cannot have a query as ControlSource. A combobox and listbox can have query as RowSource.

    I am confused. There is a textbox on the form with a functioning DCount() expression.
    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.

  11. #26
    bradp1979 is offline Competent Performer
    Windows 7 64bit Access 2007
    Join Date
    Jul 2015
    Location
    San Francisco, CA
    Posts
    234
    Yes. The functioning DCount() expression shows me the number of clients the current client has referred. Now I need a way to see who those clients are, and how many events each individual client has booked with us.

    I figured the listbox (again, I was using incorrect verbiage) would be the best way to do that.

    Right now there is a list box (lboEvent) that shows me each event that the client showing has booked. I want another listbox that will show me the names of the clients that the current client has referred and how many events each has booked.

  12. #27
    June7's Avatar
    June7 is offline VIP
    Windows 7 64bit Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,822
    Need to change Reference field to number type. The RowSource for List177 joins tblEvent ClientID to tblClient Reference:

    SELECT tblClient.Reference, tblEvent.EventID, tblEvent.EventNatureID, tblClient.ClientID FROM tblEvent INNER JOIN tblClient ON tblEvent.ClientID = tblClient.Reference WHERE tblClient.ClientID=[Text177];

    And need Me.List183.Requery in Current event
    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.

  13. #28
    bradp1979 is offline Competent Performer
    Windows 7 64bit Access 2007
    Join Date
    Jul 2015
    Location
    San Francisco, CA
    Posts
    234
    Quote Originally Posted by June7 View Post
    Need to change Reference field to number type. The RowSource for List177 joins tblEvent ClientID to tblClient Reference:

    SELECT tblClient.Reference, tblEvent.EventID, tblEvent.EventNatureID, tblClient.ClientID FROM tblEvent INNER JOIN tblClient ON tblEvent.ClientID = tblClient.Reference WHERE tblClient.ClientID=[Text177];

    And need Me.List183.Requery in Current event
    Ok, the SELECT tblClient.Reference.... looks like SQL code, is that where I write it?

    Where would I add Me.List183.Requery?

    And finally, how do I join tblEvent ClientID to tblClient Reference with List 177? (Where is list 177? I see Text 177, but not list)

  14. #29
    June7's Avatar
    June7 is offline VIP
    Windows 7 64bit Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,822
    Sorry, for typo - should be List183 RowSource. Recommend change to more informative names.

    That is SQL statement for listbox RowSource property.

    The requery code goes in form Current event, same as for the other listbox.
    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.

  15. #30
    bradp1979 is offline Competent Performer
    Windows 7 64bit Access 2007
    Join Date
    Jul 2015
    Location
    San Francisco, CA
    Posts
    234
    I'm afraid I'm just completely lost at this point. I'm trying to figure all this out, but I just have no idea where I need to be putting these things.

    I changed the name of the listbox from List183 to lboReferral. The current RowSource is qryReferral, should it instead be that SQL statement?

    How does the SQL statement account for the IIf IsNull statement in my original code? The current SQL for the listbox says this:

    SELECT tblEvent.ClientID, tblClient.EntityName, IIf(IsNull([tblClient.EntityName]),[tblContact.PrimaryFirstName] & " " & [tblContact.PrimaryLastName]) AS Name, DCount("*","tblClient","Reference = '" & [tblClient.ClientID] & "'") AS EventsFROM tblClient INNER JOIN (tblContact INNER JOIN tblEvent ON tblContact.ContactID = tblEvent.ContractingPartyContactID) ON (tblClient.ClientID = tblContact.EntityID) AND (tblClient.ClientID = tblEvent.ClientID);

    The IIf statement is very important since the tblClient has EntityName, but tblContact has PrimaryFirstName and PrimaryLastName. If one is null, it needs to display the other.

Page 2 of 4 FirstFirst 1234 LastLast
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