Form Field Reference in RowSource

[gray]

Hi

I've had grief with this before but even referring to my previous notes has not helped...

I'm building an SQL string for the rowsource of a combo. This needs a reference to a field in it's own host form. The recordset for that Form has joined tables and the participating tables have common field names... consequently table aliases are present e.g.

TBL1.ID, TBL1.Item_Name
TBL2.ID, TBL2.Item_Name
TBL3.ID, TBL3.Item_Name

etc

How does one reference a field in a form's Rs that has aliases please?

wxyz=[Forms].[My_Form].Form![TBL1.ID]
wxyz=Forms.My_Form!TBL1.ID
wxyz=Forms!My_Form!TBL1.ID
wxyz=Forms!My_Form![TBL1].[ID]
wxyz=Forms.My_Form.Form!TBL1.ID
wxyz=(Forms)!(My_Form).TBL1.ID

..... plus a hundred other attempts!.... thanks


My form has

 

[spikepl]

You presumably mean " a reference to a CONTROL in it's own host form." - check what the names of the controls are

 

[boblarson]

As spike says, you are looking in the wrong place. You reference the CONTROL name and not the field name. The binding of the control in its control source handles the interface between the control and the field, so you need not worry about it.

 

[gray]

Hi

I've tinkered a little more with this ... I think this seems to be the case:-

1. When there ARE joined tables but the individual field is NOT common to more than one table, both notations can be used:-

MsgBox Forms.My_Form.Form![Address_Line1]
or
MsgBox Forms.My_Form.Form![TBL1.Address_Line1]

2. When there ARE joined tables and the individual field IS common to more table, this notation must be used:-

MsgBox Forms.My_Form.Form![TBL1.ID]

3. When there ARE joined tables and a FINDFIRST is being done then use this notation:-

rst.FindFirst "TBL1.ID=" & Current_Unqe_No

i.e. No brackets

4. When tables are NOT joined but an alias is still employed in the form RS, use:-

MsgBox Forms.My_Form.Form![ID]

the following does not work despite there being an alias,

MsgBox Forms.My_Form.Form![TBL1.ID]


So, no confusion there then .... actually I guess it is consistent with using the QBE.

cheers

 

[spikepl]

DId you read my and Bob's posts ? The references are to CONTROLS. So check on your form what the names are. The first one may well have the same name as the field in the record set, but the next one will have Text123 and such like

 

[boblarson]

Gray -

I've noticed that once you get on a path, it is very difficult for you to see anything but that path. spike and I both have told you to concentrate on the CONTROL names and NOT the field names. It DOES NOT MATTER if the same field name is in the query with two different tables. You do NOT need to refer to them if you are trying to use a reference to a control on the form. The control would be bound to the field in question so that is solved. It is the CONTROL NAME, CONTROL NAME, CONTROL NAME that is important.

 

[gray]

HI

I always try to read them most carefully gents ... it would be rude not to when someone has taken up their time trying to help.

All my control names comprise of field-name suffixed by control-type so.... for ID, ID_TextBox - this removes the possibility of controls being confused with fields. In this particular case, I needed to access items in the RS that were not necessarily bound to a control.

If I understand correctly when referring to form controls the notation uses a "."
e.g. Forms.My_Form.ID_TextBox
but when referring to a field, the notation uses "!" e.g. Forms.My_Form!ID
In this way fields not bound can be accessed?

You presumably mean " a reference to a CONTROL in it's own host form

Wasn't the case, I was deliberately trying to access fields.

Cheers..

 

[boblarson]

Wasn't the case, I was deliberately trying to access fields.

Again, I have to ask Why? This is another case of you doing something which is not the norm in Access and everything you have posted so far, from your saving of filters to this has been going places which seem to need great workarounds to do something that is a very simple thing to do. Why are you trying to reference the field of a form instead of the bound control? Are you saying that the field you want is not on the form, but only in its recordsource? Again, the fact that you have a query trying to reference a field which is not on the form comes back as very suspicious to me as far as a very strange thing to be doing.

