Combo Box

[Guest]

I have a form with with a subform.

form1 has client information with client name as the master field
form2 has invoice information related to the client. client name being the
child field

I have a combo box on form 2 to lookup invoices. I only want to lookup
invoices related to the current client chosen.

In my combo box I filter the lookup to the control listing the client name
[clientname].

This works the first time I use the combo box. Each time thereafter when I
change the client name the combo box still only returns invoices for the
first client I chose. It does not see the update to the [clientname].

I have to set the onenter property of the combo box to requery the
[clientname] control, I have also tried to set it to Docmd.requery in the
code window to requery to whole database.

Nothing has worked I can only use this combo box once.

What is the correct way to get this to work?

 

[Graham Mandeno]

Hi Stefan

You need to requery the combo box, not the [clientname] textbox, and the
requery needs to be performed whenever [clientname] changes.

Put [comboboxname].Requery in the AfterUpdate event procedure for
[clientname]. If [clientname] is a bound textbox, then add the line to
Form_Current also, so that the combobox will be requeried as you go from one
client record to the next.

 

[Guest]

I believe you need to requery the combo box in the after update event.

Me.cboComboBox.requery

hth

 

[Guest]

I am afraid I am still having the same problem.

Any further advice how to troubleshoot?
--
Thanks in advance
Stefan

Hi Stefan

You need to requery the combo box, not the [clientname] textbox, and the
requery needs to be performed whenever [clientname] changes.

Put [comboboxname].Requery in the AfterUpdate event procedure for
[clientname]. If [clientname] is a bound textbox, then add the line to
Form_Current also, so that the combobox will be requeried as you go from one
client record to the next.
--
Good Luck!

Graham Mandeno [Access MVP]
Auckland, New Zealand

I have a form with with a subform.

form1 has client information with client name as the master field
form2 has invoice information related to the client. client name being
the
child field

I have a combo box on form 2 to lookup invoices. I only want to lookup
invoices related to the current client chosen.

In my combo box I filter the lookup to the control listing the client name
[clientname].

This works the first time I use the combo box. Each time thereafter when
I
change the client name the combo box still only returns invoices for the
first client I chose. It does not see the update to the [clientname].

I have to set the onenter property of the combo box to requery the
[clientname] control, I have also tried to set it to Docmd.requery in the
code window to requery to whole database.

Nothing has worked I can only use this combo box once.

What is the correct way to get this to work?

 

[Graham Mandeno]

Hi Stefan

Some questions:

1. Is [clientname] a bound textbox?

2. What code is in the Form_Current event procedure for the main form?

3. As you move between records on the main form, do you see only the related
invoices (for that client) in the subform?

4. Is your combobox bound?

5. What is the RowSource of the combo box?

6. What code is in the AfterUpdate event procedure for the combobox?

--
thanks!

Graham Mandeno [Access MVP]
Auckland, New Zealand

I am afraid I am still having the same problem.

Any further advice how to troubleshoot?
--
Thanks in advance
Stefan

Hi Stefan

You need to requery the combo box, not the [clientname] textbox, and the
requery needs to be performed whenever [clientname] changes.

Put [comboboxname].Requery in the AfterUpdate event procedure for
[clientname]. If [clientname] is a bound textbox, then add the line to
Form_Current also, so that the combobox will be requeried as you go from
one
client record to the next.
--
Good Luck!

Graham Mandeno [Access MVP]
Auckland, New Zealand

I have a form with with a subform.

form1 has client information with client name as the master field
form2 has invoice information related to the client. client name being
the
child field

I have a combo box on form 2 to lookup invoices. I only want to lookup
invoices related to the current client chosen.

In my combo box I filter the lookup to the control listing the client
name
[clientname].

This works the first time I use the combo box. Each time thereafter
when
I
change the client name the combo box still only returns invoices for
the
first client I chose. It does not see the update to the [clientname].

I have to set the onenter property of the combo box to requery the
[clientname] control, I have also tried to set it to Docmd.requery in
the
code window to requery to whole database.

