Subform & Tab Control - Not sure if it is possible??

nancybrown

Well-known Member
Joined
Apr 7, 2005
Messages
868
I have a main form with a cbo box with two subforms, one for contributions and the other for deals . . . all linked to the cbo parent. I want to have
an additional form using the Tab Control format. Is there a way that when the results return for deals suborm, the user can double click on a single line item
to open the tab control form for additional deal details for that line item only . . all subforms resources are queries.
 

Some videos you may like

Excel Facts

Copy PDF to Excel
Select data in PDF. Paste to Microsoft Word. Copy from Word and paste to Excel.

Micron

Well-known Member
Joined
Jun 3, 2015
Messages
2,019
Office Version
  1. 365
Platform
  1. Windows
I want to have an additional form using the Tab Control format. Is there a way that when the results return for deals suborm, the user can double click on a single line item to open the tab control form for additional deal details for that line item only . . all subforms resources are queries.
Your post is not clear to me. Reads like you want a record double-click to open another form that has a tab control on it. The subject title suggests you want another subform on the main form that already has two subforms; the 3rd one to have a tab control. Could you clarify what you have and what you need?
 

nancybrown

Well-known Member
Joined
Apr 7, 2005
Messages
868
I'm not seeing where my title suggested another sub to the main form, sorry if it read that way to you.

What you've said below is correct. My main form has a cbo box that drives results on two separate subforms on the main form . . . both subforms are query driven are in spreadsheet view, call one: sfrmContributions; call two: sfrmDeals.

I have another subform that is in not on the main form and in tab-control format, call it "sfmDealDetails. I would like the user to be able double click on a single line item in sfrmDeals that will open sfrmDealDetails matching the cbo selection from the main form.

QUOTE=Micron;4452895]Your post is not clear to me. Reads like you want a record double-click to open another form that has a tab control on it.
 

nancybrown

Well-known Member
Joined
Apr 7, 2005
Messages
868
So sorry, I have to correct myself, my form is set to continuous view not spreadsheet, not sure that matters, but wanted to clarify.
 

Micron

Well-known Member
Joined
Jun 3, 2015
Messages
2,019
Office Version
  1. 365
Platform
  1. Windows

ADVERTISEMENT

I'm not seeing where my title suggested another sub to the main form, sorry if it read that way to you.
The reason would be that a subform is a form that resides in a subform control that has to be on a main/parent form. So to ask if it's possible to have a tab control on a subform (Subform & Tab Control - Not sure if it is possible??) the answer is yes. However,
double click on a single line item to open the tab control form
suggested another form was to be opened, not a subform. Hence my guess. Yet you are calling that form a subform...
I have another subform that is in not on the main form and in tab-control format
and I don't understand the notion of "tab-control" format other than to guess it is a form with a tab control on it.
What you've said below is correct. My main form has a cbo box that drives results on two separate subforms on the main form . . . both subforms are query driven are in spreadsheet view, call one: sfrmContributions; call two: sfrmDeals.
So I will assume you want a double click event on a subform on the main form to open another form to display records linked to the combobox control on that main form.
First, identify the primary key of the sub form (which links it to the main form). That field should also be the primary key of the recordset for the other form you want to open. Next, because you are using datasheet (not spreadsheet) view, you will have to decide whether to enforce the user to click on a specific field, or allow any field to accept the double click event, or force user to click on the record selector (left side of record row). If record selector, you can use the subform's double click event. If only one field, you will have to indicate which and use that field/control's double click event. To allow any, you will have to use the dc event for each control of your datasheet. In the later case, rather than repeat the same code several times (a pain if you have to modify it) I would create a form level function that can be called by the double click event of ANY of the subform datasheet controls. You would simply type in the function name in the dc event to affect the function call. This function would look like
Code:
Private Function OpenDealDetails()
DoCmd.OpenForm "thirdFormName",,,"[DealDetailsID] = " & Me!DealID
End Function
where DealDetailsID is the primary key on the third form and DealID is the primary of the main form's subform, which also happens to be the primary of the main form ID. In effect, you are simply linking the third form to the subform the same way the subform is linked to the main form. You may have noticed that I omitted any reference to the combo box. The method I describe is more reliable because I do not know anything about the combo's row source. It could be a value list or have its allow edits or list additions properties set in such a way that a user can change what's displayed, then invoke the double click event. You could end up trying to load records that don't exist into the third form.