So, I apologize if I, in any way hurt your feelings with this, but frankly I would hate to be the developer that comes after you that has to try to figure out your stuff. Like I said with the filter stuff, what you were doing was confusing as hell and it didn't need to get that complex. It is almost like you are trying to find the most complex solution to a problem and ignoring trying to find simplicity in architecture of your code and all.

 

[spikepl]

I second, and third, what Bob is saying. I do not know how much software development you have done so far, but in my experience, any "smart" thing ceases to be smart, and becomes a huge pain in the derriere, when you have to go back to your own code 6 months later, and cannot figure out the why's and how's anymore. In other words: any deviation from any standard features needs at least some in-code documentation to explain the "smart" thing, if you insist on it.

I prefer the KISS-principle. It may not be the most efficient, but at least I have a chance of re-using bits and pieces without trying to puzzle out what goes on, and remembering all the necessary supporting conditions hidden in other corners of the code. And nowadays, computer power and storage is nothing, compared to development costs.

 

[gray]

Hi

Thanks for the feedback. No... my feelings are not hurt ... but my heart rate has risen over the past few months!

I set out some time ago with a handful of, what seemed to me, very un-ambitious aims. Believe me, at no point did I intend to drill so deep into Access! Use built-in features was my mantra! I've now drilled so deep i think I spotted the Higgs-Boson!

I'll say straight away that I came into this as an Access novice and I accept that a lot of problems were my failing rather than the product. I'm truly grateful for the invaluable help I've rec'd from this forum without which I would be playing a harp on a cloud by now (or maybe somewhere warmer?).

A few notes follow as to my arrival at this thread.. treat them as a cautionary tale for noobies, treat them with disdain or dismiss them altogether... they are nevertheless a journey thru' Access as seen from my novice eyes. I'm happy to be contradicted/educated on any of it but most of all I'll be happy if it helps others avoid my pitfalls.

My Requirements
1. The ability to cancel a new record or any given update.. be it a quotation, change of address, invoice or whatever.

2. Custom forms with custom buttons for New, Edit, Save, Cancel, Copy etc. The idea was to put something together for novice PC users. Disabling most of the things experienced Access users might use (toolbars/menus etc) was a requirement.

3. Ease of navigation. To display records in a datasheet fashion with the ability to use the above buttons against any given record.

4. 'Reasonable' filter and sort capability. This, to me, meant staying with the built-in features.

5. To consider multi-user use during the build - this really boils down to preferences and record-locking.

1. Cancel Requirement
a) I was introduced to the BeforeUpdate Event and Cancel=True. However, It simply did NOT work in my project! Neither did it fail (which would have been more useful). Access always committed changes. Impossible I hear you say? See f).

b) Research led me to try disconnected recordsets. However, my tests indicated that, under certain circumstances, changes in disconnected-RSs did not get committed accurately. If I recall, an edit followed by an addition, a deletion and then a cancel of the deletion confused the 'cursor'. Thus, when the recordsets were sync'd UN-deleted records disappeared... apologies for the double negative construct.

c) When I learned it was possible to change the recordsource of a form, I scrapped the above scheme in favour of local (username-stamped) temporary tables..
So...
->User presses edit
->Load selected details into temp table(s)
->Set Form to use temp tables
->User carries out tasks
->Save back to permanent tables or throw away
->Reset Form back to permanent tables.
All pretty usual stuff..

Unfortunately, the management of this became bedazzling and especially so when trying to reset forms at startup. I could not figure out for the life of me why Access was telling me a Form could not be found when there it was sitting in the navigation pane. Cannot be Found?? A prompt of "you need to open this form before you can edit it" would have proven more useful because this spun me off into all sorts of directions trying to understand Access' object model and forms referencing.

d) To lose the above overhead and de-risk it, I scrapped it in favour of the use of temporary fields within permanent tables. So...
-> Re-point controls at temp fields
-> Take actions
-> Copy to permanent fields if "Save" is pressed.
-> Re-point controls at perm fields

