Comboxes not updating

Forgive me if I have posted this previously, but this problem continues to plauge me.

(I have changed the names of my fields and tables for confidentially reasons and although it does look like the nortwind db, it's not)

I have a main form (ORDERS). I have a subform (ORDERDETAILS) in form ORDERS. In that subform is a combox (FOODITEM). The row source for FOODITEM is a table "FOODS" which has several fields like FOODID,TYPE (ie Veggies, Meats, Candies, etc), PRICE, WEIGHT, etc. The control source for the combox is in the table MAIN and it is storing the value of FOODID. The LimitToList property is set to YES for the combox. If I enter something in FOODITEM that is not in the table FOODS already, the form triggers the NotInList event and asks me if I want to add the new item to FOODS. After pressing yes, I can continue entering the rest of the data in the subform for that particular record. During the process for adding the new item, I only ask the user to enter a new description, not the PRICE, WEIGHT etc bc it would be too cumbersome to do at this point. So what I did was create a button on the mainform that would open a form (EDITFOODS) based on the table FOODS so I can edit the PRICE, WEIGHT, etc info for the new item or simply add a new set of items.

Now the subform ORDERDETAILS displays the values, PRICE, WEIGHT etc for each of the FOODS in the FOODITEM combox by way of several other comboxes. For instance, the PRICE combox in the ORDERDETAILS subform, has its control source as MAIN.FOODID. The row source is then a query on the table FOODS such as:


SELECT FoodsID, Price
FROM Foods

and the WEIGHT combox row source is

SELECT FoodsID, Weight
FROM Foods


The problem is, that after I close the form EDITFOODS, the comboxes for PRICE, WEIGHT, etc do not update to show the new data I just entered despite adding Me.Refresh and Me.requery or Me.Recalc statments to the FormClose event in EDITFOODS. I have tried these for the main and subform. Maybe I did it incorrectly? The only way I can get the comboxes to update is to either close the form and reopen or take the form to design view and then back to the datasheet view of the form.

Completely befuddled.

Hi,

I hope I follow the whole story correctly...
If you added/modified the underlying data, to account for the changes made you
want to repopulate the combox (meaning Clear its content and use .AddItem method)

Regards,
Shurik.

So I guess Access does not do that automatically when you do a refresh or requery? How come it will do it once I leave the form and then open it back up?
Is it bc Access is actually doing .additem for the combox(s) everytime it opens a from, but not on refresh or requery? Or to put it another way, checking to see if the underlying data in a combox has changed is not included in the refresh / requery code?

Another though...
It seems like when the form opens up, it only takes a snapshot of what is in the row sources for the comboxes and if you want the combox rows to be update you have to use the .additem method. Hence the row source is not a "dynamic link" and must be update manually?

Are you calling the .refresh on the form or the combobox itself?

These are bound access combobox's right?

refreshing the form. I think I tried on the combox but seem to remember not being able to do that - "method not found" or some other similar error for
combox.refresh

Is there a way to refresh just the combox? I would like to know.

Thanks

The combo's should have a .requery method.....

I tried but can't seem to get to it.

The code for the Edit FOODs Button is in the Main form ORDERS and is as follows Private Sub EditFOODButton_Click()
On Error GoTo Err_EditFOODButton_Click

Dim stDocName As String
Dim stLinkCriteria As String

stDocName = "f_FOOD_Edit"
DoCmd.OpenForm stDocName, acFormDS, , stLinkCriteria
Me.OrderDetails.Requery

Exit_EditFOODButton_Click:
Exit Sub

Err_EditFOODButton_Click:
MsgBox Err.Description
Resume Exit_EditFOODButton_Click

End Sub and this did not work.


I tried Me.ORDERDETAILS.Price (where Price is the name of one of the controls on the subform) but that doesn't actually work bc after I type the "." after "ORDERDETAILS", the quick list does not show any of the controls on the subform.

So the question is, assuming the requery of the combox will work, how do you "get to it" if your code is on the main form?

Ahhhh!

Its been a long time since I did subforms....

Its something like Forms!FormName!SubformName.Form!ControlName - I cant remember exactly Im afraid. Try Google or MSDN for the exact syntax.

Okay,
got it to work...
Forms![ORDERS]![ORDERDETAILS].Form.Refresh and I put this code in the FormClose event of the EDITFOODs form.

Looks to be doing good.

However, I could not figure out how to update just one combox. When I would put Forms![ORDERS]![ORDERDETAILS].Form.MyCombox.Refresh I would either get a "Object required" or "Could not find field 'MyCombox' " errors.

Thanks

I think it's:

Forms![main form name]![subform name].Form![control name].Requery

Tried it, but I get the "can't find field 'MycontrolName' " error.

Can you post the code you are using again pls?

You mean the code from POST #10?

No, none of the code in post #10 looks like my code in post #11 :p

No, none of the code in post #10 looks like my code in post #11 :p
I think this is what you want:
Private Sub Form_Close()

On Error GoTo Err_Form_Click

Dim stDocName As String

stDocName = "m_MTOUpdate_Tags"
DoCmd.RunMacro stDocName
Forms![ORDERS]![ORDERDETAILS].Form![FOOD].Refresh 'Trying to refresh just the combox - but this did not work

Exit_Form_Click:
Exit Sub

Err_Form_Click:
MsgBox Err.Description
Resume Exit_Form_Click

End Sub

Hmmm well it's requery not refresh.

If that still doesnt do it I'll make one and try it. Will be a while tho as my Access is completely tied up importing 25000 files atm.

I changed it to requery but it still did not work. Its not an issue of requery or refresh, I'm just not giving the code the right "street address" - I'm in the neighborhood but "can't find the right house"

Ok try:

Forms![ORDERS].[ORDERDETAILS]![FOOD].Requery

why r u trying to refresh your ComboBox?
have you added or deleted something?
you should try Shurik's advice and .Clear and .AddItem (repopulate) the combobox each time it is edited

Access combos dont work like VB combos, and by the looks of it they are bound.

Yes, I did change the data for the row source, and refreshing the whole subform works well at least for my purposes.

Hope this helps anyone out there.

This is in regards to another db problem but the .requery works well for this instance where I needed to requery the combox bc its value was based on another combox and refreshing the whole form Me.refresh, was too time consuming. The previous problem was fixed by refreshing the whole form.