Complicated form design

SteveL13
SteveL13 used Ask the Experts™
on
I am trying to figure out how, if it's even possible, to design a form like the embedded example.    Any guidance much appreciated.

Notes:

Blue: Horizontal list from tblProjects (text to be as shown)                                                
Orange: Combo-Box Selection                                                
Green: List from tblCode                                                
Yellow: Numeric data entry                                                


Form Example
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Yes, its possible.
It is not that easy, and does requery fairly good coding skills. You can see an example of how it can be achieved here:Editable Crosstab in Access
ste5anSenior Developer

Commented:
What is your question?

Well, do you want a bound or unbound form? Do you want data sheet view or controls? What process are you implementing? How does your data model look like (cardinalities?)?
Jim Dettman (EE MVE)President / Owner
Most Valuable Expert 2017
Most Valuable Expert 2012
Commented:
As Ander's said, it's doable and there are a number of approaches.

 Some would use the MSFlexGrid control, which is an outdated (at this point) grid control.   Other's would go the "unbound" route, using a series of a text controls and push/pull data into the controls as needed, possibly in conjunction with a continuous form.   Some might also embed an Excel spreadsheet and do it that way.

Jim.
Ensure you’re charging the right price for your IT

Do you wonder if your IT business is truly profitable or if you should raise your prices? Learn how to calculate your overhead burden using our free interactive tool and use it to determine the right price for your IT services. Start calculating Now!

Distinguished Expert 2017
Commented:
I may have offered this to you for another question.  It is a solution based on queries and a couple of lines of code.  It is not completely flexible though.  You will have to determine ahead of time, the maximum number of projects you want to show.  The solution was conceived to handle months for a forcasting application but I expanded it to do expenses as you can see from one of the examples.  Your situation is something in between.  Look at the third item and see how the Generate Seq button works.  It should give you an idea of how to translate the "months" to "projects"..  The solution will allow you to do projects 1-n and then select n+1 - z if you provide a criteria option so you can ultimately access all the projects a set at a time.

