cascade combo boxes with duplicate values

[macattack]

Hi guys!

I'm trying to Identify a particular entry in my table for editing via combo boxes. The same serial number will come in multiple times but a different job number each time it comes in. To identify a record for editing I need the user to identify the serial number in combo1 and then the job # in combo2 (cascaded combo boxes).

The issue I'm running into is that combo1 has dozens of serial number duplicates (combo2 is acting as it should). When I change some settings around I'm able to get combo1 to eliminate duplicates but now combo2 only shows 1 job # when there should be dozens for that serial #. I need all like serial numbers to show their job # in combo2 and I have not had any luck surfing around the net/forums/experimenting.

I'm pretty sure I have to use a Junction Table but I haven't messed with that and I'm not 100% sure that's the best/only way to do it. Any help would be appreciated!

Mac

 

[macattack]

I threw in a picture to help illustrate what I'm trying to do. Once JCN is selected a 3rd combo box will be introduced (hidden) that will be linked to the ID. The ID will then be used to edit data (or so I hope). I'm probably over-complicating this so if anyone has ideas of how to make this simpler I'm all ears

 

[jdraw]

What tables do you have in your database? What is your database about?
Have you Normalized your tables?

see these:
http://www.datapigtechnologies.com/flashfiles/combobox1.html
http://www.datapigtechnologies.com/flashfiles/combobox2.html

 

[macattack]

What tables do you have in your database? What is your database about?
Have you Normalized your tables?

see these:
http://www.datapigtechnologies.com/flashfiles/combobox1.html
http://www.datapigtechnologies.com/flashfiles/combobox2.html

Those two videos showcase exactly what I want to do but I can't get it to work haha. I deleted my combo boxes, made new ones and followed his instructions, doesn't work. Is there a table setting that must be set that he doesn't explain?

 

[jdraw]

What is your database about -- in plain English?

 

[RainLover]

For a start I would have the "JCN" s all listed in a separate Table.

I would use this as a look up for any Table that uses this information an in particular for the Combo Box. This would comply to database Normalisation rules.

EDIT

Also there would be no duplications by creating a unique index.

 

[macattack]

What is your database about -- in plain English?

Parts come into my shop and we repair them. They are tracked serially (for history purposes) and via another generated code (JCN, it is based on a date/time). People must be able to enter that data into the system and detail what is accomplished to fix the part. It is necessary to revisit these parts individually sometimes (to add more data) so people must be able to look up the parts (via serial and JCN).

Essentially it's a way of documenting our maintenance. People have asked for the ability to search a range of dates (for that part), for specific wording with a type of part (there are 20 types of parts we work on), and the entire history of a single part (serially).

 

[macattack]

EDIT: FIXED

"where" option field had to be changed. I had to do a me.requery on change rather than afterupdate, don't know why.

 

[jdraw]

Glad you have it solved.