Consulting

Page 1 of 2 1 2 LastLast
Results 1 to 20 of 21

Thread: Dynamic creation of graphs by user input in form and editing options at run time

  1. #1
    VBAX Regular
    Joined
    Oct 2017
    Posts
    14
    Location

    Dynamic creation of graphs by user input in form and editing options at run time

    hi i am new to access i would like to have the user enter the values in a form and as a result it shows graph based on values then user can test the curves by editing some values already given when the user satisfies then he presses save button.

    Input data will be for the graph as follows
    A,B,C,D,E,TEMPERATURE(HIGH),TEMPERATURE(LOW)
    But Each 'A' can have 5values. similarly b,c,d,e,tempH,tempL

    Also graph has on x-axis temp and on y-axis a formula with those a,b,c,d,e values

    Should i have to write a vba code which generates expected output
    Thank you
    cheers.

  2. #2
    VBAX Guru
    Joined
    Mar 2005
    Posts
    3,296
    Location
    It should not take much VBA code, the main thing is to set up the Form and base queries on the Form data and then create the Graphs from the queries.
    Access Graphs are no where as near as user friendly as Excel, although later versions of Access may be better.
    The VBA would be required to to requery the Graph to update it to the user inputs.

  3. #3
    VBAX Regular
    Joined
    Oct 2017
    Posts
    14
    Location
    hi could you be more specific about the instructions. also the form which i mentioned takes different values from each texboxes and combines to one value and then writes to database. so if i make the query it is asking me to select from a table but which has combined values in that specific field.
    1. Either i choose from the table specific field and split in the query and then perform the calculations and then generate graph ?
    or
    2. take the each values and calculate in the formula and get the values of c and low temp and high temp , thus create a graph ?
    Also if i need to do some calculations and i hope vba coding will be better
    kinda confused thanks.

  4. #4
    VBAX Guru
    Joined
    Mar 2005
    Posts
    3,296
    Location
    Calculations can be done in queries, they have built in functions like averaging, min, max etc as well as using VBA on a form.
    The form values can also be used to filter the query results, for instance only values greater than the value on the form or between 2 dates.
    So what data is going to be graphed, you say they can have 5 values but do not provide any examples.
    So can you provide examples of any calculations that need to be carried out at examples of the data to be graphed?
    Can you provide data or better still a dummy database with the data in the table(s)?
    Once set up the graph will display whatever values are in the underlying query that provides the data.

    Also which version of Access are you using?

  5. #5
    VBAX Regular
    Joined
    Oct 2017
    Posts
    14
    Location
    hi i can send a screen shot roughly i just made in excel SAMPLE.jpg

    as you can see the first table is the form fields given for user but there is no table for it.
    it appends a,b,c,d,e into one field(V1) of table T1 same as temp low and temp high all values

  6. #6
    VBAX Guru
    Joined
    Mar 2005
    Posts
    3,296
    Location
    What is the calculation carried out on a,b,c,d,& e?

  7. #7
    VBAX Regular
    Joined
    Oct 2017
    Posts
    14
    Location
    At+bt^2+ct^3....until e but i didnt get why u require this

  8. #8
    VBAX Guru
    Joined
    Mar 2005
    Posts
    3,296
    Location
    To establish the way to reproduce the graphing values, either in a query or using VBA, or do you already have the calculation in Access?
    In that series what is "At" in relation to "A"?

  9. #9
    VBAX Regular
    Joined
    Oct 2017
    Posts
    14
    Location
    A has 4 values ,b has 4 values and c,d,e,temp h,temp l has 4 values each so we will get 4 equations and then 4 values as given in table2 c(formula)
    i can calculate the values equation but i dont know how to show it in graph at run time when user gives all the values. if you can just tell me how to generate run time graphs its appreciated.
    thanks cheers(yes A are values and t is temp and its the equation At)

  10. #10
    VBAX Guru
    Joined
    Mar 2005
    Posts
    3,296
    Location
    OK, on your current form click on the top menu Design to get the form in to design mode, this will give you a whole new suite of menu items.
    In the middle is a group called "Controls" which can be inserted in to the form, in the top middle is "Insert Chart" and folow the Wizard instructions.
    The Calculated values have to be in the Table that you use for the chart, or calculated in the Query you choose.
    Once you have created and formatted the chart to how you want you can then insert a Command button to refresh it, which are also in the Controls Group.
    I will not be available after 7:00pm UK time, about 25 minutes.

  11. #11
    VBAX Guru
    Joined
    Mar 2005
    Posts
    3,296
    Location
    I should have said you can also use the Create menu to Create a PivotTable/Graph Form as well.

  12. #12
    VBAX Regular
    Joined
    Oct 2017
    Posts
    14
    Location
    hi dont know why it says forbidden words how sick. can you check the image i have written exactlytext.JPG

  13. #13
    VBAX Guru
    Joined
    Mar 2005
    Posts
    3,296
    Location
    What version of Access are you using?
    Can you upload a zipped copy of your database in Access 2007 or earlier format?
    What "forbidden word" did you use?
    If the user tries different value do you wan to keep them all or just the last ones input, ie overwrite the old data with the new data.

  14. #14
    VBAX Regular
    Joined
    Oct 2017
    Posts
    14
    Location
    its access2013 and yes i want to over write. but i think your idea should work of inserting into table and then generate graphs. but if i have 3 'A' values then i get three equations. then how can i link them to 1 compund ?

  15. #15
    VBAX Guru
    Joined
    Mar 2005
    Posts
    3,296
    Location
    OK, here is what we can do.
    1. You put some notes on your spreadsheet that you provided a screenprint of, including the Formula and all values you want tested.
    2. You "Upload" the Workbook using the "Go Advanced" and "Manage Attachments".
    3. I will create any Tables, Queries and Form needed to do what you want based on your notes.

    You could display all 3 sets of of values at once by using 3 graphs, it would depend on how large you want then to be.

  16. #16
    VBAX Regular
    Joined
    Oct 2017
    Posts
    14
    Location
    Also for the formula a+bt+ct^2+dt^3+et^4 i tried to be as much simple i can Instructions.JPG

  17. #17
    VBAX Guru
    Joined
    Mar 2005
    Posts
    3,296
    Location
    That is a screenshot, can you upload the actual workbook?

  18. #18
    VBAX Regular
    Joined
    Oct 2017
    Posts
    14
    Location
    attached worksheet
    Attached Files Attached Files

  19. #19
    VBAX Guru
    Joined
    Mar 2005
    Posts
    3,296
    Location
    Rickz, I have taken your Excel Data and placed it in an Access table, 2 queries one for the Subform and one for the Graph, created a background form, a Continuous Mode Subform to display the data and added a Chart to the background form based on the values in a Query of the Table.

    There is a button on the background form that "Re-queries" the Chart, so that you can make changes to the values on the form and therefore in the table and reflect those changes in the Chart.
    However the table, queries and form just used the values for "C(FORMULA OF A,B,C,D,E)" that you supplied as I just cannot make the Formula re-create anything even close to those values.

    See the attached database.
    Attached Files Attached Files

  20. #20
    VBAX Regular
    Joined
    Oct 2017
    Posts
    14
    Location
    Thanks alot, As i said before yes i guess we have to push the data from user into database into table then it can generate graph based on table values. what if i create for new entry then i think table has no value thus no graph. hmm sorry just thinking
    1. so in the graph on x-axis how did you set low bound to low temperature and high bound to high temperature ???
    2. how can i add these 5 values of records to one record in different table master.
    3. So if i have the values in the master table for A=a1,a2,a3,a4,a5 similarly for B,C,D,E,hightemp,lowtemp. i can pull the values from database but i think everytime i pull them on textboxes i shouls automatically write into data table which you have given.
    but can i write the data and then generates form although if user closes form then i will make the values in the table empty. i wonder writing and deleting values will be a good practice or not ?
    also can you tell about this three form data,form1,graph ?

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
  •