continuous form that allows users to resize and reorder columns like a datasheet

[CJ_London]

I developed this because datasheets have some useful functionality, but limitations as well - among them

• Datasheets can't show controls such as buttons, images, attachments, etc
• Datasheets can't show a record over multiple rows within the detail section
• Datasheets don't have header and footer sections
• Sometimes the 'look' of a datasheet doesn't fit with the overall look of your app

So if you require any of the above, you can't use a datasheet - and sometimes it is useful for the user to be able to resize and rearrange columns when those requirements are necessary

Please note this is a 32bit .accde and wont work on 64bit versions of Access. I hope to create a 64bit in the near future

The attached file (ContinuousDS.accde) has a number of forms you can select to demonstrate a variety of scenarios and change the properties of each form by modifying the parameters

click 'Show Form' to apply the the parameters

To use in your own project do the following

In your app VBE window click on tools>references then browse to this file (change type of file to .accde) and select it. Library files can be in the same folder as the calling app so easy to redistribute to other users

Then for each form you want to apply this to the form module:

Option Compare Database
Option Explicit
Dim cfrm  As ContinuousDSLib.clsContDSForm

'and in the form load event put

Private Sub Form_Load()

    Set cfrm = ContinuousDSLib.New_clsContDSForm
    'set parameters as required - for multi layered forms, populate the seed with the name of a header control on each layer separated by a comma e.g. "Label0,Label7"
    cfrm.setLayout Me, "Label0", True, True, 0, 50

End Sub

'and the unload event

Private Sub Form_Unload(Cancel As Integer)

    Set cfrm = Nothing

End Sub

I have included another file (ContDS using Lib.accdb) which demonstrates using the library. It is simply a db with one table and the continuous form created using the multiple items form wizard, then just added the above code.

Click on an item in this list for more information

edit: file updated with a minor change to instructions

 

[arnelgp]

only small comment:
very nice.
also the form on .accde is working but that on .accdb is not, please check.

 

[CJ_London]

on .accdb is not, please check.

Thanks for pointing that out - the resize worked, but I'd forgotten to remove layouts so you could move columns around. Have updated the attachment with a corrected .accdb

 

[arnelgp]

on the moving of control it seems that you are just adding X to the
left of both the label and corresponding textbox control when
the mouse is depressed.
i'll test more.

 

[CJ_London]

Not sure if you are saying there is a problem?

when the mouse is depressed, the app picks up the X parameter value, when the mouse moves it picks up the new X parameter and compares with the mousedown X value to determine how much the mouse has moved

 

[SBS]

When I use provided accdb, I get "3021: No Current Record" error for all records. Also, I tried to create a new form with a subset of fields. Has the same error with additional error "91: Object variable or With block variable not set".

 

[CJ_London]

I'll try and take a look at your issue but have to concentrate on my paying customers

In the meantime, suggest download the file again and see if you still have the same issue

 

[June7]

Nice.

In CJ's db, seems to be issue with moving image control. It overlays column to right. I reduced width of control and works better.
In my db, I get same errors SBS described. Triggered by:

cfrm.setLayout Me, "Label0,Label1", True, True, 0, 50

Testing a multi-layer arrangement. Label0 is on first and Label1 on second.

 

[SBS]

I believe that the issue is at minimum with "Resize on Open" or trying to resize by double clicking the label. If I had access to the code, most probably I could fix it.

 

[CJ_London]

I have now had a chance to investigate. I'm not sure why the error is now occurring as it wasn't when I originally developed the app.

A simple fix is to embed the form as a subform in a main form

In the pipeline is an updated version with more functionality as well as a 64bit version. The logic used is improved and the code simpler. However I have other higher priorities right now so no promises on a release date

 

[June7]

Embedding as subform does not fix. I tried building a new form in CJ's db and still get same errors, even though original form works.

 

[CJ_London]

Embedding as subform does not fix.

this works for me - the attached is the db downloaded from post #6 with Form1 added and nwdemployees added as a subform.

I don't think it makes a difference but I'm running 2010 32bit on win10

 

[June7]

This is so weird.

I can build a new form with your table as source, copy all controls from the old form and this works, even as a stand-alone form.
Building a new form with my imported data almost works. Get "3265: Item not found in this collection." when opening form. No change as a subform.

Then I took the new copy of your form and changed it to my imported table and fields (deleting all but 3 textboxes). I get same error when form opens but after that it works. Crud! I swear it did work but now it fails. So, I tried again. Just copied your form, changed source and textboxes. Getting 3265 errors on open but move works. This one seems to be holding.

I finally got a different form and table of mine to almost work. This form is set for multi-layer. Opening still triggers 3265 errors also 3021: No current record.

Discovered source of 91 error is when labels and textboxes are not properly aligned. Also, don't have controls on different layers have same left position - "stagger" them.

Maybe you can use this info in your mods or others in testing, but I think I am done.

 

[CJ_London]

@june - many thanks for your investigation

Discovered source of 91 error is when labels and textboxes are not properly aligned. Also, don't have controls on different layers have same left position - "stagger" them.

this is actually mentioned in form design requirements

however error 3021 remains a mystery for me at the moment

 

[SBS]

Any updates?

 

[isladogs]

For info, I have my own version of this functionality:

Move/Resize Columns in Continuous Forms

This is the first part of a two part article showing how the layout of continuous forms can be modified by repositioning and/or resizing selected columns. The article includes an example app with all required code.

www.isladogs.co.uk

 

[arnelgp]

if you have, i also have.
with code at your persusal.

Move/Resize(Width) Column of Continuous Form

this is somewhat similar of the same demo here, but with full code that you can inspect (not an .accde). there may be some errors or unwanted behavior, since this is the initial upload. if you see any errors or unwanted behaviour, you try to fix them. there is no documentation on the code at the...

www.access-programmers.co.uk