Most popular unanswered question in this forum...

[Pat Hartman]

Each control has over 40 properties. That's what the third dimension is. Field, property, row. I thought you were calling properties, identifiers so my statement wasn't clear.

Rebuild because there is nothing in your array that coorelates to data. It is all positional. And once you sort the data, you change its position. Row 1 is not the same record it was before so the control array no longer relates to the data.

 

[jdjewell]

Not so fast my friend...

Rebuild because there is nothing in your array that coorelates to data. It is all positional. And once you sort the data, you change its position.

In VB, you can get the number of elements in the control array. You then can iterate through each row (albeit that row 8 might be first and row 1 might be last now) and perform your operation on the row, because all of the other information for the record will still be associated with it. Maybe this isn't making sense.

Take this example:

[b][u]Name[/u]  [u]Address[/u]       [u]City[/u]      [u]State[/u] [u]Shipping Covered[/u][/b]
Adam  118 Easy St   Elinwood  KS
Brad  1018 Main St  Boston    MA
Chad  804 E St      New York  NY

As we output each line, we can evaluate whether a checkbox in our "Shipping Covered" should be checked or not. Our logic could look something like this

for i = 0 to uBound(txtState) 'txtState is the name of the control array for the State
  if txtState(i) <> "NY" then
    chkShipping(i).value = 1 'checked
  end if
next

You see, it doesn't matter if we re-order the rows, since each control for a given row will have the same identifier, and we will always be able to perform our logic for any given row.

This would work pretty much the same way on the web, since we would be associating all records in a row by a single common identifier.

So, let's recap:
VB: Can
Web: Can
Access: CAN'T

 

[Rich]

Funnily enough we do this in Access with an unbound control without the need for a slow array=Iif([State]="NY",True,False)

 

[jdjewell]

Can't...

Where do you think you would place that logic? It will, at best only evaluate the first row of output and then make all rows the same (either T or F).

The only way I have found to get around these issues in Access, is to handle it in the SQL of a query, and then base your tabular form off of the query. To me, and this is just an opinion, I shouldn't have to do a bunch of hocus pocus in the 100th revision of a piece of $300 software to make something work, that works simply in other environments (vb and web).

 

[Rich]

"Where do you think you would place that logic? It will, at best only evaluate the first row of output and then make all rows the same (either T or F)."
Like I said in the control source of an unbound check box which is in the detail section of your form, Access is so poor you don't even need the field State on the form, only in the underlying record source.
Try it and see

 

[jdjewell]

You're reply should have said "CAN"

I stand corrected. That worked...but back to Graedus' post, is there in effect a way to enable or disable a bound or unbound field (on each line in a tabular form), based upon a value in the record...the answer is still:
VB: Yes
Web: Yes
Access: NO

...sorry Graedus, some day the developers for MS Access will figure out how to put some more rudimentary functionality into their program. I don't know how to do it, nor how long it would take (off the top of my head), but you might be able to emulate the clicking of each record selector in the details section and put the code in Form_Current -- as discussed before -- that would take care of it (not very straightforward, I know). Outside of that, I'm at a loss for a clean solution in Access.

 

[Rich]

You miss the point on that one too, for users to edit a field they have to enter the field, the form current will then decide the validity of the control

 

[jdjewell]

Back to previous...

Your last post is true, but on the previous subject:

Like I said in the control source of an unbound check box which is in the detail section of your form, Access is so poor you don't even need the field State on the form, only in the underlying record source.

I just tried it, and it doesn't work. I will attach the database for your viewing.

 

[jdjewell]

Sorry for confusion,

When I previously said it worked, I only tested with a textbox, and I changed the control source (like you suggested), and I saw the value come up appropriately (-1 or 0). I forgot to check it on a checkbox, to see if the checkbox was enabled and disabled accordingly. When I tried it, it didn't work, so I made the last post and attached the .mdb.

 

[Rich]

