Searching for records on sub form using Combo Box

[Guest]

Hi,

I have created a form called frm_GrantPot which consists of a subform called
frm_GrantApplicant subform. For each record displayed in frm_GrantPot, there
are many related records displayed in frm_GrantApplicant subform (one-many
relationship).

I have created a combo box in frm_GrantApplicant subform that allows the
user to search for all records in frm_GrantApplicant subform. Unfortunately
the combo box displays all records that have been entered in
frm_GrantApplicant subform rather than displaying only the records relating
to the current record is open in frm_GrantPot.

How do I set the combobox in the subform so that it only displays records
relating to the record currently open in the main form? Hope someone can help?

Kind Regards

Ammo

 

[Brian Bastl]

ok, if I understand correctly, you need to add a Where clause referencing
frmGrantPot in your combo's rowsource.

Supposing that your form/subform link is GrantPotID:

1. open subform in design mode
2. open the Properties sheet for your combo
3. click the (...) in the rowsource field to invoke the query builder
4. select and drag GrantPotID into a blank column (leave unchecked)
5. add Forms!frm_GrantPot!GrantPotID to the criteria line
6. close query builder and save changes

Then in the On Current event for your main form (frm_GrantPot), requery the
combo in the subform

Me!frm_GrantApplicant.Form![YourCombo].Requery

HTH,
Brian

 

[Guest]

Hi Brian,

Tried doing what you suggested, but get the following message after adding
the requery line and going back into Form View:

Microsoft Access can't find the macro 'Me!frm_GrantApplicant subform.'

The macro (or its macro group) doesn't exist, or the macro is new but hasn't
been saved. Note that when you enter the macrogroupname.macroname syntax in
an argument, you must specify the name the macro's macro group was last saved
under.

Please note that the name of the subform is: frm_GrantApplicant subform and
the GrantPotID you are referring to is actually named: GrantPotNumber.

Kind Regards

Ammo

ok, if I understand correctly, you need to add a Where clause referencing
frmGrantPot in your combo's rowsource.

Supposing that your form/subform link is GrantPotID:

1. open subform in design mode
2. open the Properties sheet for your combo
3. click the (...) in the rowsource field to invoke the query builder
4. select and drag GrantPotID into a blank column (leave unchecked)
5. add Forms!frm_GrantPot!GrantPotID to the criteria line
6. close query builder and save changes

Then in the On Current event for your main form (frm_GrantPot), requery the
combo in the subform

Me!frm_GrantApplicant.Form![YourCombo].Requery

HTH,
Brian

Hi,

I have created a form called frm_GrantPot which consists of a subform called
frm_GrantApplicant subform. For each record displayed in frm_GrantPot, there
are many related records displayed in frm_GrantApplicant subform (one-many
relationship).

I have created a combo box in frm_GrantApplicant subform that allows the
user to search for all records in frm_GrantApplicant subform. Unfortunately
the combo box displays all records that have been entered in
frm_GrantApplicant subform rather than displaying only the records relating
to the current record is open in frm_GrantPot.

How do I set the combobox in the subform so that it only displays records
relating to the record currently open in the main form? Hope someone can help?

Kind Regards

Ammo

 

[Brian Bastl]

Hi Ammo,
sorry I couldn't get back to you earlier. Had to demolish a bathroom today.

Anyhow, what is the name of the subform control? If you open your Main form
in design view, and right-click the subform control and select "Properties"
to bring up its property sheet, what is the Name of the control? I ask,
because the name of the subform control isn't necessarily the name of the
subform. Just want to make sure...

If it is in fact "frm_GrantApplicant subform" then you need to bracket the
entire reference due to the space between Applicant and subform. (not an
ideal naming convention. better to use something like sfrmGrantApplicant for
the name of the subform control. That way you don't have to worry about
bracketing.)

Me![frm_GrantApplicant subform].Form![YourCombo].Requery

So I'd put the code in the On Current event of your Main form, and the
AfterUpdate event of GrantPotNumber (also in the main form).

You'll need to change [YourCombo] to the actual name of the combobox in your
subform.

One last thing: of course I'm assuming that you are using the vba editor,
and not typing this directly on the line next to On Current. Right? If not,
then click the On Current| click the down arrow| Select [Event Procedure]|
click the ellipes(...). This will open the vba Editor. Then just insert the
above between Private Sub Form_Current() and End Sub.

Post back if you need further assistance.

Brian

Hi Brian,

Tried doing what you suggested, but get the following message after adding
the requery line and going back into Form View:

Microsoft Access can't find the macro 'Me!frm_GrantApplicant subform.'

