By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
429,426 Members | 1,729 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 429,426 IT Pros & Developers. It's quick & easy.

Different unbound controls in continuous form view

P: 13
Is there a way to have unbound, uncalculated controls (or a calculated control that is based on a function) that retains a different value for each record?
Aug 11 '08 #1
Share this Question
Share on Google+
24 Replies


missinglinq
Expert 2.5K+
P: 3,532
No! How could you? How could you expect Access to know what value you've placed in it if it's not stored (bound) to that particular record? All unbound controls hold the latest data that's been entered in any of the instances of the control. In a Continuous View form, you can see this. If it's an unbound control and holds a calculated value and the form is in Single View form, you can cheat and make it appear to hold a different value for each record, by re-calculating it as you move from record to record. But in actuality when the control on RecordA holds 10, all instances of this control hold 10, you just can't see the other instances of the control.

Welcome to Bytes!

Linq ;0)>
Aug 11 '08 #2

P: 13
What a quick response!
I don't understand the question, "how could it be otherwise." It would make sense to remember state across different records. Events are always generated from a specific record, so any code modifying a control's state obviously means to modify that state for that record. And if the user directly modifies the control he certainly means to modify the one he interacted with! Checking a checkbox next to a field should be allowed to trigger code that will perform some operation on that record! No one expects to see all the check boxes nearby to suddenly pop on or off!
Anyway, enough philosophising. Maybe you can help me with my specific problem.
An order is priced based on a number of components, aspects of the order. The price by default is calculated from the inventory tables, but the user should be allowed to override it and enter a custom price for the current order. Since the official price must be stored in either case so that it's possible to revert to the official price at the time of selection, the custom price must exist in separate field, set to null to indicate that it's the regular price that's being used. I'd rather not have an extra field just to store whether or not the price has been customized. That should be determined from whether the custom field is null.
Some of the price components are rows selected from a table, so part of the "invoice" column is a subform, displayed in continuous form view obviously, so you can see all the components of the price at once.
The problem is that the user interface should not require two columns of fields. When the price is customized you should not see the real price, and when it's not you certainly don't need to see the empty text box.
The ideal way would be to have a check box which toggles the control source of a single text box. Apparently that's impossible.
Aug 11 '08 #3

P: 13
Now I tried having text boxes for each and toggling them visible/invisible (real price is visible, double-click sets it invisible and custom visible, afterupdate with null sets custom invisible and real price visible). Guess what? Again, settings apply for every record! Ideas, anyone??
Aug 11 '08 #4

missinglinq
Expert 2.5K+
P: 3,532
"I don't understand the question, "how could it be otherwise." It would make sense to remember state across different records."

Of course it does! And you do this by using a bound control!

Any control that you want to be specific to a given record has to be bound to a field in your underlying table! An unbound field is just that, unbound to any record! If you want to tick a checkbox on RecordA, and have it always stay ticked on RecordA, RecordA has to have a field, in the underlying table, that the checkbox is bound to! Data cannot be stored for a record otherwise.

Linq ;0)>
Aug 11 '08 #5

NeoPa
Expert Mod 15k+
P: 31,438
...
I don't understand the question, "how could it be otherwise." It would make sense to remember state across different records. Events are always generated from a specific record, so any code modifying a control's state obviously means to modify that state for that record. And if the user directly modifies the control he certainly means to modify the one he interacted with! Checking a checkbox next to a field should be allowed to trigger code that will perform some operation on that record! No one expects to see all the check boxes nearby to suddenly pop on or off!
...
The question explicitly stated UNBOUND controls.

Access (and database generally) work depends on attention to detail and logical thinking.

Logically, it could not make sense otherwise for unbound controls. If this isn't clear to you then you will struggle with databases.
Aug 11 '08 #6

P: 13
I didn't say I wanted to persist the state! I think I understand databases quite well, thank you. There's no reason that Access can't remember transient state that would help to provide some functionality. Anyone who has programmed databases with a real programming language can understand that! Forms are more than table views with a different shape!
In other words, often it's necessary to remember something about a record temporarily, in order to affect the record in a more complex way than a directly editing a bound control would allow for.
Perhaps a simpler example of this concept would be a button that needs to have a different caption for different records, or controls that have to be hidden for particular records. A table might hold records of the same general category but that differ in various ways, and the form needs to provide a modified interface for some records.
Anyway, enough philosophizing. I need some practical ideas to accomplish my goal. It is extremely inelegant to have to store three columns (fields) when the same thing could be accomplished with two.
Aug 11 '08 #7

