Onclick in subform updates another subform

[Caracarn]

Hello,
I have a main form (Offices) with 2 subforms (State) and (Details).
The State subform contains a list of offices in that State in a continuous form so I see all 12 offices in the subform at once.

I want to be able to click on the OfficeName field in the State subform and have it update the Details subform to only display the related records for that office eg address, site contact etc.

All forms use the same query as their source which contains tables linked by OfficeID.

I have created an onclick event for the OfficeName field, but I am unsure of the underlying code to accomplish what I am after.

I assume what is required will be:
Click on OfficeName
Set current record to record that contains the OfficeName I just clicked on
SetFocus to Details subform
Requery Details subform so only the records where OfficeID field matches are displayed.

I hope someone can point me in the right direction and/or suggest the correct functions to do what I am after.

Any assistance is much appreciated.

 

[HiTechCoach]

Hello,
I have a main form (Offices) with 2 subforms (State) and (Details).
The State subform contains a list of offices in that State in a continuous form so I see all 12 offices in the subform at once.

I want to be able to click on the OfficeName field in the State subform and have it update the Details subform to only display the related records for that office eg address, site contact etc.

All forms use the same query as their source which contains tables linked by OfficeID.

I have created an onclick event for the OfficeName field, but I am unsure of the underlying code to accomplish what I am after.

I assume what is required will be:
Click on OfficeName
Set current record to record that contains the OfficeName I just clicked on
SetFocus to Details subform
Requery Details subform so only the records where OfficeID field matches are displayed.

I hope someone can point me in the right direction and/or suggest the correct functions to do what I am after.

Any assistance is much appreciated.

Caracarn,

All forms use the same query as their source which contains tables linked by OfficeID.

This is probably not the best design. Each form/sub form should probably have a different table (usually only one table) in the record source. I only include in a form's record source a single table with only the fields that the for will display. A found should not include that data that will be displayed in the sub form(s).


About your two sub forms.

I normally link the two sub form together using the Master/child linking fields. In the second sub form, set the master linking field to reference a control in the first sub form.

In the first sub form's On Current event, you will need to requery the second (linked) sub form.

If you still can't get it to work then if you will post a sample of your database I will take a look at it to see what I can do to help.

 

[Caracarn]

Hello HiTechCoach, Thanks for your help, but i'm still having problems. I have done what you suggested and used separate tables for each form, but my requery does not seem to work, I'm obviously doing something wrong.

I have added the same code to OnClick and OnCurrent because I want the address subform to update each time I click the OfficeName in the State subform.

A sample of my DB can be found at http://www.camanda.com/Access/Sample.mdb

Thanks for your help.

 

[HiTechCoach]

Are these just test tables and not your real design?

All I had to do to get it working was set the master/child linking fields for the second sub form.

 

[Caracarn]

The tables are just for testing, but the design will be what I will use, if I can get it working.

What you have done is exactly what I am after, except for one small problem. There will be 7 state subforms, 1 for each state. I want to be able to see all of my offices, across all of our states on the one dashboard, I will then click on the office name in a particular State subform to have it bring up that office's details in the Office subform. As you reference the existing State subform by name in the office subform Link Master Fields, this won't work if for additional state subforms.

While waiting for your reponse I have been doing some searching and found a post answering a similar problem on another forum, I adapted the code and got it working, but it isnt as smooth as what you have done and the screen flickers a bit and puts 'Calculating...' at the bottom of the page.

On the dashboard main form I put a textbox "txtBxLink"

I then have the code in the State subform:

Private Sub Form_Current()
Me.Parent.txtBxLink = Me.OfficeID
End Sub

​

I then link the Office subform to the textbox using Master field: [txtBxLink]

In conjunction with the code I already had below, it worked!

Private Sub OfficeName_Click()

DoCmd.GoToControl "OfficeSubForm"
DoCmd.GoToControl "OfficeID"
DoCmd.FindRecord OfficeID

End Sub

​

I believe this will work with multiple state subforms, but it would be great if I could replace the DoCmd's with something a bit more efficient.

Any suggestions?

I have uploaded my latest attempt to http://www.camanda.com/Access/Sample2.mdb

Thanks for your help!

Caracarn