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. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
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
|
|||
|
|||
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
|
|||
|
|||
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 | |
|
|