continuous form that allows users to resize and reorder columns like a datasheet (1 Viewer)

CJ_London

Super Moderator
Staff member
Local time
Today, 15:11
Joined
Feb 19, 2013
Messages
16,555
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
1638063491118.png

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:

Code:
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
1638068035924.png


edit: file updated with a minor change to instructions
 

Attachments

  • ContinuousDS (2).zip
    710.8 KB · Views: 676
Last edited:

arnelgp

..forever waiting... waiting for jellybean!
Local time
Today, 23:11
Joined
May 7, 2009
Messages
19,175
only small comment:
very nice.
also the form on .accde is working but that on .accdb is not, please check.
 

CJ_London

Super Moderator
Staff member
Local time
Today, 15:11
Joined
Feb 19, 2013
Messages
16,555
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

..forever waiting... waiting for jellybean!
Local time
Today, 23:11
Joined
May 7, 2009
Messages
19,175
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

Super Moderator
Staff member
Local time
Today, 15:11
Joined
Feb 19, 2013
Messages
16,555
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

New member
Local time
Today, 11:11
Joined
Mar 17, 2024
Messages
2
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".
 

Attachments

  • ContDS using Lib.accdb
    1.1 MB · Views: 18

CJ_London

Super Moderator
Staff member
Local time
Today, 15:11
Joined
Feb 19, 2013
Messages
16,555
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

AWF VIP
Local time
Today, 07:11
Joined
Mar 9, 2014
Messages
5,425
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.
 
Last edited:

SBS

New member
Local time
Today, 11:11
Joined
Mar 17, 2024
Messages
2
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

Super Moderator
Staff member
Local time
Today, 15:11
Joined
Feb 19, 2013
Messages
16,555
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
1710839965539.png


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

AWF VIP
Local time
Today, 07:11
Joined
Mar 9, 2014
Messages
5,425
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

Super Moderator
Staff member
Local time
Today, 15:11
Joined
Feb 19, 2013
Messages
16,555
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
 

Attachments

  • ContDS using Lib.accdb
    904 KB · Views: 11

June7

AWF VIP
Local time
Today, 07:11
Joined
Mar 9, 2014
Messages
5,425
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

Super Moderator
Staff member
Local time
Today, 15:11
Joined
Feb 19, 2013
Messages
16,555
@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
 

Users who are viewing this thread

Top Bottom