missinglinq
Expert 2.5K+
P: 3,532
Since you obviously have all the answers, good luck to you!
Aug 11 '08 #8

MMcCarthy
Expert Mod 10K+
P: 14,534
I didn't say I wanted to persist the state! I think I understand databases quite well, thank you.
This is not a database issue. These controls are unbound which means the data is not being stored in the database. Your question is about how Access handles unbound (unstored) values not about databases.

There's no reason that Access can't remember transient state that would help to provide some functionality. Anyone who has programmed databases with a real programming language can understand that! Forms are more than table views with a different shape!
Your understanding of transient state seems faulty. The data in unbound controls are not stored anywhere therefore the forms in question are not representing a table view. An unbound control on a form while it is displayed in each instance of the form in continuous form view only exists once. Therefore, any data entered in an unbound control is shown wherever that control is visible. That is, in each instance of the continuous form.

In other words, often it's necessary to remember something about a record temporarily, in order to affect the record in a more complex way than a directly editing a bound control would allow for.
My understanding of the point you are making here is that you are speaking of what are known as calculated values. These are not stored as a matter good practice in RDBMS for the aforementioned reason.

Perhaps a simpler example of this concept would be a button that needs to have a different caption for different records, or controls that have to be hidden for particular records.
The second point you are making is addressed using conditional formating. The first point regarding label captions cannot be done in the way you are addressing it on continuous forms.

A table might hold records of the same general category but that differ in various ways, and the form needs to provide a modified interface for some records.
This sounds like you have a problem with the structure of your data. A table that holds records of the same general category but differ in various ways require at the very least a look up table.

Anyway, enough philosophizing. I need some practical ideas to accomplish my goal. It is extremely inelegant to have to store three columns (fields) when the same thing could be accomplished with two.
I reiterate that I believe your issue is with the structuring of your data. If you explain the data you are trying to store and what its relationship is with the other data in your table we may be able to help. However, from what I have read you are trying to use unbound controls in a manner for which they were not intended.
Aug 11 '08 #9

NeoPa
Expert Mod 15k+
P: 31,438
Or to put it more simply, there seems little evidence of of the understanding you claimed in response to the help you were offered.

From what you've posted, it's clear to me why you're struggling with your current task. There are a number of fundamental ideas that one needs to grasp in order to work effectively within an RDBMS.

Please understand I don't say this to be insulting, but as you've made a claim to understanding these issues I wanted to make sure that anyone reading this thread knows not to take what you say as established fact. We have a number of members who are just starting to learn about these issues. They can make their own minds up of course, but I felt it necessary to make that clear for them.

I think they will be able to make their own minds up as to the wisdom of the attitude you've taken towards people offering their time for free in order to help those who are asking for help. That includes you in case you've forgotten.
Aug 11 '08 #10

