Access Subform won't update with combobox change

[bcooler]

OK, I'm about 15 hours into this problem....AGGHH!

I have a form called "frmEval" with a combobox called "cboRMA" and a subform (control name is "sfrmRecall") which was created using the subform wizard.

Ultimately, I'd like the subform to update as I change the combobox. I thought this would happen automatically and without VBA, but maybe not.

There are 2 scenarios I've come up with to requery the subform after the combobox change.

SCENARIO 1: If I open the form (from the switchboard button) in an attempt to create a NEW record with

DoCmd.OpenForm "frmEval", acNormal, , , , , "Eval_Start"
Forms("frmEval").DataEntry = True

OR

DoCmd.OpenForm "frmEval",acNormal, , ,acFormAdd, , "Eval_Start"

then I correctly open with a new record. However, any changes to the combobox do not result in subform changes, even when using Forms![frmEval]![sfrmRecall].Requery in my "cboRMA_AfterUpdate()" event.

I then began using a Me.Requery in the same "cboRMA_AfterUpdate()" event to requery the whole form, but I believe Access is creating a new record at this time because my combobox disappears and the record number changes to "new", and multiple records are showing up in the related table (I think due to my new record requirement in the openform command).

SCENARIO 2: I open the form using

DoCmd.OpenForm "frmEval", acNormal, , , , , "Eval_Start"

PLUS

DoCmd.GoToRecord , "", acNewRec

PLUS

Me.Requery in the "cboRMA_AfterUpdate()" event

which seems to work for the first form that I enter. Within this instance of the form opening, I can change the cboRMA combobox and the subform will update correctly. When I close the form and try another entry (from the switchboard), modification of another combo box I enter (called cboContr) tied to a "ME.Requery", causes the previous record's data to enter into all other boxes on the form. I'm afraid the operator won't modify to correct. I don't know why the form is remembering the previous data?

Sorry for the long post, but I need answers to to the following:

a) What do I do to get the subform to update when the combobox changes? I'm afraid the more code I put in, the more likely it will fail for a scenario I haven't considered.

b) Why doesn't the subform automatically update when I am changing the "master" table via combobox change?

P.S- I've tried a lot of things that I've seen posted such as

1) Refresh or Repaint instead of Requery
2) Making sure I reference the correct subform control when requerying (I use the autofill from VB)
3) Placing a messagebox on either side of the query to see the cboRMA change from "Null" to the previous record's data. HUH???

Thanks again. The prize for a good solution is my pulled out hair sent to your address. I'm sure you could make a small toupee by now.

 

[MajP]

When you say "subform update" what do you expect to happen? That is kind of a generic term.

If you want a change in the combo box to change the subform records to the linked records then simply link the subform to the combobox. In the subform control link the master and child fields like

Link Master Fields: [cboRMA]
Link Child Fields: [yourFieldThatLinksToTheValueInTheCombo]

Now any change of the combo will change the subform to the associated records.

If that is not what you want to do then explain what you mean by update.

 

[bcooler]

Yes, I want the subform records list to reflect the choice made in the combobox "cboRMA".

I like what you're saying, but I'll need a little help as this is my first subform and I felt very lucky to have gotten this far.

For some history, I have 3 tables with correct relationships already created.

tblUnit: Maintains info about the specific unit.

tblReturn: When a unit returns from the field, this form adds a record with some info plus a link to a given unit from tblUnit.

tblEval: Once a unit has been returned, we need to do an evaluation on it by creating a frmEval which adds some info plus links a returned unit from tblReturn.

In the frmEval form, I am trying to connect a returned unit to the evaluation through the cboRMA combobox (no problem). In the mentioned subform, I am also trying to see recalls that may be associated with the given unit connected with a given return. As you can see above, the combobox talks to the return table, and only indirectly to the specific unit (via a query setup in the frmEval recordsource).

Because of this, the combobox bound column is not what I want as the "Parent" link, but "UnitID", 12 columns over in the query.

The "child" link for the subform field is (and I think should be) "UnitID". This is based on a query specifically created for the subform.

How do I specify the parent link in this situation? I tried [cboRMA].column(12), but got errors.

 

[bcooler]

OK. I had some minor success.

If (in the AfterUpdate event for the cboRMA combobox) I copy the needed "parent link" info into a hidden textbox and then use the hidden textbox as the parent link, it works.

Is there anyway around this bandaid solution?

 

[MajP]

Using a hidden text box is a great idea. You can actually use that concept to link multiple continous subforms. So put a hidden textbox called "txtLink" on the form. In your comb after update

Me.txtLink = me.cboRMA.column(11)
(columns start with a zero index)
now link the subform control

Link Master Fields: [txtBxLink]
Link Child Fields: [unitID]

When you change the combo it updates the text box, and the subform links to the txtLink.

Here are some expansion on that concept.

http://www.4shared.com/file/97759751/2cc085c4/SubForms.html

But it sounds like you figured it out.