Replies

Thanks Mark,

gotopage worked fine for me in this instance.

Seems that I was trying to do something that noone in their right mind would, but as I’ve now cracked it might as well post it.

As I couldn’t use the BeforeUpdate event because of the number of refreshes forced by conditional comboboxes and subforms, the OnCurrent event was the key.

Having disabled close and navigation buttons and set up my own buttons to run some checks before doing this, I also got the buttons to set a flag. If this flag is set, the OnCurrent procedure loads the value of current record into a global variable. If this flag is not set, then the OnCurrent procedure returns to the record whose number is in this variable, and resets all flags. I have had to add a couple of extra flags and conditional clauses and have 2 passes at this event in order to prevent the oncurrent events cascading, and set the flag in the forms onopen event procedure to prevent problems entering the first form, but it now picks up all finds, ctrlF’s, CtrlPgDwn, menu navigation events etc, and slaps wrist for not having used the buttons I have so thoughtfully provided. I have a similar system in the forms onunload event procedure to catch any menu driven closes or CtrlF4’s.

I do have a number of minor problems left:
1) As these navigation tools are now buttons, the onexit event of any control that has the focus when one of these buttons are clicked still happens, which did not happen using the built in navigation buttons. Is there a way round this?

2) I cannot find any scroll controls in Access VBA. When the focus shifts back from the subform to the main form, I can find no way of controlling the position of the form in the window – it seems predetermined that the control (not even the label, but the control) which now has the focus is positioned to the far left and just below the top corner of the window. Short of creating a dummy control which would induce satisfactory positioning of the form in the window and directing the focus first to this and then immediately on to the control I want the focus to be with, I cannot seem to find a way round this. Excel scroll commands don’t work either, and as the only way to move the position of the form in the window is by the scroll bars, I can’t even use the sendkeys statement.

3) Although I have set the properties of the subforms to border size – resizable (in their form properties) and can grow (in their “control of parent form” properties), they persistently refuse to grow to accomodate all the records. Turning the scroll bars off doesn’t seem to help, either.

Just while I’m on a list of questions – I’ve noticed there are a lot of topics in online help which don’t seem to be available (click on them in search results and nothing happens). Many, but not all, of these are marked ADO or DAO. What gives?

I’d also like to find out if there’s anyway that a called routine can stop execution of all code (rather than allowing the calling procedure to continue as in exit sub) and return the focus to the form.

I hope one day to be able to answer at least 25% of the number of questions that I ask.

Sorry, I was trying not to write another essay.

Once a record has been entered, I want to make sure that any editing is performed through certain structures, so that various hidden identity fields are updated correctly. It is not possible just to disable the controls, however, as some editing may be necessary. I have structures in place that in the absence of any errors should ensure that all the data is edited in the correct way (the mechanism I have in place is procedures running on the GotFocus event of the controls in question, which force the user into a particular edit path (via subforms)), but there are ways round this, notably if any errors occur. The most difficult form of error to handle is where the primary key value already exists in one of the tables, which is possible due to the poor quality of the underlying data (and the poor quality of my first attempts to get this to work).

So what I’d like to do is set flags when data is edited in the controls. These flags are then cleared if the correct editing procedure is followed, but I want to check before a user leaves that record that none of these flags are up, and force a re-edit if they are. Another possibility would be to lock all the fields, giving an additional level of protection, but this can only happen when they have been updated (in the case of a new record or a record that has been edited), and the moment when that record is left to maove to another would be the perfect time to do that. It is more difficult to do that on form update, because I have to force a number of refreshes during the entry of a record to populate some comboboxes appropriately.

What I meant by finding out about the last record at the on current event is this: the on current event happens when the user moves to another record. In order to conduct the procedures noted above I would need to find out the identity (either primary key value or record number) of the record that was just left.

I should add that I want to be able to perform these checks any time the user leaves a record – be it new, old, edited or unedited, so that I can catch any previously missed events.

I hope this is making some sense.

Mark

