Confusing Form problem

[j.smith1981]

Ok I have a real problem here.

I have a parent combo box named 'cmb_cartridge_manufacturer' yea? (as its reffered to on the access 2007 form)

I also have a child one thats called 'cmb_cartridge_select', when the user selects a manufacturer it limits the number of cartridges in the child combo box yea?

Like so (in the SQL):

SELECT [cartridge Query].cartridge.id, [cartridge Query].cartridge_code, [cartridge Query].manufacturer, [cartridge Query].Manufacturer.id
FROM [cartridge Query]
WHERE ((([cartridge Query].Manufacturer.id)=[Forms]![data_form]![select_cart_man]))
ORDER BY [cartridge Query].manufacturer, [cartridge Query].Cartridge_Code;

Ok?

The problem is, when I shift through records on the form, the form looses the 2 field values.

Is there anyway of making Access get those values back?

Its just I cant have the manufacturer table existing as a foreign key on the data table, because the manufacturer id is on the cartridges table as a foreign key as it where.

Like so (may explain it further):

manufacturers:
id
manufacturer_name

cartridges (explained above yea?)
id
manufacturer_id (the primary key from manufacturers table)
name

I should be able to make it look this value up, just cant figure out how, like get the cartridge code back on the form, then get it to look up the manufactuers id for that cartridge code, are you with me?

I just cant get this to work and its really beginning to bug me.

Can someone help me with this please?

Thanks,
Jeremy.

 

[j.smith1981]

I think I have worked this one out.

Without making this way too overblown and complex to script/program in VBA or whatever.

I have decided to make a manufacturer_type table, with the following example:

manufacturer_types:
id | type
1 Cartridge
2 Printer

This then links to the manufacturer table as so:

id | manufacturer | manufacturer_type
1 Brother 1
2 Brother 2 (uniquely identifies the entity yea?)

But then we fall into a problem with making this work on the form.

So when the user selects a manufacturer, how would I go about.

Filtering it better and to allow only when the user selects a manufacturer on the printer combo box.

It only adds in the manufacturer id so it can display it in the form?

This is quite a questions but this would enable me to get around this pesky problem lol.

Any help as usual is greatly appreciated, not sure why I didnt see this.

I am working on a solution myself aswell so I may come back with a solution before someone replies.

Thanks and I look forward to any responses,
Jeremy

 

[vbaInet]

This is how you do a cascading combo box:

http://baldyweb.com/CascadingCombo.htm