Data not staying in form cbo

[WoodyAccess]

I have an Order form with Order Detials Subform.

The Orders Form has a cbo to select Supplier, with an AfterUpdate of

Me![Order Details Subform].Form!cboProduct.Requery

The subform has a Product cbo which has SQL statement which works.

SELECT tblProducts.ProductID, tblProducts.Product, tblProducts.UnitPrice,
tblProducts.UnitType, tblProducts.OperationsCode, tblProducts.Discontinued,
tblProducts.SupplierID
FROM tblProducts
WHERE (((tblProducts.SupplierID)=[Forms]![Add an Order and
Details].[Form]![cboSupplierID]))
ORDER BY tblProducts.ProductID, tblProducts.Product;

And an AfterUpdate code of

Me.UnitPrice = Me.cboProduct.Column(2)
Me.UnitType = Me.cboProduct.Column(3)
Me.OperationsCode = Me.cboProduct.Column(4)

However, when i use the record selector to view the next order or the
previous order (which I have already inputted the necessary data) the product
text in the Product cbo disappears but all the other relevant information (ie
UnitPrice, UnitType etc) remain.

Also if I try and put in extra data in the subform, the Product cbo dropdown
options are not linked to the supplier combo on the orders form. I have to
reselect the supplier to get the correct dropdown options.

Why does the disappear and how can i stop it?
And how can I make the dropdown options always relevant to the supplier when
I go through the records?

 

[Allen Browne]

Requery the combo in the Current event of the main form, as well as the
AfterUpdate event of the Supplier.

When the main form changes record, its Current event fires, and the newly
loaded record may have a different Supplier.

Like this:
Private Sub Form_Current()
Me![Order Details Subform].Form!cboProduct.Requery
End Sub

 

[scubadiver]

If the problem is that the information you enter into the bound text boxes
remains and the referenced information disappears try this in the "on
current" event:

Me.cboproduct.Requery

It should (hopefully) help you. As you cycle through the records, the
referenced info should change.

As far as your other query is concerned, do you have a cascading combo
set-up for the drop down options for each supplier?

 

[WoodyAccess]

Thanks! Works perfectly
--
Thanks
WoodyAccess

Requery the combo in the Current event of the main form, as well as the
AfterUpdate event of the Supplier.

When the main form changes record, its Current event fires, and the newly
loaded record may have a different Supplier.

Like this:
Private Sub Form_Current()
Me![Order Details Subform].Form!cboProduct.Requery
End Sub

--
Allen Browne - Microsoft MVP. Perth, Western Australia

Reply to group, rather than allenbrowne at mvps dot org.

I have an Order form with Order Detials Subform.

The Orders Form has a cbo to select Supplier, with an AfterUpdate of

Me![Order Details Subform].Form!cboProduct.Requery

The subform has a Product cbo which has SQL statement which works.

SELECT tblProducts.ProductID, tblProducts.Product, tblProducts.UnitPrice,
tblProducts.UnitType, tblProducts.OperationsCode,
tblProducts.Discontinued,
tblProducts.SupplierID
FROM tblProducts
WHERE (((tblProducts.SupplierID)=[Forms]![Add an Order and
Details].[Form]![cboSupplierID]))
ORDER BY tblProducts.ProductID, tblProducts.Product;

And an AfterUpdate code of

Me.UnitPrice = Me.cboProduct.Column(2)
Me.UnitType = Me.cboProduct.Column(3)
Me.OperationsCode = Me.cboProduct.Column(4)

However, when i use the record selector to view the next order or the
previous order (which I have already inputted the necessary data) the
product
text in the Product cbo disappears but all the other relevant information
(ie
UnitPrice, UnitType etc) remain.

Also if I try and put in extra data in the subform, the Product cbo
dropdown
options are not linked to the supplier combo on the orders form. I have to
reselect the supplier to get the correct dropdown options.

Why does the disappear and how can i stop it?
And how can I make the dropdown options always relevant to the supplier
when
I go through the records?

 

[WoodyAccess]

Thanks! The On current and after update requery solved everything. Works
perfectly
--
Thanks
WoodyAccess

If the problem is that the information you enter into the bound text boxes
remains and the referenced information disappears try this in the "on
current" event:

Me.cboproduct.Requery

It should (hopefully) help you. As you cycle through the records, the
referenced info should change.

As far as your other query is concerned, do you have a cascading combo
set-up for the drop down options for each supplier?

I have an Order form with Order Detials Subform.

The Orders Form has a cbo to select Supplier, with an AfterUpdate of

Me![Order Details Subform].Form!cboProduct.Requery

The subform has a Product cbo which has SQL statement which works.

SELECT tblProducts.ProductID, tblProducts.Product, tblProducts.UnitPrice,
tblProducts.UnitType, tblProducts.OperationsCode, tblProducts.Discontinued,
tblProducts.SupplierID
FROM tblProducts
WHERE (((tblProducts.SupplierID)=[Forms]![Add an Order and
Details].[Form]![cboSupplierID]))
ORDER BY tblProducts.ProductID, tblProducts.Product;

And an AfterUpdate code of

Me.UnitPrice = Me.cboProduct.Column(2)
Me.UnitType = Me.cboProduct.Column(3)
Me.OperationsCode = Me.cboProduct.Column(4)

However, when i use the record selector to view the next order or the
previous order (which I have already inputted the necessary data) the product
text in the Product cbo disappears but all the other relevant information (ie
UnitPrice, UnitType etc) remain.

Also if I try and put in extra data in the subform, the Product cbo dropdown
options are not linked to the supplier combo on the orders form. I have to
reselect the supplier to get the correct dropdown options.

Why does the disappear and how can i stop it?
And how can I make the dropdown options always relevant to the supplier when
I go through the records?