Replies

In case anyone should be interested in the code (stored in the module), which, I presume, is moreless what HansV suggested:

‘Sort active form ascending using active control
Public Function ffrmSortAsc()
Dim frm As Form, ctl As Control
Set frm = Screen.ActiveForm
Set ctl = frm.ActiveControl
frm.OrderBy = ctl.ControlSource
frm.OrderByOn = True
End Function

‘Sort active form descending using active control
Public Function ffrmSortDesc()
Dim frm As Form, ctl As Control
Set frm = Screen.ActiveForm
Set ctl = frm.ActiveControl
frm.OrderBy = ctl.ControlSource & ” DESC”
frm.OrderByOn = True
End Function

In attachment you find an example using a menu button, which calls a macro (which calls a function) sorting your form on the active control. I created two buttons, for ascending & descending sort order respectively.
You still need to convert it from Access 97 to 2K but that shouldn’t raise any problems (!?). It’s the first time I put an attachment on this forum. I hopes it works…
Greetings,
Hasse

(No Text)

Gunny,
go to table design – properties dialog box of the field in question.
Double click the button ‘…’ behind the property ‘Input mask’ and you’ll be guided through the appropriate whizzard.
I guess it’s most efficient if you only use the dashes while displaying your numbers, and not for storing them. As such, you can keep the field type ‘numeric’ – … which is the best choice for a key field relating different tables (right, other members of the board?).
I hope this is enough to solve your problem,
Hasse

It might be quiet too late but you never know…
I got the same error, due to a numeric – decimal field type. After I changed it to Double, the problem was solved.

An example…
Consider a multimedia library database. ‘Documents’ include maps, books, magazines, articles, CD-ROM’s, files stored on network locations,…
One property is ‘location’ (both for digital copy as hard copy). The locations are ‘hierarchical’:
– site: head quarters, lab,…
– room: office1, office2, office3, cellar room 1, cellar room 2
– furniture: book closet 1, book closet 2, desk1, desk2
(- optional: shelf,…)
I would like to offer the user the choice to specify the hard copy location as much as possible, for example because the specific location still might be unknown at the time of data entry.
I propose a self joined tblLocations = lc_IDlocation + lc_Name (+ lc_Level) + lc_IDParendLocation.
Now I’ll probably find some solution, but I still wonder if there is no more methodical way for e.g. presenting all these locations (at all levels) available for entry through a combobox, or constructing a form for easy location data management,…
I realize that solutions can be quite case specific. On the other hand, I dealt in a couple of other situations with similar problems, so I started wondering whether there aren’t any white papers/articles describing how one should efficiently & methodically manage such data.
I hope that this example explains a bit better what I meant…
Hasse

Charlotte,
I write more after Christmas (must leave for family reunion…) but already thanks for your attention.
Generally, I thought there could be some thumb rules/ways/… to construct forms in such a way that the two points you asked more information about could be handled as user friendly as possible. I’ll try to catch up later.
Hans

RBW,
for what it still is worth… Tony d’Ambra’s FreeAccess site offers a free utility which draws out the table structure ‘designed’ in the relationships window to a printable & more-a-less editable report. http://www.aadconsulting.cjb.net/vizrel.html
He has a Documenter alternative too (…/ezydoc.html), which just documents the important information on your objects – often preferable to what Access provides.
Hans

Charlotte,

It’s good to know that such tool as AccessToVB exists, so I can check it out with one of my friends who is a professional VB programmer. Still, based on your & Wendell’s information, the application concerned probably is too complex to convert it to VB.
Therefore, most of all, your comments on the developer’s edition-Wise-Sagekey is most valuable.

By ‘not for commercial use’ I meant: we play a game with a couple of friends in which each of us, based on certain criteria, puzzles together a virtual team of sportsmen which gather points in races throughout the year. The calculation of the results of this competition can be done in Excel but then, you can’t do much more with the data. Therefore, I created a database application in which all information is far more easy to manage, consult & analyse. I would have liked to share that with my friends, but (even though some have it available) not all of them can or want to pay for it the Access market price… it’s still just a game… Now, at my office, I got the chance to get the developer’s edition installed & I thought: well, here we go… But (as often occurs) this didn’t seem so evident as well.

