Design Problem -- only get partial results on multiple join

Hi Evi:

Yes, my combo box is named AgreementWith.

Yes, I close the Org Form after I've entered the new record (the Agree Form,
where the combo box is, is closed when I'm entering data in to the Org Form).

I'm not sure it'll help, but let me give you a little more info on my combo
box:
My combo box is based off of a query -- should that matter? My combo
box has 5 columns showing Org, POC (pt of contact), AWID (Org ID), AWJID (Org
Join ID), & AgreeID.
What currently shows when I go to the dropdown arrow of my combo box is
the 5 columns I have above. If an Org has more then 1 Agreement, it's listed
twice.

One other clarification from my previous post -- I do NOT know VB (Sorry for
the obvious oversight).

Thanks Evi.

"Evi" wrote:

Is your combo named AgreementWith (go to Properties and look at its Name on
the Other tab)?

The On Focus only happens if you have clicked back onto your combo after
clicking off it (the code that sets focus on another control, ensures that
you do that but you can do it yourself while you are testing the code.)

Are you closing the Org form (or pressing Shift and Enter) so that the
record is saved to your table for the combo to read when it requeries?



Evi


"GIraffe" wrote in message
...
Hi Evi (I don't know if I need to restart this thread or not ...)

I finally got around to trying the "GotFocus" suggestion you gave me last
week. I appreciate you providing me with the code string, because I do
know
VB.

I tried both the code suggestions (1 at a time) you provided me. For the
first one, my line looks like: Me.AgreementWith.Requery

I ran a test under each code attempt, and my new organization addition
(Test) does not appear in the ComboBox. Historically, when I needed to
add a
new Org, I would go to my Org form, add it, put the agreement info in the
agreements form, then, then do an append query to pull the 2 together.
And I
can continue to do this if necessary.

What could be wrong? Can you give me some thoughts on what to look for or
try to troubleshoot? Your VB suggestion seems like it would work really
well
and provide me with a few less steps (ie, going to my "add new" button,
which takes me to the form to add a new Org, then when I'm done the form
closes and takes me back to my agreement form to finish adding the
agreements
data, then on to my append query).

Thank you Evi. You have been wonderfully patient.

"Evi" wrote:

Oh I see. OK, all you need to do is go into the Properties of the
Organization combo, Click on Got Focus, choose an Event Module, click
just
right of that to open up a code page and just above where it says End
Sub
type
Me.TheNameOfYourCombo.Requery.
Then, as soon as you click back onto your combo, the new name will
appear.
For my own use, I often use the DoubleClick Event to open my connected
forms
and the code I put in the double click event first undoes what I tried
to
type into the combo before finding it didn't have my choice, then puts
my
cursor in the previous field to the combo so that I'm all ready to enter
it,
causing it to requery.

For that, above the End Sub of the DoubleClick Code section type

Me.YourCombosName.Undo
Me.TheNameOfYourPreviousField.SetFocus
DoCmd.OpenForm "TheFormthat contains your organizations"

(there are other ways to do this using NotInList but this will get you
started)

Evi

"GIraffe" wrote in message
...
Hi Evi:

I usually add an agreement first to tAgreement table, then scroll
through
my
organizations via the combo box, if an organization does not exist, I
go
to
my organization form (filling the organization table) and add the
organization. The other problem I have is when I go back to my form
for
the
tAgreement table, the organization does not appear in the combo box.
I
usually have to do an append query to append the new organization to
the
new
agreement (at least I have a work around for this piece).

I do not have OrgID in the tAgree, because I link tAgreementWithJoin
(which
is the in between table with tAgree & tOrg) using AgreeID. I'll try
adding
the OrgID from tAgreementWithJoin and see if it makes a difference.
However,
isn't this a double join for the same info?

One thing I have learned through this exercisie is that the
relationship
setups can sure mess up how the output looks!

I appreciate your patience and I thank you for all your help.

"Evi" wrote:

I don't know what Agreements are (and probably wouldn't get it, if
you
tried
to explain) but if each Agreement only exists when an Organization
is
added,
so that you never need to add an Agreement more than once to
Agreements
table and it never needs to exist seperately from an organization ,
then
your structure is quite right and you only need a combo box for the
organization and a seperate organization table (I think that's your
AggreementWith table, if I understand rightly)
So your ammended structure will be:

tAgreements:
AgreeID (PK)
Agreement
OrgID (Joined From tOrganization in the Relationships window)



tOrganization
OrgID (PK)
Organization


Using the tAgreements, if you filter by orgID, you will see all the
agreements which that organization has.


But if Agreements can exist before they are added to an organization
(eg
you
have different types of Agreements available and an Organization can
choose
which type they want) then you need the 3-table structure.

Evi


"GIraffe" wrote in message
...
Hi Evi:

Thank you SO much for your response. Sometimes I get a little
"wordy"
on
my
filed names. Thanks to this board, I have learned some critical
things
about
field names ... and have stumbled in to some problems when I
previously
used
special characters, etc. So I'm getting better. I appreciate
your
paraphrasing.

For the most part you have it. However, I do not have a combo box
based
on
tAgreements. I do have a combo box for tOrganization. My
tAgreements
table
is basically my "primary" table that everything either links in to
or
is
based out of.

I think it's some kind of relationship thing. I'm just not clear
how
to
figure this out.




"Evi" wrote:

It looks normal
The names are distracting me a bit so excuse me if I paraphrase.
Given that the amendments are OK
What I'd expect to see for the other 3 tables is
tAgreements:
AgreeID (PK)
Agreement

tOrganization
OrgID (PK)
Organization

tAgreementOrganization
AgreeOrgID (PK)
AgreeID (Join from tAgreements to this field)
OrgID (Join from tOrganization to this field)
Other unjoined fields


Your form should be based on tAgreementOrganization.
You should have one combo box based on tAgreements and one based
on
tOrganization
Is that (in essence) what you have?
Evi





"GIraffe" wrote in message
...
Thank you for your time and patience. This board has been a
wealth of
information and knowledge. I hope you can help me with my
latest
Access
challenge. I've been troubleshooting for the last 3 days and
have
exhausted
my troubleshooting knowledge. I'd appreciate any suggestions
you
may
have.

Bottom line - I have 1 table with 2 other tables linked to it
by a
common
field. One of the 2 tables retrieves all the data correctly,
the
2nd
of
the
2 tables only retrieves part of the data. Here's my
structu

Relationships (All are 1 Many)
tAgreements.AgreementID tAgreementsAmds.AgreementID
tAgreements.AgreementID tAgreementWithJoin.AgreementID
(referential
integrity)
tAgreementsWithJoin.AgreementWithID
tAgreementWith.AgreementWithID

tAgreements
AgreementID [autonumber] (Primary Key Links w/
tAgreementsAmds.AgreementID &
tAgreementWithJoin.AgreementID)
AgreementDate [date]
AgreementNo [text]

tAgreementsAmds
AgreementsAmdsID [autonumber] (Primary Key)
AgreementID [number] (Links with tAgreements.AgreementID)
AmendmentNo [text]
AmendmentDate [date]

This structure works fine . For every agreement, I get all the
Amendments
associated with the agreement. It's the next join I'm having
problems
with.

tAgreementWithJoin
AgreementWithJoinID [autonumber] (Primary Key)
AgreementID [number] (Links with tAgreements.AgreementID)
AgreementWithID [number] (Links with
tAgreementWith.AgreementWithID)

tAgreementWith
AgreementWithID [autonumber] (Primary Key) (Links with
tAgreementWithJoin.AgreementWithID)
AgreementWith [text]

I often have an agreement with more then 1 organization. For
each
agreement, using a combo box in Datasheet form, I choose each
organization
that is part of each agreement. Here's my problem, for each
agreement,
only
the last organization I choose will appear in my search
queries or
in
my

your combo being based on a query is not a problem, nearly all of mine are.

You shouldn't need to close the form containing the combo - that's why you
have the Requery code, but if you still aren't seeing the data after
re-opening the closed form then your coding isn't the problem.

It *sounds* as if your combo's query is based on the wrong table if each Org
is listed more than once.

It should be based on the Org Table which should list each org only once.
You can link other tables to Org query if you need to (In a combo containing
names you might want to link the Address from the address table) but each
Org would only be used once.
The usual structure for a combo would be the Primary field first (OrgID) -
this is usually hidden in the combo by making its Column Width Property 0cm
so that the field you actually see is the User Friendly field - Org - then
any other fields which could be handy..
The Control Source of this combo would be OrgID which should be a Foreign
Key field in the table on which your form is based. The important field in
the combo, the one that does all the work, is usually the first (ugly,
hidden) one so if you are putting the OrdID number into your form using your
combo then OrgID should be the first field in the Combo.

Or have I missed something important (perfectly possible)
Evi




"GIraffe" wrote in message
...
Hi Evi:

Yes, my combo box is named AgreementWith.

Yes, I close the Org Form after I've entered the new record (the Agree
Form,
where the combo box is, is closed when I'm entering data in to the Org
Form).

I'm not sure it'll help, but let me give you a little more info on my
combo
box:
My combo box is based off of a query -- should that matter? My combo
box has 5 columns showing Org, POC (pt of contact), AWID (Org ID), AWJID
(Org
Join ID), & AgreeID.
What currently shows when I go to the dropdown arrow of my combo box
is
the 5 columns I have above. If an Org has more then 1 Agreement, it's
listed
twice.

One other clarification from my previous post -- I do NOT know VB (Sorry
for
the obvious oversight).

Thanks Evi.

"Evi" wrote:

Is your combo named AgreementWith (go to Properties and look at its Name
on
the Other tab)?

The On Focus only happens if you have clicked back onto your combo after
clicking off it (the code that sets focus on another control, ensures
that
you do that but you can do it yourself while you are testing the code.)

Are you closing the Org form (or pressing Shift and Enter) so that the
record is saved to your table for the combo to read when it requeries?



Evi