Far easier to manage but requires record-locking to ensure 'ownership' of the temp fields. I had intended to use Access' built-in record-locking.

e) After a few days I could not quite figure out how to use the built-in locks to implement d) effectively. I was rather expecting to find :-

"LockRecord(Record=123,Table=XYZ,Lock=lock/unlock)"?

I could not so I wrote my own... this is significant later.

f) At about this time I occasioned across a thread using Cancel=VBTrue in the BeforeUpdate event. I tried this in my Project and, lo, this version of the truth worked!! errr.... =True/=VBTrue? ..... Still, we all live and learn. I was now able to scrap all the temp/perm shenanigans because I now had a working cancel model.. brilliant I can crack on... or so I thought.

2. Custom Forms, Subforms and Buttons
a) Forms Crashing - Whilst designing my forms I was continually hampered by crashes. Merely clicking a control on a form whilst in design mode crashed Access...5 to 10 times a day!... Worse still, despite pressing "Save" regularly, my form design and VB changes would be discarded... uhh?. I presume Access re-opened itself at some checkpoint taken before my last session of changes? I invested several weeks trying to get to the bottom of these somewhat irritating crashes.

During this investigation, it was speculated that the I had exceeded the number of controls on a form thus causing the crashes in design mode. This seemed reasonable but my forms had about 90 controls max. I discovered this was a 'lifetime limit': incremented upon addition of controls but not decremented with their deletion? Interesting concept! Thinking the limit was 255 (but later found it to be 700-odd), I thought it was possible I had exceeded it and especially given all the form design changes. I began re-importing forms into new empty DBs and completely rebuilding the 'busier' forms. All to no avail. It still crashed regularly.

b) Subforms - One possible cure was to use subForms thus reducing the number of controls on any given form. I researched them, considered them useful and implemented a few. This is how I arrived at subforms. I had not anticipated the host of new challenges with them.

c) Subforms and zero records - One day I was testing filtering (the right-click, built-in filters) when my form aborted. Several days later when trying to figure out what was happening, it occurred to me that, when the filter returned 0 records, Access unloaded the subform; subsequent references to it then caused errors. I dismissed the idea: a form crashing because of zero records seemed to me to be incredibly unlikely? Nevertheless it is so!

I saw on a site advice to use AllowAdditions=True for zero record conditions. Great... but my attempts to detect zero records in a subform continually floundered. Using the recordcount to test for 0 records failed, of course, when there were 0 records (derr..?). At one time I had about 6 nested IF statements employing all sorts of tricks trying to check the status of a subform. Access' own very promising "IsLoaded" turned out not to work on Forms...shame.
Eventually, I found a handy utility someone had kindly written. This cycled thru' all the forms collection and then all of the controls on those forms to verify if a form was loaded. I must say it seemed somewhat processing-expensive but c'est la vie, it worked and I thought my subform problems were over.


c) Cascading comboboxes (the reason for this thread). I was surprised to learn that these do not work in datasheet view particularly given that they are one of most widely used controls on user interfaces.

Being unaware of this, most unlikely, restriction, I had built a good section of my project with them! Experienced Access developers might understand that datasheet view is not quite what it seems but the inability to use cascades was ... err.. a shock?

To get around this, I placed code in the ChangeView event to re-jig certain combos to use DLookups when in datasheet view. I added code in double-click events to produce pop-ups whereby they could be edited. Unfortunately, I had now stumbled into a filtering and sorting minefield.. see 4 below.

d) Custom buttons - Having taken up use of Cancel in the BeforeUpdate event I now hit another problem. Cancel=VBTrue did not work quite as I had expected. The cancel prevented the focus being moved from subForm to mainform when a change had been made and so rendered the custom buttons (sited in the mainform header) utterly useless. Equally, it was no good re-siting buttons onto the subforms because form headers disappear when in datasheet view. Where to go from here...?

