Lookup Query

[Alvin]

At this point I am totaly lost,
I have Two tables, Well these are the only ones of concern right now., They
are

"Females" & "Breeders"

I also have Two Queries, They are

"Male Siblings" & "Female Siblings"

Male Siblings Query looks for males with same Mother & Father Values that
are equal and The Female Siblings does the same for females. That way I show
all brothers in 1 query and all sisters in the other how can I get those
results into one to show what females and Males are siblings.
I can't breed brother and sister. On my breeder form The males are listed in
the parent form and I choose a female in the subform "DataSheet". I want to
be able to pick a female from a combo box in the subform and if she is a
sister to the male I want a msgbox to warn me of the two being siblings.
Again I am totaly stumped!
Thanks in advance.
Alvin

 

[Klatuu]

I don't believe you need an alert. I would suggest you use an SQL statement
in the row source of the Combox Box that will exclude females where the Sire
and Dam are the same.

And no thanks, I have six daschunds already, I don't need any more

 

[Alvin]

lol .
Can you give me some example code I am kinda knew at this.
And Thanks for the reply

 

[Klatuu]

The easiest way to do this would be to create a query in the query builder
(you can do that, because you already have). The difference is, you will
have to add parameters to reference whatever controls have the sire and dam.
This is what creates the filtering.

Then, once you have the query working, change from design view to SQL view.
Then copy the code and paste it into the row source for your combo box.

 

[Alvin]

By The way In both the Females Table and the Breeders Table I have a "Mother"
& a "Father" Field
The cbobox pulls from Females table.
Thanks again
Alvin
PS; Are you sure you don't want anymore daschunds? lol

 

[Klatuu]

Okay, so if the Breeders Table is for the Males, or whatever table has the
males, you would want to make it:
[Females]![Mother] <> [Males]![Mother] And [Females]![Father] <>
[Males]![Father]

lol, if one snuck in, I might not even notice.

 

[Alvin]

I can't seem to get it. Sorry, I have tried everything I know with no
success. You Can download the database I have at
http://www.pittmangamecalls.com/Breed.mdb
Maybe I wasn't explaining it right or something.
Thanks again for the help.

 

[Klatuu]

I have received 2 messages saying there is a new post in this thread, but
nothing shows up. Try again, please

 

[Alvin]

Here is the post again, It is listed in the threads. Any way here goes,

I can't seem to get it. Sorry, I have tried everything I know with no
success. You Can download the database I have at
http://www.pittmangamecalls.com/Breed.mdb
Maybe I wasn't explaining it right or something.

I want to be able to tell if a female is a sister to the male she is
breeding or not have her available for that particular male. You'll see what
I mean if you look at it.
Thanks again for the help.
Alvin

 

[Klatuu]

Got it. I will have a look and get back to you.

Here is the post again, It is listed in the threads. Any way here goes,

I can't seem to get it. Sorry, I have tried everything I know with no
success. You Can download the database I have at
http://www.pittmangamecalls.com/Breed.mdb
Maybe I wasn't explaining it right or something.

I want to be able to tell if a female is a sister to the male she is
breeding or not have her available for that particular male. You'll see what
I mean if you look at it.
Thanks again for the help.
Alvin

 

[Klatuu]

SELECT Females.FemaleID, Females.FemaleName FROM Females WHERE Females.Mother
<> forms!Breeders!Text72 And Females. Father <> forms!Breeders!Text 74 ;

I put this in the Femalse Lookup in the Mating Orders table. See if that
does what you want.

 

[Alvin]

Doe 44 shouldn't be listed because I have it as a sister to the male xx1
They both have the same mother and father

 

[UpRider]

PMFJI, but the former syntax had a couple of spaces where there shouldn't
have been.

The syntax below will give you NO matches until you give all the females
table SOME number in both the mother and father fields. Zero is ok for
testing. Nulls give the query a problem.

SELECT Females.FemaleID, Females.FemaleName FROM Females WHERE
Females.Mother
<> forms!Breeders!Text72 And Females.Father <> forms!Breeders!Text74;