Thanks for your help Charlotte (as always).

I have one more event related question.

I want an event to happen each time the user leaves a record to move to another one in a form (single form view). The OnCurrent event is not useful unless there is some way of finding out the previous record viewed, which I have yet to find. I could disable the navigation buttons and make my own, which is not only a hassle, but also does not catch moving between records using the wheel of a wheel mouse or CTRL+PageDown.

I have read the description of every event that I can find in Help, and also looked at MS online Access offerings, but to no avail. A lot of the promising entries about being able to identify the last record that I found in help, however, don’t seem to be available (usually marked DAO).

Am I missing something?

Thanks

Mark

Phil,

My main problem with Cleansweep under win2k is that it frankly wasn’t working. it would not pick up the majoritiy of installations, despite being 2001 with all the liveupdates up to date. I also found that it would often report crucial dll’s as being deletable (fortunately I never did, but moved them to another folder), and the report summary windows were often truncated, even when saved to a file or printed. also when starting the uninstall wizard, I would have to wait 5 minutes (under win98) for the hourglass to become a pointer, otherwise selecting any program to uninstall would prompt an analysis of every file on the drive.

True, it did give a measure of analysis, but much of this was obvious, and its recommendations on dll’s in windows and win/sys were not reliable. It did offer general cleanup, but I often empty temp files manually, and as my internet connection is so poor (not like you lucky developed world broadbanders), I quite like to keep my temporary internet files.

Because I was using Norton Systemworks I also had the infuration of not being able to choose which elements I had installed etc etc. I also had a lot of problems with liveupdate, which would never resume, and often time out after downloading 1.5 from 1.7Mb in about 30minutes. Never found many of the utilities useful either, I once tried to use the registry monitor to monitor an installation, and fell asleep (admittedly with a 2gb hd and PI in the dim and distant past). I still use norton antivirus, though.

Overall, it seems that there are smaller, lighter, freer (!) utilities to do the job better (although I was struggling to find the win2k version!).

Having said all this, if someone makes an installation monitor which backs up all the files being replaced, catches every installation no matter how sneaky, and tracks the fate of dlls through multiple installations, allowing one to selectively, safely, and completely remove an app, I am ready to buy (and spend 20 mins waiting for every installation to complete for the privilege).

Mark

Installing SR1a on the other users computer did the trick. Thanks.

Don’t recall seeing it doucmented, but then I never can remember the list of bugs fixed unless I’ve suffered the bug myself.

Thanks. I’ll check we’re all sr1’d up.

Charlotte, can I check about the locking. Both the database default locking and the form properties locking are set to No Locks, but the default option does include Record Level locking – is this OK?

Thanks

John’s point about endlessly editing labels has not been addressed – the ideal of course, would be for Access design views to have a replace facility, so one could name using underscore characters (or whatever), and then replace all these with spaces automatically. Never quite understood why these sort of facilities weren’t available (adding numbers to field labels is another example).

I sincerely hope someone will reply to this telling me how stupid I am and that there’s an easy way to do all this. Any offers?

Mark

Sorry, forgot step 7 – go back to properties and remove client for microsoft networks (I found that I was getting more DNS errors before I did this).

mark

This cobbled together, illogical, don’t know which bits of it are necessary solution worked for me.

1) Delete connection in question.
2) Reboot.
3) Recreate connection, adding username and password and asking it to remember password in wizard.
4) Before connecting, select properties and add client for microsoft networks.
5) Connect.
6) Reboot.

As I said, probably only one or two of these steps are necessary, but which ones is anyone’s guess.

running win2k, sp1 with IE5.01 SP1.

HTH

Thank you once again.

I’m afraid one of the weaknesses of being self taught from Help files is that my nomenclature is sloppy. when I said popups what I meant were subforms that I hide and unhide as necessary, and essentially the design is exactly as you say, Charlotte, for the selection forms, although the correction and new forms are not true subforms as they are not linked, just provide a separate space to enter fresh data. As these last 2 are based on separate underlying tables, there is no problem with updateability.

