Combobox in a subform

[Shawn Repphan]

I have a masterform called [Companies] with two subforms: [Contacts] and
[Accounts]. [Companies] and the subforms are all linked with a field called
[companyCode]. Everything works as expected except a combobox on the subform
[Accounts] is supposed to display only the contacts that are linked to the
[Companies] form. I have tried everything I can but the combobox will always
show ALL of the contacts, not just the ones with the associated
[companyCode] field.

Any workaround to this?
Thanks

 

[Ken Snell [MVP]]

What is the RowSource query for that combo box? Do you requery that combo
box whenever the main form moves to a different record?

 

[Shawn Repphan]

The first record should not have any contacts in the combo box. The second
record should have two contacts.(There are only two records). Even with a
requery, both records show all the contacts. FYI: I am using GUID's for
PKeys.

What is the RowSource query for that combo box? Do you requery that combo
box whenever the main form moves to a different record?

--

Ken Snell
<MS ACCESS MVP>

I have a masterform called [Companies] with two subforms: [Contacts] and
[Accounts]. [Companies] and the subforms are all linked with a field
called [companyCode]. Everything works as expected except a combobox on
the subform [Accounts] is supposed to display only the contacts that are
linked to the [Companies] form. I have tried everything I can but the
combobox will always show ALL of the contacts, not just the ones with the
associated [companyCode] field.

Any workaround to this?
Thanks

 

[Ken Snell [MVP]]

And the RowSource query for the combo box is.....?

--

Ken Snell
<MS ACCESS MVP>

The first record should not have any contacts in the combo box. The second
record should have two contacts.(There are only two records). Even with a
requery, both records show all the contacts. FYI: I am using GUID's for
PKeys.

What is the RowSource query for that combo box? Do you requery that combo
box whenever the main form moves to a different record?

--

Ken Snell
<MS ACCESS MVP>

I have a masterform called [Companies] with two subforms: [Contacts] and
[Accounts]. [Companies] and the subforms are all linked with a field
called [companyCode]. Everything works as expected except a combobox on
the subform [Accounts] is supposed to display only the contacts that are
linked to the [Companies] form. I have tried everything I can but the
combobox will always show ALL of the contacts, not just the ones with the
associated [companyCode] field.

Any workaround to this?
Thanks

 

[Shawn Repphan]

At the moment: SELECT Contacts.contactName, Contacts.contactCode FROM
Contacts;

I have tried many different queries for the rowsource. I've included the
[companyCode] field as well as used VBA functions to read [companyCode] from
the masterform.

And the RowSource query for the combo box is.....?

--

Ken Snell
<MS ACCESS MVP>

The first record should not have any contacts in the combo box. The
second record should have two contacts.(There are only two records). Even
with a requery, both records show all the contacts. FYI: I am using
GUID's for PKeys.

What is the RowSource query for that combo box? Do you requery that
combo box whenever the main form moves to a different record?

--

Ken Snell
<MS ACCESS MVP>

I have a masterform called [Companies] with two subforms: [Contacts] and
[Accounts]. [Companies] and the subforms are all linked with a field
called [companyCode]. Everything works as expected except a combobox on
the subform [Accounts] is supposed to display only the contacts that are
linked to the [Companies] form. I have tried everything I can but the
combobox will always show ALL of the contacts, not just the ones with
the associated [companyCode] field.

Any workaround to this?
Thanks

 

[Ken Snell [MVP]]

The combo box is in the subform, right? Try this as the RowSource:

SELECT Contacts.contactName, Contacts.contactCode FROM
Contacts WHERE CompanyCode=[Parent]![companyCode];

You also will need to requery the combo box when the main form moves from
one record to another. This typically is done in the main form's Current
event:

Private Sub Form_Current()
Me.NameofSubformControl.Form.NameOfComboBoxInSubform.Requery
End Sub

--

Ken Snell
<MS ACCESS MVP>

At the moment: SELECT Contacts.contactName, Contacts.contactCode FROM
Contacts;

I have tried many different queries for the rowsource. I've included the
[companyCode] field as well as used VBA functions to read [companyCode]
from the masterform.

And the RowSource query for the combo box is.....?

--

Ken Snell
<MS ACCESS MVP>