The end of the story is that I probably only send the application to those friends which can run it & when I have some time to spend, dedicate myself to the possibilities discussed above.
By the way: in the meanwhile another possibility came up in my mind: an Access driven interactive website… but that probably will be a completely different story…

This thread remains red flagged for when I need to share a database for professional use…
THANKS!
Hans

Thanks Wendell.
I’ll wait with blinking eyes for any possible comments by Charlotte.

ps In the meanwhile: I know it’s not legal, but… when I reading about the efforts to be done, it makes me think about whether it’s all worth the hassle and, certainly because it’s not for commercial use, we might be better of by just i n s t a l l i n g an Access 97 copy right away (?)

I’m writing a Word macro/procedure to add a three-columns-one-row table to the document’s footer containing filename (left), page x/y (middle) & date saved (right). I get a run time error message indicating that the fields.add method is not available.

Can anyone tell me
(1) what I’m doing wrong for having no access to the fields.add method in the footer table cell?
=> I assume there must be some incompatibility between the table cell as a range and the fields.add method.
Why? Everything works when adding the field in the ‘body’ of the document (so fields.add works), when adding it without a table in the footer of the document (so footer range seems to be no obstacle either) or when adding text to footer table cells using e.g. tmpRange.text = “text”, and tmpRange.insertafter = “text” methods (so adding text to table cells works too).
(2) a better way to get the result?
=> I could avoid the table and use one centered paragraph in which I insert something like left field – tabtabtab – middle fields & text – tabtabtab – right field, measuring a bit untill it always +- fits on one line. This might work if the field name length doesn’t vary (too) much (the other fields lengths remain stable). But that seems such a hassle fi there’s an alternative…
(3) optional: how to add the mixture of text & fields for ‘page X/Y’ in the middle column… (I got all mixed up with how to properly use ranges & range methods here.)

Any help is greatly appreciated. I lost already more hours than I can afford, so I’m ready for giving up the fight… but it’s too much a pity, being (probably) so close.
So… thanks in advance!

Hans

P.S. My purpose: I want to standardise (+- uniform layout) 2000 word documents for delivery to our partners. I use an Access file which executes a Word macro (stored in the normal template) on multiple documents (using a table in which the file paths are previously automatically ‘loaded’ into). So it works great, if ( ) the macro does…
FYI: I only use the RANGE object as I’ll probably hide the Word application to speed up the processing and I’ve read (!?) that the SELECTION object might not work in these circumstances.

StuartR,
I encountered the same problem and your solution works!

I should never never never have figured this out myself (*).
Thanks!
Hans, Belgium

(*) I found your post ‘too late’ after I posted (197819) a new thread on this matter (“Can’t add field to table cell (Word 97)”). So I quoted your post over there, hoping that I did it right & didn’t ‘mess up’ threads too much like this.

Steve
great job!

One possible addition to front-end-back-end: converting the front-end into a mde reduces multi-user Access corruption on a network, especially in Access 97 (other versions: no experience). Be careful not to delete the original, as you’ll need it for further updates!!!! (more info about this topic can be found at another thread at the Lounge- I’m sorry for not having to provide more information on this)

Greetings,
Hasse

Rupert, (and the others, fyi… as I don’t want to mix this most useful thread with another discussion…)

when thinking about implementing this way of naming fields, I would suggest to use abbreviations instead of the full name, like this:
em_IDem = employee table key field
em_Name = employee name
em_IDcp = employee’s company (referring key)
cp_IDcp = company table key field
cp_Name = company name
This keeps field names into a reasonable length.
(Otherwise, Leszynski and others offer a list of possible ‘universal’ abbreviations of common names as comp(any),…)
Still, you keep the main advantage of your proposal: providing unique field names, which is handy for programming, designing queries, forms or reports.

For the ID’s you could consider writing out the ‘entitiy’ to the full, e.g.
cp_IDcompany, em_IDcompany
I just haven’t made up my mind yet if this is good for readability’s sake, or bad for (in)consistency’s sake…

(I caught this up from my boss at my first professional project… and it proved very useful. But more experienced programmers & developers of huge databases might know about some disadvantages…)

Hasse

Wendell,
I’ll check it out. It looks promising.
Thanks!
Hans