Subform query 2

Topic

New Reply

Just when I thought I had it sussed (see previous thread subform query). My subform now shows the information I need it to, but the whole idea is that it displays a list, from which one can select the correct individual. I had been doing this with a check box, which had some additional code attached. Because the subform is now based on a union query, the recordset is not updateable, and I have lost the bto use a checkbox on that row.

I have circumvented this problem by creating the relational model Grugeon described, and using this to create a table containing the required information to underly my subform. The problem here is that as the household information is updated in the future, I will have to manually update the tables underlying the subforms.

Is there a way to create an updateable recordset from a union query but to keep its dynamic relationships with the underlying tables? Is there another way I could select the correct person from the list without needing to have an updateable recordset?

Thank you once again for your thoughts.

Mark

Reply | Quote

Replies

Since I don’t really know SQL I may be off the track. I would just create the relationship (one to many) in Access and then most of the rest of it takes care of itself.

Reply | Quote

Union queries are not updateable and no recordset based on one will be updateable. Think about it: What would you be updating and in which table?

You haven’t explained exactly what you’re trying to do with the subform. You want to use it to select an individual for something, but what? If all you want to do is pop up another form, you don’t need a checkbox or a union query. If you want to edit that record, you can’t use a union query anyhow. I suspect the real problem lies between the records in your parent form and those in the subform. Give us some more information so we can help you.

Reply | Quote

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

Reply | Quote

Well, I’m still trying to understand this: “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.” Do you mean, in case no individuals have been listed for the household, or no adults, or what?

Let’s get down to some basics of design here and try to clear away some of the complexity. Once you try some of these suggestions, you may reduce your problem to something more specific.

An important issue is that you seem to be trying to base the forms and subforms and popups on the same query, which isn’t the way you do it. Popups are NOT subforms, even though you may think of them that way. Popups are main forms that you launch from another main form and they aren’t inherently connected to the calling form the way subforms are, so you’re dealing with two different issues.

Base your main form on a query that returns just the records required to populate that form. Base any subforms on that main form on queries that return the data for that subform and includes a foreign key to the data in the main form. Then set the Master and Child linking between the form and subform control to the shared key field or fields. That automatically filters the records in the subform down to those that are appropriate. In other words, if you have a main form based on surveys, your data might include the survey number and household ID. Then you might have a subform of household members that has a recordsource that includes the data about the members and has the household ID in it as well (the linking field between the underlying tables). The data in the subform will be filtered automatically based on the Master and Child links established using the Household ID fields from each recordset.

Popups are independent forms, and there are a couple of fairly simple ways to handle them. One is to use the openargs argument of DoCmd.OpenForm to pass a filter string (i.e., “[Household]=1”) to the popup. In the Open or Load even of the popup, examine the openargs property of the form and see if a string was passed. If it was, set the popup’s filter property to the passed string and set FilterOn = true.

The other way is to use openargs to pass a SQL string to the popup that will return just the recordset you need to work with. In that case, you generally use a popup that opens with an empty recordset (the recordsource is set to a statement that always evaluates to false, so no records are returned). Again, use the open or load event to examine the openargs property and see if a string was passed and, if so, set the RecordSource property of the form to the SQL string. That will automatically requery the popup and return just the records specified in the SQL string.

Try playing with these ideas and see if you can come closer to what you’re trying to do.

Reply | Quote

There is a further technique that I use on occasions if I want to ‘synchronise’ a number of forms. Instead of passing a setting with openargs I have a module with a global variable and functions to set/read said variable. When a dependant form is launched from somewhere in the interface then the value can be read with the OnOpen event and appropriate action taken. It works, but for purists that dislike global vars it is not neat. Also it can be more awkward to debug if things go wrong and if an unhandled error ocurrs then one usually loses the value currently set.

Reply | Quote

Yep, I’m one of the purists who dislike global variables. The nature of global variables means they can be changed from anywhere in the database app, and that is far too loose for my comfort.

Reply | Quote

It was a special case with the view on the PopUps dependant on more than one other forms settings both for filtering and what data was displayed.
Generally I won’t use them, however I forgot to add in the original post the variables involved were private, accessed through functions to help prevent naming clashes.
The whole matter of the scope of variables is something I much prefer prefer using C++ with. With VBA I am always uncertain is this a copy, a reference or a pointer to an object when I pass it to another function.

Reply | Quote

There is another reason not to use global variables. In case of an error the content of a global variable can be lost!
I normally write a function with a static variable to contain the value I want to store.
Generally this function looks like this:

public function fnGetIt() as string

Static strToGet as string

if strToGet “” then
‘Value allready stored
fnGetIt = strToGet
Exit Function
end if

‘Do all the things to fill strToGet
….
fnGetIt = strToGet

end function

This works well, and if the value of the static is lost, it will be filled again.

Reply | Quote

You already know this, but it doesn’t hurt to restate as clarification for those who are unclear about variables and scope:

Keeping track of scope is usually solved by applying naming conventions to your VBA variables: mstrWhatever is a module level string variable, gstrWhatever is global/public, and anything without a prefix of m or g is local.

The scope of the variable in VBA depends on where you declare it and whether you use the public or private keyword. All variables are private to their container (module, class or routine) unless declared public, which can only be done at the module level. Variables declared as public are available to other modules and classes in the application.

Variables are indirect pointers to the address where the values are actually stored. All variables are passed as ByRef unless you specify ByVal. Variables passed ByRef allow the called routine to change the value in the original address. Object variables can only be passed ByRef. Variables passed ByVal are local copies and don’t affect the original.

Did I leave anything out?

Reply | Quote

I would just like to recommend that the
‘Option Explicit’
is set at the start of a module. This will trap spelling mistakes such as
Dim lNumber as long
lNumbeer = 5
then test if something = lNumber

The other point is for efficiency and declare the type of variable.
Dim lNumber as long
instead of
Dim lNumber
which sets it as a variant. Then the code will always have to convert it to a long when it is being used. Also I suspect the line
lNumber = “6”
would be accepted but not behave as one expects.

Reply | Quote

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?

Reply | Quote

That’s OK, I’m largely self-taught too. One of the best ways to learn a language is to take apart code written in that language until you understand every last bit of it.

Subforms as forms embedded in aother form. It doesn’t matter whether they’re linked or not, they’re still subforms. The only place I use unlinked subforms is when they’re on unbound forms. In that case, there’s nothing to link anyhow.

I suspect your subform problems are actually the result of the underlying queries. You shouldn’t try to use the same query for all the forms and subforms. Each form or subform should be based on a query that returns only the fields and keys needed by that form. The larger grouping goes into the parent form. So your parent form might be based on tblProperty. You could have a continuous subform based on tblPerson that listed all the people. If you linked the parent and child form on PropertyUID, then the household selected in the parent form will restrict the records shown in the subform to only those people with the same PropertyUID in their records. You could have a separate subform for Head of Household. It would also be linked on PropertyUID and would only display a single record, the record for the person designated as head of that particular household. Is that starting to make sense to you?

Looking at your tables, I’d design them something like this:

tblProperty:
PropertyUID <–primary key
Address
…

tblPerson
PersonID <–Primary key
PropertyUID <–foreign key, duplicates OK
…

tblHeadOfHousehold
PersonID <–Primary key
PropertyUID <–Unique key

The tblHeadOfHousehold table is in a one-to-one relationship with tblPerson, because it's a subclass of tblPerson. It's in a one-to-one join with tblProperty because you only have one head of household. You probably don't require any other fields in that table because it only exists to subclass a particular person in each household. tblProperty has a one-to-many relationship with tblPerson because more than one person can be in the same household.

Reply | Quote