Query to reference List Box rowsource

[April15Hater]

Hi-

I have 2 listboxes:
lstUnassigned
.rowsource:

SELECT tblProductionTracking.ProductionTrackingID, tblProductionTracking.ProductionID, tblProductionTracking.FunctionTrackingID, tblProductionTracking.TrackingNumber
FROM tblProductionInput INNER JOIN tblProductionTracking ON tblProductionInput.ProductionID = tblProductionTracking.ProductionID
WHERE (((tblProductionTracking.FunctionTrackingID)=[Forms]![frmProductionStep3b]![cboTrackableSel]));

dblClick Procedure:

Private Sub lstUnassigned_DblClick(Cancel As Integer)
Dim rsListBox As ADODB.Recordset
Set rsListBox = New ADODB.Recordset
With rsListBox
    .ActiveConnection = CurrentProject.Connection
    .Source = "SELECT * FROM tblProductionStep3blstAssigned"
    .CursorType = adOpenDynamic
    .LockType = adLockOptimistic
    .Open
    .AddNew
    !ProductionID = Me.txtProductionID.Value
    !ProductionTrackingID = Me.lstUnassigned.Column(0)
    !FunctionTrackingID = Me.cboTrackableSel.Value
    !TrackingNumber = Me.lstUnassigned.Column(3)
    .Update
    .Close
End With
Me.lstAssigned.Requery
End Sub

lstAssigned
.rowsource:

SELECT tblProductionStep3blstAssigned.ProductionTrackingID, tblProductionStep3blstAssigned.FunctionTrackingID, tblProductionStep3blstAssigned.TrackingNumber
FROM tblProductionStep3blstAssigned
WHERE (((tblProductionStep3blstAssigned.FunctionTrackingID)=[forms]![frmProductionStep3b]![cboTrackableSel]));

What I'm trying to do is get it so that when I double click unassigned, it moves it over to assigned (which currently works), but I also need it to not show in unassigned. I would like to use a WHERE NOT IN clause in lstUnassigned.Rowsource, but I can't figure ou how to refer to another objects rowsource in SQL.

Any help is appreciated.

Joe

 

[LPurvis]

You don't seem to select or limit any fields using the tblProductionInput table in your unassigned listbox.
Is that still used to limit the results from tblProductionTracking by joining to a limited result set?

I can understand you wanting to use a Not In condition - which might look approximately like...

SELECT T.ProductionTrackingID, T.ProductionID, T.FunctionTrackingID, T.TrackingNumber
FROM tblProductionInput I INNER JOIN tblProductionTracking T ON I.ProductionID = T.ProductionID
WHERE T.FunctionTrackingID=[Forms]![frmProductionStep3b]![cboTrackableSel]
AND T.ProductionTrackingID Not In (SELECT A.ProductionTrackingID FROM tblProductionStep3blstAssigned A)


However the db engine will probably find it easier to optimise

SELECT T.ProductionTrackingID, T.ProductionID, T.FunctionTrackingID, T.TrackingNumber
FROM (tblProductionInput I INNER JOIN tblProductionTracking T ON I.ProductionID = T.ProductionID) LEFT JOIN tblProductionStep3blstAssigned A ON T.FunctionTrackingID = A.ProductionTrackingID
WHERE T.FunctionTrackingID=[Forms]![frmProductionStep3b]![cboTrackableSel]
AND A.ProductionTrackingID Is Null

I'm guessing there that FunctionTrackingID is the unique identifier of a row here (and FunctionTrackingID is a foreign key).

If you want a working example - have a look at the examples page linked to in my sig below - and in "List Selections" there's the "Two Listboxes Demo".

 

[April15Hater]

Leigh,
That demo is quite helpful! I'm a lil confused on the rowsource though. I see that id references "CAT" tables but I did not see them in the object list. Is there something I'm missing here?

Looking at your suggested query I don't understand how the 'IS Null' is going to filter out the ones that are in the unnassigned list. I have a feeling it is related to my question above.

aje,
I'm the same way. My Front end is attached. See frmProductionStep3b

Thanks Guys!

Joe

 

[LPurvis]

Just for the sake of completeness, have you actually tried the suggested queries?
(Despite them being air code...)

 

[April15Hater]

I didn't before I posted, but doing such answered my first question. I never knew you could just define temp tables like that on the fly. It didn't work though. It did show everything it was supposed to, but it didn't remove it from the unassigned list.

 

