Continuous form with columns

[RichO]

Hi all, I used to be a regular Access user, able to figure out most on my own, but I have not used it in many years now and suddenly I seem to have forgotten so much of what I know.

Anyways, I am looking to create a continuous form with 2 fields - a text box and a check box. I want this to display in 4 columns, top to bottom and then across, so it can all fit on the screen without scrolling. I can figure this out in a report but I can't figure out how to do it in a form, if it's even possible.

Thanks

 

[tvanstiphout]

This is not possible with forms, unless you go to extremes.

 

[Minty]

Do you need to edit the data, or is it purely for display purposes?
If it's the latter you can use a Report in a form, if that helps?

Otherwise you need to repeat a sub form 4 times and figure out how to display the correct records in each one. And if you want to scroll around them it gets even more interesting....

 

[Richard Rost]

If all you need is for it to *display* that way, then Minty's suggestion is the easiest route. You can build a report with multiple columns (just like you described), and then embed that report inside a form. The report can handle the top-to-bottom-then-across layout very easily, and it will look exactly the way you want on the screen.

However, as Tom mentioned, if you want it to behave like a normal form and allow editing of the data, that's where things get tricky. Forms don't natively support that kind of columnar flow. It can be done, but it requires some creative tinkering.

I've done something similar once using a temporary table where I basically flattened the layout into fields like Column1Field1, Column1Field2, Column2Field1, etc. After the user entered the data in that layout, I translated it back into the properly normalized table structure. It worked, but it was a lot of extra plumbing just to satisfy a very specific visual layout requirement. In my case it was an insurance form that had to match a printed format exactly on the screen (hey, the client wanted it that way, and they were paying the bill, sooo...)

So if display-only works for your scenario, the report-in-a-form approach is by far the simplest solution.

LLAP
RR

 

[RichO]

Not for display purposes only. It's for the ability to check or uncheck the box next to the field and keep a count. I have all of that working except for the columns. I may end up filling for form with 100 unbound fields and using using VBA to populate them, as Tom said, extremes

 

[CJ_London]

What I have done in the past is create two or more subform controls. Lets say you want 20 rows. You only need the one subform, but apply to multiple subform controls

Depends on how you determine which 20 to appear in each subform (might use the TOP predicate or a filter) but as an example in the main form load event put something like this aircode

SForm1.form.recordsource="SELECT fld1, fld2 FROM myTable WHERE PK<=20"
SForm2.form.recordsource="SELECT fld1, fld2 FROM myTable WHERE PK between 21 and 40"
SForm3.form.recordsource="SELECT fld1, fld2 FROM myTable WHERE PK between 41 and 60"

using TOP it might be something like
SForm1.form.recordsource="SELECT TOP 20 fld1, fld2 FROM myTable ORDER BT PK"
SForm2.form.recordsource="SELECT TOP 20 fld1, fld2 FROM myTable WHERE PK NOT IN (SELECT TOP 20 PK FROM myTable ORDER BY PK) ORDER BY PK
SForm3.form.recordsource="SELECT TOP 20 fld1, fld2 FROM myTable WHERE PK NOT IN (SELECT TOP 40 PK FROM myTable ORDER BY PK) ORDER BY PK

 

[DHookom]

I have also used two copies of the same subform and added a calculated column in the subform’s recordsource that returns either a 1 or a 2. Then I add two text boxes on the main form with control sources of:
=1
=2
I use these two text boxes as the Link Master from the main form and the calculated column in the subform.

 

[RonPaii]

Are you look to display a 4 x 4 matrix of check box controls along with a label for each. 16 check boxes and 16 labels?

If yes, an un-bounded form or sub-form may work. In the on-current event fill the values. In the after-update event, update the tables.

 

[June7]

I tried displaying a columnar report on a form and it did not work. Loses columns and displays only one record at a time - I can navigate through records. I tried both down-across and across-down.

AFAIK, columns display only in PrintPreview and report embedded on form is displayed in Report view.

 

[Mark_]

If you are using unbound controls, I would suggest using buttons instead of check boxes. Larger area for users to click on and you have more control over exactly how you want your form to respond. Depending on exactly what you need to show, you could even just use the caption for the button to reflect both fields. Something like
IF Fld_Checkbox = True then Btn_MyRecord.Caption = "√ " & Fld_Text Else Btn_MyRecord.Caption = Fld_Text
This cuts your controls in half while still providing the same data.

You would then have a button click that updates the actual record and resets the caption.

 

[Imb]

However, as Tom mentioned, if you want it to behave like a normal form and allow editing of the data, that's where things get tricky. Forms don't natively support that kind of columnar flow. It can be done, but it requires some creative tinkering.

It can be done, but it requires a different way of "binding".
A "normal form" is (in the classical way) a form where the controls are bound to the fields of the RecordSet. This makes the form very static.
The RecordSet dictates only how the data will be stored, and not how the controls should behave in all aspects. For that you need the BeforeUpdate and AfterUpdate events.

The different way of binding is the - what I call - Late Control Binding. As soon as you Enter a control, all characteristics of that control are retrieved from a metadata table, including the corresponding field in the table.
It does not make any difference on what kind of form this "control" is placed, its behaviour is always related to same metadata record. The "form" is just reduced to a carrier of unbound "dummy" controls, that are tuned in the Enter event.
In this way the form becomes completely dynamical, is re-usable and sharable.

But is a complete different way of thinking!

It is NOT a creative tinkering, it is generalization of forms (or better the processes to display them) to the "extremes".

 

[Ken Sheridan]

Anyways, I am looking to create a continuous form with 2 fields - a text box and a check box. I want this to display in 4 columns, top to bottom and then across, so it can all fit on the screen without scrolling. I can figure this out in a report but I can't figure out how to do it in a form, if it's even possible.

If the total number of rows is finite, you could use a query like the following to return Partition values from zero upwards, and then base four side-by-side subforms each restricted to a partition value. In this case the maximum number of rows per subform would be 50, so the maximum total number of rows would be 200. The AllowAdditions property of the first three subforms would be set to False (No).

SELECT
    (DCOUNT (
        "*",
        "Transactions",
        "TransactionDate  <=  #" & Format(TransactionDate, "yyyy-mm-dd") &
        "# AND (TransactionID <= " & TransactionID & " 
        OR TransactionDate <> #" & Format(TransactionDate, "yyyy-mm-dd") & "#)"
    )-1)\50 AS Partition,
    CustomerID,
    TransactionDate,
    TransactionAmount
FROM
    Transactions
ORDER BY
    TransactionDate,
    TransactionID;

PS: If the rows can be ordered by a single column of distinct values, then the criteria for the DCount function call would be much simpler of course, as you would bot need to bring the primary key into play as a tie-breaker in the event of two or more transactions being on the same day, as the above allows for.