The first record should not have any contacts in the combo box. The
second record should have two contacts.(There are only two records).
Even with a requery, both records show all the contacts. FYI: I am using
GUID's for PKeys.

What is the RowSource query for that combo box? Do you requery that
combo box whenever the main form moves to a different record?

--

Ken Snell
<MS ACCESS MVP>

I have a masterform called [Companies] with two subforms: [Contacts]
and [Accounts]. [Companies] and the subforms are all linked with a
field called [companyCode]. Everything works as expected except a
combobox on the subform [Accounts] is supposed to display only the
contacts that are linked to the [Companies] form. I have tried
everything I can but the combobox will always show ALL of the contacts,
not just the ones with the associated [companyCode] field.

Any workaround to this?
Thanks

 

[Shawn Repphan]

Still gives me nothing.

The combo box is in the subform, right? Try this as the RowSource:

SELECT Contacts.contactName, Contacts.contactCode FROM
Contacts WHERE CompanyCode=[Parent]![companyCode];

You also will need to requery the combo box when the main form moves from
one record to another. This typically is done in the main form's Current
event:

Private Sub Form_Current()
Me.NameofSubformControl.Form.NameOfComboBoxInSubform.Requery
End Sub

--

Ken Snell
<MS ACCESS MVP>

At the moment: SELECT Contacts.contactName, Contacts.contactCode FROM
Contacts;

I have tried many different queries for the rowsource. I've included the
[companyCode] field as well as used VBA functions to read [companyCode]
from the masterform.

And the RowSource query for the combo box is.....?

--

Ken Snell
<MS ACCESS MVP>

The first record should not have any contacts in the combo box. The
second record should have two contacts.(There are only two records).
Even with a requery, both records show all the contacts. FYI: I am
using GUID's for PKeys.

What is the RowSource query for that combo box? Do you requery that
combo box whenever the main form moves to a different record?

--

Ken Snell
<MS ACCESS MVP>

I have a masterform called [Companies] with two subforms: [Contacts]
and [Accounts]. [Companies] and the subforms are all linked with a
field called [companyCode]. Everything works as expected except a
combobox on the subform [Accounts] is supposed to display only the
contacts that are linked to the [Companies] form. I have tried
everything I can but the combobox will always show ALL of the
contacts, not just the ones with the associated [companyCode] field.

Any workaround to this?
Thanks

 

[Shawn Repphan]

I've even been able to replicate the problem in a new database without
resolving it.

Still gives me nothing.

The combo box is in the subform, right? Try this as the RowSource:

SELECT Contacts.contactName, Contacts.contactCode FROM
Contacts WHERE CompanyCode=[Parent]![companyCode];

You also will need to requery the combo box when the main form moves from
one record to another. This typically is done in the main form's Current
event:

Private Sub Form_Current()
Me.NameofSubformControl.Form.NameOfComboBoxInSubform.Requery
End Sub

--

Ken Snell
<MS ACCESS MVP>

At the moment: SELECT Contacts.contactName, Contacts.contactCode FROM
Contacts;

I have tried many different queries for the rowsource. I've included the
[companyCode] field as well as used VBA functions to read [companyCode]
from the masterform.

And the RowSource query for the combo box is.....?

--

Ken Snell
<MS ACCESS MVP>

The first record should not have any contacts in the combo box. The
second record should have two contacts.(There are only two records).
Even with a requery, both records show all the contacts. FYI: I am
using GUID's for PKeys.

What is the RowSource query for that combo box? Do you requery that
combo box whenever the main form moves to a different record?

--

Ken Snell
<MS ACCESS MVP>

I have a masterform called [Companies] with two subforms: [Contacts]
and [Accounts]. [Companies] and the subforms are all linked with a
field called [companyCode]. Everything works as expected except a
combobox on the subform [Accounts] is supposed to display only the
contacts that are linked to the [Companies] form. I have tried
everything I can but the combobox will always show ALL of the
contacts, not just the ones with the associated [companyCode] field.

Any workaround to this?
Thanks

 

[Ken Snell [MVP]]

My suggested RowSource string assumes that you have a control named
companyCode on the main form. If you don't, change it to the control that
holds the company code value on the main form.

