Design Problem -- only get partial results on multiple join

[GIraffe]

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 structure:

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
report.

Thank you for your help.

 

[Evi]

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]

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.

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

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 structure:

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
report.

Thank you for your help.

 

[Evi]

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:
OrgID (Joined From tOrganization in the Relationships window)


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

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.

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

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 structure:

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
report.

Thank you for your help.

 

[GIraffe]

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.

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:
OrgID (Joined From tOrganization in the Relationships window)


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

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.

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





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 structure:

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
report.

Thank you for your help.

 

[Evi]

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

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.

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

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.




:

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





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 structure:

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

report.

Thank you for your help.

 

[GIraffe]

Many, Many thanks. I'll give this a try and let you know how it goes. I
truly do appreciate your patience.

Curious, why do you choose the DbleClick vs. SingleClick option? May be I'm
overlooking something.

Thanks.

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

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.

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


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.




:

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





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 structure:

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
report.

Thank you for your help.

 

[Evi]

Because everytime you enter the field, you could easily fire the single
click option without wanting to.
Eventually, you will want to put some nice NotInList code to the combo but
my slip-shod methods will get things moving at this stage.
Evi

Many, Many thanks. I'll give this a try and let you know how it goes. I
truly do appreciate your patience.

Curious, why do you choose the DbleClick vs. SingleClick option? May be I'm
overlooking something.

Thanks.

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

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.

:

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


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.




:

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





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 structure:

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
report.

Thank you for your help.

 

[GIraffe]

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.

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

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.

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


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.




:

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





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 structure:

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
report.

Thank you for your help.

 

[Evi]

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

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.

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

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.

:

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


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.




:

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





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 structure:

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
report.

Thank you for your help.

 

[GIraffe]

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.

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

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.

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

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.

:

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


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.




:

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





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 structure:

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

 

[Evi]

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

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.

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

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.

:

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

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.

:

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


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.




:

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





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 structure:

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

 

[GIraffe]

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]

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

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

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




AWJID
(Org box
is (Sorry
for Name
on ensures
that me
last I
do For
the needed
to in
the look
for or the
form says
End then
puts to
enter get
you my
form combo
box. organization
to I'll
try it,
if for
the that's
your all
the Organization
can combo
box links
in to not
clear

 

[GIraffe]

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

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

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

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




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.

:

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


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.

:

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

 

[GIraffe]

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

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

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

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




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.

:

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


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.

:

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

 

[Evi]

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

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

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

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



:

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




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.

:

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


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.

:

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

 

[GIraffe]

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

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

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

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



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



:

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




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

 

[Evi]

Hi G, That sounds really good.

Evi

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

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

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




:

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



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



:

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




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

 

[GIraffe]

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

Hi G, That sounds really good.

Evi

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

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


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




:

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



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.