Nothing has worked I can only use this combo box once.

What is the correct way to get this to work?

 

[Guest]

1. Is [clientname] a bound textbox?
It is bound on form1 the client info table.
On the subform the control for clientname only refers to the form1 control
=Forms!frm_Invoice!ClientName. The control on the subform does display the
correct client name when changed on form1

2. What code is in the Form_Current event procedure for the main form?
I do not see any procedures for any events in the property box.

3. As you move between records on the main form, do you see only the related
invoices (for that client) in the subform?
YES I ONLY SEE THE RELATED RECORDS.

4. Is your combobox bound?
NO THE COMBO BOX IS NOT BOUND

5. What is the RowSource of the combo box?
It is a query based on the invoice table. I have the clientname field
criteria set to the value of the clientname control
=Forms!frm_Invoice!ClientName

6. What code is in the AfterUpdate event procedure for the combobox?
It is the standard code created when using the wizard to make a combo box.
I select find a record when using the wizard.

Hope that helps.

--
Thanks in advance
Stefan

Hi Stefan

Some questions:

1. Is [clientname] a bound textbox?

2. What code is in the Form_Current event procedure for the main form?

3. As you move between records on the main form, do you see only the related
invoices (for that client) in the subform?

4. Is your combobox bound?

5. What is the RowSource of the combo box?

6. What code is in the AfterUpdate event procedure for the combobox?

--
thanks!

Graham Mandeno [Access MVP]
Auckland, New Zealand

I am afraid I am still having the same problem.

Any further advice how to troubleshoot?
--
Thanks in advance
Stefan

Hi Stefan

You need to requery the combo box, not the [clientname] textbox, and the
requery needs to be performed whenever [clientname] changes.

Put [comboboxname].Requery in the AfterUpdate event procedure for
[clientname]. If [clientname] is a bound textbox, then add the line to
Form_Current also, so that the combobox will be requeried as you go from
one
client record to the next.
--
Good Luck!

Graham Mandeno [Access MVP]
Auckland, New Zealand

I have a form with with a subform.

form1 has client information with client name as the master field
form2 has invoice information related to the client. client name being
the
child field

I have a combo box on form 2 to lookup invoices. I only want to lookup
invoices related to the current client chosen.

In my combo box I filter the lookup to the control listing the client
name
[clientname].

This works the first time I use the combo box. Each time thereafter
when
I
change the client name the combo box still only returns invoices for
the
first client I chose. It does not see the update to the [clientname].

I have to set the onenter property of the combo box to requery the
[clientname] control, I have also tried to set it to Docmd.requery in
the
code window to requery to whole database.

Nothing has worked I can only use this combo box once.

What is the correct way to get this to work?

 

[Graham Mandeno]

Hi Stefan

Aha! No 2 is the problem. See other comments inline below...

1. Is [clientname] a bound textbox?
It is bound on form1 the client info table.
On the subform the control for clientname only refers to the form1 control
=Forms!frm_Invoice!ClientName. The control on the subform does display
the
correct client name when changed on form1

It would be usual to omit the related field completely from the subform. As
it will always show the same data as the master field on the main form, it's
just a waste of space. If you do want to display it, bind it to the
[ClientName] field of the invoice recordsource, not to the main form.

2. What code is in the Form_Current event procedure for the main form?
I do not see any procedures for any events in the property box.

HERE IS THE PROBLEM. As you navigate from one record to another, the combo
box needs to be requeried so that it picks up the new client name as the
filter.

Select [Event Procedure] for the OnCurrent property of the main form, and
add the following code:

Private Sub Form_Current()
Me![name-of-subform-control].Form![name-of-combobox].Requery
End Sub

3. As you move between records on the main form, do you see only the
related
invoices (for that client) in the subform?
YES I ONLY SEE THE RELATED RECORDS.

Everything else looks OK.

 

[Guest]

I am getting an error when I open the form.

Me![name-of-subform-control].Form![name-of-combobox].Requery

which control on the sub form and I referring to, the field with the
clientname?

I tried this code

Me![clientname].form![FindInvoice].requery

when I open the form I get object doesn't support this property or method.

Thanks again for the help
--
Thanks in advance
Stefan

Hi Stefan

Aha! No 2 is the problem. See other comments inline below...

1. Is [clientname] a bound textbox?
It is bound on form1 the client info table.
On the subform the control for clientname only refers to the form1 control
=Forms!frm_Invoice!ClientName. The control on the subform does display
the
correct client name when changed on form1

It would be usual to omit the related field completely from the subform. As
it will always show the same data as the master field on the main form, it's
just a waste of space. If you do want to display it, bind it to the
[ClientName] field of the invoice recordsource, not to the main form.

2. What code is in the Form_Current event procedure for the main form?
I do not see any procedures for any events in the property box.

HERE IS THE PROBLEM. As you navigate from one record to another, the combo
box needs to be requeried so that it picks up the new client name as the
filter.

Select [Event Procedure] for the OnCurrent property of the main form, and
add the following code:

Private Sub Form_Current()
Me![name-of-subform-control].Form![name-of-combobox].Requery
End Sub

3. As you move between records on the main form, do you see only the
related
invoices (for that client) in the subform?
YES I ONLY SEE THE RELATED RECORDS.

Everything else looks OK.

 

[Graham Mandeno]

No No No... the subform control is the control on the *main* form that
*contains* the subform.

It often (but not always) has the same name as the subform object inside it
(the Source Object). It is the subform *control* (not the actual subform)
that has the Link Master/Child Fields properties.

If you click on the border of the subform in design view, you should select
the control. In the properties sheet you will see Name, Source Object, Link
Child Fields, Link Master Fields, etc. You should use what is in the Name
cell, not the Source Object (if they are different) to refer to your subform
at run-time.

So, your code should look something like this:
Me![sbfClientInvoices].Form![FindInvoice].Requery

....assuming "sbfClientInvoices" is the name of your subform control, and
"FindInvoice" is the name of your combobox.
--
Good Luck!

Graham Mandeno [Access MVP]
Auckland, New Zealand

I am getting an error when I open the form.

Me![name-of-subform-control].Form![name-of-combobox].Requery

which control on the sub form and I referring to, the field with the
clientname?

I tried this code

Me![clientname].form![FindInvoice].requery

when I open the form I get object doesn't support this property or method.

Thanks again for the help
--
Thanks in advance
Stefan

Hi Stefan

Aha! No 2 is the problem. See other comments inline below...

1. Is [clientname] a bound textbox?
It is bound on form1 the client info table.
On the subform the control for clientname only refers to the form1
control
=Forms!frm_Invoice!ClientName. The control on the subform does display
the
correct client name when changed on form1

It would be usual to omit the related field completely from the subform.
As
it will always show the same data as the master field on the main form,
it's
just a waste of space. If you do want to display it, bind it to the
[ClientName] field of the invoice recordsource, not to the main form.

2. What code is in the Form_Current event procedure for the main form?
I do not see any procedures for any events in the property box.

HERE IS THE PROBLEM. As you navigate from one record to another, the
combo
box needs to be requeried so that it picks up the new client name as the
filter.

Select [Event Procedure] for the OnCurrent property of the main form, and
add the following code:

Private Sub Form_Current()
Me![name-of-subform-control].Form![name-of-combobox].Requery
End Sub

3. As you move between records on the main form, do you see only the
related
invoices (for that client) in the subform?
YES I ONLY SEE THE RELATED RECORDS.

Everything else looks OK.
--
Good Luck!

Graham Mandeno [Access MVP]
Auckland, New Zealand

4. Is your combobox bound?
NO THE COMBO BOX IS NOT BOUND

5. What is the RowSource of the combo box?
It is a query based on the invoice table. I have the clientname field
criteria set to the value of the clientname control
=Forms!frm_Invoice!ClientName

6. What code is in the AfterUpdate event procedure for the combobox?
It is the standard code created when using the wizard to make a combo
box.
I select find a record when using the wizard.