Did you try the Current event procedure as well for the main form? It's not
clear from your short replies what you're actually doing/trying and what
your setup is/has been changed to be.

--

Ken Snell
<MS ACCESS MVP>

Still gives me nothing.

The combo box is in the subform, right? Try this as the RowSource:

SELECT Contacts.contactName, Contacts.contactCode FROM
Contacts WHERE CompanyCode=[Parent]![companyCode];

You also will need to requery the combo box when the main form moves from
one record to another. This typically is done in the main form's Current
event:

Private Sub Form_Current()
Me.NameofSubformControl.Form.NameOfComboBoxInSubform.Requery
End Sub

--

Ken Snell
<MS ACCESS MVP>

At the moment: SELECT Contacts.contactName, Contacts.contactCode FROM
Contacts;

I have tried many different queries for the rowsource. I've included the
[companyCode] field as well as used VBA functions to read [companyCode]
from the masterform.

And the RowSource query for the combo box is.....?

--

Ken Snell
<MS ACCESS MVP>

The first record should not have any contacts in the combo box. The
second record should have two contacts.(There are only two records).
Even with a requery, both records show all the contacts. FYI: I am
using GUID's for PKeys.

What is the RowSource query for that combo box? Do you requery that
combo box whenever the main form moves to a different record?

--

Ken Snell
<MS ACCESS MVP>

I have a masterform called [Companies] with two subforms: [Contacts]
and [Accounts]. [Companies] and the subforms are all linked with a
field called [companyCode]. Everything works as expected except a
combobox on the subform [Accounts] is supposed to display only the
contacts that are linked to the [Companies] form. I have tried
everything I can but the combobox will always show ALL of the
contacts, not just the ones with the associated [companyCode] field.

Any workaround to this?
Thanks

 

[Shawn Repphan]

Sorry about that. I have tried changing the pkeys from ReplicationID's to
Longs in the "test" database I have written and that worked, even without
the companyCode control, but that is not a possibility in the production
version. Is there something that I don't know about using Replication ID's?
I have tried using StringFromGUID and GUIDFromString with the control with
no luck.

My suggested RowSource string assumes that you have a control named
companyCode on the main form. If you don't, change it to the control that
holds the company code value on the main form.

Did you try the Current event procedure as well for the main form? It's
not clear from your short replies what you're actually doing/trying and
what your setup is/has been changed to be.

--

Ken Snell
<MS ACCESS MVP>

Still gives me nothing.

The combo box is in the subform, right? Try this as the RowSource:

SELECT Contacts.contactName, Contacts.contactCode FROM
Contacts WHERE CompanyCode=[Parent]![companyCode];

You also will need to requery the combo box when the main form moves
from one record to another. This typically is done in the main form's
Current event:

Private Sub Form_Current()
Me.NameofSubformControl.Form.NameOfComboBoxInSubform.Requery
End Sub

--

Ken Snell
<MS ACCESS MVP>


At the moment: SELECT Contacts.contactName, Contacts.contactCode FROM
Contacts;

I have tried many different queries for the rowsource. I've included
the [companyCode] field as well as used VBA functions to read
[companyCode] from the masterform.

And the RowSource query for the combo box is.....?

--

Ken Snell
<MS ACCESS MVP>

The first record should not have any contacts in the combo box. The
second record should have two contacts.(There are only two records).
Even with a requery, both records show all the contacts. FYI: I am
using GUID's for PKeys.

What is the RowSource query for that combo box? Do you requery that
combo box whenever the main form moves to a different record?

--

Ken Snell
<MS ACCESS MVP>

I have a masterform called [Companies] with two subforms: [Contacts]
and [Accounts]. [Companies] and the subforms are all linked with a
field called [companyCode]. Everything works as expected except a
combobox on the subform [Accounts] is supposed to display only the
contacts that are linked to the [Companies] form. I have tried
everything I can but the combobox will always show ALL of the
contacts, not just the ones with the associated [companyCode] field.

Any workaround to this?
Thanks

 

[Ken Snell [MVP]]

You're using a replicated database. Ahhh.... I have not worked with them, so
I do not know if they introduce some additional complexity. And I cannot
comment about the GUID questions, I'm sorry.