The sample is to large to upload so if you would like it, please send a pm with your email address.
Bound-Denormalized-Forms.pdf
Software & Systems Engineer
Commented:
Well i am sure that a lot of objection would rise due to my proposal of MsFlexGrid but if you want a solution that would "self adjust" to each and every case i am afraid that this the way to go...take a look at a classic sample here
On the other hand nothing is stopping you from selecting one of the other methods that are proposed here but do take into account that are not that flexible in future changes (i will keep a doubt about editable cross - tab as i have never saw that before although the concept is somewhat known...but in other cases)
Appart from the above it would helpful to have an inside view about what excatly are your requirements...for example ..you could easily create a "matching" form based on some a continuous form design and some temp tables that would "rotate" the recordsources..
For example you could have something like 20 -30 textboxes that are unbound by design and on the Load event populate them on some criteria...but this mean that a "user" would only see 20-30 projects at a glance while the MsFlexGid solution is almost limitless (limited only by memory...which it isn't its strong feature but again we are talking in thousands)
Is there a 64bit version of the msFlexgrid available?
John TsioumprisSoftware & Systems Engineer

Commented:
Even the newest commercial siblings of Grid-alike controls are not usable under 64bit Access as for the time being is a very small market... since there isn't a clear advantage of using 64bit Access.
EDIT1 : Here you can find all the "benefits" of Using 64-bit Access...too bad there aren't any real user case except for Large Number datatype.
I realize that there are no real benefits of running 64bit access, but alas Access is just one cog in the office suite, and people might install office 64bit to get support for larger datasheets in excel.

Add to that, from Office 2019 and on, the default installation is 64bit. I would therefore strongly recommend against a solution that will only work on 32 bit.
John TsioumprisSoftware & Systems Engineer

Commented:
the default installation is 64bit
Sometimes you need to take a look behind the curtain...For years Ms has being recommending 32bit Office against 64 bit but it seems that conflicts with their marketing agenda so i guess the time has come to force 64bit to avoid the fuss....of course they don't care about the true user needs ...i have being reading about people complaining for the lack of extra controls ...the answer was : "Just gather more votes to consider it"....controls that have being widely used in the past (like Treeview)
IMHO if you are developing for an in house solution that would never take off besides the company premises then 32bit/64bit makes no difference ...and to be honest Access targets "local" development...
On the other hand i don't see any real advantage so its up to developer to pick...but personally i would like to have code pieces working right away without wasting work hours ...
Back when AMD released the 1st 64bit processor there were huge talks and promises about the wonderful 64bit applications...the years have passed and besides a handful of applications that really take advantage of 64bit (like 3D) the revolution hasn't came...
To me its just a natural confusion...you get a 64 OS why not to use a 64bit Office....too bad there are cons/pros to consider before installing.
Jim Dettman (EE MVE)President / Owner
Most Valuable Expert 2017
Most Valuable Expert 2012

Commented:
@John,

  On this:

EDIT1 : Here you

 You are not pointing to the current content as you are pointing to the Office 2016 tab; a lot has changed in the past three years.  This is the current link:

https://support.office.com/en-ie/article/choose-between-the-64-bit-or-32-bit-version-of-office-2dee7807-8f95-4d0c-b5fe-6c6f49b8d261#32or64Bit=Newer_Versions

 for Office 2019 and forward (hard to say there is a "version" anymore) and at the end, there is one specific reason for using 64 bit Access that is  given:

You’re working with the Large Number data type in Access,

 also as we have discussed in other threads, the other reason is the process address space, which right now, Access is limited to 2GB.   While that's not a major problem at the moment, it is becoming a problem.   More and more posts are showing up where users are getting the "out of system resources" messages with the latest releases.

So while I certainly would agree that we are not yet there with Active-x controls, if you don't use them there is no reason not to switch to 64 bit at this time and there are benefits over 32 bit.    If you do use them, then it's time to start pushing for 64 bit versions of them or find alternatives, which I think is what Ander's was trying to get at.  No sense in building a solution if it's not going to be workable going forward.

I would not be shocked either if in the next five years or six years Microsoft no longer offered 32 bit Office.   With that said, I have no idea if that will be the case or not, but the push is on to move to 64 bit and not just from Microsoft.

At the very least, you should be looking at what it will take you to get there and always give that consideration when your working on solutions.

There will be a day when we leave 32 behind and that day is coming.

Jim.
John TsioumprisSoftware & Systems Engineer

Commented:
I reckon this would take roughly a decade so probably it would be wiser to move to another platform than yo argue about 32 vs 64.
One thing i know for sure is that my copy of  Office 2016 is going to accompany me for many years more
Jim Dettman (EE MVE)President / Owner
Most Valuable Expert 2017
Most Valuable Expert 2012

Commented:
I reckon this would take roughly a decade ...

 umm...don't think it will be that long.  Could be, but we've already had 64 bit in Office since 2010.

 Certainly Access 2016 will be workable for many years and there's nothing wrong with using it well into the future.  Even 2019 and Office 365 are still available in 32 bit.   Right now, 64 bit is just the default install.     But once Microsoft moves onto 64 bit only,  you'll be stuck with the feature set that it offers.  If you can live with that, then you will be set for a long time.  I don't see 32 bit support dying out in windows for quite some time.

 But as you pointed out, it depends on the type of development you do to an extent (in-house vs out).   I think though that even in-house will become difficult.

 From Office 2010 and up, any version of Office will need to be the same bitness, and all products within the install need to be the same.  So by saying "I'll stick with 32 bit Access", that means everything else (Excel, Word, Outlook, etc) as well will be 32 bit and that will limit you.  Especially as we move forward over the next few years.

Also one thing with Access is that you can't get around the large number data type limitations using 32 bit.   Yes it's supported in 32 bit, but only to an extent.  The only clean way of handling the large number data type is with the 64 bit edition.   Something like that may be out of your control, i.e. you develop apps that run against databases that are not yours.

There is a very mixed bag of Access apps out there and some may be able to live with 32 bit Office for quite some time.  Heck I still have clients on Access 2000!    But I see that segment narrowing more and more as we move forward over the next five or six years.

Sooner or later everyone will need to get over the 64 bit hump just as we did with 16 bit.

Jim.

Author

Commented:
All of this went way over my head.  But thanks to those that contributed.  I've decided to go a different route.
Jim Dettman (EE MVE)President / Owner
Most Valuable Expert 2017
Most Valuable Expert 2012

Commented:
Anything we can help you understand about what's been discussed or with your new direction on this?

Jim.

Author

Commented:
Thanks Jim, but not at this time.  I went off in a different direction which seems to be working out.  I do appreciate the offer however.
Jim Dettman (EE MVE)President / Owner
Most Valuable Expert 2017
Most Valuable Expert 2012

Commented:
OK.

Jim.

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial