Filtered Combo Box on the detailed section of a subform

[kcdoell]

Hello:

I have a form (Default view =single form) with a subform (Default view =continuous forms) embedded into it. In the form I have three controls that display the Division, Working Region & Credit Region. The subform displays the data/records. The record source for both my form and subform is driven by a query, which is the way I filter the records that the end user will see via another form I created.

Everything is working fine but I can not seem to figure something out that I have never done before.

The issue is that the end user can add a new record via my subform. My challenge is that one of the fields that is displayed is called “Product” There are a lot of products to choose from on this drop down list but not as many if the list was filtered by Working Region. The layout of my product table is as follows:

Product:
tblProduct
ProductID = Autonumber
ProductsName = Text
WrkRegID = Number (This is for my one to many relationship; a working region can have many different Products)

Is there a way to filter the list of my product field on my subform for new records?

I was trying to experiment with doing this and can not figure it out. What I did get to work was on the header of my subform I dropped in a combo box and then on the load event of that form wrote the following code:

Private Sub Form_Load()
'When the Working Region has been displayed on the form, the appropriate Product list
'will display in the drop down list of CboProduct
 
  With Me![cboProduct]
    If IsNull(Me!txtWrkReg) Then
      .RowSource = ""
    Else
      .RowSource = "SELECT [ProductsName] FROM tblProduct WHERE [WrkRegID]=" & _
                    DLookup("[WrkRegID]", "tblWrkRegion", "[WrkRegionName] = '" & Me![txtWrkReg] & "'")
    End If
    Call .Requery
  End With
 
End Sub
 

As mentioned this worked. The problem is that the minute I embedded that control “cboProduct” in the detail portion of my subform I do not get any errors, the list will be appropriately populated but if let’s say three records are displaying, the minute I make a selection from cboProduct all of the records will be change to that selection. I only want it to change new records not the old ones.

Does anybody have any idea on how this can be done??

Thanks,

Keith.

[Stewart Ross]

Hi Keith. From what you say it would appear that the combo is not bound to one of the fields in your subform, as unless you are calling an after-update event to update records when you make a selection from the combo it cannot of itself change multiple records - but it can give the appearance that it is.

A subform actually repeats the same set of controls on each row shown on the form, and if one of those is unbound you see the same value repeated on every row. This makes it look like you have changed all records - but as mentioned unless there is an After Update event in use it has not actually done anything at all to your records.

An unbound control is one whose controlsource is blank - one that is not bound to a specific field in your underlying table. If you are embedding an unbound combo box control in the detail section of your subform it's in the wrong place.

Ask yourself what you want the combo to do on your subform, and if that is to provide a list for one of the table values in your subform you will need to bind it to the appropriate field.

By the way, the form load event is fired only when you first open the form - is this the right timing for the conditional recordsource loading in your event code?

-Stewart

[kcdoell]

Hello:

Let me answer the last question first:

"By the way, the form load event is fired only when you first open the form - is this the right timing for the conditional recordsource loading in your event code?"

Once the end user in on the form the "working region" parameter can not be changed within it, so there is no problem with the load event only firing once.

On another note, I am not yet an advance Access person so please be patient if I don't entirely understand your response.

Let me see if I can better explain the scenario:

Let's say my subform is displaying 3 records that only have 3 fields:

RecID      Product         ImageDate         Company
1            ASL           12/12/2005      Richardson Inc.
2            BAA           11/10/2005      Peterson Corp.
3            DWW           08/21/2005      Harolds Inc.

Currently in my “Product” field it is displaying the full gamut of Products (when you click on the combo box) because its row source is pointing to my Product table (tblProduct). So if I add a new record, once I key into the product field my choices will be the full list. What I am trying to do is have it so that the choices is limited by the working region. In essence, I am trying to filter the list by working region so the end user can only select those now limited choices.

How would one do that? Do you have an idea I could try?

Thanks,

Keith.

[Stewart Ross]

Hi Keith. You can use a combo for selection of the product field, a combo which is bound to the value of the product field itself. To do this you delete your existing product field on the subform and replace it with a combo, using the combowizard to build the basic query for the recordsource from your product table. This can be tweaked with a WHERE clause similar to the one in your form load event to filter for the region. The DLookup should not be necessary.

The SQL for this will look something like:

SELECT [Product], [ProductsName] FROM tblProduct WHERE [WrkRegionName] = '" & Me![txtWrkReg] & "'"

assuming I am interpreting your fields correctly.

The main difference between this and what you had before is that the rowsource is returning the product ID and the product name. It is the product ID which is the value that you store in the subform table, not the name of the product. The combo wizard will itself recommend that you hide the key value - showing the product name, but recording the product ID instead. This is done by setting the ID column width of the combo to 0 to hide the column.

-Stewart

[kcdoell]

I have been trying to get this to work all day......... My problem is that it is looking for the ID number but is only finding the name of the Working Region...

[Stewart Ross]

I have been trying to get this to work all day......... My problem is that it is looking for the ID number but is only finding the name of the Working Region...

Hi Keith. Is it the case that you do not have a product ID field in your table? If you don't it would explain why you are only returning the product name. You need to bind the combo to whatever is the key field of your product table; if this is the product name, then you wll only need the one column in your combo and you should just return the name and nothing else.

In relational table design there is always a primary key which uniquely identifies rows in a table. There is a good article on table design and normalisation in the HowTo section of the forum, at http://www.thescripts.com/forum/thread585228.html

