Can you put a filter expression on a lookup?

[bdockery]

One to Many relationship: Manufactuerer to Model



Manufactuerer table has [ManufacturerID], [ManufacturerName]


Products Table has [ProductID], [ProductName], [ManufacturerID]


Order table has [OrderID], [ManufacturerID], [ProductID]


In an order form, I'm trying to filter the [ModelID] lookup based on the already selected [ManufacturerID] lookup. I'm looking for some help on what to use as a filter expression, and where to add it for the ModelID lookup. All guidance appreciated guys thanks.

[MMcCarthy]

In the After Update event of the ManufacturerID combobox (cboManufacturer) you can change the row source of the ModelID combobox (cboModel). This assumes that ManufacturerID is a foreign key in the Model table. It also assumes ManufacurerID is a number type.

Private Sub cboManufacturer_AfterUpdate()
 
  Me.cboModel.RowSource = "SELECT ModelID FROM Model " & _
                  "WHERE ManufacturerID=" & Me.cboManufacturer
  Me.cboModel.Requery
 
End Sub
 

Mary

[NeoPa]

This Tutorial (Example Filtering on a Form.) will give a full explanation of the subject.

[bdockery]

First off, thanks a ton for the help.

There's one weird thing I can't clear up, but I think I'm almost there. When I make a selection in the cboManufacturer lookup it does filter the ModelID lookup correctly. The only thing is, all the selections are INVISIBLE.

The reason I can tell this, is that if I select a manufacturer that has very few models, the ModelID lookup has very few, invisible, selections. Conversly if I select a manufacturer that has a ton of models, the ModelID lookup has a huge, invisible, list.

If I check the table that the controls are bound to, the form IS updating the records correctly, there I CAN see the values and they're NOT invisible.

So weird!

Here's my code:

Private Sub ManufacturerID_AfterUpdate()
    Me.ModelID.RowSource = "SELECT ID FROM" & _
    " ProductDetails WHERE ManufacturerID = " & Me.ManufacturerID & _
    " ORDER BY ModelName"
 
    Me.ModelID = Me.ModelID.ItemData(0)
End Sub

[NeoPa]

What values do you have in your ComboBox for the following properties :

1. Column Count?
2. Column Widths?
3. List Rows?
4. List Width?

[bdockery]

Here's the data - hope it makes sense!

Column Count
2

Column Widths?
0";1"

List Rows?
16

List Width?
1"

[Rabbit]

Here's the data - hope it makes sense!

Column Count
2

Column Widths?
0";1"

List Rows?
16

List Width?
1"

Column Count = 1, you only have one column in your row source.
Column Width = 1", it's invisible because you set your first column to 0", which is the only column you have in the first place.

[bdockery]

Ohh man. Makes perfect sense. Those settings were left over because the combobox had an ID column that was hidden.

That fixed the problem - I can see it now - only I see the ID numbers instead of the name.

Is there any way to make it work like a normal combo box where it shows the ProductName text column and still actually inputs the ID number?

You rock. Thanks

[Rabbit]

Private Sub ManufacturerID_AfterUpdate()
    Me.ModelID.RowSource = "SELECT ID, ProductName  FROM" & _
    " ProductDetails WHERE ManufacturerID = " & Me.ManufacturerID & _
    " ORDER BY ModelName"
 
    Me.ModelID = Me.ModelID.ItemData(0)
End Sub

Column Count = 2
Bound Column = 1, this is the column that will be stored in your table. So first column which will be ID.
Column Widths = 0"; 1"

[NeoPa]

That's what I woulda said :)
Nice one Rabbit.

[bdockery]

Yeah that makes perfect sense also. Somehow column 2 isn't being populated with the txt name though, It's just blank. With both clumns set to 1", column 1 has the ID's, but column 2 is empty. Is there something in the code that isn't populating it?

It should be coming from the ProductName column in the ProductDetails table.

The ProductDetails table has the following columns:

ID (populated into column 1)
ProductName
ManufacturerID (foreign key to manufacturer Details table)
ProductCost

We're almost there! It's killing me!

[Rabbit]

You set both to 1"? Did you try 0";1" first? The reason I ask is because your list width is set to 1" which means anything longer than that won't be shown. And if you set both to 1", then only the ID field will show.

[bdockery]

Yeah I tried that first, then did 1,1 to troubleshoot.