I don't have your version so I couldn't check yours, here's a very quick example, I'm looking for three cities, Rio, Madrid and London

 

[jdjewell]

Impressive

O.K., so I should be able to see this working as well, but like I said, my db doesn't work. Yours works smoothly. Here is the same db that I attached before (I converted to prior version). See if you can open it now and make heads or tails of it.

Thanks!

 

[Rich]

You forgot the equals sign
=Iif etc

 

[jdjewell]

Thanks!

I'm not completely sold on the clicking into the record, and then having it enable/disable (graedus' question)...if a user wanted to be able to visually see enabled/disabled without clicking the record, problems arise. But, I digress, the fact remains that you have shown Access to be a little more viable than I originally thought.

 

[Rich]

You can use the same method for a textbox as the checkbox to display a visual indication of the record status

 

[The_Doc_Man]

I'm going to open up this can of worms here yet again. When you speak of Access YES or NO, Web YES or NO, CRYSTAL YES or NO, etc. - your scorecard is meaningless. It is an apples vs. oranges vs. kumquats comparison. Would you expect the same final scores from a pro hockey game, a pro baseball game, and a pro basketball game? No, they are all different.

Pat suggested that you THINK about what you are doing. It appears that you really did not, or that you don't understand the underlying logic that has to occur in Access to do other than what it is doing now. You are not necessarily wrong to want other things done in Access, but you have to understand what it is that you are asking to have happen. When you open up a continuous form, THIS is what you are doing:

You open a pointer into a recordset for the form. That pointer allows you to see the contents of ONE record. The fact that you can show other records when in continuous mode is immaterial. Only ONE of them is current because Access is record-stream oriented and uses a single-pass algorithm to get there most of the time. The events that fire can only apply to the current record - because that is the Access form model. And the Access sub-form model, too. So if the next record in the continuous sub-form display does event-oriented self-modification of its properties, its events have not occurred yet because the record isn't current yet. The data displayed in non-current subform instances is STATIC.

In the event model, you work on the CURRENT record when you work at all. There is NO SYNTAX in Access - or, at the other end of the scale, even in ORACLE - to say "While I'm working on XYZ record #2, show me the contents of XYZ record #3" while working with only one recordset. To see another record, you need another recordset (or data cursor or pointer - same difference, different nomenclature).

The SQL model is based on this crucial concept. Let me spell it out for you .... SEQUENTIAL Query Language. You do everything in the query ONE RECORD AT A TIME in the exact sequential order imposed by either the record stream's primary key or the SQL ORDER BY and/or GROUP BY clauses. But it is ONE RECORD AT A TIME. "Last record" and "Next record" issues DON'T EXIST. That would be PQL, PARALLEL QUERY LANGUAGE. Don't hold your breath waiting for that one to come around.