What I've been suggesting is based on getting the value of the "linking"
field from the main form and using it to filter the query that populates the
combo box. It may be that the main form is not using a "companycode" value?

What is the exact field/control in the mainform whose value dictates which
choices are to be in the combo box?

--

Ken Snell
<MS ACCESS MVP>

Sorry about that. I have tried changing the pkeys from ReplicationID's to
Longs in the "test" database I have written and that worked, even without
the companyCode control, but that is not a possibility in the production
version. Is there something that I don't know about using Replication
ID's? I have tried using StringFromGUID and GUIDFromString with the
control with no luck.

My suggested RowSource string assumes that you have a control named
companyCode on the main form. If you don't, change it to the control that
holds the company code value on the main form.

Did you try the Current event procedure as well for the main form? It's
not clear from your short replies what you're actually doing/trying and
what your setup is/has been changed to be.

--

Ken Snell
<MS ACCESS MVP>

Still gives me nothing.

The combo box is in the subform, right? Try this as the RowSource:

SELECT Contacts.contactName, Contacts.contactCode FROM
Contacts WHERE CompanyCode=[Parent]![companyCode];

You also will need to requery the combo box when the main form moves
from one record to another. This typically is done in the main form's
Current event:

Private Sub Form_Current()
Me.NameofSubformControl.Form.NameOfComboBoxInSubform.Requery
End Sub

--

Ken Snell
<MS ACCESS MVP>


At the moment: SELECT Contacts.contactName, Contacts.contactCode FROM
Contacts;

I have tried many different queries for the rowsource. I've included
the [companyCode] field as well as used VBA functions to read
[companyCode] from the masterform.

And the RowSource query for the combo box is.....?

--

Ken Snell
<MS ACCESS MVP>

The first record should not have any contacts in the combo box. The
second record should have two contacts.(There are only two records).
Even with a requery, both records show all the contacts. FYI: I am
using GUID's for PKeys.

message What is the RowSource query for that combo box? Do you requery that
combo box whenever the main form moves to a different record?

--

Ken Snell
<MS ACCESS MVP>

I have a masterform called [Companies] with two subforms:
[Contacts] and [Accounts]. [Companies] and the subforms are all
linked with a field called [companyCode]. Everything works as
expected except a combobox on the subform [Accounts] is supposed to
display only the contacts that are linked to the [Companies] form.
I have tried everything I can but the combobox will always show ALL
of the contacts, not just the ones with the associated
[companyCode] field.

Any workaround to this?
Thanks

 

[Shawn Repphan]

The database is not replicated yet. Because of the design, I need unique key
values accross all tables(many, many tables). The field in the main form is
called companyCode. I have also tried using a control named companyCode.

