List box no specific to current record

[MetalicPrism]

I have a listbox on a form that is populated by a query.

SELECT Parts.PartID, Parts.[Part#], Parts.Description FROM Parts INNER JOIN ([Order Details] INNER JOIN NonConformance ON [Order Details].OrderID = NonConformance.OrderID) ON Parts.PartID = [Order Details].PartID WHERE ((([Order Details].OrderID)=[Nonconformance].[orderid])) ORDER BY Parts.[Part#];

This works well but populates the box will all records in the NonConformance table and is not specific to the current record. There is a orderID on the form, however the methods I have tried to use to query that... are not working. the listbox is also bound to a field in the Noconformance table to add the selection to the table. Which BTW will be the next problem as sometimes there is more then on item to add to the table so a multi select would be perfered.

Can anyone point me in the right direction?

 

[Eupher]

Hi, MetalicPrism,

Which field on your form holds the data you want to use as the criteria for your listbox?

Ken S.

 

[MetalicPrism]

PurchaseOrderID is it's name, and it is bound to the OrderID in my Nonconformance table. txtPoID would have been wiser but hey you know what happens when you mess with one thing too long. Thanks for the look

 

[Eupher]

WHERE ((([Order Details].OrderID)=[Forms]![MyFormName]![PurchaseOrderID]))

Ken S.

 

[MetalicPrism]

Hello Again Ken

That works well however it is only working for the first record on the load of the form. I tried Me.lstPartsList.Requery in the current event of the form. and even removing each item from the listbox one at a time in this event.. but it seems to not effect the listbox. Is this a listbox limitation in Access... or most likey am I using the wrong event to do this.

Thanks

 

[Eupher]

How about Me.Recalc?

Ken S.

 

[MetalicPrism]

this did not work either

 

[Eupher]

Does the value of PurchaseOrderID on your form change as you navigate through the form's recordset? What is the PK of the forms' recordset?

Ken S.

 

[MetalicPrism]

Yes it changes as the records do

The PK of the record source of the form is NonconID. The table is Nonconformance.

 

[TheAceMan1]

How are ya MetalicPrism . . . . .

[blue]Requery[/blue] doesn't work on a listbox.

In the [blue]OnCurrent[/blue] event, this should:

[blue]Me.lstPartsList.RowSource = Me.lstPartsList.RowSource[/blue]

Also:

[blue] . . . [purple]the listbox is also bound to a field[/purple] in the Noconformance table to add the selection to the table. Which BTW will be the next problem as [purple]sometimes there is more then on item to add to the table so a multi select would be perfered.[/purple][/blue]

[purple]You can't bind more that one selection to a field![/purple]

See Ya! . . . . . .

 

[MetalicPrism]

Still no luck on the list box.

Me.lstPartsList.RowSource = Me.lstPartsList.RowSource has no effect.

I know it is connected because If I change the "rowsource type" on the listbox that selects only the current order number part information, while it is not in design mode, and then change it back the list box populates with the current order information. But in code it will not seem to touch it. I am using the intelacense as well so i know it is not a spelling error. ACK

This is the query

SELECT Parts.PartID, Parts.[Part#], Parts.Description FROM Parts INNER JOIN ([Order Details] INNER JOIN NonConformance ON [Order Details].OrderID = NonConformance.OrderID) ON Parts.PartID = [Order Details].PartID WHERE ((([Order Details].OrderID)=[Forms]![Master_Nonconformance]![PurchaseOrderID])) ORDER BY Parts.[Part#];

 

[MetalicPrism]

An Error results when I change the when rowsource type it indicates no current reccord, but still find the right information

 

[TheAceMan1]

[blue]An Error results when I change the when rowsource type it indicates no current reccord, but still find the right information)[/blue]

Don't understand the sentence! Please qualify!

If you make a query instead, does the query work (don't forget the form has to be open)?

See Ya! . . . . . .

 

[TheAceMan1]

MetalicPrism . . . . .

Scratch my last post . . . .

Leave the form open & minimize it. If you goto the query window & run the query . . . what do you get?

I suspect the query is only returning one record . . .

See Ya! . . . . . .

 

[MetalicPrism]

HI

Thanks for the help. I figured it out. I programmed the got focus event to requery the listbox and now it changes with the record change.