[LPurvis]

Well, there are no temp tables defined or created.
There is a subquery (also known as a derived table) and the other tables are aliased in the SQL for easier reading.

As for the queries themselves (I can't see your example application - do you have workgroup security applied to it?)...
They do need filtering down in the subqueries... (but that they run at least says that the aircode is refering to the correct objects ;-)

SELECT T.ProductionTrackingID, T.ProductionID, T.FunctionTrackingID, T.TrackingNumber
FROM tblProductionInput I INNER JOIN tblProductionTracking T ON I.ProductionID = T.ProductionID
WHERE T.FunctionTrackingID=[Forms]![frmProductionStep3b]![cboTrackableSel]
AND T.ProductionTrackingID Not In 
  (SELECT A.ProductionTrackingID 
   FROM tblProductionStep3blstAssigned A 
   WHERE A.FunctionTrackingID=[Forms]![frmProductionStep3b]![cboTrackableSel]
  )

and

SELECT 
  T.ProductionTrackingID, T.ProductionID, T.FunctionTrackingID, T.TrackingNumber
FROM 
  (tblProductionInput I 
       INNER JOIN 
   tblProductionTracking T 
       ON I.ProductionID = T.ProductionID
  ) 
      LEFT JOIN 
  (SELECT A.ProductionTrackingID 
   FROM tblProductionStep3blstAssigned A 
   WHERE A.FunctionTrackingID=[Forms]![frmProductionStep3b]![cboTrackableSel]
  ) X 
      ON T.FunctionTrackingID = X.ProductionTrackingID
WHERE T.FunctionTrackingID=[Forms]![frmProductionStep3b]![cboTrackableSel]
AND X.ProductionTrackingID Is Null

 

[April15Hater]

Oh me and my blonde moments. I gave Admin full rights in this one.

So is the alias defined in the FROM clause?

I tried the null one with no luck, but the one with the subquery worked. I'm still not sure what the Null part of the WHERE clause does to narrow it down. It could be that I've been looking at this to long today, lol!

 

[LPurvis]

That MDB doesn't have much in the way of local tables. :-s

Did you try the revised queries?

The Null part of the criteria is from the frustrated Outer Join to exclude entries from another table (a standard technique alternative to a "Not In" Where condition).

 

[missinglinq]

Here's a tutorial on moving items from one listbox to another. The listboxes can be single-select, multi-select (either type) and based on value lists or queries. I've never used it, but it comes from a very reliable source:

http://www.tek-tips.com/faqs.cfm?fid=6326

Hope it helps!

Linq

 

[LPurvis]

The awkward thing is, what we're trying to get going here isn't difficult.
(Though I dare say the class based example at that tek-tips FAQ might confuse the issue for our Titanic loving OP ;-)

It's just a question of either trying the queries - finding any shortfall (air-code typos) or seeing the application in action (to remove the aircode ambiguity).

We'll see though.

Cheers.

 

[April15Hater]

Yep, that's how I knew that the first one worked great. I'd like to get the second one to work because learning a new technique always makes me smile. I have a fresh head this mornin so after the coffee brews up, I'm gonna spend some time and see if I can't wrap my head around it. I certainly appreciate your help. I just learned the Not In command too so I was pretty impressed that I was able to apply it, but I always prefer faster.

Sorry, but because of confid. constraints, I'm unable to post any back-end data. I can give ya the table structure though. See attached. Thanks again!

 

[LPurvis]

Yeah, not wanting to sound like a tool or anything - but I really don't have the time to recreate backend tables based on an image ;-)

The queries are as offered. I still don't know if theyre working for you.
The examples are as offered to also help you understand the underlying process and reasoning.
Beyond that (and without a BE to at least use test data in) then there's nothing more to offer.

Cheers.

 

[April15Hater]

Ahh, I didn't know u were lookin to test it; sorry for the confusion. I certainly don't expect you to do anything like recreate it. I thought by looking at the table structure you would be able to determine something else that wasn't evident. I'm grateful just for you taking the time to help us newbs learn the ropes! Getting it to work is always the goal, but I think learning something new is way more valuable, and in that you have certainly succeeded!

I was able to get the Null query to work after tinkering with the the air code like you suggested. I think access arranged it a little differently. But after seeing the AS clause, the aliasing makes whole a lot of sense now too! Thanks again for helping me out!

SELECT T.ProductionTrackingID, T.ProductionID, T.FunctionTrackingID, T.TrackingNumber
FROM (tblProductionInput AS I INNER JOIN (tblProductionTracking AS T LEFT JOIN (SELECT A.ProductionTrackingID FROM tblProductionStep3blstAssigned AS A WHERE A.FunctionTrackingID=[Forms]![frmProductionStep3b]![cboTrackableSel])  AS X ON T.ProductionTrackingID = X.ProductionTrackingID) ON I.ProductionID = T.ProductionID
WHERE (((T.FunctionTrackingID)=[Forms]![frmProductionStep3b]![cboTrackableSel]) AND ((X.ProductionTrackingID) Is Null));

 

[LPurvis]

Well, if the queries are working and you understand the concept then I'd say we've won.

So - what's so bad about the 15th of April? Are you, as I postulated earlier, a fan of the Titanic? ;-)

 

[April15Hater]

I was really wondering what that was in reference to! Actually, I'm an accountant, and in US tax returns are due on April 15, so its my biggest deadline. I am however an April 16 Lover

 

[April15Hater]

I'm stuck again....

Let me explain what I'm trying to do, and maybe someone can give me a better idea of how to get what I'm looking for. Put simply our company draws maps of powerlines in AutoCAD. We are hired to engineer an area, and there are several different steps (functions) to our work. The company is assigned areas to work on. Each area per function is split into a grid known as a gridmap with gridded map numbers assigned to each part of the grid. Therefore each function has a different gridmap for the same area and the boundaries of each individual map in the grid between the functions do not match either. One function must be done before then next can be done. So lets say we perform Function A to Map 1. When we go to do Function B, it is going to be derived from maps from Function A. IOW, Function B Map X1, could be derived of Function A's Maps A1, A2, A3, A4.

With that in mind, what I am trying to set up is the same two list boxes. When I enter a map into the system (our production), I want the predeccessor function's map numbers to show in an unassigned list box. IOW with the example above, if I were entering in Map X1 from Function B, I'd like all of the maps from function A to to show up as unassigned, and allow me to double click to move them to the assigned box.

The map tracking data is stored in a table called tblProductionTracking. Which is made up of the PK ProductionTrackingID, 2 FK's ProductionID and FunctionTrackingID, and a TrackingNumber.

tblProduction is where all of the detail map information is stored for each individual map. Anything having to do with a map has a ProductionID.

tblFunctionTracking holds the different tracking identifiers for each function and they are assigned to the production in tblProductionTracking. The table also has a column called hierarchy so Access knows how to find the predecessor function.

What I would like to do is set the Unassigned listbox to show Tracking Numbers, with the rowsource to show all the Tracking Numbers from tblProductionTracking where the FunctionTrackignID match a certain object on the form, AND where the tracking number is not in the assigned list. The assigned would then simply have a where clause to filter ProductionID and FunctionTrackingID using objects on the form.

I think I'm really close, but I can't get the unassigned listbox to work just right. I need it to filter by FunctionTrackingID and not in the tracking number cannot be in the assigned listbox. I'll post some code that I thought would owrk, but it doesn't, maybe you guys can see something I don't.

SELECT tblProductionTracking.ProductionID, tblProductionTracking.FunctionTrackingID, tblProductionTracking.TrackingNumber
FROM tblProductionTracking INNER JOIN tblProductionStep3blstAssigned ON tblProductionTracking.ProductionTrackingID = tblProductionStep3blstAssigned.ProductionTrackingID
WHERE (((tblProductionStep3blstAssigned.ProductionID)<>[Forms]![frmProductionStep3b]![txtProductionID]) AND ((tblProductionStep3blstAssigned.FunctionTrackingID)<>[Forms]![frmProductionStep3b]![cboTrackableSel]) AND 
((tblProductionStep3blstAssigned.TrackingNumber) Not In (
SELECT tblProductionStep3blstAssigned.TrackingNumber 
FROM tblProductionStep3blstAssigned 
WHERE (((tblProductionStep3blstAssigned.ProductionID)=[forms]![frmProductionStep3b]![txtProductionID]) AND ((tblProductionStep3blstAssigned.FunctionTrackingID)=[forms]![frmProductionStep3b]![cboTrackableSel])))));

Thanks guys!