Columns - Continuous Form

[Tieval]

I have a set of data which has just one field of interest which I would like to list in a continuous form but with hundreds of records would not want to show it as a really long list.

Is it possible to make a form with multiple columns with the data sorting across and then down in the same way that you can produce a report?

 

[Uncle Gizmo]

Yes. You need a main form and this will hold a form displaying the continuous form in a subform/subreport Control. I normally do this with a form showing records in datasheet view but I can't see any reason it won't work with a continuous form.

Now this "subform"..... this is a misleading name for it because it is essentially a normal form just displayed in a special control called a subform/subreport Control. I will incorrectly refer to it as a "window" as this is a good way of thinking about it, think of it as a window.

So you now have a main form with a window on it showing a continuous form. You need to adjust the query behind the continuous form so that instead of showing all of the records it only shows for example the first 10 records. Once you have your form just showing 10 records then make a copy of it and create another form which shows the next 10 records. Now create another subform window next to the first one and you will be displaying 20 records in two columns. Now I should mention that you actually only need one of the "continuous forms" because you can create instances of it to fill the other subform windows. But it's important to get it working first before thinking about improving it.

 

[Tieval]

Many thanks for this but my problem is greater than this.

I have a changing amount of data, there may be 100 records, there may be 1000 records. What I am trying to do is display all records at any time in the same way that a report can have six columns and put record 1 in column 1, record 2 in column 2 etc. and then put record 7 in column 1 row 2 etc.

 

[Uncle Gizmo]

This method can display a 1000 records, 10 columns each with 100 records.

 

[Tieval]

Many thanks, I guess that is not too limiting.

I can see a lot of the logic here and can obviously create a query that selects the top 100 but how can you select the data for all the other instances without cascading ten queries.

Any advice would be greatly appreciated.

 

[Uncle Gizmo]

Create the 1st two as suggested, post them here and it will most likely be possible to construct a VBA statement to create all 10 automatically. But it really depends if your database follows the recommended method of construction.

 

[Tieval]

Many thanks for the offer Uncle Gizmo but my database is a bit of a pain to work on and would take a lot of getting the hang of.

I have now achieved it using a slight variation on your method, I created a table from my query and then altered the table to give it a primary key to make it easier to select the first 100, second 100 etc. (ID between 1 and 100 etc.), I then deleted the table when I closed the form.

Many thanks again for your help.

 

[Uncle Gizmo]

Do you have, or are going to have 10 subforms?

 

[Tieval]

At present I have ten sub-forms but that is just to get it working, I am now trying to work out how to load ten instances of the same form and filter them all differently.

 

[Uncle Gizmo]

You can also set the instance forms' record sources' with code

 

[Tieval]

You can also set the instance forms' record sources' with code

and here it is:

Dim strSQL1 As String
    strSQL1 = "SELECT Blade FROM Choice WHERE ID < 101"
    Me.ListSub1.Form.RecordSource = strSQL1
Dim strSQL2 As String
    strSQL2 = "SELECT Blade FROM Choice WHERE ID Between 101 and 200"
    Me.ListSub2.Form.RecordSource = strSQL2
Dim strSQL3 As String
    strSQL3 = "SELECT Blade FROM Choice WHERE ID Between 201 and 300"
    Me.ListSub3.Form.RecordSource = strSQL3
Dim strSQL4 As String
    strSQL4 = "SELECT Blade FROM Choice WHERE ID Between 301 and 400"
    Me.ListSub4.Form.RecordSource = strSQL4
Dim strSQL5 As String
    strSQL5 = "SELECT Blade FROM Choice WHERE ID Between 401 and 500"
    Me.ListSub5.Form.RecordSource = strSQL5
Dim strSQL6 As String
    strSQL6 = "SELECT Blade FROM Choice WHERE ID Between 501 and 600"
    Me.ListSub6.Form.RecordSource = strSQL6
Dim strSQL7 As String
    strSQL7 = "SELECT Blade FROM Choice WHERE ID Between 601 and 700"
    Me.ListSub7.Form.RecordSource = strSQL7
Dim strSQL8 As String
    strSQL8 = "SELECT Blade FROM Choice WHERE ID Between 701 and 800"
    Me.ListSub8.Form.RecordSource = strSQL8
Dim strSQL9 As String
    strSQL9 = "SELECT Blade FROM Choice WHERE ID Between 801 and 900"
    Me.ListSub9.Form.RecordSource = strSQL9
Dim strSQLA As String
    strSQLA = "SELECT Blade FROM Choice WHERE ID Between 901 and 1000"
    Me.ListSubA.Form.RecordSource = strSQLA
Dim strSQLB As String
    strSQLB = "SELECT Blade FROM Choice WHERE ID Between 1001 and 1100"
    Me.ListSubB.Form.RecordSource = strSQLB
Dim strSQLC As String
    strSQLC = "SELECT Blade FROM Choice WHERE ID Between 1101 and 1200"
    Me.ListSubC.Form.RecordSource = strSQLC
Dim mval As Integer
mval = DMax("ID", "Choice")
If mval < 1200 Then Me.Label21.Visible = False

A form with a sub form in twelve instances and just for good measure, a label on the main form informing you if there is more data than that.

 

[gemma-the-husky]

I know you think you want the form to behave as you mention, but why? Why would you need to see 100s of values?

 

[Tieval]

I know you think you want the form to behave as you mention, but why? Why would you need to see 100s of values?

I have a database of results from a piece of testing equipment, it throws out all sorts of in-depth statistics but occasionally there is a simple requirement.

A user may want to view a list of just the serial numbers of parts that have been tested over a period of time and this could be over a thousand items. Obviously a report can show this nicely with multiple columns going across and down but most people don't want a report.

A continuous form or list box would just be a single very long column so the idea was to split it into columns to get more on the screen. I now have a form that opens with up to 1200 entries that can be quickly and easily looked through.