Enter data into a combo box whose recordset is limited 1

[TimTDP]

In Access 2000, I have a combo box in a sub form. The recordset of the combo box is limited by criteria form the main form as follows:

SELECT DISTINCT tblProduct.ProductId, tblProduct.ProductDescription, tblOrganisationOrderProduct.OrganisationOrderId
FROM tblProduct INNER JOIN tblOrganisationOrderProduct ON tblProduct.ProductId = tblOrganisationOrderProduct.ProductId
WHERE (((tblOrganisationOrderProduct.OrganisationOrderId)=[Forms]![frmOrganisation]![frmOrganisationOrder].[Form]![OrganisationOrderId]))
ORDER BY tblProduct.ProductDescription;

My problem is that I need to enter a ProductID that does not appear on the main form. I have tried to enter the correct data in the underlying table, but when I open the form containing the combo box I can see the records but the combo box is blank!
How can I show the show the record in the combo box, but keep the criteria?

 

[ProgramError]

Hi timTDP

The data in the combo box has to be in the underlying record set of the combo box and in turn the sub form. I am currently experiencing this problem and as I go from record to record the combo box record set was re queried and so certain record data would disappear. see thread

http://www.tek-tips.com/viewthread.cfm?qid=1380565&page=1

As a result, I allowed the combo box record set to contain all the data and shown the limited combo box list in a list box on the main form.

hope this helps.


Ian Mayor (UK)
Program Error
Always make your words sweet and nice. Because you never know when you may have to eat them.