By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
424,486 Members | 2,061 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 424,486 IT Pros & Developers. It's quick & easy.

Problem with Subforms and Synchronized ComboBoxes

P: 3
In Access 2003 I have a form that has synchronized combo box, linked by criteria in their separate queries.

Example query criteria for combobox2:

IIF(IsNull([Forms]![Form1]![combobox1]), [combobox1data],[Forms]![Form1]![combobox1])

thus limiting the choices in combobox2 to records contain the infomation in combobox1.

This works perfectly, except for when I then place the form into another form as a subform. Then the query for combobox2 is not able to find Form1 with combobox1 and prompts me for a parameter. How do I enable these sychronized comboboxes to work when they are placed on a subform? Any help would be much appreciated.
Nov 15 '06 #1
Share this Question
Share on Google+
9 Replies


MMcCarthy
Expert Mod 10K+
P: 14,534
Syntax for subforms.

IIF(IsNull([Forms]![MainFormName]![SubformName].Form![combobox1]), [combobox1data([Forms]![MainFormName]![SubformName].Form![combobox1])

Fill in the bold with your main form and subform names

Note: The subformname is not always the name of the subform.

Open the main form in design view and open the properties. Click on the frame around the subform and under the other tab check the value in the Name property. This is the subformname.
Nov 16 '06 #2

NeoPa
Expert Mod 15k+
P: 31,186
Alternatively try :
Expand|Select|Wrap|Line Numbers
  1. Nz([combobox1]), [combobox1data])
The information about subforms is a minefield and well worth knowing.
However, I don't (think), it's necessary here.
Please let me know if this works for you.
Nov 16 '06 #3

MMcCarthy
Expert Mod 10K+
P: 14,534
Alternatively try :
Expand|Select|Wrap|Line Numbers
  1. Nz([combobox1]), [combobox1data])
The information about subforms is a minefield and well worth knowing.
However, I don't (think), it's necessary here.
Please let me know if this works for you.
Good point Adrian.

Since the original post included a form reference, I just assumed there was some reason for it and didn't think any more about it.

Mary
Nov 16 '06 #4

P: 3
Thank you all for your info. I ended up changing the criteria a little bit so that the Nz() function would not apply, but its good to have the info.

My problem now is that my subform is a continuous form and when I requery the dependent combobox, it resets the dependant comboboxe of all the records in the subform, not just the record I am currently working with. Here's how I am doing it currently:

The two combo names are:
Category_Select
Description_Select

The main form is
Ticket Form

The Subform is
Transaction Subform

Expand|Select|Wrap|Line Numbers
  1. Private Sub Category_Select_AfterUpdate()
  2.  
  3.     Me!Description_Select.Requery
  4.     Me!Description_Select = Me!Description_Select.ItemData(0)
  5.  
  6.     Me!SubDescription_Select.Requery
  7.     Me!SubDescription_Select = Me!SubDescription_Select.ItemData(0)
  8.  
  9. End Sub
Any thoughts?
Nov 16 '06 #5

MMcCarthy
Expert Mod 10K+
P: 14,534
Thank you all for your info. I ended up changing the criteria a little bit so that the Nz() function would not apply, but its good to have the info.

My problem now is that my subform is a continuous form and when I requery the dependent combobox, it resets the dependant comboboxe of all the records in the subform, not just the record I am currently working with. Here's how I am doing it currently:

The two combo names are:
Category_Select
Description_Select

The main form is
Ticket Form

The Subform is
Transaction Subform

Expand|Select|Wrap|Line Numbers
  1. Private Sub Category_Select_AfterUpdate()
  2.  
  3. Me!Description_Select.Requery
  4. Me!Description_Select = Me!Description_Select.ItemData(0)
  5.  
  6. Me!SubDescription_Select.Requery
  7. Me!SubDescription_Select = Me!SubDescription_Select.ItemData(0)
  8.  
  9. End Sub
Any thoughts?
Sorry I don't understand what you mean.
Nov 16 '06 #6