Works for me.
UpRider

 

[Wolfgang Kais]

Hello Alvin.

At this point I am totaly lost,
I have Two tables, Well these are the only ones of concern right now.,
They are "Females" & "Breeders" I also have Two Queries, They are
"Male Siblings" & "Female Siblings"
Male Siblings Query looks for males with same Mother & Father Values
that are equal and The Female Siblings does the same for females.
That way I show all brothers in 1 query and all sisters in the other
how can I get those results into one to show what females and Males
are siblings.

Merge the Males and Females tables to one table (Individuals?) that has
a YesNo field (IsMale or IsFemale).

I can't breed brother and sister. On my breeder form The males are
listed in the parent form and I choose a female in the subform
"DataSheet". I want to be able to pick a female from a combo box
in the subform and if she is a sister to the male I want a msgbox to
warn me of the two being siblings.

Add the Mother & Father columns to the combo box list as - let's say -
columns 3 and 4 (since 1 hopefully is an invisible primary key and 2 is
the name of the female). In the before update event procedure of the
combo box do the comparison as follows:

If cboFemales.Column(2) = Me.Parent.Mother And _
cboFemales.Column(3) = Me.Parent.Father then
MsgBox "Can't breed brother and sister", vbExclamation
Cancel = True
End If

 

[Alvin]

It does kinda work but here is the problem.
Rename the females:
doe 1
doe 2
doe 3
doe1's Mother=Doe2
doe1's Father=buck2
doe2's Mother=doe3
doe2's Father=buck3
Then Rename the males
Buck 1
Buck 2
Buck 3
buck 1's Mother=Doe2
Buck1's Father = Buck2
buck 2's Mother=doe3
buck2's Father=buck3
Buck 2 & doe2 cannot mate. They are siblings, But
doe1 & Buck 2 can. the cbobox dosn't change values when I go to next record.
If someone could help me populate a text box in the subform with the mother
& father values for the female then I believe I could conditionaly use a
message Box to worn of the equal mother father values related to the text
boxes on the main form. lol this is so darn confusing. I am laughing my
rear end off!!!!

Thank you guys so much for trying to help me populate my rabbits.
Alvin

 

[UpRider]

In the strange world of boolean logic, when NOTs are used, things are not
what they seem. Try this:

SELECT Females.FemaleID, Females.FemaleName FROM Females WHERE
Females.Mother
<> forms!Breeders!Text72 OR Females.Father <> forms!Breeders!Text74;

Notice the OR in the criteria instead of AND. Doesn't seem right but it
works!

UpRider

 

[Alvin]

Thank you Wolfgang,

That works pretty good. 1 step further and I believe it's done.
Lets say they choose one that is related, At that point it does let you
select a Related Female and it does give the msgbox value like it is supposed
to, But Is there anyway to get it to auto clear the selected female or to
just not insert the selected female. The reason for this is the user may
decide to research a little at that point to select another Female. The way
is is you can't back out of it without selecting a female. By The way I
changed the And to Or so it will not let half brothers and half sisters mate
either. Below is the actual code I have.

-----------------Here is the code---------------------
If cboFemales.Column(2) = Me.Parent.Mother Or _
cboFemales.Column(3) = Me.Parent.Father Then
MsgBox "These two are to closely related to be Bred together",
vbExclamation, "Brother & Sister"
Cancel = True
End If

 

[Alvin]

Thank you Also Klatuu & UpRider
You all are very helpful and I just want all of you to know I greatly
appreciate you!!!!
Cheers

 

[Wolfgang Kais]

Hi Alvin.

Alvin wrote:
[...]

But Is there anyway to get it to auto clear the selected female or
to just not insert the selected female.

[...]

The "Cancel = True" prevents from saving the value.
To clear it out, try: "cboFemales = Null" or "cboFemale.Undo"
Then, delete the Cancel=True and change "or" back to "and" if you like.