So why, then, do you expect a dynamic form in continuous mode to violate the SQL concept? In truth, the REAL solution to your problem (which you wouldn't like) is to take away "Continuous" mode for sub-forms. That way, you couldn't kvetch about the fact that the non-current images of the subform don't conform to your views of what they should look like. If they aren't current, they don't mean anything. They mislead you, and THAT is where you went wrong. You assumed a significance that just doesn't exist. Do I have to explain the problems inherent in the word "assume" ?

Now, before you go nuts because you think I'm trashing you, let me tell you that I'm not. I'm trying to explain this in a way you can understand, because you apparently didn't catch on to Pat's explanation.

The fact that a Web page can do this and Access can't is because a web page isn't conceptually the same thing as an Access form. Would you expect a document written in MS Word to exhibit this behavior that you have described? No, you wouldn't, because there you already have the mental image of the different thrust of what the Word application represents when compared to web pages or Access. But you have incorrectly given yourself a mindset regarding how close Access and Web pages should be to each other because they can, at least superficially, produce something that looks similar to what the other can produce.

The ability of a web page to show you multiple format variations of the same subform in continuous form replication is because, as Pat has tried to explain to you, the data flow of a web page and the data flow of an Access form are NOT THE SAME. A web page is a REPORT that happens to have certain active areas in it IF PROGRAMMED THAT WAY. It ain't magic. You have to do the syntax things that link back from the spot on the page to a corresponding record. No back-links or hot spots, no dynamic action. All static stuff. If you build in a hot-spot, you can go back an do something to that record. But web pages do that because that is THEIR data flow model.

In order for Access to do subforms in continuous mode individually, there has to be a dynamically created context for each instance of the subform, cloning recordsets once for each context, so that each instance of the subform can react to its individual context. Now, when you use the record navigation features to change the set of records visible in the subform area, EACH SUBFORM INSTANCE HAS TO FIRE OFF ALL EVENTS as a new record becomes current. Which means that you have to synchronize each subform instance with requeries just in case you made a change on one of the subforms. It has to propagate to its sibling instances depending on which way you navigate. That is not a trivial thing to program.

What do you do if an event in one of the subforms wants to update a record in the act of releasing it? (For example, if you are tracking "record touches" analogous to a web page's hit counter.) Now the question is, if you navigate forwards in the recordset do you propagate events from top to bottom in the continuous window? Do you switch that to bottom to top order if you navigate in the backwards direction? Does each subform instance's entire sheaf of events have to fire to completion before the next instance's events even begin? If the subform happens to do something to another record in passing (a technical violation of the SQL model), do you have to re-fire the events in the sub-form instance showing the affected record? THIS is the can of worms you open when you ask for continuous mode subforms to be a bit more dynamic.

I'm not saying it can't be done, but I wonder if you realized just how complex a thing you are asking. And you kvetch about what Access does for $300? Hell, the previous paragraph is a conundrum that cannot be easily answered for $300,000! Try to do the above with ORACLE Tools - without writing your own special event handlers. At our site, the price tag for our ORACLE license was actually $800,000 - and it STILL doesn't do it any differently on forms than Access does. ORACLE just shares better and works faster because of the mainframe-class machine on which it runs. But the data-flow model is exactly the same.

 

[jdjewell]

Not as long-winded

O.K., you said a key word in all of that..."assume". You assumed that I am working with subforms in continuous mode, and directed at least 50-60% of your technical lecture on that subject. I am not dealing with a subform in this instance whatsoever.

I understand the difference between Access and the Web, I still have a right to be pissed that Access doesn't have more functionality. It would take little more effort for the programmers at MS to give Access the ability to know which record (from the tabular form's recordset) is being operated on (form_current) and update that record when changes are made (this functionality is covered, we all can agree upon this); and, in addition, have the form be built dynamically rather than statically pointing at a recordset.

Just because the number of rows varies when the form appears, because the recordset will return a different number of rows at different times, doesn't mean it is working dynamically. It means what you said before, that the form is merely a static pointer to a recordset. If the form built itself dynamically, there would be an event associated with the processing of each rows data (which doesn't currently exist) before it was printed.

My gripe is, that I am working on a project for a guy, and he initially wants it done in Access, then migrate to the web (and possibly SQL Server). I can't tell you how many times he has asked to do something on this tabular form, and it either can't be done, or the code-around because it is access requires 10x the amount of time as it would take to do on the web. None of the requests he has made will be impossible on the web, and by-and-large, they will require much less time.

I know the last paragraph is going to piss you off again, because I draw comparisons between Access and the web. Sorry, but that is because of what I said earlier, and what my gripe has been: if MS would make changes to make their forms to be built dynamically rather than static pointers, my world would be a better place.

As for your $300,000 problem that you think this would be, I can build the exact program I am talking about in less than 100 hours (with VB), guaranteed. Let's say I make $100/hr (I wish), that's $10,000 -- 1/30 of your estimate.

There is no need to assume that I have not been able to understand Pat, Rich, or your comments for that matter. I know my head from a hole in the ground, and I am not the only person who believes that Access is not the be-all, end-all for any problem. You said you weren't trashing me, well, what sort of response do you expect the following comment to get?

