continuous subform based on same table as main form

[moltisanti]

hello,

for a breeding project i'm creating a database which holds all animals. i'm trying to make the parents selectable in a subform based on the same table as the main form - this doesn't work for whatever reason ("table is already opened exclusively by another user,..."). it can't just be one field or a query, it needs to be a subform because besides name and eartag i also want to display a small picture of the parent and only the filename is being stored.

i'd really like it to be selectable records in a continuous subform, so the risk of selecting a wrong id is minimized. is there a way around this not being able to use the same table for a subform?

 

[YNWA]

Sounds like your database is not normalised.

If I read you right the main form is going to hold animal details. Then your subform you are going to assign parents (as in mother and father) to each animal. But these parents can also be found in the main form?

If so, would a drop down menu not be more appropriate?

Is your subform a datasheet or a form?

More details needed as I dont understand what you are trying to achieve.

 

[Mihail]

An idea
Base the subform on a query. The query must "simulate" the table.
But this approach (A Form with a Subform based on the same recordset) looks as a cycle.
I expect a lot of problems from here.

 

[moltisanti]

Sounds like your database is not normalised.

If I read you right the main form is going to hold animal details. Then your subform you are going to assign parents (as in mother and father) to each animal. But these parents can also be found in the main form?

If so, would a drop down menu not be more appropriate?

Is your subform a datasheet or a form?

More details needed as I dont understand what you are trying to achieve.

the parents are also found in the main form yes, and i would like the subform to be a form.

the problem with a drop down menu is that there is no single required field. for example, names are not always given to all offspring. if i instead use the primary key id (autonumber) to identify the animal in a dropdown list, it will be confused with the "ear tag id", which is a different number ("ear tag id" can't be the primary key because they are only given when an animal reaches a certain age). it's very important there is no ambiguity here. all of this is why i thought a picture, supplemented with some other data, would be the best choice, but the only way i can see this work is with a different form as the subform.


@Mihail: for the above reasons i also can't use a query as the subform. i realise that this could produce problems, but i'm having trouble coming up with a better alternative. if there is one, i'm happy to alter my method.

 

[JHB]

@Mihail: for the above reasons i also can't use a query as the subform. i realise that this could produce problems, but i'm having trouble coming up with a better alternative. if there is one, i'm happy to alter my method.

Which problem?
What the other way round, use a query for the main form?
It is difficult to image what it looks like, if we don't have any picture, (or database).

 

[nanscombe]

You can actually show more than 1 column in a dropdown whilst picking then show any additional field in a textbox which references it's data.

Example:

Combobox parentMale
Column(0) - ID (Autonumber)
Column(1) - animalName
Column(2) - earTagId

When you drop down the list you can see all three columns. When it is closed all you see is column(0).

However if you set up a couple of textboxes:
Textbox01 - Control Source =[parentMale].[column](1)
Textbox02 - Control Source =[parentMale].[column](2)

These would show the male parents animalName and earTagId respectively.

You could then have another Combobox parentFemale
Column(0) - ID (Autonumber)
Column(1) - animalName
Column(2) - earTagId

And another couple of textboxes:
Textbox03 - Control Source =[parentFemale].[column](1)
Textbox04 - Control Source =[parentFemale].[column](2)

These would show the female parents animalName and earTagId.

Take a look at the attached database as an example of how to do it.

 

[nanscombe]

Here is another example based on subforms.

 

[Mihail]

I guess very interesting this project and I wish to participate to the develop process.
Because that, and with your accept, moltisanti, I'll become very critical (in the good sense) with any answer in this thread.
So, let's start.

@moltisanti
1) In your database, I think that is strongly necessary to store the gender for the animals in order to allow us to choose the right animal for ParentMale and ParentFemale.
2) I don't understand if you refer to ALL animals (like to ZOO) or you have a specific type of animals (dogs, cats, tigers ...). If your table contain ALL animals, you must be more specific because a cat can't have as parents a lion and a pig. So you (we) need one more table with animals types (species). And need to include this information in the main table.

When we choose a parent, we must ensure that the animal is from the same specie with correct gender. (For more precission, the parent can't be too young or too old, but let's forget this for now)
Also, we must ensure that the parent is not himself.

