Page 1 of 2 12 LastLast
Results 1 to 15 of 16
  1. #1
    salonikije is offline Novice
    Windows 10 Access 2016
    Join Date
    May 2017
    Location
    Montenegro
    Posts
    21

    Question View report for specific employee by clicking a employee name in combobox

    Dear,
    In attachment is a part of my application for task management etc... (I explained some details in my previous post: https://www.accessforums.net/showthread.php?t=66013).


    I have a table called Contacts (this is, logically, a table for contact information).
    Also, I have a query called "Contacts Extended", which collects information from the table Contacts, and joins First and Last name as one name, called Zaposleni (eng. Employee).




    I've created a Report called "Obuke - po zaposlenom", which presents all trainings by employee, stored in table Obuke (eng. Training). I've grouped a report by field ImeZaposlenog, which represents a name of the employee.


    Hence, I have a form called "Statistika" and combobox on it. For this combobox I've set Row Source:


    SELECT [Contacts Extended].ID, [Contacts Extended].[Zaposleni] FROM [Contacts Extended] ORDER BY [Contacts Extended].[Zaposleni];


    and this gives me a list of all employees.


    Now, I have a problem when click on the one of employees. I want to open a mentioned report "Obuke - po zaposlenom", but only for that employee.


    I tried to build a macro in After Update option, but this gives me a full report, for all employees.




    Thnx in advance!
    Aleksandar
    Attached Files Attached Files

  2. #2
    aytee111 is offline Competent At Times
    Windows 10 Access 2013 64bit
    Join Date
    Nov 2011
    Location
    Nomad
    Posts
    3,936
    In the macro where you open the report, add a WHERE condition: ID=Forms!formname!comboboxname

  3. #3
    salonikije is offline Novice
    Windows 10 Access 2016
    Join Date
    May 2017
    Location
    Montenegro
    Posts
    21
    Is this condition the same for Reports?

  4. #4
    aytee111 is offline Competent At Times
    Windows 10 Access 2013 64bit
    Join Date
    Nov 2011
    Location
    Nomad
    Posts
    3,936
    Not sure what you are asking. Did you add the condition to the macro?

  5. #5
    salonikije is offline Novice
    Windows 10 Access 2016
    Join Date
    May 2017
    Location
    Montenegro
    Posts
    21
    I need this setting for opening a Report through combobox in a form.
    When I input a condition in format ID=Forms!formname!comboboxname, it doesn't give me a combobox name as a choice.
    When I input a condition:
    [ID]=[Reports]![Obuke - po zaposlenom]![ComboObuke], it opens a requested Report, but without any data....

    Click image for larger version. 

Name:	macro.png 
Views:	23 
Size:	9.0 KB 
ID:	28606

  6. #6
    aytee111 is offline Competent At Times
    Windows 10 Access 2013 64bit
    Join Date
    Nov 2011
    Location
    Nomad
    Posts
    3,936
    The value exists on the form, not on the report, as I stated in post #2 (read our answers!). You are saying: open a report and show me only records where the ID (a field in the report's record source) is equal to the value in THIS field on THIS form.

  7. #7
    salonikije is offline Novice
    Windows 10 Access 2016
    Join Date
    May 2017
    Location
    Montenegro
    Posts
    21
    I apologize for my inexperience ... but even this does not give the desired results...

  8. #8
    aytee111 is offline Competent At Times
    Windows 10 Access 2013 64bit
    Join Date
    Nov 2011
    Location
    Nomad
    Posts
    3,936
    Post another screen shot as in post #5.

    Post the record source of the report.

  9. #9
    salonikije is offline Novice
    Windows 10 Access 2016
    Join Date
    May 2017
    Location
    Montenegro
    Posts
    21
    Here are screenshots:

    Click image for larger version. 

Name:	1.png 
Views:	22 
Size:	34.9 KB 
ID:	28608 Click image for larger version. 

Name:	2.png 
Views:	22 
Size:	8.8 KB 
ID:	28609 Click image for larger version. 

Name:	3.png 
Views:	22 
Size:	7.6 KB 
ID:	28610

  10. #10
    aytee111 is offline Competent At Times
    Windows 10 Access 2013 64bit
    Join Date
    Nov 2011
    Location
    Nomad
    Posts
    3,936
    Your where condition is comparing two fields - one field on the report must match one field on the form. In your first post your SQL for the combobox showed the ID as the first field. Now it seems that you have removed that field and the combobox only contains the Zaposleni.

    The matching field on the report - please post the record source for the report - what is it called?



  11. #11
    ssanfu is offline Master of Nothing
    Windows XP Access 2010 32bit
    Join Date
    Sep 2010
    Location
    Anchorage, Alaska, USA
    Posts
    9,664
    PMFJI,

    I looked at your dB and the biggest problem your naming convention... well, actually, it is your lack of naming convention.
    You have

    Click image for larger version. 

Name:	Test1.jpg 
Views:	21 
Size:	45.0 KB 
ID:	28611
    Some of the problems are:
    Spaces in the names (always a bad idea)
    Special characters ( the dash in e-mail and starting a field name with the percent sign)
    The worst is (IMHO) is using "ID" as the primary key field name in all 3 tables.

    (There are also Lookup FIELDS in the tables...... another bad idea. See http://access.mvps.org/access/lookupfields.htm )


    What is happening in the report is that you are getting the ID number (Contacts.ID) from the combo box on the form "Statistika" then, in the macro (I really hate macros ), trying to limit the employee setting by the WHERE clause to "[ID] = Forms!Statistika.Combo514".
    BUT, the ID field in the where clause is "Obuke.ID", NOT to a foreign key field for contacts.
    Field Contact.ID has 2 records with PK values of 3 & 4.
    Field Obuke.ID also has 2 records but with PK values of 5 & 6.

    How many records would you expect to be returned? And, if there were records where the PK fields matched, you would see the wrong records!!!



    I worked a few hours to see if I could straighten out the problems...
    I didn't fix the field name problems or the lookup fields in the tables, but I did get the queries, forms and reports working.
    This is what the structure now looks like:
    Click image for larger version. 

Name:	Test2.jpg 
Views:	21 
Size:	41.7 KB 
ID:	28612
    Obviously, I renamed the PK field names and the FK fields.


    Because of the language barrier (on my part ), it took a long time, but it is possible to fix the current dB.
    Once you get the names corrected, I think you will be able to understand how everything works together. (if not, post back )


    Remember, NO spaces in field names, table names, query names, form names or report names.

  12. #12
    salonikije is offline Novice
    Windows 10 Access 2016
    Join Date
    May 2017
    Location
    Montenegro
    Posts
    21
    Dear, very thank you for this instructions... As I said earlier, I'm a beginner in Access and every correct instruction is a very big thing for me....

    Problem in naming filds is not mine I used a template "Task Management" from Access templates, and just renamed fileds from English to Montenegrin My problem is that I did not know that this can be a problem....

    However, I'll try to follow your instructions But I'm not sure I'll succeed

  13. #13
    ssanfu is offline Master of Nothing
    Windows XP Access 2010 32bit
    Join Date
    Sep 2010
    Location
    Anchorage, Alaska, USA
    Posts
    9,664
    You are right.
    Microsoft has published some terrible templates.

    Try fixing your dB... If you can't I'll post what I have..... Good luck..

  14. #14
    salonikije is offline Novice
    Windows 10 Access 2016
    Join Date
    May 2017
    Location
    Montenegro
    Posts
    21
    You can send that what you have, anyway that can help me to understand things better.... In my full application I have two more modules which are similar like this for training... If I understand right relations and naming for this module, I think I could apply it for two other...
    Last edited by salonikije; 05-11-2017 at 09:56 AM.

  15. #15
    ssanfu is offline Master of Nothing
    Windows XP Access 2010 32bit
    Join Date
    Sep 2010
    Location
    Anchorage, Alaska, USA
    Posts
    9,664
    OK, you asked for it!!!

    I think (hope) I found all of the changes and fixed everything I broke.......
    Attached Files Attached Files

Page 1 of 2 12 LastLast
Please reply to this thread with any new information or opinions.

Similar Threads

  1. Replies: 2
    Last Post: 05-09-2017, 04:12 PM
  2. Employee Roster weekly view
    By BamaBlast in forum Access
    Replies: 2
    Last Post: 12-11-2013, 12:52 PM
  3. Replies: 1
    Last Post: 03-13-2013, 12:00 PM
  4. Replies: 8
    Last Post: 06-06-2012, 12:28 PM
  5. Qry that asks for specific employee name
    By oxicottin in forum Queries
    Replies: 0
    Last Post: 02-05-2007, 08:50 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