Change record (find record) on a different form 2

[tobymom]

Dear Experts,

Here's where I'm stuck:

I have two forms: Form A, Form B
both forms have same key - invoice number
I have a pull down menu in each forms - cboInvNum
I used macro on that PDM - GoToControl then findRecord to go to specific record on both forms.

What I want is,

when both forms A and B are open, if user changes record using the PDM on one form, both forms A and B should go to the same record.

ex) if I change InvNum on form A to "123", the form B should go to InvNum "123" also.

is there anyway I can do this using VBA or Macro?

 

[SoCalAccessPro]

Sure. VBA.

PDM on form A, in the On_Change Event.

YourPDM_On_Change
forms.formB.Pulldownmenu = me.pulldownmenu
End Sub

Tyrone Lumley
SoCalAccessPro

http://www.socalaccesspro.com

 

[tobymom]

Dear SoCalAccessPro,

Thank you so much! I never thought of using Forms!...

Anyways, the form B's PDM value is changed now thanks to you. I want the Form B's whole record to be changed to that particular value.

Could anyone have advice?

thanks!

 

[SoCalAccessPro]

Sure.

Same basic principle.

Look up the values you need using a query or Dlookup.

Then

Forms!formB.TExtBox1 = somevalue
Forms!formB.TExtBox1 = someothervalue

Note, do not use Forms!formB.TExtBox1.value as Access will complain that the control does not have the focus.

Tyrone Lumley
SoCalAccessPro

http://www.socalaccesspro.com

 

[SoCalAccessPro]

Sorry, should have been

Forms!formB.TExtBox1 = somevalue
Forms!formB.TExtBox2 = someothervalue

Couldn't find the edit post function

Tyrone Lumley
SoCalAccessPro

http://www.socalaccesspro.com

 

[Remou]

It seems that you wish to synchronize the records between the two forms, so I would use a recordset and the After Update event of the PDM. It would be possible to use common code, however, here is an example for one form (B):

If CurrentProject.AllForms("frmA").IsLoaded Then
  Forms!frmA.Recordset.FindFirst "TheNumericID=" & Me.cboPDM
End If

You would need delimiters for a text field.

 

[SoCalAccessPro]

Good point, and another way of doing it.

I forgot to mention the other form has to be loaded.

Tyrone Lumley
SoCalAccessPro

http://www.socalaccesspro.com

 

[Remou]

SoCalAccessPro

Your method involves changing the record in the other form or the use of unbound forms. I do not think that this is what the OP wishes, I believe that what is required is a synchronization of the recordsets, however, that is for the OP to decide.

 

[tobymom]

Remou:
Thank you so much. Your code did exactly what I wanted to accomplish.

SoCalAccessPro:
Thank you again for all your help.

 

[MajP]

may also want to look at FAQ702-5860. Similar idea to what Remou gave you, but may give you some other ideas.