Hope that helps.

 

[Guest]

I put the code in the on current property of the main form.


Private Sub Form_Current()

Me![sfrm_clientinvoices].Form![FindInvoice].Requery

End Sub

I found the control property is and named it sfrm_clientinvoices

Now when I open the form I immediately get and error message and access
closes!!
When I remove the code the form opens fine.

Thanks again for all your time. If you this for a living I would be happy
to pay you!

--
Thanks in advance
Stefan

No No No... the subform control is the control on the *main* form that
*contains* the subform.

It often (but not always) has the same name as the subform object inside it
(the Source Object). It is the subform *control* (not the actual subform)
that has the Link Master/Child Fields properties.

If you click on the border of the subform in design view, you should select
the control. In the properties sheet you will see Name, Source Object, Link
Child Fields, Link Master Fields, etc. You should use what is in the Name
cell, not the Source Object (if they are different) to refer to your subform
at run-time.

So, your code should look something like this:
Me![sbfClientInvoices].Form![FindInvoice].Requery

....assuming "sbfClientInvoices" is the name of your subform control, and
"FindInvoice" is the name of your combobox.
--
Good Luck!

Graham Mandeno [Access MVP]
Auckland, New Zealand

I am getting an error when I open the form.

Me![name-of-subform-control].Form![name-of-combobox].Requery

which control on the sub form and I referring to, the field with the
clientname?

I tried this code

Me![clientname].form![FindInvoice].requery

when I open the form I get object doesn't support this property or method.

Thanks again for the help
--
Thanks in advance
Stefan

Hi Stefan

Aha! No 2 is the problem. See other comments inline below...

1. Is [clientname] a bound textbox?
It is bound on form1 the client info table.
On the subform the control for clientname only refers to the form1
control
=Forms!frm_Invoice!ClientName. The control on the subform does display
the
correct client name when changed on form1

It would be usual to omit the related field completely from the subform.
As
it will always show the same data as the master field on the main form,
it's
just a waste of space. If you do want to display it, bind it to the
[ClientName] field of the invoice recordsource, not to the main form.

2. What code is in the Form_Current event procedure for the main form?
I do not see any procedures for any events in the property box.

HERE IS THE PROBLEM. As you navigate from one record to another, the
combo
box needs to be requeried so that it picks up the new client name as the
filter.

Select [Event Procedure] for the OnCurrent property of the main form, and
add the following code:

Private Sub Form_Current()
Me![name-of-subform-control].Form![name-of-combobox].Requery
End Sub

3. As you move between records on the main form, do you see only the
related
invoices (for that client) in the subform?
YES I ONLY SEE THE RELATED RECORDS.

Everything else looks OK.
--
Good Luck!

Graham Mandeno [Access MVP]
Auckland, New Zealand

4. Is your combobox bound?
NO THE COMBO BOX IS NOT BOUND

5. What is the RowSource of the combo box?
It is a query based on the invoice table. I have the clientname field
criteria set to the value of the clientname control
=Forms!frm_Invoice!ClientName

6. What code is in the AfterUpdate event procedure for the combobox?
It is the standard code created when using the wizard to make a combo
box.
I select find a record when using the wizard.

Hope that helps.

 

[Graham Mandeno]

You mean Access crashes completely with "msaccess.exe has caused an error in
.... and will close..." message?

What version of Access are you using?

Either your database is corrupt or you have found a bug.

First, take a backup copy of your database. Then open it and do a compact
and repair (under Tools>Database utilities). If this does not work, then
try decompiling it:
Start>Run
<path to msaccess>msaccess.exe <path to your mdb file> /decompile

Then open it again and open any module and compile it (Debug>Compile
<projectname>)

When you've done all that (or when it's working) report back.

I do design and build database applications for a living, but I answer
questions in the newsgroups only because I like helping people
--
Good Luck!

Graham Mandeno [Access MVP]
Auckland, New Zealand

I put the code in the on current property of the main form.


Private Sub Form_Current()

