MS Access Tab switching

[Tekture]

I hope this is being posted in the right place.
I have a search form that uses the "details" field to populate the search results. The search form is the 4th tab in a 4tab group. In the search result, I have a field named "JobID". I am trying to make it so I can click on that search result, pull that JobID and open up that job on my 2nd Tab (Jobs). To me, this sounds pretty simple to accomplish, but i'm no guru in VB. Can anyone scratch out a simple piece of code that will accomplish this? If not, can anyone point me to the VB commands I need to use and I will try to research it so I can at least build this one expression.

The field: JobID is found on both the Search and Jobs tab.

 

[RuralGuy]

Me.TabCtlName = 0 using your TabCtlName of course, will switch to the 1st tab.

 

[Tekture]

OK, so I would assume the code needs to resemble:

Onclick (for the textbox i'm wanting to call)
call that textbox (JobID)
Me.TabCtlName=0 (change tabs)
filter by JobID=What i called.

Does that seem about right?
To bad VB can't read layman!

Thanks for the help RG

 

[RuralGuy]

I do not know how you make your forms run. Maybe if you supplied some specific details on what you need to do manually, we could offer some useful advice on how to automate it.

 

[Tekture]

Hrmm... OK.
Its a simple database of previously listed jobs and open jobs with reports showing which are open, how long they were open for, etc, etc. The search form that I am using is an adoption of free code supplied at: "Allenbrowne.com/ser-62.html"
My final version looks very similar. If you see the picture on that site, it uses the details part of the form to make a "list" of results. Within that list i have put a "JobID" field. I want to be able to click that JobID field for whichever job is shown (as a result) and have it bring me to another tab where i view that one specific job.

I only have 1 main form where the tab function is located. It's called hrForm.
The tab with the Jobs page has a subform called hrJobs.
The tab with the Search page has a subform called hrSearch.
Both subforms have the field: JobID

Hopefully that is a clear description and will offer you enough details.

 

[RuralGuy]

It sounds like you want to put the focus in or double click in a control in a SubForm on a tab and then move to a different tab that contains a SubForm and select a record on that SubForm. Is that an accurate discription?

 

[Tekture]

yes RG, that is an accurate description.
The name of the table that the "Jobs" subform uses is called "Jobs"
I do not use a query, it's all done through the code.

 

[RuralGuy]

Is there a JobID field in the Jobs table? Have you tried using the LinkChild/MasterFields properties of the SubForm control?

 

[Tekture]

I hadn't known about that function, but I am reading up on it now. I first got the error that my form was unbound. The main form that has the tabs is not linked to anything, only the subforms within the tabs are.

I now linked my main form to the same database (Jobs) but I will have to wait untill i get home to alter the search form. My database is in pieces at the moment.

Do you still think this is the best approach?
Thanks again for all your help RG.

Edit: After thinking about this, I am going to have 2 search forms. One for Jobs, the other for Employees. So, I don't think this method will work.

 

[Tekture]

So, is there no other easy way to accomplish this? Anyone know?

 

[Tekture]

The only problem i am trying to fix is this:
I have a table which uses the field "job ID" (autonumber) that does not match the record ID at the bottom of the form (because some records are deleted in between) Now I built this spiffy search form so people can find names faster, but the form is useless if i can't get them from the search form to the regular form. Can you help me come up with a simple fix for this untill i can figure out how to do what i want to do?
Can i reference the "Record ID" in my search form? Then people can see that and go to the normal form and just punch in the record number at the bottom and poof, they are there.

Ideally i want them to just click on the search form and have them sent to the other tab with that record they selected being displayed. In order to do that, some other people at this forum have helped me come up with this code:

Private Sub JobID_Click()
tabControl1.Value = 1
Dim SQL As String
SQL = "SELECT * FROM tblJobs WHERE LastName = '" & LastName.Value & "'"
Me.subform1.Form.RecordSource = SQL
Me.subform1.Requery
End Sub


But it doesn't work.
Any ideas?

 

[RuralGuy]

What is the name of the SubFormControl? I doubt it is named SubForm1 but I could be mistaken here. Do you want to be on Tab #2 of your Tab control?

 

[Tekture]

tabPublisher = name of tab control on main form
hrMain = name of main form
hrJobs = name of subform located on tab 2
hrSearch = name of subform located on tab 4
JobID = (autonumber) key for tblJobs. Also the name of the control that I want to click on in the hrSearch subform.

When trying to use the code i posted. It goes to a debugger and highlights the very first line.

it also appears that it's not reckognizing "tabPublisher" since it is on the main form and not the subform where the "Click" is being done.

 

[RuralGuy]

Even though Access seems to prefer a control having the same name as the field to which it is bound because that is the default name, it is strongle recommended that you not leave it that way. Add a prefix such as txt for a TextBox or cmd for a CommandButton to differentiate the two objects and eliminate any ambiguity in the names. You say that tabPublisher is not recognized by your code but I do not see where you are using that name in your code. Maybe you need to publish the code again in a post.
A form is displayed on another form by means of a SubFormControl. This SubFormControl has a name of its own that need not be the same as the form it is displaying. You know you are looking at the SubFormControl property sheet when the Data tab shows the LinkChild/MasterFields properties. It is the name of the SubFormControl and *not* the form it is displaying that is required to reference a control on the SubForm.

 

[RuralGuy]

BTW, here's a link for your favorites that is very helpful with the syntax needed to reference controls/forms and SubForms.

 

[Tekture]

Ok, i believe i have what you're asking for now.

The tabs are as follows:
tabPublisher = the name of the tab control
sfHelp = name of control on tab 1
tabHelp = name of the actual tab that controls tab1
sfJobs = name of control on tab 2
tabJobs = = name of the actual tab that controls tab2
sfEmp = name of control on tab 3
tabEmp = = name of the actual tab that controls tab3
sfJobSearch = name of control on tab4
tabJobSearch = = name of the actual tab that controls tab4
sfEmpSearch = name of control on tab5
tabEmpSearch = name of the actual tab that controls tab5

disregard the changes i have made to the original code given. I have been making changes back and forth trying to understand what it is that MS Access doesn't like. I am assuming that "tabControl1" should really be "tabPublisher" as well as "subform1" should be "sfJobs"?

 

[RuralGuy]

This is what the code would look like if it were on the MainForm somewhere.

Private Sub JobID_Click()
   Me.tabPublisher = 1
   Dim SQL As String
   SQL = "SELECT * FROM tblJobs WHERE LastName = '" & Me.LastName & "'"
   Me.sfJobs.Form.RecordSource = SQL
End Sub

 

[RuralGuy]

In Post #13 you said the "Click" in on a SubForm. Which one?

 

[Tekture]

Ideally, one would click the control named "JobID" on "sfJobSearch" and be moved to Tab2, or "sfJobs", the subform on Tab2 is called hrJobs and the table that is bound to the hrJobs subform is called "Jobs".



Thanks for your help RuralGuy.

 

[RuralGuy]

OK, give this a try in your control.

Private Sub JobID_Click()
   Dim SQL As String
   SQL = "SELECT * FROM tblJobs WHERE LastName = '" & Me.LastName & "'"
   Me.[b][COLOR="Red"]Parent.[/COLOR][/b]sfJobs.Form.RecordSource = SQL
   Me.[b][COLOR="Red"]Parent.[/COLOR][/b]tabPublisher = 1
End Sub