Ignoring used records in list boxes

[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

 

[Arvin Meyer]

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

 

[John Vinson]

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?

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;

 

[levpet]

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

 

[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

 

[John Vinson]

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

Requery the listbox:

Forms!formname!listboxname.Requery