Me![sfrm_clientinvoices].Form![FindInvoice].Requery

End Sub

I found the control property is and named it sfrm_clientinvoices

Now when I open the form I immediately get and error message and access
closes!!
When I remove the code the form opens fine.

Thanks again for all your time. If you this for a living I would be happy
to pay you!

--
Thanks in advance
Stefan

No No No... the subform control is the control on the *main* form that
*contains* the subform.

It often (but not always) has the same name as the subform object inside
it
(the Source Object). It is the subform *control* (not the actual
subform)
that has the Link Master/Child Fields properties.

If you click on the border of the subform in design view, you should
select
the control. In the properties sheet you will see Name, Source Object,
Link
Child Fields, Link Master Fields, etc. You should use what is in the
Name
cell, not the Source Object (if they are different) to refer to your
subform
at run-time.

So, your code should look something like this:
Me![sbfClientInvoices].Form![FindInvoice].Requery

....assuming "sbfClientInvoices" is the name of your subform control, and
"FindInvoice" is the name of your combobox.
--
Good Luck!

Graham Mandeno [Access MVP]
Auckland, New Zealand

I am getting an error when I open the form.

Me![name-of-subform-control].Form![name-of-combobox].Requery

which control on the sub form and I referring to, the field with the
clientname?

I tried this code

Me![clientname].form![FindInvoice].requery

when I open the form I get object doesn't support this property or
method.

Thanks again for the help
--
Thanks in advance
Stefan


:

Hi Stefan

Aha! No 2 is the problem. See other comments inline below...

1. Is [clientname] a bound textbox?
It is bound on form1 the client info table.
On the subform the control for clientname only refers to the form1
control
=Forms!frm_Invoice!ClientName. The control on the subform does
display
the
correct client name when changed on form1

It would be usual to omit the related field completely from the
subform.
As
it will always show the same data as the master field on the main
form,
it's
just a waste of space. If you do want to display it, bind it to the
[ClientName] field of the invoice recordsource, not to the main form.

2. What code is in the Form_Current event procedure for the main
form?
I do not see any procedures for any events in the property box.

HERE IS THE PROBLEM. As you navigate from one record to another, the
combo
box needs to be requeried so that it picks up the new client name as
the
filter.

Select [Event Procedure] for the OnCurrent property of the main form,
and
add the following code:

Private Sub Form_Current()
Me![name-of-subform-control].Form![name-of-combobox].Requery
End Sub

3. As you move between records on the main form, do you see only the
related
invoices (for that client) in the subform?
YES I ONLY SEE THE RELATED RECORDS.

Everything else looks OK.
--
Good Luck!

Graham Mandeno [Access MVP]
Auckland, New Zealand

4. Is your combobox bound?
NO THE COMBO BOX IS NOT BOUND

5. What is the RowSource of the combo box?
It is a query based on the invoice table. I have the clientname
field
criteria set to the value of the clientname control
=Forms!frm_Invoice!ClientName

6. What code is in the AfterUpdate event procedure for the combobox?
It is the standard code created when using the wizard to make a
combo
box.
I select find a record when using the wizard.

Hope that helps.

 

[Guest]

It does crash completely.

I am running access 2002. I will try the steps you suggested.

Do you have a website I can contact you through for future help?

I am on Maui and can't get any education or support so it makes a difficult
learning curve for me. I am running a small business and trying to automate
as much as I can.

Thanks
--
Thanks in advance
Stefan

You mean Access crashes completely with "msaccess.exe has caused an error in
.... and will close..." message?

What version of Access are you using?

Either your database is corrupt or you have found a bug.

First, take a backup copy of your database. Then open it and do a compact
and repair (under Tools>Database utilities). If this does not work, then
try decompiling it:
Start>Run
<path to msaccess>msaccess.exe <path to your mdb file> /decompile

Then open it again and open any module and compile it (Debug>Compile
<projectname>)

When you've done all that (or when it's working) report back.

I do design and build database applications for a living, but I answer
questions in the newsgroups only because I like helping people
--
Good Luck!

