Combo Box After Update

G

Guest

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,
 
W

Wayne Morgan

The syntax to refer to a control on a subform from the main form is:

Me.NameOfSubformControl.Form.NameOfControlOnSubform

The Subform Control is where you set the Master/Child link properties. The
name of this control may or may not be the same as the form it holds.
 
G

Guest

Thanks for your response. However I couldn't get it to work. My main form is
named Form 1 and my subform control as you described is Form2. So what I did
in the AfterUpdate field of the Category combo box (in the main form) was:

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

End Sub

Is this correct? As I got an error message when I tried to use the form. The
error said "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.

What should I do next? I'm getting frustrated as I'm very new to access and
programming codes. I really appreciate your time and effort in helping me
with this problem.

Karl
 
W

Wayne Morgan

No, you need one more item (the word Form) in the line,

Me.Form2.Form.Product = Null
Me.Form2.Form.Product.Requery
Me.Form2.Form.Product = Me.Form2.Form.Product.ItemData(0)
 
G

Guest

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

Wayne Morgan said:
No, you need one more item (the word Form) in the line,

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

--
Wayne Morgan
MS Access MVP


Karl said:
Thanks for your response. However I couldn't get it to work. My main form
is
named Form 1 and my subform control as you described is Form2. So what I
did
in the AfterUpdate field of the Category combo box (in the main form) was:

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

End Sub

Is this correct? As I got an error message when I tried to use the form.
The
error said "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.

What should I do next? I'm getting frustrated as I'm very new to access
and
programming codes. I really appreciate your time and effort in helping me
with this problem.
Click to expand...
Click to expand...
 
G

Guest

Hello again

I was able to figure out the problem there was some other junk typed in the
Event Procedure. I just had not noticed it until now when I scrolled up. I
delete it and it works fine now. Such a simple problem. Thanks a lot for your
help.

Unfortunately I've already encountered my next problem. 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.

Karl

Karl said:
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

Wayne Morgan said:
No, you need one more item (the word Form) in the line,

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

--
Wayne Morgan
MS Access MVP


Karl said:
Thanks for your response. However I couldn't get it to work. My main form
is
named Form 1 and my subform control as you described is Form2. So what I
did
in the AfterUpdate field of the Category combo box (in the main form) was:

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

End Sub

Is this correct? As I got an error message when I tried to use the form.
The
error said "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.

What should I do next? I'm getting frustrated as I'm very new to access
and
programming codes. I really appreciate your time and effort in helping me
with this problem.
Click to expand...
Click to expand...
Click to expand...
 
W

Wayne Morgan

Some things to check,

1) How many columns are you now showing in the Column Count property for the
combo box you've bound?

2) Which column is the Bound Column?

3) In the Row Source, are the fields listed in the order necessary to line
up the ID field with the Bound Column (i.e. the first field listed is the
first columns, the second field listed is the second column, etc)? While the
Column property of the combo box is zero based, the Bound Column property is
one based.
 
G

Guest

Good day Wayne,

I checked the column count and it is 3.
The bound column is 1.

The control source is product name

The row source is
SELECT [Product Table].[Product ID], [Product Table].[Category ID], [Product
Table].[Product Name] FROM [Product Table] WHERE ((([Product Table].[Category
ID])=Forms!Form1!Category));

In the Product table the fields are listed as:
Product ID :(primary key): autonumber
Category ID : number
Product Name : Text

The record source for form 3 is the products table:
 
W

Wayne Morgan

If the Control Source of the combo box is Product Name then I would assume
that you want the name of the product stored in the form's table, not the ID
of the product, correct? However, the Bound Column is 1, which looking at
your Row Source query, the first field in it (i.e. the first column of the
combo box) is Product ID (SELECT [Product Table].[Product ID], ).

It would be normal to store the Product ID (i.e. make this the Control
Source) in the form's table and then link the form's table and Product Table
on this field in the Relationships window.

The form I'm speaking of here is the form that the combo box is located on.
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Update combo box in subform (After Update event) 10
AfterUpdate Event (Combo Box) 8
AfterUpdate Combo Boxes 1
Changing a combo box from unbound to bound! 5
Explanation needed of a VBA term 7
Access Control on subform / combo box requesting parameter 0
Combo Boxes - linking of 5
Populate a text box based on a combo box 1

Top