No slight intended, but I think you may benefit from boning up on terminology since many of the names you use to describe your db objects don't really exist. It would help anyone trying to help you.
 

Micron

Well-known Member
Joined
Jun 3, 2015
Messages
2,019
Office Version
  1. 365
Platform
  1. Windows
I think you can appreciate how long it took to compose my answer, which was based on a datasheet view, so I didn't see your next post first.
However, I think it will not matter, assuming you will put the function call on every control on the record. It's even easier to pinpoint a particular control with a label if you're using a continuous form.
 

xenou

MrExcel MVP
Joined
Mar 2, 2007
Messages
16,793
Office Version
  1. 2019
Platform
  1. Windows

ADVERTISEMENT

Just thinking out loud but:
1) in main form have a hidden field
2) in subform on the Current event run a small bit of code to update the hidden field in the main form to the value of the primary key in the subform (this will fire whenever a record is selected in the subform)
3) have the stuff in the tab control that you are interested be a subform dependent on the main form but use the hidden field for the parent-child relationship.

So all you have to do is write the double-click stuff to get to the tab control (or just let the user go their on their own without the double clicks). It might be more efficient in terms of form performance to only update the data in the tab control when the user actually gets their but I would only worry about that if performance is bad already.

Don't ask me how to do any of this. I don't actually design forms unless I get paid for it - too much work involved. Plus I find MSAccess tab controls very ugly and don't use them (not speaking of tabs in the Access 2007+, just tab controls on traditional windowed forms. Unless they have made them better in newer versions). Unlike tabs in Excel userforms which are much cleaner.
 
Last edited:

nancybrown

Well-known Member
Joined
Apr 7, 2005
Messages
868
Thanks to both of you . . . I don't like tab control either, but it's a format used in another database that users are comfortable with . . . this team does not like change so I'm trying to get as close to what they're used to working in . . . (not an easy task).

Micro, I do greatly appreciate that time you put into your answers. Thanks again.
 

Micron

Well-known Member
Joined
Jun 3, 2015
Messages
2,019
Office Version
  1. 365
Platform
  1. Windows
You're welcome. I don't find them so visually un-appealing since it's simply a matter of personal preference. I tend to overlook the aesthetics sometimes because my major customer was always my employer, and practicality, ease of use and speed of project deliverables sometimes trumps aesthetics. Not that I never considered the look of things, just that form was more important than style. My major use of tab controls was to compartmentalize things like setting database/user options, not for displaying records, so even when present, their use was limited. If the look of the standard tab control doesn't thrill you, try replacing the tabs with the built in buttons, or substitute command buttons, labels that are raised/sunken, image controls, etc. or form border styles, underlying rectangles (again, raised, lowered, shadowed, etc.). You can put lipstick on a pig but it will take more coding work. ;)
 

nancybrown

Well-known Member
Joined
Apr 7, 2005
Messages
868
I'm making it as "pretty" as possible. Right now, this employer is using many excel spreadsheets for entering various information related to various projects . . . even though they are separate projects, any of the spreadsheets contains 80 percent of the same data, a lot of redundancy. We also have a master database (so to speak) that we inherited from somewhere (no one really knows from where) but we create deal records where most of the information is currently contained all of these various excel spreadhseets. To search the master database, the city, county or school name is entered. All deal activity for that particular county, city, school returns in the form of a datasheet (not spreadsheet :)). Double-clicking any line item of interest opens up a form with a tab control, so I'm trying to mirror my process the same way, by downloading the data from the master database and importing into Access with additional fields not contained in the master database, so there is one comprehensive database that covers various projects. Selling the idea to put various projects currently in various excel spreadsheet into a database is going to be a trick . . . very old school. Thanks again for your help.
 

Watch MrExcel Video

Forum statistics

Threads
1,122,805
Messages
5,598,173
Members
414,217
Latest member
Mystix

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top