P: 13
I certainly appreciate the time everyone has taken to help me, and I'm not trying to be ungrateful. I'm just a little frustrated that no one seems to understand the points that I'm making, which I think I expressed quite clearly, and then insinuate that it's my lack of understanding of the subject matter. I understand that it's not anyone's fault. [Yes, I am quite aware of the concept of table relationships and how data can be split across two tables. But when you're talking about data that's basically the same with a few technical differences, it's silly to double the number of tables. The point of splitting tables is to avoid duplicating data and save space. If you put every field that is specific to a certain subset of the data in a separate table, you'll have fewer records recording that field, but each record will need another field to link it to the other table. So it's a matter of balance. No?]
1) [not a question but a comment] I still think it's ridiculous that just because a control is not tied (bound) to a persistent state mechanism means it should have no state at all, independent from other instances of that form. In other words, I am disappointed and frustrated by the very limiting way that Microsoft chose to implement continuous form view. Instead of taking the logical approach, which is that each row is another instance of the form, representing a different record, they decided to implement using "rubber stamping." This is similar to the way Java list cell renderers work. There is only one instance which is painted many times, with it's state changed in between. Thus each time Access repaints ten rows of a continuous form view, in theory at least it's retrieving all ten records from the database again. Probably the reason is that to create multiple instances of the form would require storing all viewable records, even those scrolled off the screen, in RAM. Or because there has to be a way to refer to the default instance. Whichever it is, there are other ways to achieve the same effect that would provide a more intuitive and flexible environment.
2) My real question. In case my prior explanation wasn't clear enough, I'll explain better what I was trying to accomplish. The more ideas the better.
An order consists of a combination of variables. These variables add up to determine the final price that the customer will be charged. These individual components of the price are generated by looking up the price for that aspect of the order in the corresponding table. However, the user (the salesman) has the ability to modify the price for a specific customer. So instead of being $5 + $5 = $10, he will change the $5 to $4.50, and then the total will be $9.50.
The complication is that it should not forget the original price. Tomorrow the price for the first component of the order will change from $5 to $6. Also, he will find out that the customer does not get a $0.50 reduction on that component. So there has to be a way to revert to that $5. On the other hand, if he switches component A to some other item which is $5.50, so if the price had been customized it should not change it. However if it had not been customized then it should change.
The logical way to design the table is with a field holding the real price, and a nullable field which can be used to override the real price. However there is no reason for the user interface to show to columns of fields. It should show only one field, which behaves with the aforementioned logic.
Some ways of implementing this which seem to be impossible, is having the two text boxes superimposed one on the other and toggle their visibility. Or to have a check box which toggles the control source of the one text box. Ideas, anyone?
Aug 12 '08 #11

MMcCarthy
Expert Mod 10K+
P: 14,534
Some ways of implementing this which seem to be impossible, is having the two text boxes superimposed one on the other and toggle their visibility. Or to have a check box which toggles the control source of the one text box. Ideas, anyone?
Either of these can be done with a simple vba command perhaps triggered by a command button which the user can control.
Aug 12 '08 #12

