Hello,
I have two combo boxes. On the main form (Form 1) is a Category combo box.
On the subform (Form 2) is a Product combo box. Using a where query I have
set it up so the Product 2 combo box is based on the category combo box. This
works fine for the first choice. I.e. the first time i open the form and
choose a category the appropriate products appear in the combo box but when i
choose a different categories the products combo box doesn't update. I've got
it to work when both combo boxes are the main form using this AfterUpdate
event:

Private Sub CategoryAfterUpdate()
Me.Product = Null
Me.Product.Requery
Me.Product = Me.Product.ItemData(0)
End Sub

However I can't figure out how to adjust this event too look for product in
the subform (Form2) (instead of on the main form).

At this time both of these combo boxes are unbound. However it would be
better for me if they were bound (i.e Bind Categories to the Categories table
(which contains Category ID and Category Name and Bind Products to the
Products table (which contains product id, product name and category ID). Is
it possible to have these combo boxes bound and still use this method for
looking up products based on the categories.

I'll also want to add a subform to form2 that shows properties of the
products, but am hoping it will link through child and master link fields. Do
you think this will work.

Thanks in Advance,

Karl wrote:
> I have two combo boxes. On the main form (Form 1) is a Category combo box.
>On the subform (Form 2) is a Product combo box. Using a where query I have
>set it up so the Product 2 combo box is based on the category combo box. This
>works fine for the first choice. I.e. the first time i open the form and
>choose a category the appropriate products appear in the combo box but when i
>choose a different categories the products combo box doesn't update. I've got
>it to work when both combo boxes are the main form using this AfterUpdate
>event:
>
>Private Sub CategoryAfterUpdate()
>Me.Product = Null
>Me.Product.Requery
>Me.Product = Me.Product.ItemData(0)
>End Sub
>
>However I can't figure out how to adjust this event too look for product in
>the subform (Form2) (instead of on the main form).
>
>At this time both of these combo boxes are unbound. However it would be
>better for me if they were bound (i.e Bind Categories to the Categories table
>(which contains Category ID and Category Name and Bind Products to the
>Products table (which contains product id, product name and category ID). Is
>it possible to have these combo boxes bound and still use this method for
>looking up products based on the categories.


The corresponding code for main form to subform is:

Private Sub CategoryAfterUpdate()
With Me.subformcontrol.Form
.Product = Null
.Product.Requery
.Product = .Product.ItemData(0)
End With
End Sub

You will also need to do the Requery in the main form's
Current event to keep the Category and products synchronized
when the main form navigates from one rcord to another.

Private Sub Form_Current()
Me.subformcontrol.Form.Product.Requery
End Sub

Bound or unbound makes a big difference in that your code is
dirtying the subform's current record, which something that
has important consequeces. OTOH, if the subform can have
more than one record, you should invalidate the product
field in every record when the Category is changed.

I suggest that you get rid of the two assignment statements
and, for now, only use the Requery. Once you get that part
to work correctly, then think long and hard about what you
need to happen when the Category is changed and the child
table now contains records with the wrong product. I
suspect that more than one field in the subform records will
become invalid.

--
Marsh
MVP [MS Access]

"Marshall Barton" wrote:

> Karl wrote:
> > I have two combo boxes. On the main form (Form 1) is a Category combo box.
> >On the subform (Form 2) is a Product combo box. Using a where query I have
> >set it up so the Product 2 combo box is based on the category combo box. This
> >works fine for the first choice. I.e. the first time i open the form and
> >choose a category the appropriate products appear in the combo box but when i
> >choose a different categories the products combo box doesn't update. I've got
> >it to work when both combo boxes are the main form using this AfterUpdate
> >event:
> >
> >Private Sub CategoryAfterUpdate()
> >Me.Product = Null
> >Me.Product.Requery
> >Me.Product = Me.Product.ItemData(0)
> >End Sub
> >
> >However I can't figure out how to adjust this event too look for product in
> >the subform (Form2) (instead of on the main form).
> >
> >At this time both of these combo boxes are unbound. However it would be
> >better for me if they were bound (i.e Bind Categories to the Categories table
> >(which contains Category ID and Category Name and Bind Products to the
> >Products table (which contains product id, product name and category ID). Is
> >it possible to have these combo boxes bound and still use this method for
> >looking up products based on the categories.
>
>
> The corresponding code for main form to subform is:
>
> Private Sub CategoryAfterUpdate()
> With Me.subformcontrol.Form
> .Product = Null
> .Product.Requery
> .Product = .Product.ItemData(0)
> End With
> End Sub
>
> You will also need to do the Requery in the main form's
> Current event to keep the Category and products synchronized
> when the main form navigates from one rcord to another.
>
> Private Sub Form_Current()
> Me.subformcontrol.Form.Product.Requery
> End Sub
>
> Bound or unbound makes a big difference in that your code is
> dirtying the subform's current record, which something that
> has important consequeces. OTOH, if the subform can have
> more than one record, you should invalidate the product
> field in every record when the Category is changed.
>
> I suggest that you get rid of the two assignment statements
> and, for now, only use the Requery. Once you get that part
> to work correctly, then think long and hard about what you
> need to happen when the Category is changed and the child
> table now contains records with the wrong product. I
> suspect that more than one field in the subform records will
> become invalid.
>
> --
> Marsh
> MVP [MS Access]
>
Hi Marshall

Thanks for helping. I am new to Access and Programming language so this
problem has been driving me nuts. Unfortunately your solution did not quite
work properly. I got an error message for both the AfterUpdate and OnCurrent
that said:

The expression On Current you entered as the event property setting produced
the following error: Invalid Outside procedure.
* The expression may not result in the name of a macro, the name of a
user-defined function, or [event procedure]
* there may have been an error evaluating the function event or macro.

I put your code in the AfterUpdate of the main form (Form1) and current
requrry in main form as well. One question i have too is that In your code
you wrote Form does that refer to the main form or my subform (Form2). I
tried it both ways and niether worked.

I am also a little unsure about what you were talking about in the bound
explanation as I am unfamilar with some of the programing lingo I.e dirtying&
invalidate.

Thank you so much for your time and help.

>> Karl wrote:
>> > I have two combo boxes. On the main form (Form 1) is a Category combo box.
>> >On the subform (Form 2) is a Product combo box. Using a where query I have
>> >set it up so the Product 2 combo box is based on the category combo box. This
>> >works fine for the first choice. I.e. the first time i open the form and
>> >choose a category the appropriate products appear in the combo box but when i
>> >choose a different categories the products combo box doesn't update. I've got
>> >it to work when both combo boxes are the main form using this AfterUpdate
>> >event:
>> >
>> >Private Sub CategoryAfterUpdate()
>> >Me.Product = Null
>> >Me.Product.Requery
>> >Me.Product = Me.Product.ItemData(0)
>> >End Sub
>> >
>> >However I can't figure out how to adjust this event too look for product in
>> >the subform (Form2) (instead of on the main form).
>> >
>> >At this time both of these combo boxes are unbound. However it would be
>> >better for me if they were bound (i.e Bind Categories to the Categories table
>> >(which contains Category ID and Category Name and Bind Products to the
>> >Products table (which contains product id, product name and category ID). Is
>> >it possible to have these combo boxes bound and still use this method for
>> >looking up products based on the categories.
>>
>>
>"Marshall Barton" wrote:
>> The corresponding code for main form to subform is:
>>
>> Private Sub CategoryAfterUpdate()
>> With Me.subformcontrol.Form
>> .Product = Null
>> .Product.Requery
>> .Product = .Product.ItemData(0)
>> End With
>> End Sub
>>
>> You will also need to do the Requery in the main form's
>> Current event to keep the Category and products synchronized
>> when the main form navigates from one rcord to another.
>>
>> Private Sub Form_Current()
>> Me.subformcontrol.Form.Product.Requery
>> End Sub
>>
>> Bound or unbound makes a big difference in that your code is
>> dirtying the subform's current record, which something that
>> has important consequeces. OTOH, if the subform can have
>> more than one record, you should invalidate the product
>> field in every record when the Category is changed.
>>
>> I suggest that you get rid of the two assignment statements
>> and, for now, only use the Requery. Once you get that part
>> to work correctly, then think long and hard about what you
>> need to happen when the Category is changed and the child
>> table now contains records with the wrong product. I
>> suspect that more than one field in the subform records will
>> become invalid.
>>
>
Karl wrote:
>Thanks for helping. I am new to Access and Programming language so this
>problem has been driving me nuts. Unfortunately your solution did not quite
>work properly. I got an error message for both the AfterUpdate and OnCurrent
>that said:
>
>The expression On Current you entered as the event property setting produced
>the following error: Invalid Outside procedure.
>* The expression may not result in the name of a macro, the name of a
>user-defined function, or [event procedure]
>* there may have been an error evaluating the function event or macro.
>
>I put your code in the AfterUpdate of the main form (Form1) and current
>requrry in main form as well. One question i have too is that In your code
>you wrote Form does that refer to the main form or my subform (Form2). I
>tried it both ways and niether worked.
>
>I am also a little unsure about what you were talking about in the bound
>explanation as I am unfamilar with some of the programing lingo I.e dirtying&
>invalidate.


The code goes in the appropriate event **procedure**, not
in an event **property**.

Select [Event Procedure] for the event property. Then
click on the builder button in the right margin to bring up
the event procedure and add the requery statement between
the Sub and End Sub lines.

--
Marsh
MVP [MS Access]

Hello,
Unfortunately I was unable to get it to work still. I have no idea what I’ve
done wrong. In my main form (Form1). I got the following event procedure
codes in the after update and On current fields: (note my subform is named
Form2 with the combo box in it named Product, the combo box in Form 1 is
named Category)

Private Sub Category_AfterUpdate()
Me.Form2.Form.Product = Null
Me.Form2.Form.Product.Requery
Me.Form2.Form.Product = Me.Form2.Form.Product.ItemData(0)

End Sub



Private Sub Form_Current()
Me.Form2.Form.Product.Requery
End Sub

I get the following error messages when I enter the form view.

The expression AfterUpdate you entered as the event property setting
produced the following error: Invalid outside procedure.
*The expression may not result in the name of a macro, the name of a user
defined function, or [Event Procedure]
*There may have been an error evaluating the function, event or macro.



The expression on current you entered as the event property setting produced
the following error: Invalid outside procedure.
*The expression may not result in the name of a macro, the name of a user
defined function, or [Event Procedure]
*There may have been an error evaluating the function, event or macro.


Here is some additional information about my combo boxes in case this has
something to do with why its not working properly:

Both Combo boxes are unbound and in the product combo box in the subform I
have the row source set as: SELECT [Sub Categories Table].[Sub Category ID],
[Sub Categories Table].[Category ID], [Sub Categories Table].[Sub Category]
FROM [Sub Categories Table] WHERE ((([Sub Categories Table].[Category
ID])=Forms!Form1!Category));

Note my subcategories table is where the products are found. The subcategory
table has subcategory id (product id), category id, and subcategory name
(product name).

The product combo box in the subformhas no event procedures in it.

In the category combo box in the main form I have the row source set as:
SELECT Categories.[Category ID], Categories.Category FROM Categories;

Where the categories table has category id and category name.

I don’t have problems with what is listed in the combo boxes but I just
can’t get the product from combo box two to show up based on the category in
comb box one on the main form.


Thanks for your patience and help.
Karl



"Marshall Barton" wrote:

> >> Karl wrote:
> >> > I have two combo boxes. On the main form (Form 1) is a Category combo box.
> >> >On the subform (Form 2) is a Product combo box. Using a where query I have
> >> >set it up so the Product 2 combo box is based on the category combo box. This
> >> >works fine for the first choice. I.e. the first time i open the form and
> >> >choose a category the appropriate products appear in the combo box but when i
> >> >choose a different categories the products combo box doesn't update. I've got
> >> >it to work when both combo boxes are the main form using this AfterUpdate
> >> >event:
> >> >
> >> >Private Sub CategoryAfterUpdate()
> >> >Me.Product = Null
> >> >Me.Product.Requery
> >> >Me.Product = Me.Product.ItemData(0)
> >> >End Sub
> >> >
> >> >However I can't figure out how to adjust this event too look for product in
> >> >the subform (Form2) (instead of on the main form).
> >> >
> >> >At this time both of these combo boxes are unbound. However it would be
> >> >better for me if they were bound (i.e Bind Categories to the Categories table
> >> >(which contains Category ID and Category Name and Bind Products to the
> >> >Products table (which contains product id, product name and category ID). Is
> >> >it possible to have these combo boxes bound and still use this method for
> >> >looking up products based on the categories.
> >>
> >>
> >"Marshall Barton" wrote:
> >> The corresponding code for main form to subform is:
> >>
> >> Private Sub CategoryAfterUpdate()
> >> With Me.subformcontrol.Form
> >> .Product = Null
> >> .Product.Requery
> >> .Product = .Product.ItemData(0)
> >> End With
> >> End Sub
> >>
> >> You will also need to do the Requery in the main form's
> >> Current event to keep the Category and products synchronized
> >> when the main form navigates from one rcord to another.
> >>
> >> Private Sub Form_Current()
> >> Me.subformcontrol.Form.Product.Requery
> >> End Sub
> >>
> >> Bound or unbound makes a big difference in that your code is
> >> dirtying the subform's current record, which something that
> >> has important consequeces. OTOH, if the subform can have
> >> more than one record, you should invalidate the product
> >> field in every record when the Category is changed.
> >>
> >> I suggest that you get rid of the two assignment statements
> >> and, for now, only use the Requery. Once you get that part
> >> to work correctly, then think long and hard about what you
> >> need to happen when the Category is changed and the child
> >> table now contains records with the wrong product. I
> >> suspect that more than one field in the subform records will
> >> become invalid.
> >>
> >
> Karl wrote:
> >Thanks for helping. I am new to Access and Programming language so this
> >problem has been driving me nuts. Unfortunately your solution did not quite
> >work properly. I got an error message for both the AfterUpdate and OnCurrent
> >that said:
> >
> >The expression On Current you entered as the event property setting produced
> >the following error: Invalid Outside procedure.
> >* The expression may not result in the name of a macro, the name of a
> >user-defined function, or [event procedure]
> >* there may have been an error evaluating the function event or macro.
> >
> >I put your code in the AfterUpdate of the main form (Form1) and current
> >requrry in main form as well. One question i have too is that In your code
> >you wrote Form does that refer to the main form or my subform (Form2). I
> >tried it both ways and niether worked.
> >
> >I am also a little unsure about what you were talking about in the bound
> >explanation as I am unfamilar with some of the programing lingo I.e dirtying&
> >invalidate.
>
>
> The code goes in the appropriate event **procedure**, not
> in an event **property**.
>
> Select [Event Procedure] for the event property. Then
> click on the builder button in the right margin to bring up
> the event procedure and add the requery statement between
> the Sub and End Sub lines.
>
> --
> Marsh
> MVP [MS Access]
>

That message indicates that you still have something weird
in the the event **properties**. Check to make sure that
that the event properties contain only the text:
[Event Procedure]

If you have any event properties with something other than
that in them, you should delete whatever junk is in them.

As far as I can see, the combo boxes are set up corrrectly.
It seems to be just a matter of using the event properties
to properly connect the events to their event procedures.
--
Marsh
MVP [MS Access]


Karl wrote:
>Unfortunately I was unable to get it to work still. I have no idea what I’ve
>done wrong. In my main form (Form1). I got the following event procedure
>codes in the after update and On current fields: (note my subform is named
>Form2 with the combo box in it named Product, the combo box in Form 1 is
>named Category)
>
>Private Sub Category_AfterUpdate()
> Me.Form2.Form.Product = Null
> Me.Form2.Form.Product.Requery
> Me.Form2.Form.Product = Me.Form2.Form.Product.ItemData(0)
>End Sub
>
>Private Sub Form_Current()
>Me.Form2.Form.Product.Requery
>End Sub
>
>I get the following error messages when I enter the form view.
>
>The expression AfterUpdate you entered as the event property setting
>produced the following error: Invalid outside procedure.
>*The expression may not result in the name of a macro, the name of a user
>defined function, or [Event Procedure]
>*There may have been an error evaluating the function, event or macro.
>
>
>The expression on current you entered as the event property setting produced
>the following error: Invalid outside procedure.
>*The expression may not result in the name of a macro, the name of a user
>defined function, or [Event Procedure]
>*There may have been an error evaluating the function, event or macro.
>
>
>Here is some additional information about my combo boxes in case this has
>something to do with why its not working properly:
>
>Both Combo boxes are unbound and in the product combo box in the subform I
>have the row source set as: SELECT [Sub Categories Table].[Sub Category ID],
>[Sub Categories Table].[Category ID], [Sub Categories Table].[Sub Category]
>FROM [Sub Categories Table] WHERE ((([Sub Categories Table].[Category
>ID])=Forms!Form1!Category));
>
>Note my subcategories table is where the products are found. The subcategory
>table has subcategory id (product id), category id, and subcategory name
>(product name).
>
>The product combo box in the subformhas no event procedures in it.
>
>In the category combo box in the main form I have the row source set as:
>SELECT Categories.[Category ID], Categories.Category FROM Categories;
>
>Where the categories table has category id and category name.
>
>I don’t have problems with what is listed in the combo boxes but I just
>can’t get the product from combo box two to show up based on the category in
>comb box one on the main form.
>
>
>> >> Karl wrote:
>> >> > I have two combo boxes. On the main form (Form 1) is a Category combo box.
>> >> >On the subform (Form 2) is a Product combo box. Using a where query I have
>> >> >set it up so the Product 2 combo box is based on the category combo box. This
>> >> >works fine for the first choice. I.e. the first time i open the form and
>> >> >choose a category the appropriate products appear in the combo box but when i
>> >> >choose a different categories the products combo box doesn't update. I've got
>> >> >it to work when both combo boxes are the main form using this AfterUpdate
>> >> >event:
>> >> >
>> >> >Private Sub CategoryAfterUpdate()
>> >> >Me.Product = Null
>> >> >Me.Product.Requery
>> >> >Me.Product = Me.Product.ItemData(0)
>> >> >End Sub
>> >> >
>> >> >However I can't figure out how to adjust this event too look for product in
>> >> >the subform (Form2) (instead of on the main form).
>> >> >
>> >> >At this time both of these combo boxes are unbound. However it would be
>> >> >better for me if they were bound (i.e Bind Categories to the Categories table
>> >> >(which contains Category ID and Category Name and Bind Products to the
>> >> >Products table (which contains product id, product name and category ID). Is
>> >> >it possible to have these combo boxes bound and still use this method for
>> >> >looking up products based on the categories.
>> >>
>> >>
>> >"Marshall Barton" wrote:
>> >> The corresponding code for main form to subform is:
>> >>
>> >> Private Sub CategoryAfterUpdate()
>> >> With Me.subformcontrol.Form
>> >> .Product = Null
>> >> .Product.Requery
>> >> .Product = .Product.ItemData(0)
>> >> End With
>> >> End Sub
>> >>
>> >> You will also need to do the Requery in the main form's
>> >> Current event to keep the Category and products synchronized
>> >> when the main form navigates from one rcord to another.
>> >>
>> >> Private Sub Form_Current()
>> >> Me.subformcontrol.Form.Product.Requery
>> >> End Sub
>> >>
>> >> Bound or unbound makes a big difference in that your code is
>> >> dirtying the subform's current record, which something that
>> >> has important consequeces. OTOH, if the subform can have
>> >> more than one record, you should invalidate the product
>> >> field in every record when the Category is changed.
>> >>
>> >> I suggest that you get rid of the two assignment statements
>> >> and, for now, only use the Requery. Once you get that part
>> >> to work correctly, then think long and hard about what you
>> >> need to happen when the Category is changed and the child
>> >> table now contains records with the wrong product. I
>> >> suspect that more than one field in the subform records will
>> >> become invalid.
>> >>
>> >
>> Karl wrote:
>> >Thanks for helping. I am new to Access and Programming language so this
>> >problem has been driving me nuts. Unfortunately your solution did not quite
>> >work properly. I got an error message for both the AfterUpdate and OnCurrent
>> >that said:
>> >
>> >The expression On Current you entered as the event property setting produced
>> >the following error: Invalid Outside procedure.
>> >* The expression may not result in the name of a macro, the name of a
>> >user-defined function, or [event procedure]
>> >* there may have been an error evaluating the function event or macro.
>> >
>> >I put your code in the AfterUpdate of the main form (Form1) and current
>> >requrry in main form as well. One question i have too is that In your code
>> >you wrote Form does that refer to the main form or my subform (Form2). I
>> >tried it both ways and niether worked.
>> >
>> >I am also a little unsure about what you were talking about in the bound
>> >explanation as I am unfamilar with some of the programing lingo I.e dirtying&
>> >invalidate.
>>
>>
>"Marshall Barton" wrote:
>> The code goes in the appropriate event **procedure**, not
>> in an event **property**.
>>
>> Select [Event Procedure] for the event property. Then
>> click on the builder button in the right margin to bring up
>> the event procedure and add the requery statement between
>> the Sub and End Sub lines.

Hi Marshall,

Thanks alot. You were right there was some junk in the Event Procedure. I
just had not noticed it until now when I scrolled up. Such a simple problem.
It works now. Thanks a lot for solving it. Unfortunatetly I've already
encountered my next problem. Now that the combo boxes work correctely I want
to add another subform to the product product subform (form2) that allows me
to enter information about the product. So what I tried to do is change the
product combo form from unbound to bound using the product table as record
source for the table and the control source for the product combo box as
product name (from the product table). I then wanted to add a text box that
shows the product id associated with the product name chosen in the combo
box. I would then use this product id to add another subform (form 3) in
form 2 which would link product id as the master link field in form 2 to the
child link field in form 3. However the combo box appears to continue to work
but it won't automatically update the associated product id. Instead it
changes the subcategory name in the table to a number. Any ideas? Thanks
again for your help. It is greatly appreciated.

Karl

"Marshall Barton" wrote:

> That message indicates that you still have something weird
> in the the event **properties**. Check to make sure that
> that the event properties contain only the text:
> [Event Procedure]
>
> If you have any event properties with something other than
> that in them, you should delete whatever junk is in them.
>
> As far as I can see, the combo boxes are set up corrrectly.
> It seems to be just a matter of using the event properties
> to properly connect the events to their event procedures.
> --
> Marsh
> MVP [MS Access]
>
>
> Karl wrote:
> >Unfortunately I was unable to get it to work still. I have no idea what I’ve
> >done wrong. In my main form (Form1). I got the following event procedure
> >codes in the after update and On current fields: (note my subform is named
> >Form2 with the combo box in it named Product, the combo box in Form 1 is
> >named Category)
> >
> >Private Sub Category_AfterUpdate()
> > Me.Form2.Form.Product = Null
> > Me.Form2.Form.Product.Requery
> > Me.Form2.Form.Product = Me.Form2.Form.Product.ItemData(0)
> >End Sub
> >
> >Private Sub Form_Current()
> >Me.Form2.Form.Product.Requery
> >End Sub
> >
> >I get the following error messages when I enter the form view.
> >
> >The expression AfterUpdate you entered as the event property setting
> >produced the following error: Invalid outside procedure.
> >*The expression may not result in the name of a macro, the name of a user
> >defined function, or [Event Procedure]
> >*There may have been an error evaluating the function, event or macro.
> >
> >
> >The expression on current you entered as the event property setting produced
> >the following error: Invalid outside procedure.
> >*The expression may not result in the name of a macro, the name of a user
> >defined function, or [Event Procedure]
> >*There may have been an error evaluating the function, event or macro.
> >
> >
> >Here is some additional information about my combo boxes in case this has
> >something to do with why its not working properly:
> >
> >Both Combo boxes are unbound and in the product combo box in the subform I
> >have the row source set as: SELECT [Sub Categories Table].[Sub Category ID],
> >[Sub Categories Table].[Category ID], [Sub Categories Table].[Sub Category]
> >FROM [Sub Categories Table] WHERE ((([Sub Categories Table].[Category
> >ID])=Forms!Form1!Category));
> >
> >Note my subcategories table is where the products are found. The subcategory
> >table has subcategory id (product id), category id, and subcategory name
> >(product name).
> >
> >The product combo box in the subformhas no event procedures in it.
> >
> >In the category combo box in the main form I have the row source set as:
> >SELECT Categories.[Category ID], Categories.Category FROM Categories;
> >
> >Where the categories table has category id and category name.
> >
> >I don’t have problems with what is listed in the combo boxes but I just
> >can’t get the product from combo box two to show up based on the category in
> >comb box one on the main form.
> >
> >
> >> >> Karl wrote:
> >> >> > I have two combo boxes. On the main form (Form 1) is a Category combo box.
> >> >> >On the subform (Form 2) is a Product combo box. Using a where query I have
> >> >> >set it up so the Product 2 combo box is based on the category combo box. This
> >> >> >works fine for the first choice. I.e. the first time i open the form and
> >> >> >choose a category the appropriate products appear in the combo box but when i
> >> >> >choose a different categories the products combo box doesn't update. I've got
> >> >> >it to work when both combo boxes are the main form using this AfterUpdate
> >> >> >event:
> >> >> >
> >> >> >Private Sub CategoryAfterUpdate()
> >> >> >Me.Product = Null
> >> >> >Me.Product.Requery
> >> >> >Me.Product = Me.Product.ItemData(0)
> >> >> >End Sub
> >> >> >
> >> >> >However I can't figure out how to adjust this event too look for product in
> >> >> >the subform (Form2) (instead of on the main form).
> >> >> >
> >> >> >At this time both of these combo boxes are unbound. However it would be
> >> >> >better for me if they were bound (i.e Bind Categories to the Categories table
> >> >> >(which contains Category ID and Category Name and Bind Products to the
> >> >> >Products table (which contains product id, product name and category ID). Is
> >> >> >it possible to have these combo boxes bound and still use this method for
> >> >> >looking up products based on the categories.
> >> >>
> >> >>
> >> >"Marshall Barton" wrote:
> >> >> The corresponding code for main form to subform is:
> >> >>
> >> >> Private Sub CategoryAfterUpdate()
> >> >> With Me.subformcontrol.Form
> >> >> .Product = Null
> >> >> .Product.Requery
> >> >> .Product = .Product.ItemData(0)
> >> >> End With
> >> >> End Sub
> >> >>
> >> >> You will also need to do the Requery in the main form's
> >> >> Current event to keep the Category and products synchronized
> >> >> when the main form navigates from one rcord to another.
> >> >>
> >> >> Private Sub Form_Current()
> >> >> Me.subformcontrol.Form.Product.Requery
> >> >> End Sub
> >> >>
> >> >> Bound or unbound makes a big difference in that your code is
> >> >> dirtying the subform's current record, which something that
> >> >> has important consequeces. OTOH, if the subform can have
> >> >> more than one record, you should invalidate the product
> >> >> field in every record when the Category is changed.
> >> >>
> >> >> I suggest that you get rid of the two assignment statements
> >> >> and, for now, only use the Requery. Once you get that part
> >> >> to work correctly, then think long and hard about what you
> >> >> need to happen when the Category is changed and the child
> >> >> table now contains records with the wrong product. I
> >> >> suspect that more than one field in the subform records will
> >> >> become invalid.
> >> >>
> >> >
> >> Karl wrote:
> >> >Thanks for helping. I am new to Access and Programming language so this
> >> >problem has been driving me nuts. Unfortunately your solution did not quite
> >> >work properly. I got an error message for both the AfterUpdate and OnCurrent
> >> >that said:
> >> >
> >> >The expression On Current you entered as the event property setting produced
> >> >the following error: Invalid Outside procedure.
> >> >* The expression may not result in the name of a macro, the name of a
> >> >user-defined function, or [event procedure]
> >> >* there may have been an error evaluating the function event or macro.
> >> >
> >> >I put your code in the AfterUpdate of the main form (Form1) and current
> >> >requrry in main form as well. One question i have too is that In your code
> >> >you wrote Form does that refer to the main form or my subform (Form2). I
> >> >tried it both ways and niether worked.
> >> >
> >> >I am also a little unsure about what you were talking about in the bound
> >> >explanation as I am unfamilar with some of the programing lingo I.e dirtying&
> >> >invalidate.
> >>
> >>
> >"Marshall Barton" wrote:
> >> The code goes in the appropriate event **procedure**, not
> >> in an event **property**.
> >>
> >> Select [Event Procedure] for the event property. Then
> >> click on the builder button in the right margin to bring up
> >> the event procedure and add the requery statement between
> >> the Sub and End Sub lines.
>

Marshall,

Sorry I realized I didn't quite explain my new problem very clearly in the
previous post. I clean it up a bit here so hopefully it will be easier to
understand. Thanks again.

Now that the combo boxes work correctly I want to add another subform
(form3) to the product subform (form2) that allows me to enter information
about the product.

So what I tried to do is change the product combo box from unbound to bound
using the product table as the record source for subform 2 and the control
source for the product combo box as product name (from the product table).

I then added a text box that shows the product id associated with the
product name chosen in the combo box. My plan was to then use this product id
to add another subform (form 3) in form 2, which would link product id as the
master link field in form 2 to the child link field in form 3.

However the combo box appears to continue to work but it won't automatically
update the associated product id. Instead it changes the subcategory name in
the table to a number.

Any ideas? Thanks again for your help. It is greatly appreciated.

Note: I left the category combo box in the main form (form 1) unbound.

Thanks again,
Karl

"Karl" wrote:

> Hi Marshall,
>
> Thanks alot. You were right there was some junk in the Event Procedure. I
> just had not noticed it until now when I scrolled up. Such a simple problem.
> It works now. Thanks a lot for solving it. Unfortunatetly I've already
> encountered my next problem. Now that the combo boxes work correctely I want
> to add another subform to the product product subform (form2) that allows me
> to enter information about the product. So what I tried to do is change the
> product combo form from unbound to bound using the product table as record
> source for the table and the control source for the product combo box as
> product name (from the product table). I then wanted to add a text box that
> shows the product id associated with the product name chosen in the combo
> box. I would then use this product id to add another subform (form 3) in
> form 2 which would link product id as the master link field in form 2 to the
> child link field in form 3. However the combo box appears to continue to work
> but it won't automatically update the associated product id. Instead it
> changes the subcategory name in the table to a number. Any ideas? Thanks
> again for your help. It is greatly appreciated.
>
> Karl
>
> "Marshall Barton" wrote:
>
> > That message indicates that you still have something weird
> > in the the event **properties**. Check to make sure that
> > that the event properties contain only the text:
> > [Event Procedure]
> >
> > If you have any event properties with something other than
> > that in them, you should delete whatever junk is in them.
> >
> > As far as I can see, the combo boxes are set up corrrectly.
> > It seems to be just a matter of using the event properties
> > to properly connect the events to their event procedures.
> > --
> > Marsh
> > MVP [MS Access]
> >
> >
> > Karl wrote:
> > >Unfortunately I was unable to get it to work still. I have no idea what I’ve
> > >done wrong. In my main form (Form1). I got the following event procedure
> > >codes in the after update and On current fields: (note my subform is named
> > >Form2 with the combo box in it named Product, the combo box in Form 1 is
> > >named Category)
> > >
> > >Private Sub Category_AfterUpdate()
> > > Me.Form2.Form.Product = Null
> > > Me.Form2.Form.Product.Requery
> > > Me.Form2.Form.Product = Me.Form2.Form.Product.ItemData(0)
> > >End Sub
> > >
> > >Private Sub Form_Current()
> > >Me.Form2.Form.Product.Requery
> > >End Sub
> > >
> > >I get the following error messages when I enter the form view.
> > >
> > >The expression AfterUpdate you entered as the event property setting
> > >produced the following error: Invalid outside procedure.
> > >*The expression may not result in the name of a macro, the name of a user
> > >defined function, or [Event Procedure]
> > >*There may have been an error evaluating the function, event or macro.
> > >
> > >
> > >The expression on current you entered as the event property setting produced
> > >the following error: Invalid outside procedure.
> > >*The expression may not result in the name of a macro, the name of a user
> > >defined function, or [Event Procedure]
> > >*There may have been an error evaluating the function, event or macro.
> > >
> > >
> > >Here is some additional information about my combo boxes in case this has
> > >something to do with why its not working properly:
> > >
> > >Both Combo boxes are unbound and in the product combo box in the subform I
> > >have the row source set as: SELECT [Sub Categories Table].[Sub Category ID],
> > >[Sub Categories Table].[Category ID], [Sub Categories Table].[Sub Category]
> > >FROM [Sub Categories Table] WHERE ((([Sub Categories Table].[Category
> > >ID])=Forms!Form1!Category));
> > >
> > >Note my subcategories table is where the products are found. The subcategory
> > >table has subcategory id (product id), category id, and subcategory name
> > >(product name).
> > >
> > >The product combo box in the subformhas no event procedures in it.
> > >
> > >In the category combo box in the main form I have the row source set as:
> > >SELECT Categories.[Category ID], Categories.Category FROM Categories;
> > >
> > >Where the categories table has category id and category name.
> > >
> > >I don’t have problems with what is listed in the combo boxes but I just
> > >can’t get the product from combo box two to show up based on the category in
> > >comb box one on the main form.
> > >
> > >
> > >> >> Karl wrote:
> > >> >> > I have two combo boxes. On the main form (Form 1) is a Category combo box.
> > >> >> >On the subform (Form 2) is a Product combo box. Using a where query I have
> > >> >> >set it up so the Product 2 combo box is based on the category combo box. This
> > >> >> >works fine for the first choice. I.e. the first time i open the form and
> > >> >> >choose a category the appropriate products appear in the combo box but when i
> > >> >> >choose a different categories the products combo box doesn't update. I've got
> > >> >> >it to work when both combo boxes are the main form using this AfterUpdate
> > >> >> >event:
> > >> >> >
> > >> >> >Private Sub CategoryAfterUpdate()
> > >> >> >Me.Product = Null
> > >> >> >Me.Product.Requery
> > >> >> >Me.Product = Me.Product.ItemData(0)
> > >> >> >End Sub
> > >> >> >
> > >> >> >However I can't figure out how to adjust this event too look for product in
> > >> >> >the subform (Form2) (instead of on the main form).
> > >> >> >
> > >> >> >At this time both of these combo boxes are unbound. However it would be
> > >> >> >better for me if they were bound (i.e Bind Categories to the Categories table
> > >> >> >(which contains Category ID and Category Name and Bind Products to the
> > >> >> >Products table (which contains product id, product name and category ID). Is
> > >> >> >it possible to have these combo boxes bound and still use this method for
> > >> >> >looking up products based on the categories.
> > >> >>
> > >> >>
> > >> >"Marshall Barton" wrote:
> > >> >> The corresponding code for main form to subform is:
> > >> >>
> > >> >> Private Sub CategoryAfterUpdate()
> > >> >> With Me.subformcontrol.Form
> > >> >> .Product = Null
> > >> >> .Product.Requery
> > >> >> .Product = .Product.ItemData(0)
> > >> >> End With
> > >> >> End Sub
> > >> >>
> > >> >> You will also need to do the Requery in the main form's
> > >> >> Current event to keep the Category and products synchronized
> > >> >> when the main form navigates from one rcord to another.
> > >> >>
> > >> >> Private Sub Form_Current()
> > >> >> Me.subformcontrol.Form.Product.Requery
> > >> >> End Sub
> > >> >>
> > >> >> Bound or unbound makes a big difference in that your code is
> > >> >> dirtying the subform's current record, which something that
> > >> >> has important consequeces. OTOH, if the subform can have
> > >> >> more than one record, you should invalidate the product
> > >> >> field in every record when the Category is changed.
> > >> >>
> > >> >> I suggest that you get rid of the two assignment statements
> > >> >> and, for now, only use the Requery. Once you get that part
> > >> >> to work correctly, then think long and hard about what you
> > >> >> need to happen when the Category is changed and the child
> > >> >> table now contains records with the wrong product. I
> > >> >> suspect that more than one field in the subform records will
> > >> >> become invalid.
> > >> >>
> > >> >
> > >> Karl wrote:
> > >> >Thanks for helping. I am new to Access and Programming language so this
> > >> >problem has been driving me nuts. Unfortunately your solution did not quite
> > >> >work properly. I got an error message for both the AfterUpdate and OnCurrent
> > >> >that said:
> > >> >
> > >> >The expression On Current you entered as the event property setting produced
> > >> >the following error: Invalid Outside procedure.
> > >> >* The expression may not result in the name of a macro, the name of a
> > >> >user-defined function, or [event procedure]
> > >> >* there may have been an error evaluating the function event or macro.
> > >> >
> > >> >I put your code in the AfterUpdate of the main form (Form1) and current
> > >> >requrry in main form as well. One question i have too is that In your code
> > >> >you wrote Form does that refer to the main form or my subform (Form2). I
> > >> >tried it both ways and niether worked.
> > >> >
> > >> >I am also a little unsure about what you were talking about in the bound
> > >> >explanation as I am unfamilar with some of the programing lingo I.e dirtying&
> > >> >invalidate.
> > >>
> > >>
> > >"Marshall Barton" wrote:
> > >> The code goes in the appropriate event **procedure**, not
> > >> in an event **property**.
> > >>
> > >> Select [Event Procedure] for the event property. Then
> > >> click on the builder button in the right margin to bring up
> > >> the event procedure and add the requery statement between
> > >> the Sub and End Sub lines.
> >

This kind of issue is tied up in the specification of the
combo box properties. Please post the product combo box's
RowSource query. If you are using the product table for the
RowSource, then post all the fields in the same order they
are listed in the table's design view.

The combo box's BoundColumn property is absolutely critical
here. It must be the number of the field that contains the
value you want to save in the form's record source table
(normally the PK field, **not** a name field).

The ColumnCount property **must** be set to the number of
fields in the RowSource table/query.

If either BoundColumn or ColumnCount are set to the wrong
thing, all kinds of things can go wrong.

Be careful of the ColumnWidths property too. By setting the
column width of the BoundColumn to zero, you can hide the
bound value while displaying a different column (first one
with non-zero column width). It is typical to bind a combo
box to an ID field but display a name field (sounds like
what you're getting). This way, you do not need a separate
text box to coordinate with the subform. The subform
control's LinkMasterFields property can be set to the combo
box.
--
Marsh
MVP [MS Access]


Karl wrote:
>Sorry I realized I didn't quite explain my new problem very clearly in the
>previous post. I clean it up a bit here so hopefully it will be easier to
>understand. Thanks again.
>
>Now that the combo boxes work correctly I want to add another subform
>(form3) to the product subform (form2) that allows me to enter information
>about the product.
>
>So what I tried to do is change the product combo box from unbound to bound
>using the product table as the record source for subform 2 and the control
>source for the product combo box as product name (from the product table).
>
>I then added a text box that shows the product id associated with the
>product name chosen in the combo box. My plan was to then use this product id
>to add another subform (form 3) in form 2, which would link product id as the
>master link field in form 2 to the child link field in form 3.
>
>However the combo box appears to continue to work but it won't automatically
>update the associated product id. Instead it changes the subcategory name in
>the table to a number.
>
>
>"Karl" wrote:
>> Thanks alot. You were right there was some junk in the Event Procedure. I
>> just had not noticed it until now when I scrolled up. Such a simple problem.
>> It works now. Thanks a lot for solving it. Unfortunatetly I've already
>> encountered my next problem. Now that the combo boxes work correctely I want
>> to add another subform to the product product subform (form2) that allows me
>> to enter information about the product. So what I tried to do is change the
>> product combo form from unbound to bound using the product table as record
>> source for the table and the control source for the product combo box as
>> product name (from the product table). I then wanted to add a text box that
>> shows the product id associated with the product name chosen in the combo
>> box. I would then use this product id to add another subform (form 3) in
>> form 2 which would link product id as the master link field in form 2 to the
>> child link field in form 3. However the combo box appears to continue to work
>> but it won't automatically update the associated product id. Instead it
>> changes the subcategory name in the table to a number. Any ideas? Thanks
>> again for your help. It is greatly appreciated.