Results 1 to 13 of 13

Populating a field based on 2 other fields

  1. #1
    BEI77 is offline Novice
    Windows 7 64bit Access 2013
    Join Date
    May 2014
    Location
    KY
    Posts
    24

    Populating a field based on 2 other fields

    Click image for larger version. 

Name:	scrnshot.jpg 
Views:	78 
Size:	183.6 KB 
ID:	26801

    I am a novice at Access and I have built a maintenance database. I have a Repair Record table and a Equipment id table.
    I have placed a sub form for the Repair Record Table on the main form. When a service to a piece of equipment is completed the mechanic
    will enter that in the Repair Record sub form. When he enters the word "service" in the Parts No. field and the Mileage in the Hours/Mileage field
    I would like for that number to show in the Truck Last Serviced field. So the word service in one field would key the mileage in another field to be shown in


    a field on a different table. Any help with this is appreciated.

  2. #2
    ranman256's Avatar
    ranman256 is offline VIP
    Windows Vista Access 2010 32bit
    Join Date
    Apr 2014
    Location
    Kentucky
    Posts
    7,704
    this could also be a subform, the query for this will pull DMax(serviceDate) and 'service' to show last service date.
    set it to readonly so user cannot change it.

  3. #3
    BEI77 is offline Novice
    Windows 7 64bit Access 2013
    Join Date
    May 2014
    Location
    KY
    Posts
    24
    Thanks, from one KY guy to another, but what would the expression be?

  4. #4
    aytee111 is offline Competent At Times
    Windows 7 32bit Access 2013 32bit
    Join Date
    Nov 2011
    Location
    Nomad
    Posts
    3,936
    So, the green bit will be a subform with its record source getting info from the Repair Record Table. A couple of queries: first one to get the max date, the second one to use the first query and then to get the service type. In the AfterUpdate event of the lower subform have an Event Procedure which says: Forms!main form name!top subform name.Requery - this means waiting until the entire row has been entered before the top info is updated, but easier this way.

  5. #5
    BEI77 is offline Novice
    Windows 7 64bit Access 2013
    Join Date
    May 2014
    Location
    KY
    Posts
    24
    Great sorry it took so long with Christmas and all. I got that part working THANK YOU!
    Now when I create my report based on the query for max date, I get all the trucks and services. How can I only get the latest ones done per truck to show on the report?
    I have tried the no duplicates in properties but that don't work.

  6. #6
    aytee111 is offline Competent At Times
    Windows 7 32bit Access 2013 32bit
    Join Date
    Nov 2011
    Location
    Nomad
    Posts
    3,936
    Get the query working before trying to create a report, with the data exactly as you want to see it.

  7. #7
    BEI77 is offline Novice
    Windows 7 64bit Access 2013
    Join Date
    May 2014
    Location
    KY
    Posts
    24
    The query is working perfect. I generate the report to show what trucks are due a service based on mileage. When I create the report from the query it shows me each time the truck has been serviced.

  8. #8
    aytee111 is offline Competent At Times
    Windows 7 32bit Access 2013 32bit
    Join Date
    Nov 2011
    Location
    Nomad
    Posts
    3,936
    If the query it working perfectly, then why isn't the report also perfect? Doesn't make sense.

  9. #9
    BEI77 is offline Novice
    Windows 7 64bit Access 2013
    Join Date
    May 2014
    Location
    KY
    Posts
    24
    Here is a screen shot of the query and report. On the report I should have 1 line that shows the info for the last service. I had that before I used this query to make my report. I'm stumped!
    Click image for larger version. 

Name:	query screen shot.jpg 
Views:	72 
Size:	199.5 KB 
ID:	26871Click image for larger version. 

Name:	REPORT SHOT.jpg 
Views:	72 
Size:	224.5 KB 
ID:	26872

  10. #10
    aytee111 is offline Competent At Times
    Windows 7 32bit Access 2013 32bit
    Join Date
    Nov 2011
    Location
    Nomad
    Posts
    3,936
    It looks as though the report and the query are producing the same results. Is that the case, if you run the query does it look like the report above?

  11. #11
    BEI77 is offline Novice
    Windows 7 64bit Access 2013
    Join Date
    May 2014
    Location
    KY
    Posts
    24
    It gives the same information.

    Click image for larger version. 

Name:	query run shown.jpg 
Views:	70 
Size:	186.5 KB 
ID:	26878

  12. #12
    aytee111 is offline Competent At Times
    Windows 7 32bit Access 2013 32bit
    Join Date
    Nov 2011
    Location
    Nomad
    Posts
    3,936
    Loop back to post #6 - get the query working the way you want to see the data.

  13. #13
    BEI77 is offline Novice
    Windows 7 64bit Access 2013
    Join Date
    May 2014
    Location
    KY
    Posts
    24
    Ok thanks. Have a Happy New Year!!

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

Similar Threads

  1. Replies: 5
    Last Post: 06-27-2016, 07:31 AM
  2. populating field in form based on input field
    By BrandonFinn in forum Forms
    Replies: 7
    Last Post: 10-30-2014, 10:45 AM
  3. Populating one field based on another
    By thedanch in forum Access
    Replies: 1
    Last Post: 06-11-2012, 11:13 AM
  4. Replies: 7
    Last Post: 11-26-2010, 07:54 PM
  5. Populating attachment field based on text box
    By justinwright in forum Forms
    Replies: 16
    Last Post: 09-22-2010, 08:38 AM

Posting Permissions

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