Form with top 10 graph (1 Viewer)

[Vulcan1500]

If the query with the recordsource is ordered (e.g. city) this is not done as such in the form. How can it be organised that all records are ordered before step by step 10 records are displayed?

 

[sneuberg]

The solutions I posted depend on the order of a key. A solution for a recordset in any order is a different animal. I need to think about this for a while. I'll let you know if I figure something out.

I just thought of an ugly way to do it. That would be to create a make table query that creates a copy of your table with an autonumber and with the data sorted in the order you want. You would then use this temporary table for the display of the data. The autonumber would be the key.

 

[sneuberg]

You mention in your posts that the data in the form was put into a graph. Could you tell us more about that? What type of graph is it and what data is being graphed? Is the display of data the only use of the form, ie., is the data ever modified by this form or can in be readonly?

 

[sneuberg]

I think a solution might be to have a table with ten records numbered 1 through ten with the fields that need to be displayed in the form. Let's call this the GraphTable. This table would be bound to the form. When the form is loaded a recordset, global to the form, and base on a sorted query of the data would be opened and the data from first ten records (or is it five?) would be copied to the GraphTable and the form would be requery. The next and previous ten record buttons would just move the recordset position forward and backwards and copy the data to the GraphTable.

After I get the details I requested in my previous post I create a prototype of this solution for you.

 

[Vulcan1500]

Sorry, I wasn't able to answer the asked questions earlier. The graph in the form is a graph with horizontal bars. On the Y-axis the names of the clients are shown and on the X-axis the revenu in €. I started with 10 clients, but decided later to go to 5 as a maximum to display. It is just a graph to give information about the client's revenu in a specific period. Data will not be altered via this form.

 

[sneuberg]

Sounds like the number e.g, 5 or 10 should be controllable by the user so that they could get a zoom in-out effect. I'll see what I can do but in any case I'll should be able to give a solution for a fixed number soon. If you tell me what the record source of your graph looks like I'll make my prototype match so it will be easier for you to implement on your system.

 

[Vulcan1500]

Recordsource is a query called 'qryMijnBesteKlantRecordsource' and includes: pkKlant, Naam, SomExBTW, SomBTW and SOMInBTW (BTW is VAT).

 

[sneuberg]

Could you post a screen shot of the graph?

 

[Vulcan1500]

Basic example attached in which the names are made unreadable.

 

[sneuberg]

This version doesn't rely on a key value to determine the start position of the group of values to be displayed and therefore the source query can be sorted or filtered on any field. The examples in the attached database are sorted by City. I was able to implement the attached version without have to use a table to store the chart values so I was able to make the number of values displayed variable. The example forms in the attached database have a combo box to select values of either 5, 10 or 15. It defaults to 5. I found that as I increase the number of values displayed that names were being skipped. To overcome this I found I need to decrease the font size to 12 to get it to display fifteen names. It might be possible to vary the font size programmatically so that varies with the number of names but I haven't figured that out yet.

The graph you posted didn't seem to jive with the row source information you posted so not knowing how much data you wanted to display I created two examples in the attached database. The BesteKlant form displays one value per name and the BesteKlant4Column form displays three values per name. Also these forms provide an example of how the query might be filtered. The forms are filter by State or not.

The attached database includes a Readme form which has installation instructions and instructions on how the code must be modified if form references are added to the source query.

Happy New Year

 

[Vulcan1500]

Happy New Year! Many thanks Steve. This is realy a good start of the year for me with such a detailled answer including instructions. Implementation is now easy for me although I will need some time to understand your solutions fully. You even thought about small, but beautiful, details like buttons being inactivated if they reach the end of the data set. Very nice!

 

[Vulcan1500]

I needed some time to understannd and implement. It is all working now as expected. Thanks for your time and detailled advices. Maybe you can give me a last advice. I've added a combo box to the form that gives all the years in which an invoice is prepared and issued. After updating the year (cboJaar) I would like to filter the recordset based on that combo box (Me.cboJaar). Where and how can I add this filter to build up a filtered recordset?

 

[sneuberg]

The BesteKlant form has a combo box that filters the data by State. A filter for year could be done the same way. Note that this would require modification of the code as described in the ReadMe. If you want more specific instructions please post the form module code you have and the SQL of the source query.

 

[Vulcan1500]

I selected your generic form for my application. Once this was working the idea came to include a combo box to filter the year. At that moment in time I was totally focussed on what was working and didn't think about the other forms you gave. Sorry for that. Anyway I'm very happy it's working now including a combo box for the year and one for the quarter. Thanks again

 

[Vulcan1500]

After checking in spreadsheets the data set after updating the second combo box (cboKwartaal) the result is not correct. To filter on a second combo box I added a line to the code similar to the first combo box:

Set qdf = CurrentDb.QueryDefs("qryMijnKlantenRecordSource")
qdf("Forms!frmMijnKlanten!cboJaar") = Forms!frmMijnKlanten!cboJaar
qdf("Forms!frmMijnKlanten!cboKwartaal") = Forms!frmMijnKlanten!cboKwartaal

By doing it this way I think I get the results of the selected quarter (kwartaal) of all available years instead of the quater of the selected year (jaar) in the first combo box.

 

[Vulcan1500]

SOLVED. I didn't requery cboKwartaal after updating cboJaar. Now data matches!!!