Just now I did 0,2 but that didn't work either, It's just like column 2" is always blank.

[MMcCarthy]

Yeah that makes perfect sense also. Somehow column 2 isn't being populated with the txt name though, It's just blank. With both clumns set to 1", column 1 has the ID's, but column 2 is empty. Is there something in the code that isn't populating it?

It should be coming from the ProductName column in the ProductDetails table.

The ProductDetails table has the following columns:

ID (populated into column 1)
ProductName
ManufacturerID (foreign key to manufacturer Details table)
ProductCost

We're almost there! It's killing me!

Did you change the query to include ProductName as per Rabbit's query in post #9.

Mary

[MMcCarthy]

Just posting a pointer to previous post. As we posted together you may have missed it.

Mary

[bdockery]

Oh man I did miss it!! I saw the post, but missed the code. I didn't think and just assumed it was a quote of my previous post.

Anyway now it works. Man you guys rock and this site rocks. Thanks so much!

[Rabbit]

Not a problem, glad to be of assistance.

[MMcCarthy]

Oh man I did miss it!! I saw the post, but missed the code. I didn't think and just assumed it was a quote of my previous post.

Anyway now it works. Man you guys rock and this site rocks. Thanks so much!

You're welcome. We are always glad to help.

[bdockery]

Alright here's one more :)

I added some code on the "onChange" event for the modelID that does a DLookup for the corresponding price, and enteres quantity 1. This works great when the modelID is clicked and updated with input. (Hooray I did this all by myself!)

When ModelID is auto-populated from the code, though, it doesn't trigger the "onChange" event. This is only triggered if I manually click the (already populated) selection.

I tried putting the dlookup on the "afterUpdate" and "onDirty" events, but none of them are triggered. I guess it's probably some kind of requiry that needs to happen, but I tried a requery on the modelID at the end of the code that updates it with no luck. Must be something I'm missing. Here's the code:

Private Sub ManufacturerID_Change()
    Me.ModelID.RowSource = "SELECT ID, ModelName  FROM" & _
    " ProductDetails WHERE ManufacturerID = " & Me.ManufacturerID & _
    " ORDER BY ModelName"
 
    Me.ModelID = Me.ModelID.ItemData(0)
    ModelID.Requiry
End Sub

Private Sub ModelID_Change()
    OrdDetCost = DLookup("Cost", "ProductDetails", "ID=" & ModelID)
    Quantity = 1
End Sub

[Rabbit]

I don't know if this will solve your problem but it's .Requery not .Requiry.

[bdockery]

You're right I had a typo there, it was just from the pasting into the site though. It's all kosher in the code, but still doesn't work.

Stupid problem eh?

[Rabbit]

Copy and paste the ModelID_Change code into the ManufacturerID_Change code at the end.

[bdockery]

Works! Why didn't I think of that? 0.0

[Rabbit]

Glad to be of help, good luck.

[bdockery]

Alright guys the database is looking good, just a few issues lurking around though.

Since the form is in datasheet view, when one record is completed another line is automatically created below it where I can enter another order line-item.

The problem is, if I select the manufacturer and model on one line, then go back up to another line to edit the model after making a mistake, then that line's rowsource is set to the wrong manufacturerID. It's filtering the cboxModel based on the cboxManufacturer of the last line entered. Am I making sense?

It filter's based on the last manufacturer selected, not on that specific record's manufacturer.

Anyway, I tried some code to correct this, which sorta works.

Private Sub ModelID_GotFocus()
    ManufacturerID.Requery
    Me.ModelID.RowSource = "SELECT ID, ModelName  FROM" & _
    " ProductDetails WHERE ManufacturerID = " & Me.ManufacturerID & _
    " ORDER BY ModelName"
End Sub

What I'm doing here is resetting the cboxModel's rowsource everytime it gains focus. This way if I go back up a line to edit the model then it shows the correct rowsources. This actually works. The problem is though that when this happens it makes all the other rows cboxModel's disappear. The entry's are still there and will be recorded, but they stop showing since apparently the filter is applying to all rows and making the selections disappear. Hopefully I'm making sense.

[Rabbit]

I don't know why it's doing that but try putting it in the on current event of the form.

[bdockery]

I don't know either. It's confusing.

Would you mind taking a look? It's really hard to explain what's going on, but you'll see immediately if you open the database.