"GIraffe" wrote in message
...
Hi Evi (I don't know if I need to restart this thread or not ...)

I finally got around to trying the "GotFocus" suggestion you gave me
last
week. I appreciate you providing me with the code string, because I
do
know
VB.

I tried both the code suggestions (1 at a time) you provided me. For
the
first one, my line looks like: Me.AgreementWith.Requery

I ran a test under each code attempt, and my new organization addition
(Test) does not appear in the ComboBox. Historically, when I needed
to
add a
new Org, I would go to my Org form, add it, put the agreement info in
the
agreements form, then, then do an append query to pull the 2 together.
And I
can continue to do this if necessary.

What could be wrong? Can you give me some thoughts on what to look
for or
try to troubleshoot? Your VB suggestion seems like it would work
really
well
and provide me with a few less steps (ie, going to my "add new"
button,
which takes me to the form to add a new Org, then when I'm done the
form
closes and takes me back to my agreement form to finish adding the
agreements
data, then on to my append query).

Thank you Evi. You have been wonderfully patient.

"Evi" wrote:

Oh I see. OK, all you need to do is go into the Properties of the
Organization combo, Click on Got Focus, choose an Event Module,
click
just
right of that to open up a code page and just above where it says
End
Sub
type
Me.TheNameOfYourCombo.Requery.
Then, as soon as you click back onto your combo, the new name will
appear.
For my own use, I often use the DoubleClick Event to open my
connected
forms
and the code I put in the double click event first undoes what I
tried
to
type into the combo before finding it didn't have my choice, then
puts
my
cursor in the previous field to the combo so that I'm all ready to
enter
it,
causing it to requery.

For that, above the End Sub of the DoubleClick Code section type

Me.YourCombosName.Undo
Me.TheNameOfYourPreviousField.SetFocus
DoCmd.OpenForm "TheFormthat contains your organizations"

(there are other ways to do this using NotInList but this will get
you
started)

Evi

"GIraffe" wrote in message
...
Hi Evi:

I usually add an agreement first to tAgreement table, then scroll
through
my
organizations via the combo box, if an organization does not
exist, I
go
to
my organization form (filling the organization table) and add the
organization. The other problem I have is when I go back to my
form
for
the
tAgreement table, the organization does not appear in the combo
box.
I
usually have to do an append query to append the new organization
to
the
new
agreement (at least I have a work around for this piece).

I do not have OrgID in the tAgree, because I link
tAgreementWithJoin
(which
is the in between table with tAgree & tOrg) using AgreeID. I'll
try
adding
the OrgID from tAgreementWithJoin and see if it makes a
difference.
However,
isn't this a double join for the same info?

One thing I have learned through this exercisie is that the
relationship
setups can sure mess up how the output looks!

I appreciate your patience and I thank you for all your help.

"Evi" wrote:

I don't know what Agreements are (and probably wouldn't get it,
if
you
tried
to explain) but if each Agreement only exists when an
Organization
is
added,
so that you never need to add an Agreement more than once to
Agreements
table and it never needs to exist seperately from an
organization ,
then
your structure is quite right and you only need a combo box for
the
organization and a seperate organization table (I think that's
your
AggreementWith table, if I understand rightly)
So your ammended structure will be:

tAgreements:
AgreeID (PK)
Agreement
OrgID (Joined From tOrganization in the Relationships
window)



tOrganization
OrgID (PK)
Organization


Using the tAgreements, if you filter by orgID, you will see all
the
agreements which that organization has.


But if Agreements can exist before they are added to an
organization
(eg
you
have different types of Agreements available and an Organization
can
choose
which type they want) then you need the 3-table structure.

Evi


"GIraffe" wrote in message
...
Hi Evi:

Thank you SO much for your response. Sometimes I get a little
"wordy"
on
my
filed names. Thanks to this board, I have learned some
critical
things
about
field names ... and have stumbled in to some problems when I
previously
used
special characters, etc. So I'm getting better. I appreciate
your
paraphrasing.

For the most part you have it. However, I do not have a combo
box
based
on
tAgreements. I do have a combo box for tOrganization. My
tAgreements
table
is basically my "primary" table that everything either links
in to
or
is
based out of.

I think it's some kind of relationship thing. I'm just not
clear
how
to
figure this out.




"Evi" wrote:

It looks normal
The names are distracting me a bit so excuse me if I
paraphrase.
Given that the amendments are OK
What I'd expect to see for the other 3 tables is
tAgreements:
AgreeID (PK)
Agreement

tOrganization
OrgID (PK)
Organization

tAgreementOrganization
AgreeOrgID (PK)
AgreeID (Join from tAgreements to this field)
OrgID (Join from tOrganization to this field)
Other unjoined fields


Your form should be based on tAgreementOrganization.
You should have one combo box based on tAgreements and one
based
on
tOrganization
Is that (in essence) what you have?
Evi





"GIraffe" wrote in
message
...
Thank you for your time and patience. This board has been
a
wealth of
information and knowledge. I hope you can help me with my
latest
Access
challenge. I've been troubleshooting for the last 3 days
and
have
exhausted
my troubleshooting knowledge. I'd appreciate any
suggestions
you
may
have.

Bottom line - I have 1 table with 2 other tables linked to
it
by a
common
field. One of the 2 tables retrieves all the data
correctly,
the
2nd
of
the
2 tables only retrieves part of the data. Here's my
structu

Relationships (All are 1 Many)
tAgreements.AgreementID tAgreementsAmds.AgreementID
tAgreements.AgreementID tAgreementWithJoin.AgreementID
(referential
integrity)
tAgreementsWithJoin.AgreementWithID
tAgreementWith.AgreementWithID

tAgreements
AgreementID [autonumber] (Primary Key Links w/
tAgreementsAmds.AgreementID &
tAgreementWithJoin.AgreementID)
AgreementDate [date]
AgreementNo [text]

tAgreementsAmds
AgreementsAmdsID [autonumber] (Primary Key)
AgreementID [number] (Links with
tAgreements.AgreementID)
AmendmentNo [text]
AmendmentDate [date]

This structure works fine . For every agreement, I get all
the
Amendments
associated with the agreement. It's the next join I'm
having
problems
with.

tAgreementWithJoin
AgreementWithJoinID [autonumber] (Primary Key)
AgreementID [number] (Links with
tAgreements.AgreementID)
AgreementWithID [number] (Links with
tAgreementWith.AgreementWithID)

tAgreementWith
AgreementWithID [autonumber] (Primary Key) (Links with
tAgreementWithJoin.AgreementWithID)
AgreementWith [text]

I often have an agreement with more then 1 organization.
For
each
agreement, using a combo box in Datasheet form, I choose
each
organization
that is part of each agreement. Here's my problem, for
each
agreement,
only
the last organization I choose will appear in my search
queries or
in
my

Hi Evi:

I don't think you're missing anything important ... I think I've missed
something pretty critical somewhere.

My combo box is based on the Org Table & OrgID is the Foreign Key to Org ID
in the Agree Table. After reading your reply, I'm beginning to think I have
a bigger problem then I started this inquiry with (argh!).

Evi, thanks to your help, I can see that my original design of this DB was
flawed, and I can't thank you enough for helping me. You've been very
gracious with your time and advice -- I hope will you bear with me a little
more.

For starters, part of the multiple listings in Org Table is me ... when I
originally created this DB 4 years ago, I didn't realize things would blossom
in to what this has become (nor did I have the knowledge on DBs I have
today). So, for my Org Table, if I had the same Org, however, a different
field office or point-of-contact, I listed the Org twice, with new new field
office or POC. That's PART of the problem. I know how to link an Org table
to an OrgAdd table, however, for this situation, won't that require a combo
box within a combo box -- can you do that?

The other part of the problem, the same Org if it has more then one
agreement, I do not know. So I'd like to start with what my query for the
combo box looks like:

tOrgWith (this is my conjuction table between Org and Agree)
OrgWithID [PK]
AgreeID
OrgID

tOrg
OrgID [PK] [FK to tOrgWith.OrgWithID]
OrgLine1
OrgLine2

I use
tOrg.Org ASC (with an IIf statement joining OrgLine1 & OrgLine2)
tOrg.POC
tOrgWith.OrgWithID
tOrgWith.AgreeID
tOrgWith.OrgID

For this query, if I do not use the the 3 fields in the OrgWith Table,
nothing will show up in my combo box.

Secondly, my combo box is built in to a separate subform from the Agree
Table. The subform contains:

tOrgWith.OrgWithID
tOrgWith.AgreeID
tOrgWith.OrgID
OrgWith combo [based off of above query]

I added the combo box to get my Org name to show up, because I wouldn't know
what ID # which Org was.

So that's the intricacies of how all this works (or doesn't work).

Once gain, Evi, I thank you for all your help.

G



"Evi" wrote:

your combo being based on a query is not a problem, nearly all of mine are.

You shouldn't need to close the form containing the combo - that's why you
have the Requery code, but if you still aren't seeing the data after
re-opening the closed form then your coding isn't the problem.

It *sounds* as if your combo's query is based on the wrong table if each Org
is listed more than once.

It should be based on the Org Table which should list each org only once.
You can link other tables to Org query if you need to (In a combo containing
names you might want to link the Address from the address table) but each
Org would only be used once.
The usual structure for a combo would be the Primary field first (OrgID) -
this is usually hidden in the combo by making its Column Width Property 0cm
so that the field you actually see is the User Friendly field - Org - then
any other fields which could be handy..
The Control Source of this combo would be OrgID which should be a Foreign
Key field in the table on which your form is based. The important field in
the combo, the one that does all the work, is usually the first (ugly,
hidden) one so if you are putting the OrdID number into your form using your
combo then OrgID should be the first field in the Combo.

Or have I missed something important (perfectly possible)
Evi




"GIraffe" wrote in message
...
Hi Evi:

Yes, my combo box is named AgreementWith.

Yes, I close the Org Form after I've entered the new record (the Agree
Form,
where the combo box is, is closed when I'm entering data in to the Org
Form).

I'm not sure it'll help, but let me give you a little more info on my
combo
box:
My combo box is based off of a query -- should that matter? My combo
box has 5 columns showing Org, POC (pt of contact), AWID (Org ID), AWJID
(Org
Join ID), & AgreeID.
What currently shows when I go to the dropdown arrow of my combo box
is
the 5 columns I have above. If an Org has more then 1 Agreement, it's
listed
twice.

One other clarification from my previous post -- I do NOT know VB (Sorry
for
the obvious oversight).

Thanks Evi.

"Evi" wrote:

Is your combo named AgreementWith (go to Properties and look at its Name
on
the Other tab)?

The On Focus only happens if you have clicked back onto your combo after
clicking off it (the code that sets focus on another control, ensures
that
you do that but you can do it yourself while you are testing the code.)

Are you closing the Org form (or pressing Shift and Enter) so that the
record is saved to your table for the combo to read when it requeries?



Evi


