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

    Am I thinking too hard?

    Im trying to make a form that will show data from one table and will allow me to create a record in another table.



    this is the situation:
    This form is to record temperature readings on equipment

    Table one Equipment Fields: Auto ID, high temp limit, low temp limit, equipment name
    table two Fields: Auto Id, Equipment ID, Current temp, Time stamp.

    I have several pieces of equipment and i would like a list of the equipment, and next to the equipment list a way to enter the current readings
    i hope to have it look something like below
    Attached Thumbnails Attached Thumbnails option 2.PNG  
    Last edited by davedinger; 06-09-2018 at 07:53 PM. Reason: wrong Images

  2. #2
    davedinger is offline Advanced Beginner
    Windows 10 Access 2016
    Join Date
    Nov 2017
    Location
    South Dakota
    Posts
    63
    Oh cheese and crackers I screwed up the images Ill re post in a second

  3. #3
    davedinger is offline Advanced Beginner
    Windows 10 Access 2016
    Join Date
    Nov 2017
    Location
    South Dakota
    Posts
    63
    preferred concept (one line for equipment data and readings)

    Click image for larger version. 

Name:	preferred option.PNG 
Views:	41 
Size:	36.9 KB 
ID:	34370

    Or optional concept
    One line for equipment data and another for data readings
    Click image for larger version. 

Name:	option 2.PNG 
Views:	42 
Size:	25.1 KB 
ID:	34371

  4. #4
    isladogs's Avatar
    isladogs is offline MVP / VIP
    Windows 10 Access 2010 32bit
    Join Date
    Jan 2014
    Location
    Somerset, UK
    Posts
    5,954
    Yes - in answer to the thread title
    You could create an effect similar to the first screenshot using a subdatasheet ... BUT I really wouldn't recommend it

    The preferred concept is better. You could
    a) create a form using a query based on both tables with an OUTER join.
    This means all records from the equipment table would be shown even if there is no corresponding record in the readings table.
    Make sure the query is not read only
    b) use 2 subforms side by side - one for each table. However, you would need to add code so both subform scroll at the same time.
    This could get complicated and messy to code. Also not recommended
    Colin, Access MVP, Website, email
    The more I learn, the more I know I don't know. When I don't know, I keep quiet!
    If I don't know that I don't know, I don't know whether to answer

  5. #5
    davedinger is offline Advanced Beginner
    Windows 10 Access 2016
    Join Date
    Nov 2017
    Location
    South Dakota
    Posts
    63
    Thanks i was thinking too hard, but here's the new problem. To make life simple in the example above I omitted a few fields, and probably didnt explain fully.
    So just to refresh,

    -----
    Im trying to make a form that will show data from one table and will allow me to create a record in another table.

    this is the situation:
    This form is to record temperature readings on equipment

    Table one Equipment Fields: Auto ID, high temp limit, low temp limit, equipment name
    table two Fields: Auto Id, Equipment ID, Current temp, Time stamp.

    I have several pieces of equipment and i would like a list of the equipment, and next to the equipment list a way to enter the current readings
    i hope to have it look something like below
    ------
    this all worked fine, with one exception. when I enter a temperature reading it creates a new record as indicated by the new record ID (in Yellow). thats perfect, however I also need it to populate the fields in Grey. The Items in gray are basically duplicates in the two tables. The reason for this is on each reading i have to record the equipment information. so The table Thermostats is repetitive information that needs to be recorded on with each temperature reading. I accomplish this by using a default value for each field.

    I think My problem is that when the new temp is recorded (creating the new record) the query has already taken place, therefore it cant see the correct default values to record. how do I fix this?

    Current Form

    Click image for larger version. 

Name:	Capture.PNG 
Views:	34 
Size:	32.2 KB 
ID:	34380
    -------------------------------
    Thermostat / Equipment Table

    Click image for larger version. 

Name:	Equipment Info.PNG 
Views:	34 
Size:	15.5 KB 
ID:	34379
    ------------------------------
    Readings Table

    Click image for larger version. 

