Results 1 to 9 of 9
  1. #1
    davedinger is offline Advanced Beginner
    Windows 10 Access 2016
    Join Date
    Nov 2017
    Location
    South Dakota
    Posts
    63

    open form based on both columns of a combo box

    I have a form named Main with a combo box named Combo14 on it.



    Combo14 is a look up of 2 columns from a table Named Narratives.
    The 2 columns of Comb14 are: 1) Name 2) Company


    I need to open a form Named List where column 1and 2 of combo14 on the Main Form are equal to the fields Name and Company from the Table Narratives.

    in other words
    I have several narratives that as users create the narrative they enter the first name of the user and also the Company that the user is from. this is what makes up the 2 columns of combo14.


    so now i would like to see all of the narratives for a certain employee. There may be several David's all from different company's, so by using combo14 I hope to be able to select David from ABC Company or David from XYZ Company.

  2. #2
    kd2017 is offline Well, I tried at least.
    Windows 10 Access 2016
    Join Date
    Jul 2017
    Posts
    1,142
    First is suggest renaming your controls and fields to something more descriptive and to something NOT in this list: http://allenbrowne.com/AppIssueBadWord.html

  3. #3
    kd2017 is offline Well, I tried at least.
    Windows 10 Access 2016
    Join Date
    Jul 2017
    Posts
    1,142
    Something like:

    DoCmd.openform "List", , , "Name='" & me.combo14.column(0) & "' AND Company='" & me.combo14.column(1) & "'"

    But you really shouldn't be doing it this way, you should be using foreign keys. It sounds like you have table design issues. Care to post it?

  4. #4
    davedinger is offline Advanced Beginner
    Windows 10 Access 2016
    Join Date
    Nov 2017
    Location
    South Dakota
    Posts
    63
    This is the basic part, start on the main form
    click a user name I think only Dave is listed
    click new call button
    on this form you see the drop down fields of FirstName and Company. They cannot be their own tables as the list has to be built as calls come in. We never know who is going to call in or company names ect.
    when done click close

    back on the main page click on Call List Standard this will show you a list of all of the narratives done for the user name Dave. for all callers and companies. if you click on the date and time it will open the narrative of that call. click close to go back to main

    now for what i am trying to do:
    click on the drop down find tech calls select a name and company combo then click on find all calls. you should be seeing a list that has all of the calls from the tech first name, but there are multiple companies. IE Dave from abc company and dave from def company.
    i would like the find all calls button to open the form where the tech name and the company combo are found.
    Attached Files Attached Files

  5. #5
    davegri's Avatar
    davegri is online now Excess Access
    Windows 10 Access 2016
    Join Date
    May 2012
    Location
    Denver
    Posts
    3,389
    Move the code like this to the after update event of the combo box, delete the command button.
    Code:
    ="Tech ='" & [Combo14] & "' AND Company='" & Combo14.column(1) & "'"
    Click image for larger version. 

Name:	search.png 
Views:	29 
Size:	10.7 KB 
ID:	43903

  6. #6
    kd2017 is offline Well, I tried at least.
    Windows 10 Access 2016
    Join Date
    Jul 2017
    Posts
    1,142
    Quote Originally Posted by davedinger View Post
    They cannot be their own tables as the list has to be built as calls come in. We never know who is going to call in or company names ect.
    I disagree. The combobox's Not In List event would help here

    http://allenbrowne.com/ser-27-01.html
    http://www.databasedev.co.uk/not_in_list.html

    -OR- if you want to get real fancy you could utilize the combobox's List Items Edit Form property
    https://www.youtube.com/watch?v=7xy29Q4-DI4

    i would like the find all calls button to open the form where the tech name and the company combo are found.
    Try this:
    Code:
    DoCmd.OpenForm "FrmCallListStandard", , , "Tech='" & Me.Combo14.Column(0) & "' AND Company='" & Me.Combo14.Column(1) & "'"

  7. #7
    davedinger is offline Advanced Beginner
    Windows 10 Access 2016
    Join Date
    Nov 2017
    Location
    South Dakota
    Posts
    63
    The Code works great as long as it is applied to the dropdown box after update procedure. would there be a way to apply this to the control button instead? When I apply it to the on click setting of the control button it appears that the form only looks at the name and not the company. Perhaps that is because the drop down box only displays the one column after the line is selected? is there a way to have the drop down box display both columns after a record is selected?

  8. #8
    davegri's Avatar
    davegri is online now Excess Access
    Windows 10 Access 2016
    Join Date
    May 2012
    Location
    Denver
    Posts
    3,389
    If you want to select the criteria with the combo and open the report with the button;

    Change the combo after_update macro to:

    Click image for larger version. 

Name:	combo.png 
Views:	19 
Size:	8.2 KB 
ID:	43912

    and the button:

    Click image for larger version. 

Name:	button.png 
Views:	19 
Size:	11.3 KB 
ID:	43911

    For reasons that only the macro knows, trying to use the combo's specific columns as criteria in the button_click event only finds the first occurrence of Tech.
    So I used tempvars to hold the combo columns and the tempvars worked OK as criteria in the button_click.
    Last edited by davegri; 01-15-2021 at 03:56 PM. Reason: replaced first image, clarif

  9. #9
    davedinger is offline Advanced Beginner
    Windows 10 Access 2016
    Join Date
    Nov 2017
    Location
    South Dakota
    Posts
    63
    That temp variable is pretty Cool, I've never used it before.
    Thanks for the education!!

    I see where I need to add a remove temp variable also probably on the close button of the form or something but it is exactly what I was looking for.

    Thanks to davegri and kd2017 for all of your help on my issue, along with all of the others that keep this help forum going by viewing and commenting on all of the posts!!

Please reply to this thread with any new information or opinions.

Similar Threads

  1. open form based on combo box value
    By moneypennie21 in forum Forms
    Replies: 4
    Last Post: 03-15-2019, 09:45 AM
  2. Replies: 6
    Last Post: 02-23-2016, 01:45 PM
  3. Replies: 5
    Last Post: 08-06-2014, 02:39 PM
  4. Replies: 22
    Last Post: 05-10-2014, 04:43 PM
  5. Report based on combo box values as columns
    By sadozai in forum Reports
    Replies: 2
    Last Post: 12-09-2011, 04:40 PM

Tags for this Thread

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