I like your idea, Nigel, to use combos (maybe list boxes) for choosing a parent.
For saved records, I can select (in combos Row Source query) anything (Gender for parents, Specie for parents, NOT himself) because I already have this information for the animal; but how to deal with all this for a new record ?
So, I think that is better to have a form where to define the animal as Name, Gender, Specie and another form (I can't name this "subform") where to choose his parents.
If the parents fields are required then two tables are needed, isn't it ? With ONE TO ONE relationship.

I am waiting for criticism
Thank you.

 

[gemma-the-husky]

in logical terms, for any animal, all you need is a field in the record to point to the recordid of the male parent, and of the female parent.

rules that restrict an appropriate parent are not important here. (as regards the data structure)

given this scenario, you can then find.

a) parents and grand-er parents of a given animal
b) children and grand-er children of a given animal
c) siblings and half siblings of a given animal.


this can is all done by developing suitable queries. the presentation is a different matter, and is more subjective.

so can you post your table design for the animals table?

 

[Mihail]

Thank you for help, Dave. (My very old dog's name is Tony ).

Agree, with one exception: An animal can't be his own parent or child. (as regards the data structure). The rest is subjective, as you say.

 

[Mihail]

And, thinking a little bit more, an animal can't be his own grandpa or grandchild. And so on.
Seems to not be as simple as it appear.

 

[nanscombe]

I guess it depends on how strict you want to be with the rules.

You could try and stop animals being related to themselves or you could (possibly big mistake) rely on those entering the data to show a little common sense.

In this case they won't be making up data but will be supplying it based on real life.

Another useful piece of data, of course, will be Date of Birth.

 

[Mihail]

YES Nigel. This approach ensure that parents are older than children and avoid cycles.
Moltisanti should inform us if this information is available.

 

[gemma-the-husky]

what i said was that the basic premise was that a single table could hold the data (which is recursive in nature). that's what i meant when i said the rules weren't important. I was just expressing the principle.

clearly rules have to be in place to ensure that any data entry is sensible.

eg an animal cannot be its own child, indeed, cannot be a parent of any of its predecessors, or a child of its successors, or even a parent/child of its siblings,
an animals mother must be female, and an animals father must be male.
ie generally that an animal must be appropriate for the relationship.

assuming the relationships are all sensible, then a complete genealogy can be established from this single table - although actually drawing such trees graphically is quite a tricky process. (as they get very wide!)

if the op is having problems constructing a suitable form/subform, then maybe the table is not constructed correctly, or maybe the queries still need some attention.

 

[Mihail]

Hey, moltisanty ! Are you still interested ?

 

[moltisanti]

Here is another example based on subforms.

nanscombe: this is pretty much exactly what i want! strangely it doesn't work on my form this way though, upon building the form i get the following error: "Table is opened exclusively by another user or opened with the help of user interface and cannot be manipulated through programming." (more or less, i have a different language version)

i've added the file on popular demand below the main form i put a quick subform of the sfrmSelect which should hold the parents, but because of the error it's blank.

(the main form being frmDirectoryEdit)

 

[moltisanti]

oh, and about the rules and such, i didn't even think of this stuff yet, but i'll be sure to add some constraints like this when i get it to work!

 

[moltisanti]

i figured out the problem: i had the RecordLocks property set to All on my subform, which conflicted with the same data on the main form that wasn't locked at all.

assuming the relationships are all sensible, then a complete genealogy can be established from this single table - although actually drawing such trees graphically is quite a tricky process. (as they get very wide!)

gemma: any thoughts on how i could draw such a tree? i would keep it very limited, only the current record, its parents, direct offspring and possibly siblings. an added complexity is of course there is no single partner, so i would have to differentiate between half-siblings, and multiple matings as well.

 

[JHB]

nanscombe: this is pretty much exactly what i want! strangely it doesn't work on my form this way though, upon building the form i get the following error: "Table is opened exclusively by another user or opened with the help of user interface and cannot be manipulated through programming." (more or less, i have a different language version)

Because you have set the "Record Locks" property to "All Records" for the form "sfrmSelect".

 

[Mihail]

A first sketch for you