Everything else is working as it should. I have read about other versions of
Access(I'm using 2003) having problems with linking subforms using GUID's,
but I have not seen that. And I cannot seem to retrieve the GUID from VBA. I
just get a string of "?"'s. Very puzzling. Is there a property get for
retrieving the link fields that you know of?

You're using a replicated database. Ahhh.... I have not worked with them,
so I do not know if they introduce some additional complexity. And I
cannot comment about the GUID questions, I'm sorry.

What I've been suggesting is based on getting the value of the "linking"
field from the main form and using it to filter the query that populates
the combo box. It may be that the main form is not using a "companycode"
value?

What is the exact field/control in the mainform whose value dictates which
choices are to be in the combo box?

--

Ken Snell
<MS ACCESS MVP>

Sorry about that. I have tried changing the pkeys from ReplicationID's to
Longs in the "test" database I have written and that worked, even without
the companyCode control, but that is not a possibility in the production
version. Is there something that I don't know about using Replication
ID's? I have tried using StringFromGUID and GUIDFromString with the
control with no luck.

My suggested RowSource string assumes that you have a control named
companyCode on the main form. If you don't, change it to the control
that holds the company code value on the main form.

Did you try the Current event procedure as well for the main form? It's
not clear from your short replies what you're actually doing/trying and
what your setup is/has been changed to be.

--

Ken Snell
<MS ACCESS MVP>


Still gives me nothing.

The combo box is in the subform, right? Try this as the RowSource:

SELECT Contacts.contactName, Contacts.contactCode FROM
Contacts WHERE CompanyCode=[Parent]![companyCode];

You also will need to requery the combo box when the main form moves
from one record to another. This typically is done in the main form's
Current event:

Private Sub Form_Current()
Me.NameofSubformControl.Form.NameOfComboBoxInSubform.Requery
End Sub

--

Ken Snell
<MS ACCESS MVP>


At the moment: SELECT Contacts.contactName, Contacts.contactCode FROM
Contacts;

I have tried many different queries for the rowsource. I've included
the [companyCode] field as well as used VBA functions to read
[companyCode] from the masterform.

And the RowSource query for the combo box is.....?

--

Ken Snell
<MS ACCESS MVP>

The first record should not have any contacts in the combo box. The
second record should have two contacts.(There are only two
records). Even with a requery, both records show all the contacts.
FYI: I am using GUID's for PKeys.

message What is the RowSource query for that combo box? Do you requery
that combo box whenever the main form moves to a different record?

--

Ken Snell
<MS ACCESS MVP>

I have a masterform called [Companies] with two subforms:
[Contacts] and [Accounts]. [Companies] and the subforms are all
linked with a field called [companyCode]. Everything works as
expected except a combobox on the subform [Accounts] is supposed
to display only the contacts that are linked to the [Companies]
form. I have tried everything I can but the combobox will always
show ALL of the contacts, not just the ones with the associated
[companyCode] field.

Any workaround to this?
Thanks

 

[Ken Snell [MVP]]

I do not know about GUIDs so I won't even hazard a guess. You might want to
post your question in the microsoft.public.access.replication newsgroup
where people with such expertise are more likely to see the question.

Sorry I couldn't be of more assistance.

--

Ken Snell
<MS ACCESS MVP>

The database is not replicated yet. Because of the design, I need unique
key values accross all tables(many, many tables). The field in the main
form is called companyCode. I have also tried using a control named
companyCode.

Everything else is working as it should. I have read about other versions
of Access(I'm using 2003) having problems with linking subforms using
GUID's, but I have not seen that. And I cannot seem to retrieve the GUID
from VBA. I just get a string of "?"'s. Very puzzling. Is there a property
get for retrieving the link fields that you know of?

You're using a replicated database. Ahhh.... I have not worked with them,
so I do not know if they introduce some additional complexity. And I
cannot comment about the GUID questions, I'm sorry.

What I've been suggesting is based on getting the value of the "linking"
field from the main form and using it to filter the query that populates
the combo box. It may be that the main form is not using a "companycode"
value?

What is the exact field/control in the mainform whose value dictates
which choices are to be in the combo box?

--

Ken Snell
<MS ACCESS MVP>

Sorry about that. I have tried changing the pkeys from ReplicationID's
to Longs in the "test" database I have written and that worked, even
without the companyCode control, but that is not a possibility in the
production version. Is there something that I don't know about using
Replication ID's? I have tried using StringFromGUID and GUIDFromString
with the control with no luck.

My suggested RowSource string assumes that you have a control named
companyCode on the main form. If you don't, change it to the control
that holds the company code value on the main form.

Did you try the Current event procedure as well for the main form? It's
not clear from your short replies what you're actually doing/trying and
what your setup is/has been changed to be.

--

Ken Snell
<MS ACCESS MVP>


Still gives me nothing.

The combo box is in the subform, right? Try this as the RowSource:

SELECT Contacts.contactName, Contacts.contactCode FROM
Contacts WHERE CompanyCode=[Parent]![companyCode];

You also will need to requery the combo box when the main form moves
from one record to another. This typically is done in the main form's
Current event:

Private Sub Form_Current()
Me.NameofSubformControl.Form.NameOfComboBoxInSubform.Requery
End Sub

--

Ken Snell
<MS ACCESS MVP>


At the moment: SELECT Contacts.contactName, Contacts.contactCode
FROM Contacts;

I have tried many different queries for the rowsource. I've included
the [companyCode] field as well as used VBA functions to read
[companyCode] from the masterform.

message And the RowSource query for the combo box is.....?

--

Ken Snell
<MS ACCESS MVP>

The first record should not have any contacts in the combo box.
The second record should have two contacts.(There are only two
records). Even with a requery, both records show all the contacts.
FYI: I am using GUID's for PKeys.

message What is the RowSource query for that combo box? Do you requery
that combo box whenever the main form moves to a different
record?

--

Ken Snell
<MS ACCESS MVP>

I have a masterform called [Companies] with two subforms:
[Contacts] and [Accounts]. [Companies] and the subforms are all
linked with a field called [companyCode]. Everything works as
expected except a combobox on the subform [Accounts] is supposed
to display only the contacts that are linked to the [Companies]
form. I have tried everything I can but the combobox will always
show ALL of the contacts, not just the ones with the associated
[companyCode] field.

Any workaround to this?
Thanks

 

[Shawn Repphan]

Problem solved 5 minutes before the days over. What I did was store the GUID
in a textbox control on the main form. The combobox in the subform had a
where clause "WHERE companyCode = GuidFromString(VBAFunction);" and the
VBAFunction simply returned a String type obtained from a
StringFromGUID(textbox). I swear I tried that 10 times already. Anyway, your
help is more than appreciated. Thank you.

The database is not replicated yet. Because of the design, I need unique
key values accross all tables(many, many tables). The field in the main
form is called companyCode. I have also tried using a control named
companyCode.

Everything else is working as it should. I have read about other versions
of Access(I'm using 2003) having problems with linking subforms using
GUID's, but I have not seen that. And I cannot seem to retrieve the GUID
from VBA. I just get a string of "?"'s. Very puzzling. Is there a property
get for retrieving the link fields that you know of?

You're using a replicated database. Ahhh.... I have not worked with them,
so I do not know if they introduce some additional complexity. And I
cannot comment about the GUID questions, I'm sorry.

What I've been suggesting is based on getting the value of the "linking"
field from the main form and using it to filter the query that populates
the combo box. It may be that the main form is not using a "companycode"
value?

What is the exact field/control in the mainform whose value dictates
which choices are to be in the combo box?

--

Ken Snell
<MS ACCESS MVP>

Sorry about that. I have tried changing the pkeys from ReplicationID's
to Longs in the "test" database I have written and that worked, even
without the companyCode control, but that is not a possibility in the
production version. Is there something that I don't know about using
Replication ID's? I have tried using StringFromGUID and GUIDFromString
with the control with no luck.

My suggested RowSource string assumes that you have a control named
companyCode on the main form. If you don't, change it to the control
that holds the company code value on the main form.

Did you try the Current event procedure as well for the main form? It's
not clear from your short replies what you're actually doing/trying and
what your setup is/has been changed to be.

--

Ken Snell
<MS ACCESS MVP>


Still gives me nothing.

The combo box is in the subform, right? Try this as the RowSource:

SELECT Contacts.contactName, Contacts.contactCode FROM
Contacts WHERE CompanyCode=[Parent]![companyCode];

You also will need to requery the combo box when the main form moves
from one record to another. This typically is done in the main form's
Current event:

Private Sub Form_Current()
Me.NameofSubformControl.Form.NameOfComboBoxInSubform.Requery
End Sub

--

Ken Snell
<MS ACCESS MVP>


At the moment: SELECT Contacts.contactName, Contacts.contactCode
FROM Contacts;

I have tried many different queries for the rowsource. I've included
the [companyCode] field as well as used VBA functions to read
[companyCode] from the masterform.

message And the RowSource query for the combo box is.....?

--

Ken Snell
<MS ACCESS MVP>

The first record should not have any contacts in the combo box.
The second record should have two contacts.(There are only two
records). Even with a requery, both records show all the contacts.
FYI: I am using GUID's for PKeys.

message What is the RowSource query for that combo box? Do you requery
that combo box whenever the main form moves to a different
record?

--

Ken Snell
<MS ACCESS MVP>

I have a masterform called [Companies] with two subforms:
[Contacts] and [Accounts]. [Companies] and the subforms are all
linked with a field called [companyCode]. Everything works as
expected except a combobox on the subform [Accounts] is supposed
to display only the contacts that are linked to the [Companies]
form. I have tried everything I can but the combobox will always
show ALL of the contacts, not just the ones with the associated
[companyCode] field.

Any workaround to this?
Thanks

 

[Ken Snell [MVP]]

Glad you found a solution!