After updating a subform, how can I get the "Find Record" command button to search the Main Form?

[LBB]

Hi



After much searching and reading I think this could be something about GoTo, or SetFocus but I'm totally new to Access (and can't code) and am not really sure what to search for any more!

I have built a simple database for recording sports coaches and their sessions. I have two tables, one for coach details (Name, Address etc), and one for their sessions (Date of session, Number of players who attended, topic taught).

I've built a simple form/subform that brings up the coach name (mainform, stacked) and then all their session details (subform, tabular). The idea is to use this form every week to update the sessions subform with new sessions.

To make it more user-friendly I'm using the built-in "Find Record" command button to search for the coaches.

My issue is, once I've used "Find Record" to find a coach, and then updated the sessions in the subform, hitting that "Find Record" button again only searches the subform. How can I get the button, or something, to go back to searching the main form records? I essentially want a "Find Coach" button but don't know how to restrict where Access looks for records.

Hope I've explained that ok...

Thanks

[June7]

Did you click back to the main form first before clicking "Find Record"? I am not sure what you mean by 'built-in Find Record button' - do you mean Find & Replace? I build my own search functionality. Anything other than 'built-in' will require code. Examples:

http://www.datapigtechnologies.com/f...tomfilter.html

http://www.allenbrowne.com/ser-62.html

[LBB]

Hi June7

So when I add a Command Button, Access helpfully gives me a set of options - Save Record, Find Record, Go to New Record and so on. I wouldn't know how to build my own search box I'm afraid.

I can click on the Main Form and then do a search, but I am trying to not have to do that. I'm looking for a solution that cuts out that step.

Thank you

[June7]

I never use the code wizard and I don't use macros, only VBA.

Would need code that sets focus back to a control on the main form. Maybe in the subform AfterUpdate event.

[LBB]

Thanks, yes I read it might be something to do with the focus. Can you advise how I would do that as per your suggestion?

I never use the code wizard and I don't use macros, only VBA.

Would need code that sets focus back to a control on the main form. Maybe in the subform AfterUpdate event.

[June7]

Sorry, subform AfterUpdate won't be useful for this.

Is the subform set as Datasheet or Continuous view? You said 'sessions' - plural. This means multiple session records could be entered. How should Access know that you are done entering records and return to the main form?

[LBB]

The subform is in Datasheet view, and yes multiple session records can be entered.

I am trying to tell Access I'm done entering records and ready to search the mainform again by creating/using a button. At the moment the button I have only searches the "active" part of the form, in this case, the subform.

Sorry, subform AfterUpdate won't be useful for this.

Is the subform set as Datasheet or Continuous view? You said 'sessions' - plural. This means multiple session records could be entered. How should Access know that you are done entering records and return to the main form?

[June7]

I don't understand. If button is on main form to find a coach record on the main form, how could it be searching the subform?

Really need to know the code in use. If you want to provide db for analysis, follow instructions at bottom of my post.

[LBB]

It searches the subform because a record had just been entered into the subform.

As I said I don't know code, I am using Access's own function - you click an option in Form Design Tools to create a Command Button. You're then presented with a list of options for what the button does. I'm using the Action called "Find Record". When you use that button it looks like a standard Find/Replace dialog box, but it's not anything I have coded myself.

Hope that makes more sense? Apologies if I'm not explaining very well.

[Missinglinq]

Hope that makes more sense?

Not really; you say the Subform is in Datasheet View, then you speak of creating a Command Button. Although, while in Form Design View, you can add a button to a Datasheet View Form, at runtime the button will not be visible, which is why June7 (as well as myself) assumed the button in question was on the Main Form. And if the button is on the Main Form, I have to repeat June7's question:

If button is on main form to find a coach record on the main form, how could it be searching the subform?

That question is asked because the fact that you're clicking a button on the Main Form means that the Focus has now moved to the Main Form, and the Find Button should be searching in the Recordset associated with that Form, not the Recordset associated with the Subform.

Linq ;0)>

[LBB]

Thanks for your reply.

Sorry if I am not using the terms well, still learning!

Can you clarify, when you say is the button "on" the main form, it appears in the header, and the functionality is whatever the default functionality is.

Here is a screenshot of where the button "is"... hope it comes through, I have very limited internet.

From what I have read, I thought...
the main form is the Coach/Field/Division bit
the Coaches Sessions is a subform in Datasheet View
the binoculars is a Find Record Command Button

It's always visible whether I'm updating the top section or the subsection but when I'm in the subsection, clicking the button doesn't search the main form's table as I'd hoped.

If you need any more info please let me know!
Thank you.

Not really; you say the Subform is in Datasheet View, then you speak of creating a Command Button. Although, while in Form Design View, you can add a button to a Datasheet View Form, at runtime the button will not be visible, which is why June7 (as well as myself) assumed the button in question was on the Main Form. And if the button is on the Main Form, I have to repeat June7's question:



That question is asked because the fact that you're clicking a button on the Main Form means that the Focus has now moved to the Main Form, and the Find Button should be searching in the Recordset associated with that Form, not the Recordset associated with the Subform.

Linq ;0)>

[June7]

Suggest you provide db for analysis. Follow instructions at bottom of my post.