Graham Mandeno [Access MVP]
Auckland, New Zealand

I put the code in the on current property of the main form.


Private Sub Form_Current()

Me![sfrm_clientinvoices].Form![FindInvoice].Requery

End Sub

I found the control property is and named it sfrm_clientinvoices

Now when I open the form I immediately get and error message and access
closes!!
When I remove the code the form opens fine.

Thanks again for all your time. If you this for a living I would be happy
to pay you!

--
Thanks in advance
Stefan

No No No... the subform control is the control on the *main* form that
*contains* the subform.

It often (but not always) has the same name as the subform object inside
it
(the Source Object). It is the subform *control* (not the actual
subform)
that has the Link Master/Child Fields properties.

If you click on the border of the subform in design view, you should
select
the control. In the properties sheet you will see Name, Source Object,
Link
Child Fields, Link Master Fields, etc. You should use what is in the
Name
cell, not the Source Object (if they are different) to refer to your
subform
at run-time.

So, your code should look something like this:
Me![sbfClientInvoices].Form![FindInvoice].Requery

....assuming "sbfClientInvoices" is the name of your subform control, and
"FindInvoice" is the name of your combobox.
--
Good Luck!

Graham Mandeno [Access MVP]
Auckland, New Zealand

I am getting an error when I open the form.

Me![name-of-subform-control].Form![name-of-combobox].Requery

which control on the sub form and I referring to, the field with the
clientname?

I tried this code

Me![clientname].form![FindInvoice].requery

when I open the form I get object doesn't support this property or
method.

Thanks again for the help
--
Thanks in advance
Stefan


:

Hi Stefan

Aha! No 2 is the problem. See other comments inline below...

1. Is [clientname] a bound textbox?
It is bound on form1 the client info table.
On the subform the control for clientname only refers to the form1
control
=Forms!frm_Invoice!ClientName. The control on the subform does
display
the
correct client name when changed on form1

It would be usual to omit the related field completely from the
subform.
As
it will always show the same data as the master field on the main
form,
it's
just a waste of space. If you do want to display it, bind it to the
[ClientName] field of the invoice recordsource, not to the main form.

2. What code is in the Form_Current event procedure for the main
form?
I do not see any procedures for any events in the property box.

HERE IS THE PROBLEM. As you navigate from one record to another, the
combo
box needs to be requeried so that it picks up the new client name as
the
filter.

Select [Event Procedure] for the OnCurrent property of the main form,
and
add the following code:

Private Sub Form_Current()
Me![name-of-subform-control].Form![name-of-combobox].Requery
End Sub

3. As you move between records on the main form, do you see only the
related
invoices (for that client) in the subform?
YES I ONLY SEE THE RELATED RECORDS.

Everything else looks OK.
--
Good Luck!

Graham Mandeno [Access MVP]
Auckland, New Zealand

4. Is your combobox bound?
NO THE COMBO BOX IS NOT BOUND

5. What is the RowSource of the combo box?
It is a query based on the invoice table. I have the clientname
field
criteria set to the value of the clientname control
=Forms!frm_Invoice!ClientName

6. What code is in the AfterUpdate event procedure for the combobox?
It is the standard code created when using the wizard to make a
combo
box.
I select find a record when using the wizard.

Hope that helps.

 

[Graham Mandeno]

Hi Stefan

You'd be better to post questions to the newsgroup(s) here if you want
further help in the future, because I'm not always available and you'll get
a faster response that way. I know it's tempting to cling to someone you
know has helped before, but it doesn't always work

However, if you want to draw my attention to any further developments on
this issue, or even just to let me know it's all working , feel free to
drop me a line on graham at mvps dot org. I'd be very interested to hear if
the crash is resolved.
--
Good Luck!

Graham Mandeno [Access MVP]
Auckland, New Zealand

It does crash completely.

I am running access 2002. I will try the steps you suggested.

Do you have a website I can contact you through for future help?

I am on Maui and can't get any education or support so it makes a
difficult
learning curve for me. I am running a small business and trying to
automate
as much as I can.

