Results 1 to 8 of 8
  1. #1
    TrooperTrent is offline Novice
    Windows 10 Access 2016
    Join Date
    Mar 2020
    Posts
    2

    Using single form to create multiple records that have some common values

    Greenhorn Alert



    To this point our team has utilized excel for data capture. We are ready to make the leap into a tool that is more robust and also allows for the creation of a simplified user interface. Knowing that, here is my goal...

    To create a database that will enable users to enter information into a form that looks similar (but much cleaner) to this...
    Click image for larger version. 

Name:	Capture.PNG 
Views:	24 
Size:	5.2 KB 
ID:	41395

    that is then recorded into a table that would look similar to this...
    Click image for larger version. 

Name:	Capture1.jpg 
Views:	24 
Size:	71.5 KB 
ID:	41396

    Key Note
    • Currently on the Form, the filter names are just labels. I have yet to figure out how to get the field (filter name) from a separate table (filter info) to auto populate (either based on department selected or predetermined with a form created for each department). This database once created will have approximately 60 filters in 7 departments. My reports will be set up to look at historical trends of these filters on an individual base and to verify each department is entering data for every day for each filter. I will also set up max and minimum values on the magnehelic readings to trigger alarms if a filter is operating outside typical parameters.


    So to start....
    • How can I get the single date and operator fields to be applied to each filter name, mag name, and observations?


    Simple right? Thanks for whatever help or at the very least entertainment you are willing to provide!

  2. #2
    mike60smart is online now Competent Performer
    Windows 10 Access 2016
    Join Date
    May 2018
    Location
    Living in Scotland UK
    Posts
    263
    Hi

    You can set up your Data Input Form with Unbound Controls and then have a Command Button with an OnClick Event to Insert the Values into an underlying Table.

  3. #3
    TrooperTrent is offline Novice
    Windows 10 Access 2016
    Join Date
    Mar 2020
    Posts
    2
    Would it be best to use the macro builder, expression builder, or code builder to Insert the Values?

  4. #4
    CarlettoFed is offline Advanced Beginner
    Windows 7 64bit Access 2013 32bit
    Join Date
    Dec 2019
    Posts
    42
    If you attach an example file it is easier to show you how you can achieve what you would like to do.

  5. #5
    kd2017 is online now Well, I tried at least.
    Windows 10 Access 2016
    Join Date
    Jul 2017
    Posts
    513
    Quote Originally Posted by TrooperTrent View Post
    Currently on the Form, the filter names are just labels. I have yet to figure out how to get the field (filter name) from a separate table (filter info) to auto populate (either based on department selected or predetermined with a form created for each department). This database once created will have approximately 60 filters in 7 departments.
    So you will have a department table, and a filters table (elevators?), one to many relationship. To get an input form like you want it's probably most practical to use a continuous form thats based on the history table *or better a temporary table* that's pre-populated with one blank record for each item you need recorded.

    Also, I would have a separate employees table that the history table links back to.

  6. #6
    kd2017 is online now Well, I tried at least.
    Windows 10 Access 2016
    Join Date
    Jul 2017
    Posts
    513
    I got board so I made an *example* database file for you. See attached. This was whipped up in about 15 minutes, is untested, and comes with no warranty! I welcome/encourage anyone to point out problems or concerns with this solution.
    Attached Files Attached Files

  7. #7
    kd2017 is online now Well, I tried at least.
    Windows 10 Access 2016
    Join Date
    Jul 2017
    Posts
    513
    I've been having some fun with this! See attached.

    I might have over complicated this. But I decided I wanted to normalize the data a little further and then changed up the action queries to where existing data is updated in place rather than records deleted and re-inserted.

    Hope this helps and doesn't confuse you. It's been a fun exercise for me, I haven't worked much with temporary tables before.
    Attached Files Attached Files

  8. #8
    Join Date
    Apr 2017
    Posts
    1,072
    Attached is an example, where I limited user choices as much as possible (restriction to insert entry for same filter twice in same day included). I think slackening limits is always easier. Simply remove filter field from Main form, make according subform field visible, and edit subform's LiinkMasterFields and LinkChildField properties. And edit unique filter for tblRecords.

    Current example allows entering data for certain filters only for operators from certain department. (When you want to allow data entry to same filter from several departments, but not from all, then it will get more complicated.)

    The subform will always display 1 record (when reading was registered for current selections), or no records (there is no registered recording for current selections). With less restrictions, more records may be displayed in subform.
    Attached Files Attached Files

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

Similar Threads

  1. Replies: 6
    Last Post: 12-06-2013, 11:00 AM
  2. Replies: 1
    Last Post: 01-10-2013, 08:33 AM
  3. Replies: 3
    Last Post: 11-16-2011, 11:53 AM
  4. Replies: 11
    Last Post: 09-27-2011, 07:19 AM
  5. Create Multiple Records from Single Form
    By mcktigger in forum Forms
    Replies: 17
    Last Post: 09-15-2011, 11:07 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