Page 1 of 2 12 LastLast
Results 1 to 15 of 18
  1. #1
    TerraEarth is offline Advanced Beginner
    Windows 10 Access 2016
    Join Date
    Mar 2018
    Posts
    86

    How to set up this form, or perhaps a better alternative?

    I'm creating a form for data-entry that's intended for use by users. On the left hand side i have rows of textboxes for basic input.



    Here's what I would like to do:

    • Create a table that only exists inside the form (like a excel table/spreadsheet) on the right hand side of the form that allows people to input multiple items on as many lines as they need for a 'Years' field. For example: 2018, 2017, 2015, 2014, etc.
    • Store all the data inputted in both the textfields and in the 'years' field inside of a temporary table of sorts where after the user is finished inputting data for that entry, it will do a validation check and then sort the data from the temporary holder/table into the database's true tables.


    Is this feasible? How would you go about setting up something like this? Is there a better way to do what I am attempting to do?

  2. #2
    pbaldy's Avatar
    pbaldy is offline Who is John Galt?
    Windows XP Access 2007
    Join Date
    Feb 2010
    Location
    Nevada, USA
    Posts
    22,518
    Well, the table can't exist inside the form. What you use is a local table (in the front end) that the form is bound to, with your de-normalized design. Then you use code or a query to append that data to the "real" normalized table(s).
    Paul (wino moderator)
    MS Access MVP 2007-2019
    www.BaldyWeb.com

  3. #3
    TerraEarth is offline Advanced Beginner
    Windows 10 Access 2016
    Join Date
    Mar 2018
    Posts
    86
    So essentially I would need to create two fresh tables for the form to work off of in the database? One for the text fields and the other for the years list?

    The database is of course normalized, and the two fresh tables would contain user-inputted data via the forms after which it would get processed and uploaded into my database via sql queries or something along the like?

    How does that sound?

  4. #4
    pbaldy's Avatar
    pbaldy is offline Who is John Galt?
    Windows XP Access 2007
    Join Date
    Feb 2010
    Location
    Nevada, USA
    Posts
    22,518
    I'm not sure that you'd need two tables. I envision one, with fields for potential years and records would contain text values in each applicable year. That said, you know your data better than I do.

    I have a couple of apps where it makes sense for the users to enter data in a non-normalized fashion. I have a local table with that design. It is emptied when they start and then they enter their data. When they're happy and click a button, I have code to populate the normalized tables and empty the local table.
    Paul (wino moderator)
    MS Access MVP 2007-2019
    www.BaldyWeb.com

  5. #5
    TerraEarth is offline Advanced Beginner
    Windows 10 Access 2016
    Join Date
    Mar 2018
    Posts
    86
    You mean, for example, a table with the columnnames: 2018, 2017, 2016, 2015, .... 1990 where each column is a True/False field and the user marks the columns one by one?

    That is one way to do it but I chose to have another table entirely dedicated to the years as a new fiscal year would designate another new year column having to be added, as in next year we would also need a 2019 column and so on. What I mean by this is that the table has a year field, a identification field (tying it to a specific project) and also a combo primary key consisting of the ID and the year, as a project can be associated with many years. So 3 columns rather than 1+n (n being the span of the years). A one to many relationship was created between a separate document table and this 'years' table where one document ID can be linked with many different years.

    You also mentioned that you have code to populate the normalized tables and empty the local table, this is exactly what I'm going for. Did you use SQL to do this? What kind of general approach did you use for this?

  6. #6
    pbaldy's Avatar
    pbaldy is offline Who is John Galt?
    Windows XP Access 2007
    Join Date
    Feb 2010
    Location
    Nevada, USA
    Posts
    22,518
    I don't know enough about what you're trying to let the user enter to offer a suggestion on the table(s). As a rule I'd only expect one, but that's just based on my experience.

    The app that came to mind right away included an AR component. The users wanted to enter an entire transaction as a single record rather than enter separate records for each line item. That was doable since there are a limited number of possible items that could appear on an invoice (it's a limousine company, not retail). I used 3 recordsets for the local, header, and detail tables. Again, I'd need to know more about your data to offer a halfway intelligent suggestion.
    Paul (wino moderator)
    MS Access MVP 2007-2019
    www.BaldyWeb.com

  7. #7
    TerraEarth is offline Advanced Beginner
    Windows 10 Access 2016
    Join Date
    Mar 2018
    Posts
    86
    For context: the data stores information about contracts awarded out to companies and such for them to perform various tasks.

    there are 5 (4 really) main tables that the users will be interacting with: a Organizations table (who the company is, the contact POC, email, etc.) , A administrative document table (basic administrative data about the contract), a Financial information table (information about the source of funds, money amounts, etc.) and a Status (whether or not a contract is still active along with a date of latest correspondence) and a 'Years' (works as listed above, basically lists the years for which the contract was active) table. The order in which data has to be entered is as follows due to primary/foreign key relationships: company => administrative => financial => Years (timeframe).

    This information is used to fill out forms by the database which is then disseminated out to the different companies, think of it as a receipt generating database. Unlike other databases, this one requires full data entries with 100% of the data fields for it to be useful, without even a single field from one of those 4 main tables missing (the status table being somewhat of a auxiliary that's generated depending on the other 4 along with the users actions). Imagine trying to send a receipt with missing financial amounts, or a missing email/company name, etc. etc. These entries would be useless until someone went back and corrected them, but that would create even more confusion I believe. For this reason I want to force users to enter in all the relevant data all at once rather than in pieces, to force full integrity as much as possible.

    I organized these tables so that the systematically-generated reports the company already has available can be used to populate/update the 4 tables. However there will likely be cases where things will have to be entered manually (though this will likely be a rare occurence, I wouldn't expect more than 1-2 to be entered manually at any given time.)

    Originally I thought of creating a master form with a subform within a subform within a subform, however I thought that this might lead to confusion or partial data (i.e. the company/administrative data was filled out but the financial/years data was not).

    So I set out to create a entry form with all the required fields from those 4 tables (the status table will be generated systematically depending on whether or not money is leftover in the contract, along with when the receipts are generated) which about sums it up.

  8. #8
    pbaldy's Avatar
    pbaldy is offline Who is John Galt?
    Windows XP Access 2007
    Join Date
    Feb 2010
    Location
    Nevada, USA
    Posts
    22,518
    Can you attach the db here?
    Paul (wino moderator)
    MS Access MVP 2007-2019
    www.BaldyWeb.com

  9. #9
    TerraEarth is offline Advanced Beginner
    Windows 10 Access 2016
    Join Date
    Mar 2018
    Posts
    86
    Unfortunately I cannot. I'm contractually tied

  10. #10
    pbaldy's Avatar
    pbaldy is offline Who is John Galt?
    Windows XP Access 2007
    Join Date
    Feb 2010
    Location
    Nevada, USA
    Posts
    22,518
    Without data? Or a picture of the entry form? I'm just trying to get a handle on how the data flows.
    Paul (wino moderator)
    MS Access MVP 2007-2019
    www.BaldyWeb.com

  11. #11
    TerraEarth is offline Advanced Beginner
    Windows 10 Access 2016
    Join Date
    Mar 2018
    Posts
    86
    Click image for larger version. 

Name:	screenshot2.png 
Views:	19 
Size:	19.8 KB 
ID:	34399

    Here's what my form currently looks like. The form is based off of a table and the fiscal years is also based off another table. They're related in a one-to-many relationship between the document table and the fiscal years table (one document has potentially many associated fiscal years)

    The PK/FK is ContractNumber.

    When a user inputs data using this form, the entry will be processed via a update/append query into the main tables. This will only take place if the data is processed and validated (for data integrity purposes). If something already exists for that entry in the main tables it will be updated, otherwise it will be appended. After this is done i plan to have it wipe the entries in the data entry tables.

  12. #12
    pbaldy's Avatar
    pbaldy is offline Who is John Galt?
    Windows XP Access 2007
    Join Date
    Feb 2010
    Location
    Nevada, USA
    Posts
    22,518
    I don't see fiscal years; is that what would be entered to the right? I'll assume the form works the way you need it to, so let me know if you need help with the append/update process.
    Paul (wino moderator)
    MS Access MVP 2007-2019
    www.BaldyWeb.com

  13. #13
    TerraEarth is offline Advanced Beginner
    Windows 10 Access 2016
    Join Date
    Mar 2018
    Posts
    86
    I'm having issues setting up validation checks on this form.
    For the two tables that were used to create the mainform/subform, I have set all data fields to 'required.'
    What I want to do is ensure that the user inputs all data on the main form, and has at least one filled field in the FiscalYears subform.
    My Code so far looks like this:
    Code:
    Private Sub cmdSave_Click()
    On Error GoTo Err_cmdSave_Click
         If CStr(Me!Child86.Form!FYSummary.Value) <> "" Then 'Child86 is the name of the control that houses/contains the subform in my mainform. It was                                                                                                   'automatically generated by Access 
            Debug.Print CStr(Me!Child86.Form!FYSummary.Value)
            MsgBox "Save Succeeded."
            DoCmd.RunCommand acCmdSaveRecord
            MsgBox "Record was Saved."
            Else
            MsgBox "Please make sure that there is at least one corresponding entry in the Fiscal Years table on the right side of the form."
            Exit Sub
        End If
    Err_cmdSave_Click:
        If Err.Number <> 0 Then
        MsgBox Err.Number & ": " & Err.Description
        MsgBox "Record encountered an error."
        'Resume cmdSave_Click
        End If
    End Sub
    Any Advice?

    Sometimes it will work, and othertimes it will not. See the screenshot for two instances:

    Click image for larger version. 

Name:	screenshot3.png 
Views:	13 
Size:	27.4 KB 
ID:	34408

    It behaves differently depending on if i remove/add/click in or out of the box, etc.

  14. #14
    orange's Avatar
    orange is online now Moderator
    Windows 10 Access 2010 32bit
    Join Date
    Sep 2009
    Location
    Ottawa, Ontario, Canada; West Palm Beach FL
    Posts
    16,716
    Can you show us a mock up (fictitious data) of the form (valid and invalid) and what you want done?
    A copy of the db seems out of the question, but if you could give us something with PorkyPig, DonaldDuck and places like HomeTown and Centerville, it would help readers understand your issue and offer something.

  15. #15
    TerraEarth is offline Advanced Beginner
    Windows 10 Access 2016
    Join Date
    Mar 2018
    Posts
    86
    I'm essentially just trying to make sure that at least one year (two digits) is entered into the subform on the right hand side.
    It is basically a column that indicates which years a document was open in:
    e.g. 2018 (18), 2017 (17), 2015 (15), 2009 (09), etc.

    Click image for larger version. 

Name:	screenshot4.png 
Views:	13 
Size:	14.8 KB 
ID:	34409

    As mentioned above, there are two tables that house the data in the form. One table for the main form (left hand side entries) another table for the years (right hand subform).

    After a user has entered all the data and it is proved to be valid, it will be fed into the main tables and these two temporary placeholder tables will be wiped clean of their content. ContractNumber is the primary/foreign key and it is a one to many relationship (one contract number can have many years).

    Edit: Another peculiar thing I found was that, even though a field may be set as 'required' in its table, I can still save the record in the form even when that field is empty.

    Edit: I restricted the inputs for all the fields and it seems to have worked, I had to put conditions to test if inputs were null or blank strings for every single entry on the form.
    Last edited by TerraEarth; 06-12-2018 at 04:19 PM.

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

Similar Threads

  1. Sendkey alternative help
    By Madmax in forum Access
    Replies: 1
    Last Post: 04-10-2012, 10:46 AM
  2. Dlookup alternative
    By scotty22 in forum Queries
    Replies: 19
    Last Post: 10-26-2011, 06:20 AM
  3. alternative to autofeupdate
    By TheShabz in forum Programming
    Replies: 3
    Last Post: 07-19-2011, 11:38 AM
  4. Alternative to mapnetworkdrive?
    By dwcolt in forum Programming
    Replies: 1
    Last Post: 05-11-2011, 08:58 AM
  5. Alternative to AbsolutePosition?
    By snorkyller in forum Access
    Replies: 4
    Last Post: 03-04-2011, 01:04 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