-Stewart

[kcdoell]

Okay:

So what I did was go into the combo box of "Product" and put the row source as follows:

SELECT [ProductName] FROM tblProduct WHERE [WrkRegID] = '" & Me![txtWrkReg] & "'"

What I am getting is a syntax error message when I click on the drop down box. I believe my other problem is that txtWrkReg is displaying the name where my tblProduct is looking for the Working Region Name.

Could that be the reason I am having the syntax errors or is the code missing something?

Keith.

[kcdoell]

Hi Keith. Is it the case that you do not have a product ID field in your table? If you don't it would explain why you are only returning the product name. You need to bind the combo to whatever is the key field of your product table; if this is the product name, then you wll only need the one column in your combo and you should just return the name and nothing else.

In relational table design there is always a primary key which uniquely identifies rows in a table. There is a good article on table design and normalisation in the HowTo section of the forum, at http://www.thescripts.com/forum/thread585228.html

-Stewart

Below is my table layout:

Product:
tblProduct
ProductID = Autonumber
ProductsName = Text
WrkRegID = Number (This is for my one to many relationship; a working region can have many different Products)

As noted earlier it does have a unique ID number and it has a relationship with the working region ID (One to many).

Does that answer your question????

[kcdoell]

I just read the article. I am not an expert but I believe that my structure is normalized with my 1to1 and 1toMany relationships.

[Stewart Ross]

Below is my table layout:

Product:
tblProduct
ProductID = Autonumber
ProductsName = Text
WrkRegID = Number (This is for my one to many relationship; a working region can have many different Products)

As noted earlier it does have a unique ID number and it has a relationship with the working region ID (One to many).

Does that answer your question????

Hi Keith. Thank you for the clarification and your previous post. You do indeed have an ID field which is unique; and you are right about the region name/ number mismatch causing the drop-down error. We will need to restore the use of the Dlookup to get back the region number from the region name, although this should not have been necessary.

I'll post back shortly with revised SQL for your combo.

-Stewart

[kcdoell]

Below is my table layout:

Product:
tblProduct
ProductID = Autonumber
ProductsName = Text
WrkRegID = Number (This is for my one to many relationship; a working region can have many different Products)

As noted earlier it does have a unique ID number and it has a relationship with the working region ID (One to many).

Does that answer your question????

I have been doing some reading and the above WrkRegID is the foreign key of which I am trying to filter on. Since it is a number and my txtWrkReg on my form is the name, I believe that is where the problem is.

[Stewart Ross]

OK, the following SQL does return the numeric product ID and the product name for display in your combo. It does not use a Dlookup - it simply joins the wrkRegion table to the query instead.

The where clause refers to the combo box on your form. As I do not know the name of your form there is a placeholder name there which you will need to replace with the real name. Please note that because it depends on the actual value of the control you will not see anything returned by the combo query when the form is in design view - only when it is active.

You will then be able to bind the combo to the product ID field on your form, as the combo will be filtered to return only the values matching that region.

The SQL is

SELECT [ProductID], [ProductName] FROM tblProduct 
INNER JOIN tblWrkRegion ON 
tblwrkRegion.[wrkRegID] = tblProduct.[wrkRegID]
WHERE tblWrkRegion.[wrkRegionName] = forms![your form name]![txtWrkReg]
ORDER BY tblProduct.[ProductName];

I tested this by preparing two test tables with the same fields and names as your Product and Region tables, pupulating them with some dummy data, and trying out the query from the query editor after entering the raw SQL.

-Stewart

[kcdoell]

Stewart:

First thanks for hanging in there with me. I inputted the following:

SELECT [ProductID], [ProductName] FROM tblProduct 
INNER JOIN tblWrkRegion ON 
tblwrkRegion.[wrkRegID] = tblProduct.[wrkRegID]
WHERE tblWrkRegion.[wrkRegionName] = forms![Forecastform]![cboWrkReg]
ORDER BY tblProduct.[ProductName];
 

into my product combo box field, no errors but it is displaying the Product ID number and not the Product Name on the drop down list.

Any ideas?

[Stewart Ross]

Stewart:

First thanks for hanging in there with me. I inputted the following:

SELECT [ProductID], [ProductName] FROM tblProduct 
INNER JOIN tblWrkRegion ON 
tblwrkRegion.[wrkRegID] = tblProduct.[wrkRegID]
WHERE tblWrkRegion.[wrkRegionName] = forms![Forecastform]![cboWrkReg]
ORDER BY tblProduct.[ProductName];
 

into my product combo box field, no errors but it is displaying the Product ID number and not the Product Name on the drop down list.

Any ideas?

Hi Keith. I'm really glad you got the query in and it is working so far...

For the combo width issue check that the Column Count property in the combo is set to 2, and the Column Width property has two values in it like this; 1cm;5cm (these are just examples - the widths could also be in inches).

The other property that needs to match these is the List Width, which should be the sum of all the widths of the columns. for the example given, the list width would be 6cm.

To hide the ID column after you are showing both columns, you can set its width to 0, which would leave the width property showing something like 0cm; 5cm

-Stewart

[kcdoell]

Stewart:

I just want to thank you for helping me with this problem. As you know, I am still in learning mode with applying sql and vba amongst other things. Your thoughtful responses where over the top and I thank you for that. In the end, I got it to work.

Thanks to you!

Take care,

Keith.