I posted the database:

http://home.comcast.net/~bdockery81/PPC.zip

It's in Access 2007, though. Let me know if you need me to save it to 2003.

[Rabbit]

Sorry, I'm wary of downloading anyone's database. Maybe someone else will take a look.

Have you tried the On Current event? Did that work?

[bdockery]

Ok I understand. You have nothing to worry about, though.

The on current event made it worse. Every time the event hit it would clear all the modelID's, even if nothing was edited.

Without the onCurrent the ModelID values stay the same as long as nothing is edited.


What happens is that the filter is applied to every row, not just the current one. So the rowsource filter hits and all the sudden the value that was there is filtered and becomes invisible.

[Rabbit]

Well, I'm stumped. But that's good for you since I'm not the most advanced one here. I'm sure someone else will be able to help you.

[MMcCarthy]

This sounds like bad structural design. If you are filtering a lookup field in the subform based on a selection in the main form then you shouldn't have the subform in continuous view. There is no way to solve this problem as it is caused by the filter you have created.

Mary

[bdockery]

It's actually not based on the main form, though. The only thing in the main form is the OrderID and UserID.


The subform has both ManufacturerID and ModelID. ModelID is filtered off of ManufacturerID.

[MMcCarthy]

It's actually not based on the main form, though. The only thing in the main form is the OrderID and UserID.


The subform has both ManufacturerID and ModelID. ModelID is filtered off of ManufacturerID.

Then in the On current event of the subform put

Me.ModelID.Requery

Mary

[bdockery]

Thanks for the help Mary.


I just tried what you suggested above and here's what happens with the modified code:


1. Select manufacturerID, and everything works fine, modelID is filtered and auto-assigned the first selection in the lookup.

Next Row

2. Select manufacturerID and modelID is autofiltered as it should, but now the first row's modelID lookup changes to be blank. Also, now the first row's modelID lookup is filtered based off of the 2nd rows ManufacturerID, instead of the 1st row's as it should.

Thanks a lot for the help. It's really appreciated

[MMcCarthy]

You can't have them filtered separately. This is why a continuous view isn't desireable. Once you change the filter for one record it will change for all records in the view. There is no way around that. You either suffer the current status or change the view to single form rather than continuous.

Sorry but there is no other solution.

Mary

[bdockery]

Man it seems like what I'm doing should be the most common thing in the world. It's so hard to believe it's like this.


What's weird is that if I'm down to row 4 or 5, and go up and change the manufacturerID on row 1, it doesn't cause any problems. This is changing the rowsource filter, as shown in this code:

Private Sub ManufacturerID_AfterUpdate()
 ' Filter the model cboBox based on the selection in the Manufacturer cboBox
    Me.ModelID.RowSource = "SELECT ID, ModelName  FROM" & _
    " ProductDetails WHERE ManufacturerID = " & Me.ManufacturerID & _
    " ORDER BY ModelName"
    Me.ModelID = Me.ModelID.ItemData(0)
end sub

but it doesn't make the other modelID's from all the other rows disappear.



If the SAME code is triggered by the ModelID, a gotFocus for example, it does cause the modelID lookup from the other rows to disappear.

Me.ModelID.RowSource = "SELECT ID, ModelName  FROM" & _
    " ProductDetails WHERE ManufacturerID = " & Me.ManufacturerID & _
    " ORDER BY ModelName"
end sub

[NeoPa]

As Mary says, cascading ComboBoxes cannot work in a sensible way on a datasheet form. This is because there is only one definition for the ComboBox control. When you change the Row Source then there is no question of any of the ComboBox instances keeping their old Row Source.
Your only option, as far as I can see, is to convert your form to single-form view.
To lose the functionality of your cascading ComboBox would be a shame.

[bdockery]

I've almost got it "tricked" by re-applying the row-source code every time a model field gains focus, but it's never going to be quite right, I guess.

It seems like I'm just doing a very standard order processing setup that a million other people have probably done. Of course you're going to need multiple line-items on each order. So crazy.

Can anyone think of any other way to have multiple items associated to a single order that might work?

[NeoPa]

When entering or changing the data, go to a separate form (or maybe sub-form). When displaying the order as a whole, you can show the order detail lines in a list.
That's how the orders are displayed on our order-entry system (Not MS Access I may add).