The subform will return a list of several possible individuals, based on either household number, name, or head of household depending on the stage of the selection process. I need to be able to select ONE of the members of the list and to manipulate the data in that record further. It is this facility that is prevented by having a non updateable recordset. I attach the SQL statements for the original flat and the later relational models. Both return the correct records, but none allow me to tick the check box in the row of the corresponding individual.

I have tried doing this with a popup form using openargs as you describe, and again get the correct records populating my table, but the recordset is not updateable.

As I say I have bypassed the problem by creating new tables containing all the necessary information to act as recordsource for the 3 selection subforms (really subforms!), but this is clearly an undesirably flat design, and leads to complications in updating the individuals list in the future (in effect I will have to recreate the tables each time there are changes to the population list).

Is the problem that I am linking the 3 table on propertyUID, but infact all the records will have the same propertyUID and it is the personID which will be unique to a record in this query? Would it therefore work if I were to include person ID in all the tables (although this would rather defeat the purpose of a relational design)?

In the instance that there are no individuals in the list matching the individual details on the (paper) form, it is easy – I have a check box in the header, because this does not need to be record specific or pass data from a record in the subform, just trigger an event and pass data from the master form to the new entry form.

Please remember once again that there are 2 distinct “databases” (?data lists, ?pools of data?) here – the survey results database, which needs to get demographic data from the population/individuals database, which at the moment is updated separately, although I will hope that once everything is robust that parts of it will be updated through the surveys. Eventually to be more relational I will not need to put the demographic data in the survey results form, just identify the correct individual and link on the ID, but this would still require being able to choose the individual from a list, and is, anyway, a long way off. I’m not sure if the quality of our census (ie population/individuals) data will ever be good enough to do this.

Should all this be too much drivel, and as design is probably the most important thing to think about, can anyone answer this question (which I feel also gets to the nub of the how to update a relational design issue):

Now I have built the relational model of my flat table (ie moved from one table with ID, person details, address, head of household etc to three tables, namely a property table with address and PropertyUID, an individuals table with person details and ID and PropertyUID and a headofhousehold table with head of household and PropertyUID), how do I update the data in these tables should the information change. I have made a query with all 3 tables linked on PropertyUID, but again it is not updateable. If I have to update each table individually, there is massive scope for error, and I will not be able to trust it to anyone else.

Am I being stupid?

I would imagine that all the addresses were stored in the “shared contacts” folder of the address book, instead of the folder for that identity. You could check this in future by opening the address book (command prompt wab /a, or start-accessories-addressbook) and showing all contents from the file menu (you may also need to select show folders and groups from the view menu).

I don’t think it’s possible to have different address books. Have you checked out this useful OE site – http://www.tomsterdam.com/olexp/%5B/url%5D

*edited to hyperlink url*

Don’t know about 5.5, but found this about 5.01 somewhere:

In Internet Explorer, you can open a link in a new window either by Shift-clicking the link or by right-clicking the link and choosing Open In New Window from the pop-up menu.

If Internet Explorer’s settings are changed or corrupted, you can sometimes lose this capability. If that happens to you, restore it by selecting Run from the Start Menu and typing:

regsvr32 shdocvw.dll

Then click OK and restart your computer.

I would frequently get this error with Norton Systemworks symtray. Do you have other Norton apps or just AV? If you disable Norton symtray from running at startup do you still get this error?

Sorry, clarity loses to brevity, but I’m afraid this is going to be a long one.

Before I describe what I am doing, let me give another example of where I would want to have an updateable record set from a query. Having made the more relational design suggested by Grugeon, I will need to make a form for entering new data into this list, which will be based on a query with 3 tables – PropertyList, Individuals and HeadOfHousehold – linked on PropertyUID, and with address fields from PropertyList, personal fields from Individuals and head of household from HeadOfHousehold, and will need to assign new propertyUID’s and create the corresponding new records in all 3 tables. Otherwise I will have to enter in a flat table and recreate the relational tables every time I want to update the individuals list.