Expert Mod 2.5K+
P: 2,545
Hi. As msquared said, toggling visibility can be done easily in VBA (each control has a Visible property that can be set true or false as needed, perhaps from an after-update event of a control or user selection (such as selecting the item for the order).

Your idea about storing the default price is a good one. From what you have said, the user does not need to see the default price field at all, not even on overlapped controls. The field's value can be set to the list price using the After Update event of whatever control you use to select the order item, or on some other event if you don't use a control to do this. You can then set the value of the actual price field to the list price - which the data entry user can accept or change as necessary (applying a discount to the list price, say). The record will still contain the original price in the unseen list price field for reference.

There are many ways to achieve what you want here, and I am sure you will come up with more ideas of how to do it.

On some of the other points you make, about table design and so on, I would not worry about having many small tables with few rows in them - there is no penalty in doing so with current generation software, so do whatever you think is best for other maintainers who may follow you. I once had to junk a database where the person who designed it did not use relations between tables to any extent; he imported Excel tables and used macros to populate the individual tables (running INSERT and UPDATE queries complete with joins between intermediate tables to do so). After trying to decipher what he had done - for more than a day - I gave up... it was just impossible to work with. The moral of this part of the tale is that there is no harm at all in having too rigorous a relational design implemented with fully-normalised tables - but there can be real problems when apparently reasonable shortcuts make it difficult for others to understand never mind maintain an application.

Please feel free to bounce other ideas here - I'm sure we could assist you further on the order pricing matters you mentioned after you've checked out whats best for you.

Cheers

Stewart
Aug 12 '08 #13

P: 13
Thanks for responding...

Hi. As msquared said, toggling visibility can be done easily in VBA (each control has a Visible property that can be set true or false as needed, perhaps from an after-update event of a control or user selection (such as selecting the item for the order).
That's exactly what I'm talking about the whole time! Of course I tried doing it from VBA, how else? (Sorry if I sound a little exasperated.) What I'm trying to say is that changing the visibility in VBA changes the visibility for _every_ row!

Your idea about storing the default price is a good one. From what you have said, the user does not need to see the default price field at all, not even on overlapped controls. The field's value can be set to the list price using the After Update event of whatever control you use to select the order item, or on some other event if you don't use a control to do this. You can then set the value of the actual price field to the list price - which the data entry user can accept or change as necessary (applying a discount to the list price, say). The record will still contain the original price in the unseen list price field for reference.
I definitely considered doing that. The problem with this approach is that if the price has been customized then changing the selection should not update the price, whereas if it hasn't then it should. The problem is with this method it is not known whether the price has been customized. Like I said, it's possible to use a third boolean field, but it's inelegant.

As far as table design, yes that is a perfect example of why splitting tables is necessary - so that the data should be coherent, in addition to wasting unnecessary space. Yes space is cheap nowadays but that's no excuse for developers to waste their user's space. But my point, as I mentioned, is that there are two extremes to every spectrum, and for subtle differences in records it's silly to split a table into many different tables. One should weigh the two sides of the coin in every situation. When there's a significant, basic difference between the types of data then of course, split it. But for insignificant differences it's simply not worth it. Sometimes you could end up wasting more space, especially if it's being multiplied by a large number of records (again, admittedly not the most important factor but it certainly should be considered); also queries are probably slower than accessing tables directly, so if your database already consists of a large number of very complex queries that are accessed frequently this might play a role. Again, usually negligible but just goes to show that to go to the opposite extreme and always split off any records that have any difference in which fields they use to another table.
However table design is totally irrelevant - the question was about varying the UI. Splitting a table and joining it back together doesn't accomplish that. Leaving them split and connecting them with a subform is a step in the right direction, but it's not going to help. Besides, you can't put a subform inside a continuous form, or nest them more than seven deep.
Aug 13 '08 #14

Expert Mod 2.5K+
P: 2,545
When you ask 'how else' could the visibility property be set than from VBA I was referring to the property being available (statically) for all controls through form (and report) design. It is not changeable dynamically except under VBA control, of course. My comment was framed in more general terms because other readers may not know that the Visibility property is available outside of the VBA environment.

Access is a remarkably powerful tool which revolutionised PC databases - before Access the tools available were relatively primitive, such as the then-ubiquitous DbaseIII and IV - but Access has limitations, and one of them is the way that continuous forms were implemented. Access was first released in 1992, and some of its limitations go right back to decision made in the original release.

Anyhow, we cannot fix the limitations such as controls being visible or not throughout the continuous form - we either accept that this is a limitation or find a different product to implement the functionality required.

On table design I do understand your point of view. I am an advocate of the use of relations between tables as defined by the originator of the relational database methodology, EF Codd, but even with the solidly mathematical base of set arithmetic underlying relational theory table decomposition in practice is more of an art than a science. There is room for differing opinions on this topic.

I do hope you can find a workable solution to your pricing issues that meets your needs.

-Stewart
Aug 13 '08 #15

geolemon
P: 39
I have a similar dilemma...
I had a bound form that was allowing the user to update values in dimension tables when the selection box was generated to simply provide him with his possible options - being "bound", what would have happened was that particular dimension table would have had the value updated!
Of course that's wrong behavior, as the corresponding key value is what really is desired to be updated in the matrix stored in the central "fact" table (an inventory in this case).

...but when I "unbind" the control (and attempt to cleverly populate it based on the value of the invisible/hidden "bound" version of the control), then when the user sets the value for one instance of the record, it changes the value for all instances of the subform. Difficult indeed...
Clearly these subforms are a different concept than "[control name]-instance-1", "[control name]-instance-2", etc...
in other words NOT really dynamically generated instances, despite their actually BEING dynamically generated instances, lol...

So my workaround was ugly, not elegant, wasteful even....but should work:

I created an extra "temporary data" column in each table, and bound my control to that column. That way, I can play all day without changing the "real" data in the dimension tables, and in my VBA script I can clean up after myself by emptying those columns across the whole table at the end.

Is this really the only way to get a control to work without really being "bound" - it requires it to be bound to something?
Aug 28 '08 #16

P: 13
I guess that's what's necessary. That's what it sounds like...
As someone mentioned, Microsoft is stuck to whatever design decisions they made early on. They can't break compatibility, and the alternative -- to provide for both behaviors -- is not worth it for them.
I really wish there was an easy way to bind controls in Java to a database. If you could bind a control just by setting a property I would go with Java any day over Access. There are way to many limitations in Access that make you feel like you're being treated with kid gloves. Besides that I don't like the rigid syntax of VB.
There are plenty of frameworks to connect controls to a database in Java, but the ones I've seen seem to involve binding the control to a Java object, and then mapping Java objects to a database. I guess that's good for complex applications with complex data mapping, but for an Access style program, i.e., a (relatively) direct graphical interface to a database, they're overkill. I wouldn't mind a simple framework with support for record navigation and synchronizing controls with records. Not data modelling -- table/query design -- just a simple way of creating powerful user interfaces that update the database directly.
Actually, I started writing such a framework, and as a barebones framework it functions okay. However it needs some direction -- ideas where to take it etc. If anyone is interested in joining the project let me know (e.g. via PM)!
Aug 28 '08 #17

NeoPa
Expert Mod 15k+
P: 31,438
I have a similar dilemma...
...
You have asked your question in another member's thread. This is called thread hijacking and is not allowed on this site. There are various reasons for this, the principal of which is that it typically diverts the flow of the thread away from their needs and into yours. Another is that it will tend to mean that your new question is not seen as such, and may get overlooked by many of our experts (You appreciate that they're not looking for answered questions).
Please post your questions in their own threads in future (See How to Ask a Question).

ADMIN.
Aug 28 '08 #18

geolemon
P: 39
My apologies...

I happened upon this thread, which did NOT have the original poster's question answered, and had NOT had any activity in a long time.

I thought I was contributing to this thread as I had a suggestion that was not yet mentioned.

So my workaround was...
It's coincidental that my problem is the same as his, and doesn't seem like a thread "hijack" that I'm now an additional person interested in solving this same situation...

I certainly can appreciate the top-of-forum value of starting another thread, but my experience with other forums is that moderators usually are shouting at users to "use the search function" to avoid posting the same questions over, and over, and over...

My apologies, I certainly didn't intend to thread "hijack", and I think if you look at my post a little more closely, you'll see that I didn't... But I'll keep aware in future threads.
Aug 29 '08 #19

NeoPa
Expert Mod 15k+
P: 31,438
My apologies.

You are absolutely right in that I DID skim over it too quickly.

Please ignore my last post, and Welcome to Bytes!
Aug 29 '08 #20

NeoPa
Expert Mod 15k+
P: 31,438
I find I have to revise this again.

I reread your post and it did offer a solution of sorts. In that sense it is appropriate to include in this thread.

In view of the clear way you express your position, I'm happy to take a little time and effort explaining further the reasons why we discourage posters from adding their own questions in a Q&A thread.

There are two major reasons :
  1. If there is no expert interest (and I use the term loosely to refer to anyone willing to offer a solution) currently in the thread then the question is likely to garner less interest than a newly posted question might. This will often add to the complexity of the situation too, though not always.
  2. Your newly stated question may attract the interest of someone, and the topic of the thread may be diverted to your interests rather than those of the OP (Original Poster). We don't feel this is fair and generally try to protect the OPs from such.
You mention the OP hasn't received an answer to his question which technically isn't true. There is a correct answer in post #2. The OP simply didn't have the experience and understanding to appreciate that Microsoft and other experienced database users don't have his problem understanding the reasoning behind why this is so.

I mention this because from the point of view of most of us who have contributed to this thread, there is really no more to say. There is clearly no compunction about it either when the OP has exhibited such appalling manners anyway. In this situation I doubt your extra question will get much attention anyway.

I will say that I appreciate the consideration you have obviously given to the situation before posting.
Aug 29 '08 #21

FishVal
Expert 2.5K+
P: 2,653
... However, the user (the salesman) has the ability to modify the price for a specific customer. So instead of being $5 + $5 = $10, he will change the $5 to $4.50, and then the total will be $9.50.
The complication is that it should not forget the original price. Tomorrow the price for the first component of the order will change from $5 to $6. Also, he will find out that the customer does not get a $0.50 reduction on that component. So there has to be a way to revert to that $5. On the other hand, if he switches component A to some other item which is $5.50, so if the price had been customized it should not change it. However if it had not been customized then it should change.
The logical way to design the table is with a field holding the real price, and a nullable field which can be used to override the real price. However there is no reason for the user interface to show to columns of fields. It should show only one field, which behaves with the aforementioned logic.
Some ways of implementing this which seem to be impossible, is having the two text boxes superimposed one on the other and toggle their visibility. Or to have a check box which toggles the control source of the one text box. Ideas, anyone?
Hello, NaftoliGug.

I'd like to add some points to the discussion:
  • From what you've said I understand that you prefer to store orders totals instead of storing each order item immediate price. Though it is generally considered as a bad practice, sometimes it is needed because of performance reasons. Even this case there is no matter not to store item immediate price at all. This will eliminate your interface design problems and obviously make your database stronger as for auditing and data backtracing.
  • Though it has become a "good coding practice" to complain about M$ dreaded software, IMHO everything (even if you are coding on asm) has limitations. ;) I am almost sure you will not create a robust solution via unbound form control, because ... because Bill Gates doesn't want it and you are not able to force him. :D
  • A simple solution in your case could be creation of additional table field to backup original price before form is opened and restoring original value when it is closed. Also you may backup original value via TextBox.Tag property.

