 |
 | Home | Contact Us | FAQ | Search & Site Map | Link to Us |
 | Sign In | Join | Other 45 Sites in Network |
MS Access Forum / Queries / November 2005restricting combo list to only 'unused' values in other table
I have 2 tables; subnetallocation (SNA, for short) and routers. each record in routers will store a reference to a subnetidx. After a subnet gets allocated and when i next go to routers form to allocate another, i want my combo box to only show records from SNA that have NOT been referenced in routers. in essence, as time goes on and subnets get allocated, the combo box will eventually show no records available..hope this is clear. Any help would be really appreciated. Many Thanks David Dear David: The query used in your combo box can be written with a LEFT JOIN to the list of allocated numbers. Then you filter this to include only those where the key value to the allocated numbers table IS NULL. There are other ways of accomplishing this, but this method performs best with the Jet database engine, which you are probably using if your application is an MDB. Tom Ellison >I have 2 tables; subnetallocation (SNA, for short) and routers. each >record [quoted text clipped - 9 lines] > Many Thanks > David Many thanks Tom. it is late so will try in the morning. if i dont get very far (as i am not very experienced..u prob gathered), i hope i can come back to you. David > Dear David: > [quoted text clipped - 21 lines] > > Many Thanks > > David Dear David: If you will need some help, please provide some query work and/or other descriptions of what you have so far as a basis for me making more specific recommendations. Tom Ellison > Many thanks Tom. it is late so will try in the morning. if i dont get very > far (as i am not very experienced..u prob gathered), i hope i can come [quoted text clipped - 32 lines] >> > Many Thanks >> > David Hi tom; SELECT subnetallocation.subnetrngidx, subnet FROM subnetallocation LEFT JOIN routers ON subnetallocation.subnetrngidx = routers.subnetid; this gave me the complete list though. i could not see how to use your if NULL bit. > Dear David: > [quoted text clipped - 40 lines] > >> > Many Thanks > >> > David SELECT subnetallocation.subnetrngidx, subnet FROM subnetallocation LEFT JOIN routers ON subnetallocation.subnetrngidx = routers.subnetid WHERE routers.subnetid IS NULL  SignatureDoug Steele, Microsoft Access MVP http://I.Am/DougSteele (no e-mails, please!) > Hi tom; > [quoted text clipped - 61 lines] >> >> > Many Thanks >> >> > David excellent; however on rec1 in routers; choose range1, then move on to next routers record and list in combo not updated. tried 'on change' action to do save & requery via macro but does not do the trick. > SELECT subnetallocation.subnetrngidx, subnet > FROM subnetallocation LEFT JOIN routers [quoted text clipped - 66 lines] > >> >> > Many Thanks > >> >> > David whats really wierd is that even though the combo's source data is the subnetid field and this gets updated when i choose an available range, when i next go in the form does not pick up the value. if u go into the table it shows the stored value...this is becoming very frustrating..½way there, but 2 issues now. in same session, choices chosen do not get excluded from the list and 2nd when next session into form, stored values not showing...am i just doing something very obvious as the values in the rest of the field properties are the same as the rest of my combo boxes and they work fine. > excellent; however on rec1 in routers; choose range1, then move on to next > routers record and list in combo not updated. tried 'on change' action to do [quoted text clipped - 70 lines] > > >> >> > Many Thanks > > >> >> > David |
Reply to this Message |
 Sign In
 Join
 My Latest Posts My Monitored Threads My Blog My Photo Gallery My Profile My Homepage Start New Thread Enable EMail Alerts Rate this Thread
|
©2008 Advenet LLC Privacy Policy - Terms of Use
This website includes both content owned or controlled by Advenet as well as content owned or controlled by third parties.