The macro (or its macro group) doesn't exist, or the macro is new but hasn't
been saved. Note that when you enter the macrogroupname.macroname syntax in
an argument, you must specify the name the macro's macro group was last saved
under.

Please note that the name of the subform is: frm_GrantApplicant subform and
the GrantPotID you are referring to is actually named: GrantPotNumber.

Kind Regards

Ammo

ok, if I understand correctly, you need to add a Where clause referencing
frmGrantPot in your combo's rowsource.

Supposing that your form/subform link is GrantPotID:

1. open subform in design mode
2. open the Properties sheet for your combo
3. click the (...) in the rowsource field to invoke the query builder
4. select and drag GrantPotID into a blank column (leave unchecked)
5. add Forms!frm_GrantPot!GrantPotID to the criteria line
6. close query builder and save changes

Then in the On Current event for your main form (frm_GrantPot), requery the
combo in the subform

Me!frm_GrantApplicant.Form![YourCombo].Requery

HTH,
Brian

Hi,

I have created a form called frm_GrantPot which consists of a subform called
frm_GrantApplicant subform. For each record displayed in frm_GrantPot, there
are many related records displayed in frm_GrantApplicant subform (one-many
relationship).

I have created a combo box in frm_GrantApplicant subform that allows the
user to search for all records in frm_GrantApplicant subform. Unfortunately
the combo box displays all records that have been entered in
frm_GrantApplicant subform rather than displaying only the records relating
to the current record is open in frm_GrantPot.

How do I set the combobox in the subform so that it only displays records
relating to the record currently open in the main form? Hope someone

can
help?

Kind Regards

Ammo

 

[Guest]

Hi Brian,

Thanks very much for your assistance, I have managed to get the combo box
functioning how I wanted. Unfortunately your assumption about typing the code
into the vb editor was wrong as I was originally typing the line next to On
Current

Although the combo box issue is now solved, unfortunately this hasn’t
resolved the other problem of refreshing new data added in the sub form and
related filtered form (see other post I submitted). I was wondering if you
know of a solution to this problem as well, which will be much appreciated?
If you like I can email you a copy of the database if that will make life
easier?

Kind Regards

Ammo

Hi Ammo,
sorry I couldn't get back to you earlier. Had to demolish a bathroom today.

Anyhow, what is the name of the subform control? If you open your Main form
in design view, and right-click the subform control and select "Properties"
to bring up its property sheet, what is the Name of the control? I ask,
because the name of the subform control isn't necessarily the name of the
subform. Just want to make sure...

If it is in fact "frm_GrantApplicant subform" then you need to bracket the
entire reference due to the space between Applicant and subform. (not an
ideal naming convention. better to use something like sfrmGrantApplicant for
the name of the subform control. That way you don't have to worry about
bracketing.)

Me![frm_GrantApplicant subform].Form![YourCombo].Requery

So I'd put the code in the On Current event of your Main form, and the
AfterUpdate event of GrantPotNumber (also in the main form).

You'll need to change [YourCombo] to the actual name of the combobox in your
subform.

One last thing: of course I'm assuming that you are using the vba editor,
and not typing this directly on the line next to On Current. Right? If not,
then click the On Current| click the down arrow| Select [Event Procedure]|
click the ellipes(...). This will open the vba Editor. Then just insert the
above between Private Sub Form_Current() and End Sub.

Post back if you need further assistance.

Brian

Hi Brian,

Tried doing what you suggested, but get the following message after adding
the requery line and going back into Form View:

Microsoft Access can't find the macro 'Me!frm_GrantApplicant subform.'

The macro (or its macro group) doesn't exist, or the macro is new but hasn't
been saved. Note that when you enter the macrogroupname.macroname syntax in
an argument, you must specify the name the macro's macro group was last saved
under.

Please note that the name of the subform is: frm_GrantApplicant subform and
the GrantPotID you are referring to is actually named: GrantPotNumber.

Kind Regards

Ammo

ok, if I understand correctly, you need to add a Where clause referencing
frmGrantPot in your combo's rowsource.

Supposing that your form/subform link is GrantPotID:

1. open subform in design mode
2. open the Properties sheet for your combo
3. click the (...) in the rowsource field to invoke the query builder
4. select and drag GrantPotID into a blank column (leave unchecked)
5. add Forms!frm_GrantPot!GrantPotID to the criteria line
6. close query builder and save changes

Then in the On Current event for your main form (frm_GrantPot), requery the
combo in the subform

Me!frm_GrantApplicant.Form![YourCombo].Requery

HTH,
Brian


Hi,

I have created a form called frm_GrantPot which consists of a subform
called
frm_GrantApplicant subform. For each record displayed in frm_GrantPot,
there
are many related records displayed in frm_GrantApplicant subform (one-many
relationship).