"GIraffe" wrote in message
...
Hi Evi (I don't know if I need to restart this thread or not ...)

I finally got around to trying the "GotFocus" suggestion you gave me
last
week. I appreciate you providing me with the code string, because I
do
know
VB.

I tried both the code suggestions (1 at a time) you provided me. For
the
first one, my line looks like: Me.AgreementWith.Requery

I ran a test under each code attempt, and my new organization addition
(Test) does not appear in the ComboBox. Historically, when I needed
to
add a
new Org, I would go to my Org form, add it, put the agreement info in
the
agreements form, then, then do an append query to pull the 2 together.
And I
can continue to do this if necessary.

What could be wrong? Can you give me some thoughts on what to look
for or
try to troubleshoot? Your VB suggestion seems like it would work
really
well
and provide me with a few less steps (ie, going to my "add new"
button,
which takes me to the form to add a new Org, then when I'm done the
form
closes and takes me back to my agreement form to finish adding the
agreements
data, then on to my append query).

Thank you Evi. You have been wonderfully patient.

"Evi" wrote:

Oh I see. OK, all you need to do is go into the Properties of the
Organization combo, Click on Got Focus, choose an Event Module,
click
just
right of that to open up a code page and just above where it says
End
Sub
type
Me.TheNameOfYourCombo.Requery.
Then, as soon as you click back onto your combo, the new name will
appear.
For my own use, I often use the DoubleClick Event to open my
connected
forms
and the code I put in the double click event first undoes what I
tried
to
type into the combo before finding it didn't have my choice, then
puts
my
cursor in the previous field to the combo so that I'm all ready to
enter
it,
causing it to requery.

For that, above the End Sub of the DoubleClick Code section type

Me.YourCombosName.Undo
Me.TheNameOfYourPreviousField.SetFocus
DoCmd.OpenForm "TheFormthat contains your organizations"

(there are other ways to do this using NotInList but this will get
you
started)

Evi

"GIraffe" wrote in message
...
Hi Evi:

I usually add an agreement first to tAgreement table, then scroll
through
my
organizations via the combo box, if an organization does not
exist, I
go
to
my organization form (filling the organization table) and add the
organization. The other problem I have is when I go back to my
form
for
the
tAgreement table, the organization does not appear in the combo
box.
I
usually have to do an append query to append the new organization
to
the
new
agreement (at least I have a work around for this piece).

I do not have OrgID in the tAgree, because I link
tAgreementWithJoin
(which
is the in between table with tAgree & tOrg) using AgreeID. I'll
try
adding
the OrgID from tAgreementWithJoin and see if it makes a
difference.
However,
isn't this a double join for the same info?

One thing I have learned through this exercisie is that the
relationship
setups can sure mess up how the output looks!

I appreciate your patience and I thank you for all your help.

"Evi" wrote:

I don't know what Agreements are (and probably wouldn't get it,
if
you
tried
to explain) but if each Agreement only exists when an
Organization
is
added,
so that you never need to add an Agreement more than once to
Agreements
table and it never needs to exist seperately from an
organization ,
then
your structure is quite right and you only need a combo box for
the
organization and a seperate organization table (I think that's
your
AggreementWith table, if I understand rightly)
So your ammended structure will be:

tAgreements:
AgreeID (PK)
Agreement
OrgID (Joined From tOrganization in the Relationships
window)



tOrganization
OrgID (PK)
Organization


Using the tAgreements, if you filter by orgID, you will see all
the
agreements which that organization has.


But if Agreements can exist before they are added to an
organization
(eg
you
have different types of Agreements available and an Organization
can
choose
which type they want) then you need the 3-table structure.

Evi


"GIraffe" wrote in message
...
Hi Evi:

Thank you SO much for your response. Sometimes I get a little
"wordy"
on
my
filed names. Thanks to this board, I have learned some
critical
things
about
field names ... and have stumbled in to some problems when I
previously
used
special characters, etc. So I'm getting better. I appreciate
your
paraphrasing.

For the most part you have it. However, I do not have a combo
box
based
on
tAgreements. I do have a combo box for tOrganization. My
tAgreements
table
is basically my "primary" table that everything either links
in to
or
is
based out of.

I think it's some kind of relationship thing. I'm just not
clear
how
to
figure this out.




"Evi" wrote:

It looks normal
The names are distracting me a bit so excuse me if I
paraphrase.
Given that the amendments are OK
What I'd expect to see for the other 3 tables is
tAgreements:

You 're using the wrong OrgID for your combo. You need the OrgID from TblOrg
in your combo - not the OrgID from TblAgreementWith.
Don't use any fields from TblAgreementWith if you are trying to put OrgID
into TblAgreementWith using your combo. That's like trying to scratch your
left elbow with your left elbow!

If OrgID - your Primary Field key -is Unique (as it must be, if it is a
Primary Key) then having each OrgName mentioned twice can be overcome until
you have time to redesign your db. All you need you need to do is to create
a query for your combo.
If your Org Table has OrgID, OrgName and POC then add OrgID to your query
first.

In the next column type
OrgFOC: [Org] &" " & [POC]

You can concatenate as many fields as you need here to give each record a
unique name in your combo. A handy tip I learnt on the Report newsgroup is
to link your fields using

Trim(([Org] + " ") & ([POC] + " ") & ([AnotherField]))


If any of the fields are null then + inside the () means that it won't show
anything, including the space.
Use the Trim to get rid of a final space if POC has a value but AnotherField
doesn't.



When you make the combo, add OrgID and then your concatenated field but
close the OrgID column by dragging it closed in the Wizard so that it won't
show in your combo.

Will this help?

If your Org table does not have a Unique Primary Key field (OrgID) then
Access can't possibly know which of the two OrgIDs to link to your
AgreementWith table.
It can be rectified but I won't start on that (long) process until I know it
is needed.
I've had to do it a quite a few times, when I got a design wrong


Evi



"GIraffe" wrote in message
...
Hi Evi:

I don't think you're missing anything important ... I think I've missed
something pretty critical somewhere.

My combo box is based on the Org Table & OrgID is the Foreign Key to Org
ID
in the Agree Table. After reading your reply, I'm beginning to think I
have
a bigger problem then I started this inquiry with (argh!).

Evi, thanks to your help, I can see that my original design of this DB was
flawed, and I can't thank you enough for helping me. You've been very
gracious with your time and advice -- I hope will you bear with me a
little
more.

For starters, part of the multiple listings in Org Table is me ... when I
originally created this DB 4 years ago, I didn't realize things would
blossom
in to what this has become (nor did I have the knowledge on DBs I have
today). So, for my Org Table, if I had the same Org, however, a different
field office or point-of-contact, I listed the Org twice, with new new
field
office or POC. That's PART of the problem. I know how to link an Org
table
to an OrgAdd table, however, for this situation, won't that require a
combo
box within a combo box -- can you do that?

The other part of the problem, the same Org if it has more then one
agreement, I do not know. So I'd like to start with what my query for the
combo box looks like:

tOrgWith (this is my conjuction table between Org and Agree)
OrgWithID [PK]
AgreeID
OrgID

tOrg
OrgID [PK] [FK to tOrgWith.OrgWithID]
OrgLine1
OrgLine2

I use
tOrg.Org ASC (with an IIf statement joining OrgLine1 & OrgLine2)
tOrg.POC
tOrgWith.OrgWithID
tOrgWith.AgreeID
tOrgWith.OrgID

For this query, if I do not use the the 3 fields in the OrgWith Table,
nothing will show up in my combo box.

Secondly, my combo box is built in to a separate subform from the Agree
Table. The subform contains:

tOrgWith.OrgWithID
tOrgWith.AgreeID
tOrgWith.OrgID
OrgWith combo [based off of above query]

I added the combo box to get my Org name to show up, because I wouldn't
know
what ID # which Org was.

So that's the intricacies of how all this works (or doesn't work).

Once gain, Evi, I thank you for all your help.

G



"Evi" wrote:

your combo being based on a query is not a problem, nearly all of mine
are.

You shouldn't need to close the form containing the combo - that's why
you
have the Requery code, but if you still aren't seeing the data after
re-opening the closed form then your coding isn't the problem.

It *sounds* as if your combo's query is based on the wrong table if each
Org
is listed more than once.

It should be based on the Org Table which should list each org only
once.
You can link other tables to Org query if you need to (In a combo
containing
names you might want to link the Address from the address table) but
each
Org would only be used once.
The usual structure for a combo would be the Primary field first
(OrgID) -
this is usually hidden in the combo by making its Column Width Property
0cm
so that the field you actually see is the User Friendly field - Org -
then
any other fields which could be handy..
The Control Source of this combo would be OrgID which should be a
Foreign
Key field in the table on which your form is based. The important field
in
the combo, the one that does all the work, is usually the first (ugly,
hidden) one so if you are putting the OrdID number into your form using
your
combo then OrgID should be the first field in the Combo.

Or have I missed something important (perfectly possible)
Evi




"GIraffe" wrote in message
...
Hi Evi:

Yes, my combo box is named AgreementWith.

Yes, I close the Org Form after I've entered the new record (the Agree
Form,
where the combo box is, is closed when I'm entering data in to the Org
Form).

I'm not sure it'll help, but let me give you a little more info on my
combo
box:
My combo box is based off of a query -- should that matter? My
combo
box has 5 columns showing Org, POC (pt of contact), AWID (Org ID),
AWJID
(Org
Join ID), & AgreeID.
What currently shows when I go to the dropdown arrow of my combo
box
is
the 5 columns I have above. If an Org has more then 1 Agreement, it's
listed
twice.

One other clarification from my previous post -- I do NOT know VB
(Sorry
for
the obvious oversight).

Thanks Evi.

"Evi" wrote:

Is your combo named AgreementWith (go to Properties and look at its
Name
on
the Other tab)?

The On Focus only happens if you have clicked back onto your combo
after
clicking off it (the code that sets focus on another control,
ensures
that
you do that but you can do it yourself while you are testing the
code.)

Are you closing the Org form (or pressing Shift and Enter) so that
the
record is saved to your table for the combo to read when it
requeries?



Evi