Regards,
Fish
Aug 31 '08 #22

geolemon
P: 39
I find I have to revise this again.

I reread your post and it did offer a solution of sorts. In that sense it is appropriate to include in this thread.

In view of the clear way you express your position, I'm happy to take a little time and effort explaining further the reasons why we discourage posters from adding their own questions in a Q&A thread.

There are two major reasons :
  1. If there is no expert interest (and I use the term loosely to refer to anyone willing to offer a solution) currently in the thread then the question is likely to garner less interest than a newly posted question might. This will often add to the complexity of the situation too, though not always.
  2. Your newly stated question may attract the interest of someone, and the topic of the thread may be diverted to your interests rather than those of the OP (Original Poster). We don't feel this is fair and generally try to protect the OPs from such.
You mention the OP hasn't received an answer to his question which technically isn't true. There is a correct answer in post #2. The OP simply didn't have the experience and understanding to appreciate that Microsoft and other experienced database users don't have his problem understanding the reasoning behind why this is so.

I mention this because from the point of view of most of us who have contributed to this thread, there is really no more to say. There is clearly no compunction about it either when the OP has exhibited such appalling manners anyway. In this situation I doubt your extra question will get much attention anyway.

I will say that I appreciate the consideration you have obviously given to the situation before posting.
I understand, and thanks for taking the time - I do understand your position.
And I wouldn't want to be bad mannered or supporting it! But I do understand the frustration, myself coming from non-Access database environments and struggling myself to figure out not only the limitations of Access but actually more trouble navigating where I'm used to having clear Database/connectivity/UI or OLAP delineations.