e) SQL Transactions - After some considerable experimentation I got these to work and to work well. I scrapped the Cancel model re-enabling use of my custom buttons. Sadly, this time the mist had obscured a cliff edge...one where all sorts of recordsource errors lived... I think they relate to the use of workspaces.. I've parked that for now.

3. Ease of Navigation
a) Datasheet view - The most useful view of all IMHO; easy to sort, easy to navigate, easy to increase decrease column widths and/or change the order of columns. An excellent feature. So I was surprised (once more) to find how difficult it was to have a mainform (required for buttons) co-ordinated with, let's call it, a 'primary'subform in datasheet view listing all the same records . This, it seemed to me, was the most useful way to use a most useful feature. I had seen on Msoft's site that it was legal to have two forms using the same recordsource or even recordset. The one drawback, the potential to make the RS non-updateable, was not an issue for me because I had my own custom editing model.

As we know split forms are now available to do this very thing so I don't think I was barking up wrong trees?

After some very useful help from a forum contributor (who helped with co-ordinating the record pointers) I got this working and working well. There were other drawbacks looming however.

4. Filtering and Sorting
a) Comboboxes - The built-in filtering and sorting works well but seems to flounder with comboboxes that have been populated by SQL Select statements. When a user elects to sort using one of these a "Lookup_" style OrderBy is added to the form. Although, earlier, I had resolved the zero record problems they re-introduced themselves when "Lookup_"s appeared in the OrderBy. Forms began failing again. Also the Dlookups used in other combos rendered filtering and sorting inconsistent.

It was at this time that I learned about the ApplyFilter events. With these I discovered that in zero record conditions, even with AllowAdditions=True, references to the subform aborted when "Lookup_"s were present in the Orderby property. To prevent this I intended to capture the "Lookups_" and kill the filters until I'd had chance to deal with the problem.

Once more I was in for a surprise. I discovered that killing filters was not so easy. Setting me.filter="" on a form does not always get saved (irrespective of acSave settings when closing the form). As such I had to write code to store the latest filters and re-apply them upon opening of a form... or any "Lookup_"s present from a previous session could prevent the form from opening.

Having sorted this out, the previously well-behaved pointer co-ordination was kicked into touch. The presence of "Lookup_"s in the OrderBy caused the subform ApplyFilter event to fire every single time another record was navigated to.. in fact it returned the pointer to the first record in the RS and that's where it stayed!

The Present
I'm collecting OrderBys, building SQL with them and applying them to the ORDER BY clause of the Form's recordsource rather than Orderby property. Thankfully this has stopped a number of the issues I was experiencing.

This leaves me with the problem of Dlookups employed in the cascading (or rather non-cascading) combos on datasheets. I thought I might be able to fudge them by either i) using a subquery in their FROM clause or ii) by some nifty table JOINs... and that, gents, is where this thread came from.


Fields vs Controls
Sorry but I have to disagree with you here.. I can think of many cases, including my own record-lock fields from 1 d) above, where one might need to inspect or update a field that would be completely meaningless, confusing even, if exposed to an end-user. Given my experiences at 2 a) adding controls was to be avoided like the plague and especially if access to the field can be gained by other means.


Any Other Gotcha's ?
Watch out when using dates. I had a report working happily until one day it produced unexpected results. We use dd/mm/yyyy in the UK whereas other locations use mm/dd/yyyy. Being aware of this I checked things thru' carefully... again I could not for the life of me work out what was going wrong.. I looked at table and control formats.. all looked well.. I ran queries to check date formats and to ensure GT, LT all worked OK etc etc but, it seems, there are certain Access SQL functions that ignore local settings and assume mm/dd/yyyy format no matter what... one's tests might easily be using the required format but the code in the report another... and there a no months greater than 12 in the gregorian calendar to my knowledge.


How Much Development Work Have I Done?
Let's just say... quite a lot...

Thanks for reading.. if you got this far!