"GIraffe" wrote in message
...
Hi Evi (I don't know if I need to restart this thread or not ...)

I finally got around to trying the "GotFocus" suggestion you gave
me
last
week. I appreciate you providing me with the code string, because
I
do
know
VB.

I tried both the code suggestions (1 at a time) you provided me.
For
the
first one, my line looks like: Me.AgreementWith.Requery

I ran a test under each code attempt, and my new organization
addition
(Test) does not appear in the ComboBox. Historically, when I
needed
to
add a
new Org, I would go to my Org form, add it, put the agreement info
in
the
agreements form, then, then do an append query to pull the 2
together.
And I
can continue to do this if necessary.

What could be wrong? Can you give me some thoughts on what to
look
for or
try to troubleshoot? Your VB suggestion seems like it would work
really
well
and provide me with a few less steps (ie, going to my "add new"
button,
which takes me to the form to add a new Org, then when I'm done
the
form
closes and takes me back to my agreement form to finish adding the
agreements
data, then on to my append query).

Thank you Evi. You have been wonderfully patient.

"Evi" wrote:

Oh I see. OK, all you need to do is go into the Properties of
the
Organization combo, Click on Got Focus, choose an Event Module,
click
just
right of that to open up a code page and just above where it
says
End
Sub
type
Me.TheNameOfYourCombo.Requery.
Then, as soon as you click back onto your combo, the new name
will
appear.
For my own use, I often use the DoubleClick Event to open my
connected
forms
and the code I put in the double click event first undoes what I
tried
to
type into the combo before finding it didn't have my choice,
then
puts
my
cursor in the previous field to the combo so that I'm all ready
to
enter
it,
causing it to requery.

For that, above the End Sub of the DoubleClick Code section type

Me.YourCombosName.Undo
Me.TheNameOfYourPreviousField.SetFocus
DoCmd.OpenForm "TheFormthat contains your organizations"

(there are other ways to do this using NotInList but this will
get
you
started)

Evi

"GIraffe" wrote in message
...
Hi Evi:

I usually add an agreement first to tAgreement table, then
scroll
through
my
organizations via the combo box, if an organization does not
exist, I
go
to
my organization form (filling the organization table) and add
the
organization. The other problem I have is when I go back to
my
form
for
the
tAgreement table, the organization does not appear in the
combo
box.
I
usually have to do an append query to append the new
organization
to
the
new
agreement (at least I have a work around for this piece).

I do not have OrgID in the tAgree, because I link
tAgreementWithJoin
(which
is the in between table with tAgree & tOrg) using AgreeID.
I'll
try
adding
the OrgID from tAgreementWithJoin and see if it makes a
difference.
However,
isn't this a double join for the same info?

One thing I have learned through this exercisie is that the
relationship
setups can sure mess up how the output looks!

I appreciate your patience and I thank you for all your help.

"Evi" wrote:

I don't know what Agreements are (and probably wouldn't get
it,
if
you
tried
to explain) but if each Agreement only exists when an
Organization
is
added,
so that you never need to add an Agreement more than once to
Agreements
table and it never needs to exist seperately from an
organization ,
then
your structure is quite right and you only need a combo box
for
the
organization and a seperate organization table (I think
that's
your
AggreementWith table, if I understand rightly)
So your ammended structure will be:

tAgreements:
AgreeID (PK)
Agreement
OrgID (Joined From tOrganization in the Relationships
window)



tOrganization
OrgID (PK)
Organization


Using the tAgreements, if you filter by orgID, you will see
all
the
agreements which that organization has.


But if Agreements can exist before they are added to an
organization
(eg
you
have different types of Agreements available and an
Organization
can
choose
which type they want) then you need the 3-table structure.

Evi


"GIraffe" wrote in
message
...
Hi Evi:

Thank you SO much for your response. Sometimes I get a
little
"wordy"
on
my
filed names. Thanks to this board, I have learned some
critical
things
about
field names ... and have stumbled in to some problems when
I
previously
used
special characters, etc. So I'm getting better. I
appreciate
your
paraphrasing.

For the most part you have it. However, I do not have a
combo
box
based
on
tAgreements. I do have a combo box for tOrganization. My
tAgreements
table
is basically my "primary" table that everything either
links
in to
or
is
based out of.

I think it's some kind of relationship thing. I'm just
not
clear
how
to
figure this out.




"Evi" wrote:

It looks normal
The names are distracting me a bit so excuse me if I
paraphrase.
Given that the amendments are OK
What I'd expect to see for the other 3 tables is
tAgreements:

Hi Evi:

I've read over your reply several times and I had wondered if my OrgWith
table was causing some of the problem.

I am going to copy my database (so if I blow something up I have something
to go back to) and give this a try. I understand your explanation and am
looking forward to giving this a try.

I'll be back in touch next week. Have a great weekend!

G

"Evi" wrote:

You 're using the wrong OrgID for your combo. You need the OrgID from TblOrg
in your combo - not the OrgID from TblAgreementWith.
Don't use any fields from TblAgreementWith if you are trying to put OrgID
into TblAgreementWith using your combo. That's like trying to scratch your
left elbow with your left elbow!

If OrgID - your Primary Field key -is Unique (as it must be, if it is a
Primary Key) then having each OrgName mentioned twice can be overcome until
you have time to redesign your db. All you need you need to do is to create
a query for your combo.
If your Org Table has OrgID, OrgName and POC then add OrgID to your query
first.

In the next column type
OrgFOC: [Org] &" " & [POC]

You can concatenate as many fields as you need here to give each record a
unique name in your combo. A handy tip I learnt on the Report newsgroup is
to link your fields using

Trim(([Org] + " ") & ([POC] + " ") & ([AnotherField]))


If any of the fields are null then + inside the () means that it won't show
anything, including the space.
Use the Trim to get rid of a final space if POC has a value but AnotherField
doesn't.



When you make the combo, add OrgID and then your concatenated field but
close the OrgID column by dragging it closed in the Wizard so that it won't
show in your combo.

Will this help?

If your Org table does not have a Unique Primary Key field (OrgID) then
Access can't possibly know which of the two OrgIDs to link to your
AgreementWith table.
It can be rectified but I won't start on that (long) process until I know it
is needed.
I've had to do it a quite a few times, when I got a design wrong


Evi



"GIraffe" wrote in message
...
Hi Evi:

I don't think you're missing anything important ... I think I've missed
something pretty critical somewhere.

My combo box is based on the Org Table & OrgID is the Foreign Key to Org
ID
in the Agree Table. After reading your reply, I'm beginning to think I
have
a bigger problem then I started this inquiry with (argh!).

Evi, thanks to your help, I can see that my original design of this DB was
flawed, and I can't thank you enough for helping me. You've been very
gracious with your time and advice -- I hope will you bear with me a
little
more.

For starters, part of the multiple listings in Org Table is me ... when I
originally created this DB 4 years ago, I didn't realize things would
blossom
in to what this has become (nor did I have the knowledge on DBs I have
today). So, for my Org Table, if I had the same Org, however, a different
field office or point-of-contact, I listed the Org twice, with new new
field
office or POC. That's PART of the problem. I know how to link an Org
table
to an OrgAdd table, however, for this situation, won't that require a
combo
box within a combo box -- can you do that?

The other part of the problem, the same Org if it has more then one
agreement, I do not know. So I'd like to start with what my query for the
combo box looks like:

tOrgWith (this is my conjuction table between Org and Agree)
OrgWithID [PK]
AgreeID
OrgID

tOrg
OrgID [PK] [FK to tOrgWith.OrgWithID]
OrgLine1
OrgLine2

I use
tOrg.Org ASC (with an IIf statement joining OrgLine1 & OrgLine2)
tOrg.POC
tOrgWith.OrgWithID
tOrgWith.AgreeID
tOrgWith.OrgID

For this query, if I do not use the the 3 fields in the OrgWith Table,
nothing will show up in my combo box.

Secondly, my combo box is built in to a separate subform from the Agree
Table. The subform contains:

tOrgWith.OrgWithID
tOrgWith.AgreeID
tOrgWith.OrgID
OrgWith combo [based off of above query]

I added the combo box to get my Org name to show up, because I wouldn't
know
what ID # which Org was.

So that's the intricacies of how all this works (or doesn't work).

Once gain, Evi, I thank you for all your help.

G



"Evi" wrote:

your combo being based on a query is not a problem, nearly all of mine
are.

You shouldn't need to close the form containing the combo - that's why
you
have the Requery code, but if you still aren't seeing the data after
re-opening the closed form then your coding isn't the problem.

It *sounds* as if your combo's query is based on the wrong table if each
Org
is listed more than once.

It should be based on the Org Table which should list each org only
once.
You can link other tables to Org query if you need to (In a combo
containing
names you might want to link the Address from the address table) but
each
Org would only be used once.
The usual structure for a combo would be the Primary field first
(OrgID) -
this is usually hidden in the combo by making its Column Width Property
0cm
so that the field you actually see is the User Friendly field - Org -
then
any other fields which could be handy..
The Control Source of this combo would be OrgID which should be a
Foreign
Key field in the table on which your form is based. The important field
in
the combo, the one that does all the work, is usually the first (ugly,
hidden) one so if you are putting the OrdID number into your form using
your
combo then OrgID should be the first field in the Combo.

Or have I missed something important (perfectly possible)
Evi




"GIraffe" wrote in message
...
Hi Evi:

Yes, my combo box is named AgreementWith.

Yes, I close the Org Form after I've entered the new record (the Agree
Form,
where the combo box is, is closed when I'm entering data in to the Org
Form).

I'm not sure it'll help, but let me give you a little more info on my
combo
box:
My combo box is based off of a query -- should that matter? My
combo
box has 5 columns showing Org, POC (pt of contact), AWID (Org ID),
AWJID
(Org
Join ID), & AgreeID.
What currently shows when I go to the dropdown arrow of my combo
box
is
the 5 columns I have above. If an Org has more then 1 Agreement, it's
listed
twice.

One other clarification from my previous post -- I do NOT know VB
(Sorry
for
the obvious oversight).

Thanks Evi.

"Evi" wrote:

Is your combo named AgreementWith (go to Properties and look at its
Name
on
the Other tab)?

The On Focus only happens if you have clicked back onto your combo
after
clicking off it (the code that sets focus on another control,
ensures
that
you do that but you can do it yourself while you are testing the
code.)

Are you closing the Org form (or pressing Shift and Enter) so that
the
record is saved to your table for the combo to read when it
requeries?



Evi


