3rd Cascading Combo Boxes

[captiangvp]

You need to be consistent.
You have combos with a PK and description, others with just a description? Then you are trying to find the PK in a description only combo?


I doubt the DB is normalised, but if it is not yours, that is what you are stuck with? However I would suspect more than one manufacturer can make a Connie Test?
As for your type mismatch, I would expect anything with ID to be numeric? (but that is just me, being consistent), so get rid of the str()? No idea why you would want to use it anyway?

Yes, this form wasnt set up by me, its a work in progress and i'm just tasked with making ideas work. I can't change what i'm not authorized to.
Not all IDs are numerical.. some are and some arent. its all based on the manufacturer I.D.s. Its for a drop shipping American only products website :P

[Welshgasman]

Nothing to stop you saying 'it would be better this way'. If they choose to ignore it, more fool them, but at least you tried.
That is what I liked about my last employer, they would listen to options, especially as they were not that computer literate. At the end of the day, they just wanted the end product, how it got there was up to me.

[mike60smart]

Hi
You say that the Form is a "Work in Progress"

You should take note of what others have suggested.

Instead of 1 table you need multiple tables to manage the process.

Your 1 Table needs to be normalised so that when you use Cascading Combobox's

Combo 1 Allows you to select a Manufacturer
Combo 2 only shows specific Products for the Manufacturer selected.
Combo 3 then only shows specific Features for the Product selected.

This is a basic 3 table configuration but I would imagine that would be other related tables,

[moke123]

I think you need to use a filter rather than a find first. Would be much easier with normalized tables.

[moke123]

Nothing to stop you saying 'it would be better this way'. If they choose to ignore it, more fool them, but at least you tried.

I think I'd be more inclined to say "Sorry, thats just not how it works"

[Micron]

But now its running into an error when trying to auto fill the information of the rest of the sheet,

I foresaw that error. You likely changed the bound column to 2, now you're trying to enter text into a numeric field. I'm going to bow out because you're intent on heading down a rough road but I think you'll eventually get this solved by fudging things.
Wish you luck and success with this though.

[captiangvp]

I foresaw that error. You likely changed the bound column to 2, now you're trying to enter text into a numeric field. I'm going to bow out because you're intent on heading down a rough road but I think you'll eventually get this solved by fudging things.
Wish you luck and success with this though.

I did not change the bound column at all. Just removed criteria under products column

[captiangvp]

Hi
You say that the Form is a "Work in Progress"

You should take note of what others have suggested.

Instead of 1 table you need multiple tables to manage the process.

Your 1 Table needs to be normalised so that when you use Cascading Combobox's

Combo 1 Allows you to select a Manufacturer
Combo 2 only shows specific Products for the Manufacturer selected.
Combo 3 then only shows specific Features for the Product selected.

This is a basic 3 table configuration but I would imagine that would be other related tables,

I'm not sure why everyone said we would need multiple tables when I've seen multiple people use 1 table for the same result. It's fine though as I have actually solved it. Was a simple after update code needed. Wasn't a multi table issue, just simple as putting code in wrong event field.

Thanks to all who's helped

[captiangvp]

Solved. Dunno how to mark it as such from phone

[mike60smart]

Hi

Well low and behold miracles do happen.

Luck with your project.

[Welshgasman]

Because you are going down the Excel way, where Connie Test is repeated multiple times in your table, when it should just be the PK for Connie Test.

Your combo issue had nothing to do with any AfterUpdate event?

Good luck with it anyway, you are going to need it.

[orange]

I'm not sure why everyone said we would need multiple tables when I've seen multiple people use 1 table for the same result. It's fine though as I have actually solved it. Was a simple after update code needed. Wasn't a multi table issue, just simple as putting code in wrong event field.

Thanks to all who's helped

You should review Codd's rules - Relational Database Principles
to appreciate the underlying concepts of database.
Just because you can use a hammer to drive screws, or ignore traffic laws and drive and speed on the wrong side of the road doesn't mean you should. You can use Access (or any database) in a spreadsheet manner, but sooner or later you'll find yourself in deep doo-doo. Any way, good luck with your project.

[CJ_London]

as a late responder - it is pretty messy doing it the way you have to do it. And I agree, you may think it was an afterupdate event that caused the problem, but it clearly isn't - it is how you had structured the productID control - but you are not finished yet.

For example if the user selects a manufacturer, a product id and a feature - see what happens to the data if the user then changes the manufacturer.

good luck with your project, but think you have quite a way to go.

[captiangvp]

as a late responder - it is pretty messy doing it the way you have to do it. And I agree, you may think it was an afterupdate event that caused the problem, but it clearly isn't - it is how you had structured the productID control - but you are not finished yet.

For example if the user selects a manufacturer, a product id and a feature - see what happens to the data if the user then changes the manufacturer.

good luck with your project, but think you have quite a way to go.

When they change manufacturer it clears the other 2 using .requery command.

Reddit helped me fix this lol it was before and after update commands that were screwed. As of right now adding 10+ products and checking each option I'm no longer running into the errors and everything is working 100% the way I wanted!

Thanks again to everyone for their input I'll look into the multi table thing in the near future if it allows but as of right now we are good.

Edit: the after update fixed the error not the combo box being empty. Removing the criteria command is what fixed the 3rd box

[ssanfu]

It sounds like you might have got it working, but here is what I did:

Since every field is TEXT, except the PK field "id", the Row Sources have to be changed a little. (Do not include the PK field "id")
It is misleading when a field is named "productID" (the "ID" indicates to me that the field is numeric)
Because this is not a normalized structure, you must have 2 criteria for the combo 'FeatureName'.


Here is the Row Source and event Procedure code for each of the combo boxes:

Combo Name -->> Manu  '<<-- I would have usec cboManu
Row Source -->> SELECT DISTINCT Options.manu FROM Options; 

Event Procedure
----------------
Private Sub manu_AfterUpdate()
    productid = Empty
    featurename = Empty
    productid.Requery
End Sub

*************************************************************************************

Combo Name -->> productid   '<<-- I would have usec cboProductid
Row Source -->> SELECT Options.productid FROM Options WHERE Options.manu = [Forms]![Options_Form]![Manu]; 

Event Procedure
----------------
Private Sub productid_AfterUpdate()
    featurename = Empty
    Featurename.Requery
End Sub


*************************************************************************************

Combo Name-->> Featurename   '<<-- I would have usec cboFeaturename
Row Source -->> SELECT ID, featurename FROM Options WHERE Options.manu = [Forms]![Options_Form]![Manu] AND [Forms]![Options_Form]!productid = Forms!Options_Form!productid; 

Event Procedure
----------------
Private Sub Featurename_AfterUpdate()
On Error GoTo Featurename_AfterUpdate_Err

    DoCmd.SearchForRecord , "", acFirst, "[ID] = " & Str(Nz(Screen.ActiveControl, 0))

Featurename_AfterUpdate_Exit:
    Exit Sub

Featurename_AfterUpdate_Err:
    MsgBox Error$
    Resume Featurename_AfterUpdate_Exit
End Sub

'manu' and 'productid' - both have the column count set to 1
'featurename' - column count set to 2 and the column width is set to 0 (zero)
I changed the 'FeatureName' macro to an Event Procedure (see above)




Good luck with your project..........