Main Form SetFocus SubForm SetFocus Main Form

[LeonS]

Hello! I have a main form (Contacts) based on the main table (Master1). The form has many fields.
I have another table (Companies) which has a couple of fields. I am adding 2 of the Company fields into the main form via a SubForm (WebsiteSubF).
The SubForm is in between the fields on the main form. Between "Email" and "Industry".
Thus, I want to tab through the fields, as if it were one form.
The previous field in the main form is "Email".
The first field on the SubForm is "Company"
The second field on the SubForm is "Website"
The next field on the main form is "Industry".
I want to tab from the main form (Email) to the SubForm (Company), tab to the next SubForm field (Website) and then tab back to the main form into the "Industry" field.
My research has given me this code

Me.Form!WebsiteSubF.Company.SetFocus

for moving from Email to Company. The code is in the LostFocus Event Procedure of Email.


but it does not work. Access tells me that it cannot find the form. I have tried various combinations, to no avail. What do I need to change or add to make it work. I appreciate that there will be 1 line of code for each LostFocus Event Procedure in each of the fields. Many thanks - Leon

[Welshgasman]

I would expect you have to reference the subform control .Form.Control property to get into the subform control and then Me.Parent.Control to get back into the mainform control next.?
http://access.mvps.org/access/forms/frm0031.htm

[LeonS]

Welshgasman - thanks. You are right, of course! I have tried various sets of code and I really thought I was on to something when I found the "main form - Subform - main form route being recommended. I tried and tried but got nowhere. I will go back to researching that route.
Thanks for the link to Cross Posting. I do not cross post. I have only one Access Forum, and that is this. The rest of my research is via Google and the many YouTube videos. Cheers, Leon

[Welshgasman]

Good for you, however crossposting per se is not frowned upon. It is not mentioning it, that is.

You can always use the builder I believe, to get the correct syntax.

Good luck with it.

[LeonS]

Welshgasman - thanks !! Yes, I could try the Code Builder if I could get it to open!! I have been avoiding it most times, now I want it, it is elusive. When I do find it it just takes me to the Event Procedure. When I actually need it to help me. I forget who's law it is, but it is not mine!!
The code that I have been using has been obtained from MS articles. You would think it would work.
I think the answer is that I HAVE to close the form before I can delete it. The simple DoCmd.Close etc does not work!! I will research another way to CloseForm!! Cheers, Leon

[mike60smart]

Hi Leon

On your Main Form (Contacts) you need to set the Tab order so that it Tabs from "EMail" to the Subform "WebsiteSubF"

Then in the "On Exit" event of the Control "Website" you need the following:-

 Parent.SetFocus
    Parent.Industry.SetFocus

[LeonS]

mike60smart - thanks for that. I will try this afternoon (= now). That also might give e clue as to how to get on the SubForm from the Contacts (Parent) form. Leon

[LeonS]

Mike60Smart - I have been working on that, with little success. The debugger does not like the line Parent.Industry.SetFocus. The main/Parent form is called Contacts. The SubForm is called WebsiteSubF. The website control is called WebsiteSub. This is the code I am using in the OnExit control WebsiteSub.

Private Sub WebsiteSub_Exit(Cancel As Integer)


 Parent.SetFocus
    Parent.Industry.SetFocus


End Sub

[Welshgasman]

That would be Me.Parent.

[ArviLaanemets]

What you want to do feels weird! Instead I'd advice:

a)
You have a table like tblCompanies: CompanyID, CompanyName, CompanyWebsite, ...;
You have a table like tblContacts: ContactID, ContactPerson, ..., ContactMail, CompanyID, Industry, ...
You have a form based on tblContacts. The control linked to CompanyID field of tblContacts will be combo box with RowSource as "SELECT CompanyID, CompanyName[, CompanyWebsite] FROM tblCompanies ORDER BY 2", ColumnCount = 2[or 3] and ColumnWidths like "0;2.5[;0]" (in [] are optional parts of query]. User selects a company from combo - company id is stored, but it's name is displayed instead.
You add an unbound text box control to your form, which reads company website from tblCompanies and displays it - using a DLOOKUP() function, or reading 3rd column from combo box (in case you did go for optional column there.

Or
b)
You create a main single form based on tblCompanies, where you register general company info.
Into Details part of companies form, you add a continuous subform based on tblContacts, with all info about specific contact. In subform properties, you set properties LinkMasterFields = "CompanyID" and LinkChieldFields = "CompanyID" (in case Access didn't do this automatically when you dragged contacts form into companies form to create a subform).
In contacts subform, you hide the CompanyID control (and delete it's header), and place it somewhere where it doesn't affect you designing the rest of form.
Now, the subform fill always display all registered contacts for active company in main form. And whenever you add a new contact, it's linked to active company automatically.
In case you need a full list of contacts too, you can create a saved query like "SELECT cont.*, comp.CompanyWebsite FROM tblContacts cont LEFT JOIN tblCompanies comp ON comp.CompanyID = cont.CompanyID", and use this query as source for report, or for read-only form.

[LeonS]

Welshgasman - thanks for the suggestion! I have made the change you suggested. Here is the resukt, following the debugger saying "No".

Private Sub WebsiteSub_Exit(Cancel As Integer)


    Me.Parent.SetFocus
    Me.Parent.Industry.SetFocus


End Sub

Except the field

Me.Parent.Industry.SetFocus

is in yellow!

Leon

[isladogs]

Just for info
1. The Me. syntax can be omitted
2. You need to use Form. after Parent. and before Industry so you can then reference the parent form controls
3. You don't need to set the focus to the parent form itself. The line Parent.Form.Industry.SetFocus should work on its own

[LeonS]

Isladogs - thanks and I made the changes. It now reads

 Parent.Form.Industry.SetFocus

, with the Debugger reading "does not support this property or method" and showing the above code in yellow.
Someone suggested that the focus would go from the previous control (Email) on the main Contacts form, directly to the first control (Company) on the SubForm. Pressing Tab would move the focus/cursor to the next control (Website) on the SubForm. The code we have been trying to achieve would then move the cursor to the next field (Industry) on the main form (Contracts).
The cursor does not move from the main form (Contracts) control (Email) to the SubForm control (Company). Instead it jumps over the SubForm, going to Industry and missing out the SubForm.

[isladogs]

Just to confirm - you want to add code to the subform which will move the focus to the control on the main form
The attached example does exactly that.
Double click any record in the subform Postcode field to move focus to the main form Postcode control

Exactly the same code should work for you...unless perhaps you are using a 'special' form such as a Navigation form

However I wouldn't use the Exit event of the subform. That might well cause issues with using Parent. syntax.
Use a click or double click event on a suitable subform control

[Welshgasman]

Colin,
That does go to the main form postcode control, but jumps straight back to the subform control?

OK, I have got it to stick in the main form control, but have no idea as to how.?