change to SQL - where and what?

[John B. Smotherman]

I tried posting this once before and got an error. I apologize in advance if
this second try reults in duplicate posts...

First some background (running Access2007 on XP Pro):
Our inventory database has a form (frmCheckOut) containing a subform
(sfmCheckOutList) for an employee to fill out when taking equipment to a
remote location. The subform is displayed in datasheet view, and is populated
via VBA code that transfers selections in a listbox on a popup form
(frmAddItems). The listbox is populated via SQL code in the RowSource
property of the control:

SELECT qryAddItems.ID_Number, qryAddItems.ItemName,
qryAddItems.ItemDescription, qryAddItems.Manufacturer,
qryAddItems.ModelNumber, qryAddItems.SerialNumber,
qryAddItems.CurrentLocation FROM qryAddItems ORDER BY [ID_Number];

This SQL code refers to a query rather than the main items table, for it
applies some selection criteria necessary to make sure the item is available
for checkout (not already checked out to someone else, not in the calibration
lab, small enough to transport, etc).

Now my questions:
1. Because the popup form may be opened and closed multiple time, with
transfers to the subform each time, I'd like to implement a further criteria:
not already listed in the subform.
2. Would it be best to place this criteria in the query qryAddItems, or in
the SQL for the RowSource of the listbox control?
3. What would the SQL for this look like? I've already tried the following
snippet as a criteria in the query:

....WHERE (((tblItems.ID_Number) Not In
([forms]![frmCheckOut]![sfmCheckOutList]![hrID])) AND...

which resulted in only the first item in the subform not being shown. I know
I'm close. What am I missing?

Thanks!

 

[John B. Smotherman]

Thanks, Ken. It took me some time to figure out the correct syntax - SQL is
NOT one of my strong points, and in the end I cheated and let the query
design figure it out for me. I opted for the second choice of the two you
gave me. Here's the SQL for the RowSource of the list box, in case it's ever
a help to anyone else:

SELECT qryAddItems.ID_Number, qryAddItems.ItemName,
qryAddItems.ItemDescription, qryAddItems.Manufacturer,
qryAddItems.ModelNumber, qryAddItems.SerialNumber,
qryAddItems.CurrentLocation FROM qryAddItems
WHERE (((qryAddItems.ID_Number) Not In
(SELECT tblEntries.ID FROM tblEntries
WHERE ((tblEntries.DocID)=[forms].[frmCheckOut].[doc_num]))))
ORDER BY qryAddItems.ID_Number;

I should probably mention that tblEntries is a simple join table between the
main items table and the equipment checkout documents table.

Thanks again.

There are three issues here:

1. The IN operator does not accept a parameter as its argument.
2. A reference to a control in a bound form refers only to the form's
current row.
3. Rather than referencing the subform you should reference its underlying
recordset.

So there are two ways you can approach this, but first you need to determine
an SQL statement which will return all the rows in the subform's underlying
recordset. You can save this as a query if you wish. If you do then the two
options are:

1. In the list box's RowSource LEFT OUTER JOIN the qryAddItems table to the
query which returns the subform's recordset, joining on ID_Number and hrID
colums and restrict the result set to where the hrID IS NULL.

2. In the list box's RowSource include a subquery which returns the hrID
column from the subform's recordset and apply the NOT IN predicate to this
subquery.

Note that the references to the subform's recordset in the above do not refer
to its Recordset property but more generically to the set of rows from the
relevant table which the subform is currently returning.

Whichever way you do it you'll need to requery the list box whenever the
subform is updated.

Ken Sheridan
Stafford, England

I tried posting this once before and got an error. I apologize in advance if
this second try reults in duplicate posts...

First some background (running Access2007 on XP Pro):
Our inventory database has a form (frmCheckOut) containing a subform
(sfmCheckOutList) for an employee to fill out when taking equipment to a
remote location. The subform is displayed in datasheet view, and is populated
via VBA code that transfers selections in a listbox on a popup form
(frmAddItems). The listbox is populated via SQL code in the RowSource
property of the control:

SELECT qryAddItems.ID_Number, qryAddItems.ItemName,
qryAddItems.ItemDescription, qryAddItems.Manufacturer,
qryAddItems.ModelNumber, qryAddItems.SerialNumber,
qryAddItems.CurrentLocation FROM qryAddItems ORDER BY [ID_Number];

This SQL code refers to a query rather than the main items table, for it
applies some selection criteria necessary to make sure the item is available
for checkout (not already checked out to someone else, not in the calibration
lab, small enough to transport, etc).

Now my questions:
1. Because the popup form may be opened and closed multiple time, with
transfers to the subform each time, I'd like to implement a further criteria:
not already listed in the subform.
2. Would it be best to place this criteria in the query qryAddItems, or in
the SQL for the RowSource of the listbox control?
3. What would the SQL for this look like? I've already tried the following
snippet as a criteria in the query:

...WHERE (((tblItems.ID_Number) Not In
([forms]![frmCheckOut]![sfmCheckOutList]![hrID])) AND...

which resulted in only the first item in the subform not being shown. I know
I'm close. What am I missing?

Thanks!

--
Message posted via AccessMonster.com


.