Ignoring used records in list boxes

  • Thread starter Thread starter levpet
  • Start date Start date
L

levpet

Hi Guys,

I have the following problem.

I have a table with names, last name first name etc. and location.
I also have a location table.
I choose the location as a list box in the first table.
I would like to eliminate the already used locations in the names
table, so I can see what is still vacant, available.

Can anyone help me with this please?

Thx
levpet
 
Assuming that the LocationID from the Location table is being stored in the
Names table, the sql would run something like this:

Select * From tblLocation
Left Join tblNames ON tblLocation.LocationID = tblNames.LocationID
Where tblLocation.LocationID Not In ([tblNames].[LocationID])
--
Arvin Meyer, MCP, MVP
Microsoft Access
Free Access downloads:
http://www.datastrat.com
http://www.mvps.org/access
 
Hi Guys,

I have the following problem.

I have a table with names, last name first name etc. and location.
I also have a location table.
I choose the location as a list box in the first table.
I would like to eliminate the already used locations in the names
table, so I can see what is still vacant, available.

Can anyone help me with this please?
Click to expand...

You can base the listbox on a "frustrated outer join" query displaying
only those locations *not* already in the names table:

SELECT Locations.Location
FROM Locations
LEFT JOIN Names
ON Locations.Location = Names.Location
WHERE Names.Location IS NULL
ORDER BY Locations.Location;
 
Newsgroups: microsoft.public.access
NNTP-Posting-Host: 69-56-172-122.theplanet.com 69.56.172.122
Path: intern1.nntp.aus1.giganews.com!internal1.nntp.sjc.giganews.com!border2.nntp.sjc.giganews.com!border1.nntp.sjc.giganews.com!nntp.giganews.com!newshub.sdsu.edu!cyclone.bc.net!msrtrans!TK2MSFTNGP08.phx.gbl!TK2MSFTNGP12.phx.gbl
Lines: 1
Xref: intern1.nntp.aus1.giganews.com microsoft.public.access:87439


Thanks to you all guys it works!!!
levpet
 
Hi Guys,
This worked, now I have the following problem, the query does not
refresh itself therefore if I say delete one of the locations from the
main Names table, i cant select it again until i close and restart the
table. any ideas? Im new with VB and I think something refresh command
would help...
thx

levpet
 
Hi Guys,
This worked, now I have the following problem, the query does not
refresh itself therefore if I say delete one of the locations from the
main Names table, i cant select it again until i close and restart the
table. any ideas? Im new with VB and I think something refresh command
would help...
thx
Click to expand...

Requery the listbox:

Forms!formname!listboxname.Requery
 
Back
Top