To give a detailed description of what I have been trying to do and why I need to allow the data enterer to select one row from a list in the subforms:

I have the list of names and age or year of birth by sex for the members of every household in the district that we are working. These are collected by the local workers and written in exercise books. They were entered in an excel spreadsheet which I have imported into access and created the more relational model from this information. There are many errors in the data, of all kinds (missing data points, misspelling of names, confusion of names, incorrect sequential numbering of households) which are compounded by individuals moving in and out of the area and new babies being born, and by the similarity, changeability and alternative spellings of many names, as well as the changing age with time, as previously observed by Charlotte.

Every 3 months we conduct a survey of a subset of these individuals. These are very busy times, and data concerning other variables, as well as demographic details, including, supposedly, the number of the household, are entered onto forms. The current database is concerned with the entry of data from the surveys, but I would like to ensure that we know which individuals we are seeing, both over time and to establish family relationships, so I would like to tie these individuals to their identities in the village lists. A hand held card system, and a more detailed house to house census to provide better data for the village lists are unfortunately out of the question for local reasons.

The village lists are periodically updated, and this updated data needs to be entered into the database, and the resulting lists printed out in that format to be returned to the local workers.

The current survey form takes you first to field survey number, then through village, hamlet and household (which would uniquely identify a household). If a household number is entered, this takes you to a popup subform listing the individuals in that household. At this point you have 2 options, to choose the corresponding individual, currently done with a check box, or to indicate that there are no suitable individuals in that household. If an individual is selected, the code underlying the checkbox pops up a confirmatory msgbox, then if that is confirmed asks if there are any corrections need to be made (eg to spelling of name, age etc), and if not closes the popup, clearing the checkbox and entering the corresponding data in the survey form. If there are corrections to be made, another subform pops up and has the information for the selected individual automatically entered for editing. After editing, another checkbox is selected, which takes you through some error checking mechanisms, ticks some checkboxes to indicate which pieces of data were changed and then if all is fine, enters the data in the survey form. This corrections subform has a separate table as its data source as I do not want to alter anything in the original village list initially, as, given all the uncertainties, I would rather do some manual comparisons before changing the master list, and also feed back the corrections to the local workers for confirmation if they are anything other than simple typos. The entries are linked by an ID unique to each individual in the master list.

If there is no suitable individual in the household list, you are directed to the name fields of the survey form, enter the name, and are then taken to a second popup subform based on the same source as the first but linked by name only, and again have the opportunity to select an individual and go through the corrections process. This is necessary as many of the survey forms do not have the household recorded, and because errors in the exercise books (mainly in numbering of households) mean that household numbers may differ from those expected.

If there is no suitable individual in this list, you are taken to the head of household field and once this is completed to a third pop up subform based on the same source but linked by head of household. This is a last resort for cases where the household number is not recorded and there are spelling mistakes/alternative spellings of the name. Again you can select the individual and go through the corrections process.

For all 3 forms the identification tags are (household), name, age, sex, head of household, hamlet and village. I had the problem with including head of household on the forms – the subject of my initial thread.

If there is no suitable individual in this list, you are taken to a final pop up subform where you enter the details of new individuals not in the master list. Births and population movements mean there will always be some such individuals. As in the corrections form, this information is entered in a different table for similar reasons, and on completing the details you are taken through the same error checking procedures and the information is entered in the survey form. These individuals will clearly not have a propertyID or household number, but are assigned a unique individual id (100000+NewAdditionsTable row number).

I am responsible for designing and organising the studies, organising the local workers and collecting the data (together with local colleagues), designing the database, analysing the results etc,etc, thus am very aware of all the sources of possible error and confusion, hence the lengthy process. In the long run it will speed data entry, however, because it means that anyone can enter the data from the forms, not just those with a knowledge of local names and an ability to read their rendering in local handwriting. Or at least it will when the system is working!

Sorry if this is more information than you needed