Thanks
--
Thanks in advance
Stefan

You mean Access crashes completely with "msaccess.exe has caused an error
in
.... and will close..." message?

What version of Access are you using?

Either your database is corrupt or you have found a bug.

First, take a backup copy of your database. Then open it and do a
compact
and repair (under Tools>Database utilities). If this does not work, then
try decompiling it:
Start>Run
<path to msaccess>msaccess.exe <path to your mdb file> /decompile

Then open it again and open any module and compile it (Debug>Compile
<projectname>)

When you've done all that (or when it's working) report back.

I do design and build database applications for a living, but I answer
questions in the newsgroups only because I like helping people
--
Good Luck!

Graham Mandeno [Access MVP]
Auckland, New Zealand

I put the code in the on current property of the main form.


Private Sub Form_Current()

Me![sfrm_clientinvoices].Form![FindInvoice].Requery

End Sub

I found the control property is and named it sfrm_clientinvoices

Now when I open the form I immediately get and error message and access
closes!!
When I remove the code the form opens fine.

Thanks again for all your time. If you this for a living I would be
happy
to pay you!

--
Thanks in advance
Stefan


:

No No No... the subform control is the control on the *main* form that
*contains* the subform.

It often (but not always) has the same name as the subform object
inside
it
(the Source Object). It is the subform *control* (not the actual
subform)
that has the Link Master/Child Fields properties.

If you click on the border of the subform in design view, you should
select
the control. In the properties sheet you will see Name, Source
Object,
Link
Child Fields, Link Master Fields, etc. You should use what is in the
Name
cell, not the Source Object (if they are different) to refer to your
subform
at run-time.

So, your code should look something like this:
Me![sbfClientInvoices].Form![FindInvoice].Requery

....assuming "sbfClientInvoices" is the name of your subform control,
and
"FindInvoice" is the name of your combobox.
--
Good Luck!

Graham Mandeno [Access MVP]
Auckland, New Zealand

I am getting an error when I open the form.

Me![name-of-subform-control].Form![name-of-combobox].Requery

which control on the sub form and I referring to, the field with the
clientname?

I tried this code

Me![clientname].form![FindInvoice].requery

when I open the form I get object doesn't support this property or
method.

Thanks again for the help
--
Thanks in advance
Stefan


:

Hi Stefan

Aha! No 2 is the problem. See other comments inline below...

1. Is [clientname] a bound textbox?
It is bound on form1 the client info table.
On the subform the control for clientname only refers to the
form1
control
=Forms!frm_Invoice!ClientName. The control on the subform does
display
the
correct client name when changed on form1

It would be usual to omit the related field completely from the
subform.
As
it will always show the same data as the master field on the main
form,
it's
just a waste of space. If you do want to display it, bind it to
the
[ClientName] field of the invoice recordsource, not to the main
form.

2. What code is in the Form_Current event procedure for the main
form?
I do not see any procedures for any events in the property box.

HERE IS THE PROBLEM. As you navigate from one record to another,
the
combo
box needs to be requeried so that it picks up the new client name
as
the
filter.

Select [Event Procedure] for the OnCurrent property of the main
form,
and
add the following code:

Private Sub Form_Current()
Me![name-of-subform-control].Form![name-of-combobox].Requery
End Sub

3. As you move between records on the main form, do you see only
the
related
invoices (for that client) in the subform?
YES I ONLY SEE THE RELATED RECORDS.

Everything else looks OK.
--
Good Luck!

Graham Mandeno [Access MVP]
Auckland, New Zealand

4. Is your combobox bound?
NO THE COMBO BOX IS NOT BOUND

5. What is the RowSource of the combo box?
It is a query based on the invoice table. I have the clientname
field
criteria set to the value of the clientname control
=Forms!frm_Invoice!ClientName

6. What code is in the AfterUpdate event procedure for the
combobox?
It is the standard code created when using the wizard to make a
combo
box.
I select find a record when using the wizard.

Hope that helps.