"GIraffe" wrote in message
...
Hi Evi (I don't know if I need to restart this thread or not ...)

I finally got around to trying the "GotFocus" suggestion you gave
me
last
week. I appreciate you providing me with the code string, because
I
do
know
VB.

I tried both the code suggestions (1 at a time) you provided me.
For
the
first one, my line looks like: Me.AgreementWith.Requery

I ran a test under each code attempt, and my new organization
addition
(Test) does not appear in the ComboBox. Historically, when I
needed
to
add a
new Org, I would go to my Org form, add it, put the agreement info
in
the
agreements form, then, then do an append query to pull the 2
together.
And I
can continue to do this if necessary.

What could be wrong? Can you give me some thoughts on what to
look
for or
try to troubleshoot? Your VB suggestion seems like it would work
really
well
and provide me with a few less steps (ie, going to my "add new"
button,
which takes me to the form to add a new Org, then when I'm done
the
form
closes and takes me back to my agreement form to finish adding the
agreements
data, then on to my append query).

Thank you Evi. You have been wonderfully patient.

"Evi" wrote:

Oh I see. OK, all you need to do is go into the Properties of
the
Organization combo, Click on Got Focus, choose an Event Module,
click
just
right of that to open up a code page and just above where it
says
End
Sub
type
Me.TheNameOfYourCombo.Requery.
Then, as soon as you click back onto your combo, the new name
will
appear.
For my own use, I often use the DoubleClick Event to open my
connected
forms
and the code I put in the double click event first undoes what I
tried
to
type into the combo before finding it didn't have my choice,
then
puts

Hi Evi:

I'm obviously missing something. First, I chose not to concatenate my
fields, I wanted to see if I could get the principle of this to work first.

I created the query [qOrg] putting 2 tables in the query [tOrg] &
[tOrgWith], figuring I needed the tOrgWith table because it's a conjunction
table for tAgreements & tOrg, even tho I didn't use any fields from it. From
tOrg I chose OrgID, Org, POC.

I made my combo box in my fAgreements, not as a subform, but as a combo box.
I chose to use my above query, chose my 3 fields, related them by OrgID,
chose to remember the value for a later use, went in to the Column Widths of
the Properties box and made my first column (my OrgID) 0". So now only my
Org shows up (don't know why POC's not showing up). However, the Org that's
showing up is not the correct Org for the Agreement (on none of the
agreements). Do I need to rechoose each Org for each agreement? Secondly,
just to test this, I went in to my Org table and added a new Org, when back
to my form and it does not show up . And lastly, some agreements have more
then one Org, how can I do this in the combo box?

What am I missing?

Thanks for your help Evi.

G




"Evi" wrote:

You 're using the wrong OrgID for your combo. You need the OrgID from TblOrg
in your combo - not the OrgID from TblAgreementWith.
Don't use any fields from TblAgreementWith if you are trying to put OrgID
into TblAgreementWith using your combo. That's like trying to scratch your
left elbow with your left elbow!

If OrgID - your Primary Field key -is Unique (as it must be, if it is a
Primary Key) then having each OrgName mentioned twice can be overcome until
you have time to redesign your db. All you need you need to do is to create
a query for your combo.
If your Org Table has OrgID, OrgName and POC then add OrgID to your query
first.

In the next column type
OrgFOC: [Org] &" " & [POC]

You can concatenate as many fields as you need here to give each record a
unique name in your combo. A handy tip I learnt on the Report newsgroup is
to link your fields using

Trim(([Org] + " ") & ([POC] + " ") & ([AnotherField]))


If any of the fields are null then + inside the () means that it won't show
anything, including the space.
Use the Trim to get rid of a final space if POC has a value but AnotherField
doesn't.



When you make the combo, add OrgID and then your concatenated field but
close the OrgID column by dragging it closed in the Wizard so that it won't
show in your combo.

Will this help?

If your Org table does not have a Unique Primary Key field (OrgID) then
Access can't possibly know which of the two OrgIDs to link to your
AgreementWith table.
It can be rectified but I won't start on that (long) process until I know it
is needed.
I've had to do it a quite a few times, when I got a design wrong


Evi



"GIraffe" wrote in message
...
Hi Evi:

I don't think you're missing anything important ... I think I've missed
something pretty critical somewhere.

My combo box is based on the Org Table & OrgID is the Foreign Key to Org
ID
in the Agree Table. After reading your reply, I'm beginning to think I
have
a bigger problem then I started this inquiry with (argh!).

Evi, thanks to your help, I can see that my original design of this DB was
flawed, and I can't thank you enough for helping me. You've been very
gracious with your time and advice -- I hope will you bear with me a
little
more.

For starters, part of the multiple listings in Org Table is me ... when I
originally created this DB 4 years ago, I didn't realize things would
blossom
in to what this has become (nor did I have the knowledge on DBs I have
today). So, for my Org Table, if I had the same Org, however, a different
field office or point-of-contact, I listed the Org twice, with new new
field
office or POC. That's PART of the problem. I know how to link an Org
table
to an OrgAdd table, however, for this situation, won't that require a
combo
box within a combo box -- can you do that?

The other part of the problem, the same Org if it has more then one
agreement, I do not know. So I'd like to start with what my query for the
combo box looks like:

tOrgWith (this is my conjuction table between Org and Agree)
OrgWithID [PK]
AgreeID
OrgID

tOrg
OrgID [PK] [FK to tOrgWith.OrgWithID]
OrgLine1
OrgLine2

I use
tOrg.Org ASC (with an IIf statement joining OrgLine1 & OrgLine2)
tOrg.POC
tOrgWith.OrgWithID
tOrgWith.AgreeID
tOrgWith.OrgID

For this query, if I do not use the the 3 fields in the OrgWith Table,
nothing will show up in my combo box.

Secondly, my combo box is built in to a separate subform from the Agree
Table. The subform contains:

tOrgWith.OrgWithID
tOrgWith.AgreeID
tOrgWith.OrgID
OrgWith combo [based off of above query]

I added the combo box to get my Org name to show up, because I wouldn't
know
what ID # which Org was.

So that's the intricacies of how all this works (or doesn't work).

Once gain, Evi, I thank you for all your help.

G



"Evi" wrote:

your combo being based on a query is not a problem, nearly all of mine
are.

You shouldn't need to close the form containing the combo - that's why
you
have the Requery code, but if you still aren't seeing the data after
re-opening the closed form then your coding isn't the problem.

It *sounds* as if your combo's query is based on the wrong table if each
Org
is listed more than once.

It should be based on the Org Table which should list each org only
once.
You can link other tables to Org query if you need to (In a combo
containing
names you might want to link the Address from the address table) but
each
Org would only be used once.
The usual structure for a combo would be the Primary field first
(OrgID) -
this is usually hidden in the combo by making its Column Width Property
0cm
so that the field you actually see is the User Friendly field - Org -
then
any other fields which could be handy..
The Control Source of this combo would be OrgID which should be a
Foreign
Key field in the table on which your form is based. The important field
in
the combo, the one that does all the work, is usually the first (ugly,
hidden) one so if you are putting the OrdID number into your form using
your
combo then OrgID should be the first field in the Combo.

Or have I missed something important (perfectly possible)
Evi




"GIraffe" wrote in message
...
Hi Evi:

Yes, my combo box is named AgreementWith.

Yes, I close the Org Form after I've entered the new record (the Agree
Form,
where the combo box is, is closed when I'm entering data in to the Org
Form).

I'm not sure it'll help, but let me give you a little more info on my
combo
box:
My combo box is based off of a query -- should that matter? My
combo
box has 5 columns showing Org, POC (pt of contact), AWID (Org ID),
AWJID
(Org
Join ID), & AgreeID.
What currently shows when I go to the dropdown arrow of my combo
box
is
the 5 columns I have above. If an Org has more then 1 Agreement, it's
listed
twice.

One other clarification from my previous post -- I do NOT know VB
(Sorry
for
the obvious oversight).

Thanks Evi.

"Evi" wrote:

Is your combo named AgreementWith (go to Properties and look at its
Name
on
the Other tab)?

The On Focus only happens if you have clicked back onto your combo
after
clicking off it (the code that sets focus on another control,
ensures
that
you do that but you can do it yourself while you are testing the
code.)

Are you closing the Org form (or pressing Shift and Enter) so that
the
record is saved to your table for the combo to read when it
requeries?



Evi


"GIraffe" wrote in message
...
Hi Evi (I don't know if I need to restart this thread or not ...)

I finally got around to trying the "GotFocus" suggestion you gave
me
last
week. I appreciate you providing me with the code string, because
I
do
know
VB.

I tried both the code suggestions (1 at a time) you provided me.
For
the
first one, my line looks like: Me.AgreementWith.Requery

I ran a test under each code attempt, and my new organization
addition
(Test) does not appear in the ComboBox. Historically, when I
needed
to
add a
new Org, I would go to my Org form, add it, put the agreement info
in
the
agreements form, then, then do an append query to pull the 2
together.
And I
can continue to do this if necessary.

What could be wrong? Can you give me some thoughts on what to
look
for or
try to troubleshoot? Your VB suggestion seems like it would work
really
well
and provide me with a few less steps (ie, going to my "add new"
button,
which takes me to the form to add a new Org, then when I'm done
the
form
closes and takes me back to my agreement form to finish adding the
agreements
data, then on to my append query).

Thank you Evi. You have been wonderfully patient.

"Evi" wrote:

Oh I see. OK, all you need to do is go into the Properties of
the
Organization combo, Click on Got Focus, choose an Event Module,
click
just
right of that to open up a code page and just above where it
says
End
Sub
type
Me.TheNameOfYourCombo.Requery.
Then, as soon as you click back onto your combo, the new name
will
appear.
For my own use, I often use the DoubleClick Event to open my
connected
forms
and the code I put in the double click event first undoes what I
tried
to
type into the combo before finding it didn't have my choice,
then
puts

I have this horrible feeling that we are both misunderstanding something. Oh
for a crystal ball so I could see what is happening!
Let's check that we've got the structure right. (the thread is getting long
and my brain is short!)
TblAgreement should contain each Agreement only once PK AgreementID
TblOrg should have each org only once - PK OrgID
TblAgreement With should have the foreign key fields AgreementID and OrgID

The structure of your Form should be like this (basically)
Give this a try and you'll see what I mean.

Create a datasheet form based on the AgreementWith table. Don't add fields
from the other tables just yet until you get a real feel for this sort of
structure.
Add a combo to it in Design View. It should be based on TblAgreement (or on
a query that is based directly on that table - don't add fields from the
other tables for now)
It's first field should be AgreementID, pull the column closed in the
Wizard, its next field (the one which you will actually see in the combo)
can be any comination of fields in TblAgreement which will help you to
identify that Agreement. When you get to the relevant page in the Wizard,
agree to store the value in AgreementID

Similarly, add a combo based on TblOrg or a query based only on that table-
again make the first field OrgID, hide it and then add the OrgName

use the combos to fill in the 2 Foreign Key fields in TblAgreementWith.

Is this bit working, G?

Evi


"GIraffe" wrote in message
...
Hi Evi:

I'm obviously missing something. First, I chose not to concatenate my
fields, I wanted to see if I could get the principle of this to work
first.

I created the query [qOrg] putting 2 tables in the query [tOrg] &
[tOrgWith], figuring I needed the tOrgWith table because it's a
conjunction
table for tAgreements & tOrg, even tho I didn't use any fields from it.
From
tOrg I chose OrgID, Org, POC.

I made my combo box in my fAgreements, not as a subform, but as a combo
box.
I chose to use my above query, chose my 3 fields, related them by OrgID,
chose to remember the value for a later use, went in to the Column Widths
of
the Properties box and made my first column (my OrgID) 0". So now only my
Org shows up (don't know why POC's not showing up). However, the Org
that's
showing up is not the correct Org for the Agreement (on none of the
agreements). Do I need to rechoose each Org for each agreement?
Secondly,
just to test this, I went in to my Org table and added a new Org, when
back
to my form and it does not show up . And lastly, some agreements have
more
then one Org, how can I do this in the combo box?

What am I missing?

Thanks for your help Evi.

G




"Evi" wrote:

You 're using the wrong OrgID for your combo. You need the OrgID from
TblOrg
in your combo - not the OrgID from TblAgreementWith.
Don't use any fields from TblAgreementWith if you are trying to put
OrgID
into TblAgreementWith using your combo. That's like trying to scratch
your
left elbow with your left elbow!

If OrgID - your Primary Field key -is Unique (as it must be, if it is a
Primary Key) then having each OrgName mentioned twice can be overcome
until
you have time to redesign your db. All you need you need to do is to
create
a query for your combo.
If your Org Table has OrgID, OrgName and POC then add OrgID to your
query
first.

In the next column type
OrgFOC: [Org] &" " & [POC]

You can concatenate as many fields as you need here to give each record
a
unique name in your combo. A handy tip I learnt on the Report newsgroup
is
to link your fields using

Trim(([Org] + " ") & ([POC] + " ") & ([AnotherField]))


If any of the fields are null then + inside the () means that it won't
show
anything, including the space.
Use the Trim to get rid of a final space if POC has a value but
AnotherField
doesn't.



When you make the combo, add OrgID and then your concatenated field but
close the OrgID column by dragging it closed in the Wizard so that it
won't
show in your combo.

Will this help?

If your Org table does not have a Unique Primary Key field (OrgID) then
Access can't possibly know which of the two OrgIDs to link to your
AgreementWith table.
It can be rectified but I won't start on that (long) process until I
know it
is needed.
I've had to do it a quite a few times, when I got a design wrong


Evi



"GIraffe" wrote in message
...
Hi Evi:

I don't think you're missing anything important ... I think I've
missed
something pretty critical somewhere.

My combo box is based on the Org Table & OrgID is the Foreign Key to
Org
ID
in the Agree Table. After reading your reply, I'm beginning to think
I
have
a bigger problem then I started this inquiry with (argh!).

Evi, thanks to your help, I can see that my original design of this DB
was
flawed, and I can't thank you enough for helping me. You've been very
gracious with your time and advice -- I hope will you bear with me a
little
more.

For starters, part of the multiple listings in Org Table is me ...
when I
originally created this DB 4 years ago, I didn't realize things would
blossom
in to what this has become (nor did I have the knowledge on DBs I have
today). So, for my Org Table, if I had the same Org, however, a
different
field office or point-of-contact, I listed the Org twice, with new new
field
office or POC. That's PART of the problem. I know how to link an Org
table
to an OrgAdd table, however, for this situation, won't that require a
combo
box within a combo box -- can you do that?

The other part of the problem, the same Org if it has more then one
agreement, I do not know. So I'd like to start with what my query for
the
combo box looks like:

tOrgWith (this is my conjuction table between Org and Agree)
OrgWithID [PK]
AgreeID
OrgID

tOrg
OrgID [PK] [FK to tOrgWith.OrgWithID]
OrgLine1
OrgLine2

I use
tOrg.Org ASC (with an IIf statement joining OrgLine1 & OrgLine2)
tOrg.POC
tOrgWith.OrgWithID
tOrgWith.AgreeID
tOrgWith.OrgID

For this query, if I do not use the the 3 fields in the OrgWith Table,
nothing will show up in my combo box.

Secondly, my combo box is built in to a separate subform from the
Agree
Table. The subform contains:

tOrgWith.OrgWithID
tOrgWith.AgreeID
tOrgWith.OrgID
OrgWith combo [based off of above query]

I added the combo box to get my Org name to show up, because I
wouldn't
know
what ID # which Org was.

So that's the intricacies of how all this works (or doesn't work).