Pat suggested that you THINK about what you are doing. It appears that you really did not

P.S. I hope you don't truly think the S in SQL means SEQUENTIAL, and that you were trying to parlay a point that you thought was lost on a student; but, just in case you really didn't know, it stands for STRUCTURED!

 

[The_Doc_Man]

On a simple point, not everyone agrees that SQL is "Structured Query Language." For example,

http://hea-www.harvard.edu/MST/simul/software/docs/pkgs/pgsql/glossary/glossary.html

Harvard thinks it means Sequential Query Language. The "Structured" name is a newer formulation of something that had been around for a long time with its older name. I believe that after Edward Yourdon's work on structured programming, and after a few other seminal articles in various journals, the term "Structured" became a mantra for marketeers, hence the current (incorrectly applied) name of somethat that conceptually hasn't changed one iota since it was designed. Bells and whistles, yes. Extra data types, yes. Newer standards, yes. Base concept? The old finite automaton that operates on the "sequential tape" model of data flow. Still a sequential treatment of data access/manipulation.

As to the other issue, your problem isn't with Access. It is with a manager who knows not what he asks. In THAT, I commiserate with you deeply, for I have a similar situation. I work for a branch of the U. S. Goverment (indirectly through a contracting company). Many times I have been requested to do the impossible by the unknowing to mollify the unyielding into seeing the unrecognizable. In other words, communication at its finest.

As to whether you are in main forms or subforms, you are in continuous mode, which was explicitly stated somewhere earlier in this thread. And my arguments do not change on the fine point of main- or sub-forms in either continuous or datasheet mode, both of which have similar problems.

I still have a right to be pissed that Access doesn't have more functionality.

And I will join you there, though I have had other functionality in mind at different times, such as table-level events so I could implement a TRUE field-level data audit facility.

If you took offense in my response, I will first say that sometimes my writing style has that effect. Comes from having to deal with government pseudo-techies too often. You HAVE to get a little pedantic in order to convince them that you knew what you were trying to explain. But you are right, I did assume something about what you did or didn't know. For that, I apologize.

Now, as to how you solve your problem...

You can't. But perhaps you can change the NATURE of the problem by doing some of the work in the query that feeds this datasheet or continuous form view. You have objected to that, but it is not a wrong approach to think along these lines. On most modern machines, you have far more CPU speed than you do I/O speed, so your system throughput shouldn't suffer if you do some manipulations in the query. And if you can get close enough in the query to do what you wanted, isn't that really the end goal?

 

[jdjewell]

Means to an end...

I think we're both on the same page. I was only griping about putting logic into the SQL on my query that feeds the datasheet, versus us being in the nth iteration of this software, and our lives don't seem to be getting incrementally easier with each version. But, yes, it is a means to an end to do what can be done in the SQL. I have told my client of the limitations, and explained we will be alright when we put it on the web.

I think he is o.k. with it now, but he is the kind of guy that doesn't like to hear that something is "not possible." I don't like to hear it either, but the kinds of projects and problems I've done lately...I've grown more and more accustom to accepting that somethings cannot be done within certain environments.

Thanks for all the comments and if nothing else, your first post (Doc_Man), helped me to think about my problem a little more conceptually.

 

[Pat Hartman]

Well said doc. I simply did not have the time or the patience to take my explainations to that level for someone who was more interested in ranting than thinking. Sadly, I think that jdjewell still does not understand the apples vs. oranges vs. kumquats point. Hopefully, other people found the discussion enlightening so I'm sure that it was not in vain.

 

[jdjewell]

Takes a lot of intelligence to say 'ditto'

Pat,

1. I don't know why you would say I still don't get it (as though I didn't get it before). Maybe you should have read my response and Doc's response back before writing this article.

2. If his p.o.v. was what you actually felt, you would have spoke up. You aren't mime, you don't need someone else to talk for you because you're only capable of saying "ditto."