It wasn't suggested that to create a bound-but-blank control (since "bound" is a requirement of "per-row results") it follows that a blank column (aka field) would be required - so I thought I'd toss that out there.

But - enough, I'll let this thread die, and I'll create a new one, since I've actually been pursuing another alternative... maybe the OP will find something in the new thread, if he's still looking for an answer - and I'll be following wise advice to attract more/direct attention to my current state.

Thanks, and my apologies for re-bumping a painful thread!
Sep 3 '08 #23

NeoPa
Expert Mod 15k+
P: 31,438
Frankly, I don't believe any of your posts indicate lack of manners. Far from it. It's been very straightforward dealing with you.

I see you haven't yet posted the other thread, but when you do I'll try to pop in and give it a look at least (I can't promise I'll be able to help mind you).
Sep 4 '08 #24

P: 1
I have come across a way of making unbound controls take calculated values that are different for each record. In the datasource for the control type :

'=fnFunctionName(parameters)'

The fnFunctionName() would be a VBA function in the form module for instance.
Note: if you want the function to process values from other controls then simply place the names of these controls in the parameter list. I have found that accessing the other controls within the function code does not work. I think putting them in the parameter list informs Access what order to process the controls.

Enjoy.

Certainly makes life more easy.

ali
Feb 10 '09 #25

Post your reply

Sign in to post your reply or Sign up for a free account.