Once gain, Evi, I thank you for all your help.

G



"Evi" wrote:

your combo being based on a query is not a problem, nearly all of
mine
are.

You shouldn't need to close the form containing the combo - that's
why
you
have the Requery code, but if you still aren't seeing the data after
re-opening the closed form then your coding isn't the problem.

It *sounds* as if your combo's query is based on the wrong table if
each
Org
is listed more than once.

It should be based on the Org Table which should list each org only
once.
You can link other tables to Org query if you need to (In a combo
containing
names you might want to link the Address from the address table) but
each
Org would only be used once.
The usual structure for a combo would be the Primary field first
(OrgID) -
this is usually hidden in the combo by making its Column Width
Property
0cm
so that the field you actually see is the User Friendly field -
Org -
then
any other fields which could be handy..
The Control Source of this combo would be OrgID which should be a
Foreign
Key field in the table on which your form is based. The important
field
in
the combo, the one that does all the work, is usually the first
(ugly,
hidden) one so if you are putting the OrdID number into your form
using
your
combo then OrgID should be the first field in the Combo.

Or have I missed something important (perfectly possible)
Evi




"GIraffe" wrote in message
...
Hi Evi:

Yes, my combo box is named AgreementWith.

Yes, I close the Org Form after I've entered the new record (the
Agree
Form,
where the combo box is, is closed when I'm entering data in to the
Org
Form).

I'm not sure it'll help, but let me give you a little more info on
my
combo
box:
My combo box is based off of a query -- should that matter?
My
combo
box has 5 columns showing Org, POC (pt of contact), AWID (Org ID),
AWJID
(Org
Join ID), & AgreeID.
What currently shows when I go to the dropdown arrow of my
combo
box
is
the 5 columns I have above. If an Org has more then 1 Agreement,
it's
listed
twice.

One other clarification from my previous post -- I do NOT know VB
(Sorry
for
the obvious oversight).

Thanks Evi.

"Evi" wrote:

Is your combo named AgreementWith (go to Properties and look at
its
Name
on
the Other tab)?

The On Focus only happens if you have clicked back onto your
combo
after
clicking off it (the code that sets focus on another control,
ensures
that
you do that but you can do it yourself while you are testing the
code.)

Are you closing the Org form (or pressing Shift and Enter) so
that
the
record is saved to your table for the combo to read when it
requeries?



Evi


"GIraffe" wrote in message
...
Hi Evi (I don't know if I need to restart this thread or not
....)

I finally got around to trying the "GotFocus" suggestion you
gave
me
last
week. I appreciate you providing me with the code string,
because
I
do
know
VB.

I tried both the code suggestions (1 at a time) you provided
me.
For
the
first one, my line looks like: Me.AgreementWith.Requery

I ran a test under each code attempt, and my new organization
addition
(Test) does not appear in the ComboBox. Historically, when I
needed
to
add a
new Org, I would go to my Org form, add it, put the agreement
info
in
the
agreements form, then, then do an append query to pull the 2
together.
And I
can continue to do this if necessary.

What could be wrong? Can you give me some thoughts on what to
look
for or
try to troubleshoot? Your VB suggestion seems like it would
work
really
well
and provide me with a few less steps (ie, going to my "add
new"
button,
which takes me to the form to add a new Org, then when I'm
done
the
form
closes and takes me back to my agreement form to finish adding
the
agreements
data, then on to my append query).

Thank you Evi. You have been wonderfully patient.

"Evi" wrote:

Oh I see. OK, all you need to do is go into the Properties
of
the
Organization combo, Click on Got Focus, choose an Event
Module,
click
just
right of that to open up a code page and just above where it
says
End
Sub
type
Me.TheNameOfYourCombo.Requery.
Then, as soon as you click back onto your combo, the new
name
will
appear.
For my own use, I often use the DoubleClick Event to open my
connected
forms
and the code I put in the double click event first undoes
what I
tried
to
type into the combo before finding it didn't have my choice,
then
puts

Hi Evi:

Boy, to have you looking over my shoulder would sure have made this sooo
much easier to solve. Sorry for the long thread ....

Okay, here's what I have:

My tables are setup exactly as you describe, with the exception of in my
TblAgreementWith I also have an AgreementWithID as PK; however, I don't THINK
that'll make a difference .... right?

I THINK I created the datasheet form correctly. Here's what I have:

Data based on TblAgreementWith
AgreementWithID
AgreementID
OrgID
Combobox: TblAgreement.AgreementID (2nd field chosen AgreementNo)
Combobox: TblOrg.OrgID (2nd field, OrgLine1; 3rd field, OrgLine2; 4th
field, POC)

In View mode of this datasheet I have

AgreementWithID, AgreementID, OrgID, AgreementNo, OrgLine 1 (if I can get
this fixed, I will be combining several fields in one for my Orgs)

If I go to either AgreementNo or OrgLine1, I get a dropdown option and I can
actually change either one on a record.

And, guess what Evi? ALL my Org show up ... even the tests I setup as we've
been working through this. This is a FIRST. I'm very excited.

Please tell me I have it right so far.

G

"Evi" wrote:

I have this horrible feeling that we are both misunderstanding something. Oh
for a crystal ball so I could see what is happening!
Let's check that we've got the structure right. (the thread is getting long
and my brain is short!)
TblAgreement should contain each Agreement only once PK AgreementID
TblOrg should have each org only once - PK OrgID
TblAgreement With should have the foreign key fields AgreementID and OrgID

The structure of your Form should be like this (basically)
Give this a try and you'll see what I mean.

Create a datasheet form based on the AgreementWith table. Don't add fields
from the other tables just yet until you get a real feel for this sort of
structure.
Add a combo to it in Design View. It should be based on TblAgreement (or on
a query that is based directly on that table - don't add fields from the
other tables for now)
It's first field should be AgreementID, pull the column closed in the
Wizard, its next field (the one which you will actually see in the combo)
can be any comination of fields in TblAgreement which will help you to
identify that Agreement. When you get to the relevant page in the Wizard,
agree to store the value in AgreementID

Similarly, add a combo based on TblOrg or a query based only on that table-
again make the first field OrgID, hide it and then add the OrgName

use the combos to fill in the 2 Foreign Key fields in TblAgreementWith.

Is this bit working, G?

Evi


"GIraffe" wrote in message
...
Hi Evi:

I'm obviously missing something. First, I chose not to concatenate my
fields, I wanted to see if I could get the principle of this to work
first.

I created the query [qOrg] putting 2 tables in the query [tOrg] &
[tOrgWith], figuring I needed the tOrgWith table because it's a
conjunction
table for tAgreements & tOrg, even tho I didn't use any fields from it.
From
tOrg I chose OrgID, Org, POC.

I made my combo box in my fAgreements, not as a subform, but as a combo
box.
I chose to use my above query, chose my 3 fields, related them by OrgID,
chose to remember the value for a later use, went in to the Column Widths
of
the Properties box and made my first column (my OrgID) 0". So now only my
Org shows up (don't know why POC's not showing up). However, the Org
that's
showing up is not the correct Org for the Agreement (on none of the
agreements). Do I need to rechoose each Org for each agreement?
Secondly,
just to test this, I went in to my Org table and added a new Org, when
back
to my form and it does not show up . And lastly, some agreements have
more
then one Org, how can I do this in the combo box?

What am I missing?

Thanks for your help Evi.

G




"Evi" wrote:

You 're using the wrong OrgID for your combo. You need the OrgID from
TblOrg
in your combo - not the OrgID from TblAgreementWith.
Don't use any fields from TblAgreementWith if you are trying to put
OrgID
into TblAgreementWith using your combo. That's like trying to scratch
your
left elbow with your left elbow!

