Hello again. Thanks for getting back to me. I have 3 tables that are linked.
Category Table:
Category ID (primary key) -autonumber
Category Name
Products Table:
Product ID (primary key) - autonumber
Category ID (linked to category table)
Product Name
Components Table:
Component ID (primary key) - autonumber
Product ID (linked to product table)
Equipment Name
Make
Model
I have created a form (Form1) that has an unbound combo box that lists all
the names of categories for all the products I have. I have a subform (Form
2) with a unbound combo box for products. For this combo box all the products
that are associated with the chosen category are shown.
I would like to create a subform (Form 3) in Form 2 that shows the Component
information of each product selected.
My original plan was to simply link these by product id as a master and
child field, however as the product and category combo boxes are unbound I
don’t think this is possible.
I tried to make the product combo box a bound field with the products table
the record source and product name the control source (I also tried to make
product id the control source and it caused my combo box to quit working
properly).
I then added a text box with the product id as the control source that would
allow me to use as a master field for the link to the child field (product id
on form 3). However the text box with the product id doesn’t update to the
correct product id
number for the product name chosen in the products combo box. Note: I left
the category box unbound.
I was wondering why this isn’t working or if there is a better method for
connecting subform 2 to subform 3.
Thank you again,
Karl
:
Hi Karl,
You'r welcome
How are your tables related that hold the data for your main
form and subform1 and subform2?
Warm Regards,
Crystal
MVP Microsoft Access
remote programming and training
strive4peace2006 at yahoo.com
*
Have an awesome day
Karl wrote:
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.
:
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 used 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
:
Hi Karl,
1. If your subform shows just one record at a time, it can
have a subform on it
2. If you show more than one record at a time on the
subform, it cannot have a subform on it
Crystal
MVP Microsoft Access
remote programming and training
strive4peace2006 at yahoo.com
*
Have an awesome day
Karl wrote:
Hi,
Thank you for your response. I haven't got a chance to try it yet, but i do
have another question. How do i know if my subform is a continous form. I'm
not sure what you mean by a calculated form? Do you mean link each form by a
seperate master/child field to the main form?
:
Hi Karl,
if the Product combo is on your subform, you will need to
reference it differently
Private Sub CategoryAfterUpdate()
Me.subform_controlname.form.Product = Null
Me.subform_controlname.form.Product.Requery
'are you sure you want to do this next statement?
Me.subform_controlname.form.Product =
Me.subform_controlname.form.Product.ItemData(0)
End Sub
If Category is a bound field, you may need to put this code
on the form OnCurrent event as well.
"Is it possible to have these combo boxes bound and still
use this method for looking up products based on the
categories."
yes
and yes to your last question as well. If, however,
Products is a continuous form, you will not be able to embed
another subform onto it, but you can attach another subform
to the main form and use a calculated control for
LinkMasterfields.
Warm Regards,
Crystal
MVP Microsoft Access
remote programming and training
strive4peace2006 at yahoo.com
*
Have an awesome day
Karl wrote:
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