Name:	Readings table.PNG 
Views:	34 
Size:	25.5 KB 
ID:	34378
    Thanks for your help

  6. #6
    Micron is online now Virtually Inert Person
    Windows 10 Access 2016
    Join Date
    Jun 2014
    Location
    Ontario, Canada
    Posts
    12,737
    Why not just put all the equipment fields in a form header and the readings in a single subform below? No matter which date you're looking at, the equipment fields stay populated until you change the equipment selection in the equipment combo in the header - then they repopulate with new data and so does the subform. If the subform isn't a data sheet, 2 date fields on its header could provide you with all the temp readings over the period, or if it's just one date textbox, for the selected date. With 1, you'd have no records for a date that has no readings, so that might be less useful. If it is a datasheet, the unbound date fields can be on the header of the main form. It's just a bit trickier to reference them from the subform (but quite doable). Either way, the subform requires date field(s) in the recordset even if they're not visible.

    It looks to me that you might be repeating text data in child tables where there is a relationship. Rather than putting the id (PK) of the equipment in the readings table as a FK, you seem to be repeating the text value. Can work ok if you have cascading updates enabled in established table relationships, but it's not the best way to go.

    Rather difficult to decipher if you are calibrating thermometers (the equipment) and recording the test readings, or if some type of equipment is being subjected to temperature readings and the thermometer is just a tool. It appears to be the former, but either way, I don't see why a single subform wouldn't work.
    The more we hear silence, the more we begin to think about our value in this universe.
    Paraphrase of Professor Brian Cox.

  7. #7
    davedinger is offline Advanced Beginner
    Windows 10 Access 2016
    Join Date
    Nov 2017
    Location
    South Dakota
    Posts
    63
    I started with the single form and header info just like you said, and it worked fine, but then the users requested the ability to see all of the Equipment in one list so that they can see what units are done and which are not.... (users, who needs them huh).
    To answer your other question, I'm creating this DB to record the temperature readings of thermostats that are in production equipment. The Thermometers are built in to the equipment and Some units may have multiple thermometers. The temperature readings need to be verified multiple times a day.

  8. #8
    Micron is online now Virtually Inert Person
    Windows 10 Access 2016
    Join Date
    Jun 2014
    Location
    Ontario, Canada
    Posts
    12,737
    the users requested the ability to see all of the Equipment in one list so that they can see what units are done and which are not
    Why not just present those that are not done if doing them is the goal? Or done if verifying is the goal? A form could present these options, including the ability to generate reports based on those choices. To present a complete list of equipment, along with data related to that equipment's status, plus the ability to see all the records associated with the readings taken just sounds like one huge bucket of potpourri.

    So you're saying the db is for recording temperature readings taken with thermometers, against what is shown on equipment thermostats. I suppose you could have 2 continuous or data sheet type of forms on one form (one of them for readings) but you would have to cycle the readings subform records upon each selection of a record in the upper equipment list, which would have to be locked. I wouldn't worry about trying to sync the scrolling of both forms. Just click on an equipment record and requery the reading records - as long as both forms contain a common field, such as the equipment id. AFAIK, this shouldn't cause the upper list to reposition itself if you have scrolled, then leave that form by clicking into the subform.

  9. #9
    Join Date
    Apr 2017
    Posts
    1,673
    1. Create a single main form (you can use continuous one too, but then you may have problems with placing all info into form in best way) based on equipment table. On form, along with equipment data, you'll have a combo box to select equipment, and a combo box where you can select filter condition (All, Done, Not Done). Equipment table must have a field, which indicates, is the entry "Done" or "Not Done" (either the user selects "Done" after updating, or some form event(s) is/are calculating the proper Done/Not Done value after entry/entries into other control(s). The Filter Condition combo will have an AfterUpdate event, which sets the proper filter for form or sets an empty filter when "All" is selected, activates the filter, sets the query string in RowSource property of Equipment Selecting combo according with selected filter, and refreshes the form and Equipment Selecting combo. And the form must have OnOpen event, which sets the default value for Filter Condition combo and runs a code like to one in AfterUpdate event of Filter Condition combo;

    2. Create a continuous form based on Readings table.

    3. Open Equipment form in edit mode, and drag Readings form into it (at bottom when Equipment form is single, when Equipment form is continuous, has 1 - 2 not wide fields, and there is left enough room for Readings subform at right, then it may be placed at right of equipment data). A subform is created automatically. NB! You have now Equipment form, in it you have Readings subform (which is not Readings form, but a container with same name as Readings form), and in subform the Readings form as source for Readings subform. Here I'll advice, that you rename the subform so you don't confuse it with Readings form later.

    4. Open subform's properties. There you see 2 properties named LinkMasterFields and LinkChildFields. Probably Access filled them for you. When not, then enter into LinkMasterFileds the name of Primary Key in Equipment table, and into LinkChildFields the name of Foreign Key in Readings table. When you have multi-field Primary key in Equipment table (which looks being the case with your tables), then instead of single field, you have to enter the list (comma or semicolon separated - I'm not sure which) of Key fields from both tables for proper property.

    Now, when you open form, then depending on default setting for Filter Condition combo, the 1st Equipment entry is displayed. When user selects the filter condition e.g. "Not Done", the 1st record with "Not Done" value in according field is displayed, and user can select only equipment, which is "Not Done". Etc.

    Whenever user selects any equipment in main form, according entries for only this Equipment are displayed in subform. And when the user adds new entryes into subform, they are linked with main form. The subform must have controls (text boxes) for all Foreign Key fields, but they must be hidden from user (set invisible). Whenever user adds a new record into subform, hidden Foreign Key fields are getting values from linked Primary Key values from main form.

  10. #10
    Micron is online now Virtually Inert Person
    Windows 10 Access 2016
    Join Date
    Jun 2014
    Location
    Ontario, Canada
    Posts
    12,737
    Equipment table must have a field, which indicates, is the entry "Done" or "Not Done"
    Often a date field can provide this logic without adding additional fields that need to be handled. If there is a date for when a reading was taken, those machine records where the date is Null are not done. Not sure what "not done" is anyway since as I recall, several readings are taken for any one machine over the same day. Done could mean when the date/time count is 4 for a machine. In that case, a yes/no field would have 3 blank records and 1 yes for every day for every machine. The only way to avoid this would be a separate 'completion' table.

  11. #11
    Join Date
    Apr 2017
    Posts
    1,673
    Quote Originally Posted by Micron View Post
    Often a date field can provide this logic without adding additional fields that need to be handled.
    The main reason for having a separate field is, it is used to set the filter on form. With date field, it will be at least very difficult set the filter condition which applies for all records in table. Essentially you have to check is the date latest for this equipment, are all conditions for it being "Done" filled, and to have all this in single formula working for every record to set the filter. And when you manage all this, this may cause form to being slow.

  12. #12
    Micron is online now Virtually Inert Person
    Windows 10 Access 2016
    Join Date
    Jun 2014
    Location
    Ontario, Canada
    Posts
    12,737
    Don't agree. Could be as simple as 'WHERE [Some Date] Is Null'
    Maybe you have seen the db or have a better understanding of its structure and why there wouldn't be multiple records with no "done" flag values. Doesn't matter; I will bow out.

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

Similar Threads

  1. Replies: 6
    Last Post: 03-30-2018, 10:07 AM
  2. .CSV file thinking it's .SLK Can't auto import
    By MTSPEER in forum Programming
    Replies: 6
    Last Post: 08-17-2017, 11:32 AM
  3. Replies: 0
    Last Post: 05-25-2015, 06:46 PM
  4. Help with thinking about tables and forms
    By bytreeide in forum Forms
    Replies: 2
    Last Post: 05-19-2015, 02:12 PM
  5. Inventory, but not as bad as you might be thinking.
    By theevilsam in forum Programming
    Replies: 3
    Last Post: 02-15-2012, 07:10 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