If OrgID - your Primary Field key -is Unique (as it must be, if it is a
Primary Key) then having each OrgName mentioned twice can be overcome
until
you have time to redesign your db. All you need you need to do is to
create
a query for your combo.
If your Org Table has OrgID, OrgName and POC then add OrgID to your
query
first.

In the next column type
OrgFOC: [Org] &" " & [POC]

You can concatenate as many fields as you need here to give each record
a
unique name in your combo. A handy tip I learnt on the Report newsgroup
is
to link your fields using

Trim(([Org] + " ") & ([POC] + " ") & ([AnotherField]))


If any of the fields are null then + inside the () means that it won't
show
anything, including the space.
Use the Trim to get rid of a final space if POC has a value but
AnotherField
doesn't.



When you make the combo, add OrgID and then your concatenated field but
close the OrgID column by dragging it closed in the Wizard so that it
won't
show in your combo.

Will this help?

If your Org table does not have a Unique Primary Key field (OrgID) then
Access can't possibly know which of the two OrgIDs to link to your
AgreementWith table.
It can be rectified but I won't start on that (long) process until I
know it
is needed.
I've had to do it a quite a few times, when I got a design wrong


Evi



"GIraffe" wrote in message
...
Hi Evi:

I don't think you're missing anything important ... I think I've
missed
something pretty critical somewhere.

My combo box is based on the Org Table & OrgID is the Foreign Key to
Org
ID
in the Agree Table. After reading your reply, I'm beginning to think
I
have
a bigger problem then I started this inquiry with (argh!).

Evi, thanks to your help, I can see that my original design of this DB
was
flawed, and I can't thank you enough for helping me. You've been very
gracious with your time and advice -- I hope will you bear with me a
little
more.

For starters, part of the multiple listings in Org Table is me ...
when I
originally created this DB 4 years ago, I didn't realize things would
blossom
in to what this has become (nor did I have the knowledge on DBs I have
today). So, for my Org Table, if I had the same Org, however, a
different
field office or point-of-contact, I listed the Org twice, with new new
field
office or POC. That's PART of the problem. I know how to link an Org
table
to an OrgAdd table, however, for this situation, won't that require a
combo
box within a combo box -- can you do that?

The other part of the problem, the same Org if it has more then one
agreement, I do not know. So I'd like to start with what my query for
the
combo box looks like:

tOrgWith (this is my conjuction table between Org and Agree)
OrgWithID [PK]
AgreeID
OrgID

tOrg
OrgID [PK] [FK to tOrgWith.OrgWithID]
OrgLine1
OrgLine2

I use
tOrg.Org ASC (with an IIf statement joining OrgLine1 & OrgLine2)
tOrg.POC
tOrgWith.OrgWithID
tOrgWith.AgreeID
tOrgWith.OrgID

For this query, if I do not use the the 3 fields in the OrgWith Table,
nothing will show up in my combo box.

Secondly, my combo box is built in to a separate subform from the
Agree
Table. The subform contains:

tOrgWith.OrgWithID
tOrgWith.AgreeID
tOrgWith.OrgID
OrgWith combo [based off of above query]

I added the combo box to get my Org name to show up, because I
wouldn't
know
what ID # which Org was.

So that's the intricacies of how all this works (or doesn't work).

Once gain, Evi, I thank you for all your help.

G



"Evi" wrote:

your combo being based on a query is not a problem, nearly all of
mine
are.

You shouldn't need to close the form containing the combo - that's
why
you
have the Requery code, but if you still aren't seeing the data after
re-opening the closed form then your coding isn't the problem.

It *sounds* as if your combo's query is based on the wrong table if
each
Org
is listed more than once.

It should be based on the Org Table which should list each org only
once.
You can link other tables to Org query if you need to (In a combo
containing
names you might want to link the Address from the address table) but
each
Org would only be used once.
The usual structure for a combo would be the Primary field first
(OrgID) -
this is usually hidden in the combo by making its Column Width
Property
0cm
so that the field you actually see is the User Friendly field -
Org -
then
any other fields which could be handy..
The Control Source of this combo would be OrgID which should be a
Foreign
Key field in the table on which your form is based. The important
field
in
the combo, the one that does all the work, is usually the first
(ugly,
hidden) one so if you are putting the OrdID number into your form
using
your
combo then OrgID should be the first field in the Combo.

Or have I missed something important (perfectly possible)
Evi




"GIraffe" wrote in message
...
Hi Evi:

Yes, my combo box is named AgreementWith.

Yes, I close the Org Form after I've entered the new record (the
Agree
Form,
where the combo box is, is closed when I'm entering data in to the
Org
Form).

I'm not sure it'll help, but let me give you a little more info on
my
combo
box:
My combo box is based off of a query -- should that matter?
My
combo
box has 5 columns showing Org, POC (pt of contact), AWID (Org ID),
AWJID
(Org
Join ID), & AgreeID.
What currently shows when I go to the dropdown arrow of my
combo
box
is

Hi G, That sounds really good.

Evi

"GIraffe" wrote in message
...
Hi Evi:

Boy, to have you looking over my shoulder would sure have made this sooo
much easier to solve. Sorry for the long thread ....

Okay, here's what I have:

My tables are setup exactly as you describe, with the exception of in my
TblAgreementWith I also have an AgreementWithID as PK; however, I don't
THINK
that'll make a difference .... right?

I THINK I created the datasheet form correctly. Here's what I have:

Data based on TblAgreementWith
AgreementWithID
AgreementID
OrgID
Combobox: TblAgreement.AgreementID (2nd field chosen AgreementNo)
Combobox: TblOrg.OrgID (2nd field, OrgLine1; 3rd field, OrgLine2; 4th
field, POC)

In View mode of this datasheet I have

AgreementWithID, AgreementID, OrgID, AgreementNo, OrgLine 1 (if I can get
this fixed, I will be combining several fields in one for my Orgs)

If I go to either AgreementNo or OrgLine1, I get a dropdown option and I
can
actually change either one on a record.

And, guess what Evi? ALL my Org show up ... even the tests I setup as
we've
been working through this. This is a FIRST. I'm very excited.

Please tell me I have it right so far.

G

"Evi" wrote:

I have this horrible feeling that we are both misunderstanding
something. Oh
for a crystal ball so I could see what is happening!
Let's check that we've got the structure right. (the thread is getting
long
and my brain is short!)
TblAgreement should contain each Agreement only once PK AgreementID
TblOrg should have each org only once - PK OrgID
TblAgreement With should have the foreign key fields AgreementID and
OrgID

The structure of your Form should be like this (basically)
Give this a try and you'll see what I mean.

Create a datasheet form based on the AgreementWith table. Don't add
fields
from the other tables just yet until you get a real feel for this sort
of
structure.
Add a combo to it in Design View. It should be based on TblAgreement (or
on
a query that is based directly on that table - don't add fields from the
other tables for now)
It's first field should be AgreementID, pull the column closed in the
Wizard, its next field (the one which you will actually see in the
combo)
can be any comination of fields in TblAgreement which will help you to
identify that Agreement. When you get to the relevant page in the
Wizard,
agree to store the value in AgreementID

Similarly, add a combo based on TblOrg or a query based only on that
table-
again make the first field OrgID, hide it and then add the OrgName

use the combos to fill in the 2 Foreign Key fields in TblAgreementWith.

Is this bit working, G?

Evi


"GIraffe" wrote in message
...
Hi Evi:

I'm obviously missing something. First, I chose not to concatenate my
fields, I wanted to see if I could get the principle of this to work
first.

I created the query [qOrg] putting 2 tables in the query [tOrg] &
[tOrgWith], figuring I needed the tOrgWith table because it's a
conjunction
table for tAgreements & tOrg, even tho I didn't use any fields from
it.
From
tOrg I chose OrgID, Org, POC.

I made my combo box in my fAgreements, not as a subform, but as a
combo
box.
I chose to use my above query, chose my 3 fields, related them by
OrgID,
chose to remember the value for a later use, went in to the Column
Widths
of
the Properties box and made my first column (my OrgID) 0". So now
only my
Org shows up (don't know why POC's not showing up). However, the Org
that's
showing up is not the correct Org for the Agreement (on none of the
agreements). Do I need to rechoose each Org for each agreement?
Secondly,
just to test this, I went in to my Org table and added a new Org, when
back
to my form and it does not show up . And lastly, some agreements have
more
then one Org, how can I do this in the combo box?

What am I missing?

Thanks for your help Evi.

G




"Evi" wrote:

You 're using the wrong OrgID for your combo. You need the OrgID
from
TblOrg
in your combo - not the OrgID from TblAgreementWith.
Don't use any fields from TblAgreementWith if you are trying to put
OrgID
into TblAgreementWith using your combo. That's like trying to
scratch
your
left elbow with your left elbow!

If OrgID - your Primary Field key -is Unique (as it must be, if it
is a
Primary Key) then having each OrgName mentioned twice can be
overcome
until
you have time to redesign your db. All you need you need to do is to
create
a query for your combo.
If your Org Table has OrgID, OrgName and POC then add OrgID to your
query
first.

In the next column type
OrgFOC: [Org] &" " & [POC]

You can concatenate as many fields as you need here to give each
record
a
unique name in your combo. A handy tip I learnt on the Report
newsgroup
is
to link your fields using

Trim(([Org] + " ") & ([POC] + " ") & ([AnotherField]))


If any of the fields are null then + inside the () means that it
won't
show
anything, including the space.
Use the Trim to get rid of a final space if POC has a value but
AnotherField
doesn't.



When you make the combo, add OrgID and then your concatenated field
but
close the OrgID column by dragging it closed in the Wizard so that
it
won't
show in your combo.

Will this help?

If your Org table does not have a Unique Primary Key field (OrgID)
then
Access can't possibly know which of the two OrgIDs to link to your
AgreementWith table.
It can be rectified but I won't start on that (long) process until I
know it
is needed.
I've had to do it a quite a few times, when I got a design wrong


Evi



"GIraffe" wrote in message
...
Hi Evi:

I don't think you're missing anything important ... I think I've
missed
something pretty critical somewhere.

My combo box is based on the Org Table & OrgID is the Foreign Key
to
Org
ID
in the Agree Table. After reading your reply, I'm beginning to
think
I
have
a bigger problem then I started this inquiry with (argh!).

Evi, thanks to your help, I can see that my original design of
this DB
was
flawed, and I can't thank you enough for helping me. You've been
very
gracious with your time and advice -- I hope will you bear with me
a
little
more.

