restricting combo list to only 'unused' values in other table

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

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
 
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:

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
 
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.
 
SELECT subnetallocation.subnetrngidx, subnet
FROM subnetallocation LEFT JOIN routers
ON subnetallocation.subnetrngidx = routers.subnetid
WHERE routers.subnetid IS NULL
 
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.
 
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.
 
Back
Top