I have created a combo box in frm_GrantApplicant subform that allows the
user to search for all records in frm_GrantApplicant subform.
Unfortunately
the combo box displays all records that have been entered in
frm_GrantApplicant subform rather than displaying only the records
relating
to the current record is open in frm_GrantPot.

How do I set the combobox in the subform so that it only displays records
relating to the record currently open in the main form? Hope someone can
help?

Kind Regards

Ammo

 

[Brian Bastl]

Hi Ammo,

sure, I'll take a look at it, but it won't until late tonight or early
tomorrow. Really got to get to work.

you can send it to: x<at>y<dot>z

x = bastel
y = alltel
z = net

Brian

Hi Brian,

Thanks very much for your assistance, I have managed to get the combo box
functioning how I wanted. Unfortunately your assumption about typing the code
into the vb editor was wrong as I was originally typing the line next to On
Current

Although the combo box issue is now solved, unfortunately this hasn't
resolved the other problem of refreshing new data added in the sub form and
related filtered form (see other post I submitted). I was wondering if you
know of a solution to this problem as well, which will be much appreciated?
If you like I can email you a copy of the database if that will make life
easier?

Kind Regards

Ammo

Hi Ammo,
sorry I couldn't get back to you earlier. Had to demolish a bathroom today.

Anyhow, what is the name of the subform control? If you open your Main form
in design view, and right-click the subform control and select "Properties"
to bring up its property sheet, what is the Name of the control? I ask,
because the name of the subform control isn't necessarily the name of the
subform. Just want to make sure...

If it is in fact "frm_GrantApplicant subform" then you need to bracket the
entire reference due to the space between Applicant and subform. (not an
ideal naming convention. better to use something like sfrmGrantApplicant for
the name of the subform control. That way you don't have to worry about
bracketing.)

Me![frm_GrantApplicant subform].Form![YourCombo].Requery

So I'd put the code in the On Current event of your Main form, and the
AfterUpdate event of GrantPotNumber (also in the main form).

You'll need to change [YourCombo] to the actual name of the combobox in your
subform.

One last thing: of course I'm assuming that you are using the vba editor,
and not typing this directly on the line next to On Current. Right? If not,
then click the On Current| click the down arrow| Select [Event Procedure]|
click the ellipes(...). This will open the vba Editor. Then just insert the
above between Private Sub Form_Current() and End Sub.

Post back if you need further assistance.

Brian

Hi Brian,

Tried doing what you suggested, but get the following message after adding
the requery line and going back into Form View:

Microsoft Access can't find the macro 'Me!frm_GrantApplicant subform.'

The macro (or its macro group) doesn't exist, or the macro is new but hasn't
been saved. Note that when you enter the macrogroupname.macroname

syntax
in

an argument, you must specify the name the macro's macro group was

last
saved

under.

Please note that the name of the subform is: frm_GrantApplicant

subform
and

the GrantPotID you are referring to is actually named: GrantPotNumber.

Kind Regards

Ammo




:

ok, if I understand correctly, you need to add a Where clause referencing
frmGrantPot in your combo's rowsource.

Supposing that your form/subform link is GrantPotID:

1. open subform in design mode
2. open the Properties sheet for your combo
3. click the (...) in the rowsource field to invoke the query builder
4. select and drag GrantPotID into a blank column (leave unchecked)
5. add Forms!frm_GrantPot!GrantPotID to the criteria line
6. close query builder and save changes

Then in the On Current event for your main form (frm_GrantPot),

requery
the

combo in the subform

Me!frm_GrantApplicant.Form![YourCombo].Requery

HTH,
Brian


Hi,

I have created a form called frm_GrantPot which consists of a subform
called
frm_GrantApplicant subform. For each record displayed in frm_GrantPot,
there
are many related records displayed in frm_GrantApplicant subform (one-many
relationship).

I have created a combo box in frm_GrantApplicant subform that

allows
the

user to search for all records in frm_GrantApplicant subform.
Unfortunately
the combo box displays all records that have been entered in
frm_GrantApplicant subform rather than displaying only the records
relating
to the current record is open in frm_GrantPot.

How do I set the combobox in the subform so that it only displays records
relating to the record currently open in the main form? Hope

someone
can

help?

Kind Regards

Ammo

 

[Brian Bastl]

Hi Ammo,

Ok, got your db. And the answer is:

in the code module behind frm_GrantPot:
-----------------------------------------------------------
Private Sub Form_Current()

Me.frm_GrantApplicant_subform![Combo41].Requery

End Sub
-----------------------------------------------------------
Private Sub GrantPotNumber_AfterUpdate()

Me.frm_GrantApplicant_subform![Combo41].Requery

End Sub