For starters, part of the multiple listings in Org Table is me ...
when I
originally created this DB 4 years ago, I didn't realize things
would
blossom
in to what this has become (nor did I have the knowledge on DBs I
have
today). So, for my Org Table, if I had the same Org, however, a
different
field office or point-of-contact, I listed the Org twice, with new
new
field
office or POC. That's PART of the problem. I know how to link an
Org
table
to an OrgAdd table, however, for this situation, won't that
require a
combo
box within a combo box -- can you do that?

The other part of the problem, the same Org if it has more then
one
agreement, I do not know. So I'd like to start with what my query
for
the
combo box looks like:

tOrgWith (this is my conjuction table between Org and Agree)
OrgWithID [PK]
AgreeID
OrgID

tOrg
OrgID [PK] [FK to tOrgWith.OrgWithID]
OrgLine1
OrgLine2

I use
tOrg.Org ASC (with an IIf statement joining OrgLine1 & OrgLine2)
tOrg.POC
tOrgWith.OrgWithID
tOrgWith.AgreeID
tOrgWith.OrgID

For this query, if I do not use the the 3 fields in the OrgWith
Table,
nothing will show up in my combo box.

Secondly, my combo box is built in to a separate subform from the
Agree
Table. The subform contains:

tOrgWith.OrgWithID
tOrgWith.AgreeID
tOrgWith.OrgID
OrgWith combo [based off of above query]

I added the combo box to get my Org name to show up, because I
wouldn't
know
what ID # which Org was.

So that's the intricacies of how all this works (or doesn't work).

Once gain, Evi, I thank you for all your help.

G



"Evi" wrote:

your combo being based on a query is not a problem, nearly all
of
mine
are.

You shouldn't need to close the form containing the combo -
that's
why
you
have the Requery code, but if you still aren't seeing the data
after
re-opening the closed form then your coding isn't the problem.

It *sounds* as if your combo's query is based on the wrong table
if
each
Org
is listed more than once.

It should be based on the Org Table which should list each org
only
once.
You can link other tables to Org query if you need to (In a
combo
containing
names you might want to link the Address from the address table)
but
each
Org would only be used once.
The usual structure for a combo would be the Primary field first
(OrgID) -
this is usually hidden in the combo by making its Column Width
Property
0cm
so that the field you actually see is the User Friendly field -
Org -
then
any other fields which could be handy..
The Control Source of this combo would be OrgID which should be
a
Foreign
Key field in the table on which your form is based. The
important
field
in
the combo, the one that does all the work, is usually the first
(ugly,
hidden) one so if you are putting the OrdID number into your
form
using
your
combo then OrgID should be the first field in the Combo.

Or have I missed something important (perfectly possible)
Evi




"GIraffe" wrote in message
...
Hi Evi:

Yes, my combo box is named AgreementWith.

Yes, I close the Org Form after I've entered the new record
(the
Agree
Form,
where the combo box is, is closed when I'm entering data in to
the
Org
Form).

I'm not sure it'll help, but let me give you a little more
info on
my
combo
box:
My combo box is based off of a query -- should that
matter?
My
combo
box has 5 columns showing Org, POC (pt of contact), AWID (Org
ID),
AWJID
(Org
Join ID), & AgreeID.
What currently shows when I go to the dropdown arrow of my
combo
box
is

Hi Evi:

Thank you. I've very excited that this may actually work. So I go in to my
form to add this new subform and for each existing record the right agreement
# shows up with the right organization (Yes!). Then I go to add a new
record, and I am unable to choose an organization in my new subform. My
column headings show up (agreementno and organization), however, no drop down
options for either column, so I am unable to choose the organization that
goes with my new agreement.

I checked the properties and the Master and Child Links are okay
(AgreementID). What else should I check?

Thanks Evi, I couldn't have made it this far without you.

G

"Evi" wrote:

Hi G, That sounds really good.

Evi

"GIraffe" wrote in message
...
Hi Evi:

Boy, to have you looking over my shoulder would sure have made this sooo
much easier to solve. Sorry for the long thread ....

Okay, here's what I have:

My tables are setup exactly as you describe, with the exception of in my
TblAgreementWith I also have an AgreementWithID as PK; however, I don't
THINK
that'll make a difference .... right?

I THINK I created the datasheet form correctly. Here's what I have:

Data based on TblAgreementWith
AgreementWithID
AgreementID
OrgID
Combobox: TblAgreement.AgreementID (2nd field chosen AgreementNo)
Combobox: TblOrg.OrgID (2nd field, OrgLine1; 3rd field, OrgLine2; 4th
field, POC)

In View mode of this datasheet I have

AgreementWithID, AgreementID, OrgID, AgreementNo, OrgLine 1 (if I can get
this fixed, I will be combining several fields in one for my Orgs)

If I go to either AgreementNo or OrgLine1, I get a dropdown option and I
can
actually change either one on a record.

And, guess what Evi? ALL my Org show up ... even the tests I setup as
we've
been working through this. This is a FIRST. I'm very excited.

Please tell me I have it right so far.

G

"Evi" wrote:

I have this horrible feeling that we are both misunderstanding
something. Oh
for a crystal ball so I could see what is happening!
Let's check that we've got the structure right. (the thread is getting
long
and my brain is short!)
TblAgreement should contain each Agreement only once PK AgreementID
TblOrg should have each org only once - PK OrgID
TblAgreement With should have the foreign key fields AgreementID and
OrgID

The structure of your Form should be like this (basically)
Give this a try and you'll see what I mean.

Create a datasheet form based on the AgreementWith table. Don't add
fields
from the other tables just yet until you get a real feel for this sort
of
structure.
Add a combo to it in Design View. It should be based on TblAgreement (or
on
a query that is based directly on that table - don't add fields from the
other tables for now)
It's first field should be AgreementID, pull the column closed in the
Wizard, its next field (the one which you will actually see in the
combo)
can be any comination of fields in TblAgreement which will help you to
identify that Agreement. When you get to the relevant page in the
Wizard,
agree to store the value in AgreementID

Similarly, add a combo based on TblOrg or a query based only on that
table-
again make the first field OrgID, hide it and then add the OrgName

use the combos to fill in the 2 Foreign Key fields in TblAgreementWith.

Is this bit working, G?

Evi


"GIraffe" wrote in message
...
Hi Evi:

I'm obviously missing something. First, I chose not to concatenate my
fields, I wanted to see if I could get the principle of this to work
first.

I created the query [qOrg] putting 2 tables in the query [tOrg] &
[tOrgWith], figuring I needed the tOrgWith table because it's a
conjunction
table for tAgreements & tOrg, even tho I didn't use any fields from
it.
From
tOrg I chose OrgID, Org, POC.

I made my combo box in my fAgreements, not as a subform, but as a
combo
box.
I chose to use my above query, chose my 3 fields, related them by
OrgID,
chose to remember the value for a later use, went in to the Column
Widths
of
the Properties box and made my first column (my OrgID) 0". So now
only my
Org shows up (don't know why POC's not showing up). However, the Org
that's
showing up is not the correct Org for the Agreement (on none of the
agreements). Do I need to rechoose each Org for each agreement?
Secondly,
just to test this, I went in to my Org table and added a new Org, when
back
to my form and it does not show up . And lastly, some agreements have
more
then one Org, how can I do this in the combo box?

What am I missing?

Thanks for your help Evi.

G




"Evi" wrote:

You 're using the wrong OrgID for your combo. You need the OrgID
from
TblOrg
in your combo - not the OrgID from TblAgreementWith.
Don't use any fields from TblAgreementWith if you are trying to put
OrgID
into TblAgreementWith using your combo. That's like trying to
scratch
your
left elbow with your left elbow!

If OrgID - your Primary Field key -is Unique (as it must be, if it
is a
Primary Key) then having each OrgName mentioned twice can be
overcome
until
you have time to redesign your db. All you need you need to do is to
create
a query for your combo.
If your Org Table has OrgID, OrgName and POC then add OrgID to your
query
first.

In the next column type
OrgFOC: [Org] &" " & [POC]

You can concatenate as many fields as you need here to give each
record
a
unique name in your combo. A handy tip I learnt on the Report
newsgroup
is
to link your fields using

Trim(([Org] + " ") & ([POC] + " ") & ([AnotherField]))


If any of the fields are null then + inside the () means that it
won't
show
anything, including the space.
Use the Trim to get rid of a final space if POC has a value but
AnotherField
doesn't.



When you make the combo, add OrgID and then your concatenated field
but
close the OrgID column by dragging it closed in the Wizard so that
it
won't
show in your combo.

Will this help?

If your Org table does not have a Unique Primary Key field (OrgID)
then
Access can't possibly know which of the two OrgIDs to link to your
AgreementWith table.
It can be rectified but I won't start on that (long) process until I
know it
is needed.
I've had to do it a quite a few times, when I got a design wrong


Evi



"GIraffe" wrote in message
...
Hi Evi:

I don't think you're missing anything important ... I think I've
missed
something pretty critical somewhere.

My combo box is based on the Org Table & OrgID is the Foreign Key
to
Org
ID
in the Agree Table. After reading your reply, I'm beginning to
think
I
have
a bigger problem then I started this inquiry with (argh!).

Evi, thanks to your help, I can see that my original design of
this DB
was
flawed, and I can't thank you enough for helping me. You've been
very
gracious with your time and advice -- I hope will you bear with me
a
little
more.

For starters, part of the multiple listings in Org Table is me ...
when I
originally created this DB 4 years ago, I didn't realize things
would
blossom
in to what this has become (nor did I have the knowledge on DBs I
have
today). So, for my Org Table, if I had the same Org, however, a
different
field office or point-of-contact, I listed the Org twice, with new
new
field
office or POC. That's PART of the problem. I know how to link an
Org
table
to an OrgAdd table, however, for this situation, won't that
require a
combo
box within a combo box -- can you do that?

The other part of the problem, the same Org if it has more then
one
agreement, I do not know. So I'd like to start with what my query
for
the
combo box looks like:

tOrgWith (this is my conjuction table between Org and Agree)
OrgWithID [PK]
AgreeID
OrgID

tOrg
OrgID [PK] [FK to tOrgWith.OrgWithID]
OrgLine1
OrgLine2

I use
tOrg.Org ASC (with an IIf statement joining OrgLine1 & OrgLine2)
tOrg.POC
tOrgWith.OrgWithID
tOrgWith.AgreeID
tOrgWith.OrgID

For this query, if I do not use the the 3 fields in the OrgWith
Table,
nothing will show up in my combo box.

Secondly, my combo box is built in to a separate subform from the
Agree
Table. The subform contains:

tOrgWith.OrgWithID
tOrgWith.AgreeID
tOrgWith.OrgID
OrgWith combo [based off of above query]

I added the combo box to get my Org name to show up, because I
wouldn't
know
what ID # which Org was.

So that's the intricacies of how all this works (or doesn't work).

Once gain, Evi, I thank you for all your help.