A Microsoft Office (Excel, Word) forum. OfficeFrustration

If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below.

Go Back   Home » OfficeFrustration forum » Microsoft Access » General Discussion
Site Map Home Register Authors List Search Today's Posts Mark Forums Read  

change to SQL - where and what?



 
 
Thread Tools Display Modes
  #1  
Old June 2nd, 2010, 06:03 PM posted to microsoft.public.access
John B. Smotherman
external usenet poster
 
Posts: 55
Default change to SQL - where and what?

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!
  #2  
Old June 2nd, 2010, 07:18 PM posted to microsoft.public.access
KenSheridan via AccessMonster.com
external usenet poster
 
Posts: 1,610
Default change to SQL - where and what?

There are three issues he

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 a

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

John B. Smotherman wrote:
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
http://www.accessmonster.com/Uwe/For...ccess/201006/1

  #3  
Old June 3rd, 2010, 05:24 PM posted to microsoft.public.access
John B. Smotherman
external usenet poster
 
Posts: 55
Default change to SQL - where and what?

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.


"KenSheridan via AccessMonster.com" wrote:

There are three issues he

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 a

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

John B. Smotherman wrote:
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
http://www.accessmonster.com/Uwe/For...ccess/201006/1

.

 




Thread Tools
Display Modes

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

vB code is On
Smilies are On
[IMG] code is Off
HTML code is Off
Forum Jump


All times are GMT +1. The time now is 01:39 PM.


Powered by vBulletin® Version 3.6.4
Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 OfficeFrustration.
The comments are property of their posters.