NeoPa
Expert Mod 15k+
P: 31,186
I think the problem referred to here is the one where setting a control on a 'continuous' form seems to set it for all records rather than simply the one desired.
I'm sure solutions to this have been posted before but I don't remember what they were :(.
Nov 17 '06 #7

MMcCarthy
Expert Mod 10K+
P: 14,534
cellomt,

Working on continuous forms is a bit of a minefield when you are trying to manipulate data. There are some things that you just can't do.

Can you explain why you are trying to make this change?
Why it can only happen on the current record?
What values exactly are you trying to change?

The more information you give us the more likely we are to be able to help you.

Mary
Nov 17 '06 #8

P: 3
What I am working with is an orderform for warehouse inventory that involves a wide range of products. In order to make the searching and selecting of the products to place on the order, I have broken down the products into category, sub-category and description fields. The actual problem with this system of inventory control is that the warehouse refuses to put control numbers on their products and when writing up an order, the products can only be selected by the description of the product. You can see how with such a large product range this presents some problems. My job is to make this digitized order form usable without distinct usuable product numbers.

When the employee opens the order form, he enters the general information for the order and then tabs into the transaction subform to select the products to place on the order. To prevent having to scroll through 2000+ items, I want him to be able to first select the product category which will narrow down the second combobox. He will then select the product sub category, which will narrow down the third combobox. He is then able to select the product he is looking for from a relatively few amount of products (arranged by product description). He will then move onto the next line for the next product being ordered.

I have a separate table for transactions, and this is what the subform is based on. The problem is that when the employee goes to the next line to enter a new product for the order, his choices in the comboboxes change the choices he has already made for the previous line, being that the transaction subform is a continuous form. If he contiues in this fashion, the actual table table for all the lines is accurate; it will not, however, display as such on the order form.
Nov 17 '06 #9

MMcCarthy
Expert Mod 10K+
P: 14,534
It sounds like you just need to refresh the transaction subform. Put the code behind a command button (cmdRefresh) to give the user control over when they would like to reset the combo boxes.

Expand|Select|Wrap|Line Numbers
  1.  
  2. Private Sub cmdRefresh_Click()
  3.  
  4.   Me.Refresh ' as long as the button is on the transaction subform this will refresh any unbound combobox or listbox controls.
  5.  
  6. End Sub
  7.  
If you want more control:

For example, if I had three combo boxes as follows:

cboCategory (List of all categories)
cboSubCategory (List of all subCategories based on prev selection)
cboProductList (List of all products in the prev selected sub category)

The following code should give you the kind of conrol you want:

Expand|Select|Wrap|Line Numbers
  1.  
  2. Private Sub cboCategory_AfterUpdate()
  3.  
  4.   Me.cboProductList.RowSource = ""
  5.   Me.cboSubCategory.RowSource = "SELECT SubCategoryID, SubCategory FROM TableName WHERE CategoryID=" & Me.cboCategory
  6.   Me.cboProductList.Requery
  7.   Me.cboSubCategory.Requery  
  8.  
  9. End Sub
  10.  
  11. Private Sub cboSubCategory_AfterUpdate()
  12.  
  13.   Me.cboProductList.RowSource = "SELECT ProductID, Product FROM TableName WHERE SubCategoryID=" & Me.cboSubCategory
  14.   Me.cboProductList.Requery
  15.  
  16. End Sub
  17.  
  18.  

What I am working with is an orderform for warehouse inventory that involves a wide range of products. In order to make the searching and selecting of the products to place on the order, I have broken down the products into category, sub-category and description fields. The actual problem with this system of inventory control is that the warehouse refuses to put control numbers on their products and when writing up an order, the products can only be selected by the description of the product. You can see how with such a large product range this presents some problems. My job is to make this digitized order form usable without distinct usuable product numbers.

When the employee opens the order form, he enters the general information for the order and then tabs into the transaction subform to select the products to place on the order. To prevent having to scroll through 2000+ items, I want him to be able to first select the product category which will narrow down the second combobox. He will then select the product sub category, which will narrow down the third combobox. He is then able to select the product he is looking for from a relatively few amount of products (arranged by product description). He will then move onto the next line for the next product being ordered.

I have a separate table for transactions, and this is what the subform is based on. The problem is that when the employee goes to the next line to enter a new product for the order, his choices in the comboboxes change the choices he has already made for the previous line, being that the transaction subform is a continuous form. If he contiues in this fashion, the actual table table for all the lines is accurate; it will not, however, display as such on the order form.
Nov 17 '06 #10

Post your reply

Sign in to post your reply or Sign up for a free account.