If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
Update combo box in subform (After Update event)
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, |
#2
|
|||
|
|||
Update combo box in subform (After Update event)
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 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, |
#3
|
|||
|
|||
Update combo box in subform (After Update event)
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? "strive4peace" wrote: 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 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, |
#4
|
|||
|
|||
Update combo box in subform (After Update event)
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? "strive4peace" wrote: 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 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, |
#5
|
|||
|
|||
Update combo box in subform (After Update event)
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 "strive4peace" wrote: 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? "strive4peace" wrote: 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 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, |
#6
|
|||
|
|||
Update combo box in subform (After Update event)
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" wrote: 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 "strive4peace" wrote: 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? "strive4peace" wrote: 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 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, |
#7
|
|||
|
|||
Update combo box in subform (After Update event)
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. "Karl" wrote: 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 "strive4peace" wrote: 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? "strive4peace" wrote: 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 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, |
#8
|
|||
|
|||
Update combo box in subform (After Update event)
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 "strive4peace" wrote: 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. "Karl" wrote: 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 "strive4peace" wrote: 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? "strive4peace" wrote: 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 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, |
#9
|
|||
|
|||
Update combo box in subform (After Update event)
Hi Karl,
"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 can see why category would be unbound ... but not product -- it should be bound. yes, you can use unbound controls in LinkMasterFields ... just type in the control name(s) Is the Components subform ON the Product subform, or is it on the main form directly? If it is on the main form, use this in the LinkMasterFields for form3: Name -- Product_ID ControlSource -- = Subform2_controlname.form.Product_ID_controlname and this is truly the best way to do your linking... Warm Regards, Crystal MVP Microsoft Access remote programming and training strive4peace2006 at yahoo.com * Have an awesome day Karl wrote: 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 "strive4peace" wrote: 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. "Karl" wrote: 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 "strive4peace" wrote: 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? "strive4peace" wrote: 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.Reque ry 'are you sure you want to do this next statement? Me.subform_controlname.form.Product = Me.subform_controlname.form.Product.ItemDat a(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 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, |
#10
|
|||
|
|||
Update combo box in subform (After Update event)
Hi, I was able to figure it out thank you very very much for your help.
"strive4peace" wrote: Hi Karl, "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 can see why category would be unbound ... but not product -- it should be bound. yes, you can use unbound controls in LinkMasterFields ... just type in the control name(s) Is the Components subform ON the Product subform, or is it on the main form directly? If it is on the main form, use this in the LinkMasterFields for form3: Name -- Product_ID ControlSource -- = Subform2_controlname.form.Product_ID_controlname and this is truly the best way to do your linking... Warm Regards, Crystal MVP Microsoft Access remote programming and training strive4peace2006 at yahoo.com * Have an awesome day Karl wrote: 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 "strive4peace" wrote: 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. "Karl" wrote: 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 "strive4peace" wrote: 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? "strive4peace" wrote: 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.Reque ry 'are you sure you want to do this next statement? Me.subform_controlname.form.Product = Me.subform_controlname.form.Product.ItemDat a(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 |
|
Thread Tools | |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Thread Starter | Forum | Replies | Last Post |
Combo on Main form to filter combo on subform | Anne | Using Forms | 0 | March 21st, 2006 06:40 PM |
update combo box based on subform | shanebgross | Using Forms | 0 | October 3rd, 2005 06:49 PM |
Update subform from combo box | Mike_can | Using Forms | 4 | August 30th, 2005 04:30 PM |
Design questions | Bruce | Database Design | 15 | June 19th, 2004 12:12 AM |
Requery Combobox | MJ | Running & Setting Up Queries | 7 | May 25th, 2004 11:01 AM |