HELP! passig values from one form to another with VBA (1 Viewer)

[MR_G]

Not sure if this is in the right forum or not, but here it goes:

I have a cmb box (cmb_A) on subform SUB_A on Form_A (SUB_A is the name of the object, not the form, just for reference in this example). Cmb_A is the linked child field so that when a user changes the value for Cmb_A, another text box (txt_A2) on SUB_A changes accordingly.

On the NotInList event of cmb_A:

DoCmd.OpenForm "FORM_B"
Response = acErrContinue

On Form_B I have a text box (txt_B) Form_B is linked to cmb_A's rowsource Table (Table_C). Users fill in the information, then click a cmd button that has the following code:

Forms!Form_A!SUB_A.Form!cmb_A.Requery
Forms!Form_A!SUB_A.Form!cmb_A.value = Forms!Form_B!cmb_B.value
DoCmd.Close acForm, "Form_B", acSaveYes

Forms!Form_A!SUB_A!txt_A.SetFocus (txt_A is just another control in Forms!FormA!SUB_A)

I want 3 things to happen:
1) I want the info previously entered on Form_B!txt_B to be available in cmb_A.

This is okay.

2) I want cmb_A's value to automatically be set to the info just entered into Form_B!txt_B.

This is where I am starting to have problems. The value entered into FormB!txt_B is visible in Form_A!cmb_A. I even did a MsgBox cmb_A to make sure the underlying ID# (cmb_A has 2 columns, column 1 = NAME, column 2 = ID, bound column=2), and that is fine too. BUT...

3) I want Form_A!txt_A2 to be updated to the appropriate value for the new ID just entered into Form_B.

This DOES NOT HAPPEN I have to manually go reselect (if it was ever selected in the first place) the value that is already showing in Form_A!cmb_A. Then all is fine. But how do I make this happen automatically?

Also, if I try to close the form or change the record BEFORE I manually select the value I want from Form_A!cmb_A, then I get the message:

"The Microsoft Jet Engine can not find a record in the Table 'Table_C ' with the key matching fields 'my linked fields from cmb_A'"

What is going on here? Why doesn't everything update?

I have been trying everything from requery's to refreshes to repaints, and nothing seems to work automatically. HELP!

 

[sphere_monk]

I'm guessing txt_A2 is on Form_A!SUB_A not on Form_A.

If you're binding the second column to cmb_A, which is the ID field, then I'm also assuming you want to put the Name (shown as column 1 in the list) in txt_A2.

try putting

Forms!Form_A!SUB_A.Form!txt_A2.value = Forms!Form_A!SUB_A.Form!cmb_A.Column(0)

in your code here:

Forms!Form_A!SUB_A.Form!cmb_A.Requery 
Forms!Form_A!SUB_A.Form!cmb_A.value = Forms!Form_B!cmb_B.value
Forms!Form_A!SUB_A.Form!txt_A2.value = Forms!Form_A!SUB_A.Form!cmb_A.Column(0)
DoCmd.Close acForm, "Form_B", acSaveYes

Dan