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:13 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 question before but got an error response from the
server. I apologize in advance if this try results 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!
 




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 12:56 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.