Control Tab Possibllities

[csolomon]

Hello:

I have something I would like to do and I was wondering if tab controls was the best route to accomplish it.

I have 5 categories of material types. I created the application from an excel sheet that had the categories listed. My boss would like my form to be as close as possible to the excel sheet. He would like for each material to be input by material type separately, which is currently not how I have it set up. currently, you select the matType and material in the same form, for all material types. I came up with the idea to use the tabs. I had an unbound text box at the top of the tab to have the user select the matType. My idea was that based on the matType selected, the continuous subform would populate with records related to that matType, in addition allow user to add data for that matType. Initially I thought I could have one form and have the data load based on the matType, however, I never was able to make it happen using one form. I now have a form representing all of my categories and the query that is the form's record source is filtered by the matType that the subform represents. Here is what a query for matType1 looks like:

SELECT MixSample.DM_Mix, MixSample.DM_MaterialNo, MixSample.matTypeID, MixSample.materialID, MixSample.matBatchWeight, MatType.matType, Material.material, Material.materialGrav, GetYield([MixSample].[matTypeID],[matBatchWeight],[Material].[materialGrav]) AS DMYield, MixSample.pigPercent, DLookUp("matPrice","MatPrices","materialID = " & [MixSample].[materialID] & " AND matPriceActive = True") AS _matPrice, GetMixCost([MixSample].[matTypeID],[matBatchWeight],[_matPrice]) AS DMMixCost FROM Material INNER JOIN (MixSample INNER JOIN MatType ON MixSample.matTypeID=MatType.matTypeID) ON Material.materialID=MixSample.materialID WHERE (((MixSample.DM_Mix)=Forms!F_Test2_MixDesign!DM_Mix) And ((MixSample.matTypeID)=1)); 

Additionally, the materialID combo box allows the user to select the material related to that matType they are using.

I have a few questions:

1) When all 5 categories of materials are input, I need a running total that will use different information entered. Since I have different information on different forms, on diff tabs, is this possible? I ask bc even when I had everything together, it was funky about my subtotals. I had to have a text box on the subform calculate it and then on the main form just copy from the subform text box.

2) I have been trying to make it so that when the user selects a matType (located on the subform), the subform and tab it sits on, go invisible with the exception of the selected tab...I have tried so many things, but nothing works. I use a select case statement to drive the tabs:

Tab Control Name: Tabctl1 (Access does not seem to recognize my tab control name)
Tab 0 Name: pgeOne
Tab 1 Name: pgeTwo
Tab 2 name: pgeThree
" 3 " : pgeFour
" 4 " : pgeFive

I have tried:

Private Sub matTypeID_AfterUpdate()
Select Case matTypeID

Case 1

Me.materialID.Requery
Me!TabCtl1.Pages(0).SetFocus

Case 2
Me.materialID.Requery
Me.Tabctl1.Pages.Item("pgeOne").SetFocus

etc...(I have not included the whole case statement). Neither format in case 1
or 2 works. I found this information here: http://bytes.com/topic/access/insigh...bs-tab-control

Will I be able to keep a total of the data I need, even though the calculations are stored in the footer of several forms and on the main form?

Please feel free to ask additional questions related to my issue. I appreciate any assistance.

[ChipR]

One thing you can do is set

Tabctl1.Pages(x).Visible = False

for all but one page. Then it will show that page!

[csolomon]

Hi Chip,

the 'x' represents the page name or index? I tried both and unfortunately, I can't get it to work.

The only one that should show up is called Cement

Here is where I tried to use the name of the index

Private Sub Form_Current()
TabCtl1.Pages(1).Visible = False
TabCtl1.Pages(2).Visible = False
TabCtl1.Pages(3).Visible = False
TabCtl1.Pages(4).Visible = False
End Sub
 

or this

Private Sub Form_Current()
TabCtl1.Pages(pgeTwo).Visible = False
TabCtl1.Pages(pgeThree).Visible = False
TabCtl1.Pages(pgeFour).Visible = False
TabCtl1.Pages(pgeFive).Visible = False
End Sub

I don't think it's recognizing my TabCtl1 control. How can I test this?

@ChipR

[ChipR]

Yes, I was referring to the numerical index, starting at zero, like your first code. You should also be able to use .Pages("pageName"), but I haven't tested that.

If TabCtl1 is not a valid control, you're going to get an error telling you such, either when you compile, or the Current() event runs. You might want to test with some code in a command button that you can click to run.

[csolomon]

Chip,

That button suggestion worked. Now I know that it can see my tabCtl1 bc when I pressed the button all of the tabs disappear. Why can't it run in the onCurrent() event?

Also, I am getting an error for a field value. I know I can print out values from my form in the immediate window, but I have not been able to produce the code to do it. For instance on my form I have a field txtWaterBW that I want to find out why it's not working so I typed:
Parent form: F_Test2_MixDesign
Subform: SF_CementType
textbox name: txtWaterBW

Here is what I put in the immediate window:
=Form!F_Test2_MixDesign!SF_CementType.txtWaterBW

what am I doing wrong?


@ChipR

[ChipR]

I guess the question is, Is the onCurrent event being triggered by something? It sounds like it just wasn't being run.

When you refer to a control on a subform, you have to use .Form, like:

...SF_CementTypes.Form!txtWaterBW

[ChipR]

I was able to use the tab code in the onCurrent event and trigger it by opening the form or moving to another record, so I'm not sure what the problem is there.

[csolomon]

Here is what I have in my on current event:

Private Sub Form_Current()
Select Case TabCtl1
 
'Not sure if I need Case 1, but I put it there bc when I didn't have it , when i triggered the oncurrent event by opening the form, I only saw page 0.  AT any rate, this still only shows page 0.
   Case 1
TabCtl1.Pages(1).Visible = True
TabCtl1.Pages(2).Visible = True
TabCtl1.Pages(3).Visible = True
TabCtl1.Pages(4).Visible = True
TabCtl1.Pages(0).Visible = True
 
   Case 2
TabCtl1.Pages(1).Visible = False
TabCtl1.Pages(2).Visible = False
TabCtl1.Pages(3).Visible = False
TabCtl1.Pages(4).Visible = False
 
   Case 3
TabCtl1.Pages(0).Visible = False
TabCtl1.Pages(2).Visible = False
TabCtl1.Pages(3).Visible = False
TabCtl1.Pages(4).Visible = False
 
   Case 4
TabCtl1.Pages(1).Visible = False
TabCtl1.Pages(0).Visible = False
TabCtl1.Pages(3).Visible = False
TabCtl1.Pages(4).Visible = False
 
   Case 5
TabCtl1.Pages(1).Visible = False
TabCtl1.Pages(2).Visible = False
TabCtl1.Pages(0).Visible = False
TabCtl1.Pages(4).Visible = False
 
   Case 6
TabCtl1.Pages(1).Visible = False
TabCtl1.Pages(2).Visible = False
TabCtl1.Pages(3).Visible = False
TabCtl1.Pages(0).Visible = False
 
End Select
End Sub
 

Let me explain why I am using this. ORiginally I tracked everything in one form, but my job asked me to allow the user to input each category of material in different places so now where I use to have 1 form, I have 5, representing one of the 5 categories of materials...one on each tab. So when the user comes to tab 0, they will only input info related to cement and on and on.

@ChipR

[csolomon]

I tried:

=SF_CementType.Form!txtWaterBW

In the immediate window and got this error: Compile error: Expected: line number or label or statement or end of statement.

@ChipR

[ChipR]

Your code doesn't make sense.

Select Case TabCtl1

This means, "Which tab is being viewed?" I think you want to Select Case based on some other variable, like the category, and show the appropriate tab.

To use the immediate window, do

?(statement to evaluate)

=(statement) is trying to assign the value in (statement) to ??? because nothing is on the left of the =.

[ChipR]

Always have a

Case Else
MsgBox "Unexpected Case"

[csolomon]

OK...I made the selection based on the matTypeID, as opposed to tabCtl1. I then put a dot to see if the matTYpeID was being passed when the page loaded and it was empty...so I am not sure how to proceed. Also, the unexpected case box popped up. I tried to troubleshoot the field value:

?Form!F_Test2_MixDesign!SF_CementType.Form!matTypeID

also:

?SF_CementType.Form!matTypeID

and got runtime error 424, Object Required.


@ChipR

[ChipR]

I'm not sure what the matTypeID is. You said you have a text box for the user to input the matType, and you want to show the appropriate tab according to that. Just make sure you have a default value in that conrol, or set one in the onLoad event, and you can select case on that. Unless I'm misunderstanding the way you want the form to function.

[csolomon]

matTypeID represents the different categories that can be selected from. Since I have a form for each category, the control source for each form is restricted to the matTypeID I chose as the criteria...for instance matTypeID 1, Cement, would return to the form all records where the matTypeID is 1. So the text box's default value already represents the matTYpeID. The user does not have to input. Maybe that's why it isn't working. I noticed that always tab zero is visible and the others are invisible.

You understand correctly. I just didn't think it would be this difficult to accomplish this.

@ChipR

[ChipR]

Think about what is determining which tab you should show (ignore for the moment what is on the tabs), and select case on that. It seems like the user should select a category from a combo box, and the afterUpdate of the combo box should have a select case on the value chosen to show/hide the tabs.

Also, in the Select Case, mine didn't work unless I used the syntax:

Case Is = 1
Case Is = 2
etc.

[csolomon]

I don't get it Chip.

I put an unbound combo box, combo10, on the form with the MatTypeIDs. In the after event update I have this:

Select Case matTypeID
 
   Case Is = 1
TabCtl1.Pages(1).Visible = False
TabCtl1.Pages(2).Visible = False
TabCtl1.Pages(3).Visible = False
TabCtl1.Pages(4).Visible = False
 
   Case Is = 2
TabCtl1.Pages(0).Visible = False
TabCtl1.Pages(2).Visible = False
TabCtl1.Pages(3).Visible = False
TabCtl1.Pages(4).Visible = False
 
   Case Is = 3
TabCtl1.Pages(1).Visible = False
TabCtl1.Pages(0).Visible = False
TabCtl1.Pages(3).Visible = False
TabCtl1.Pages(4).Visible = False
 
   Case Is = 4
TabCtl1.Pages(1).Visible = False
TabCtl1.Pages(2).Visible = False
TabCtl1.Pages(0).Visible = False
TabCtl1.Pages(4).Visible = False
 
   Case Is = 5
 TabCtl1.Pages(1).Visible = False
TabCtl1.Pages(2).Visible = False
TabCtl1.Pages(3).Visible = False
TabCtl1.Pages(0).Visible = False
 
   Case Else
MsgBox "Unexpected Case"
End Select

No matter what selection I make from the combo, it always says object required, error424. It points to the bold line in case Is =5

@ChipR

[ChipR]

That's because you're selecting case on matTypeID and not combo10.

[OldBirdman]

I think you need to STOP!

You are trying to code a problem that has not been clearly defined, and the rules seem to change with each post.

The first thing is table design.

I have 5 categories of material types.

Is this a separate table?

each material to be input by material type separately, which is currently not how I have it set up. currently, you select the matType and material in the same form, for all material types.

Within each category is a material type, then a material. 2 tables?

Second is the main form. At first glance, this is a typical cascading combobox design.

I came up with the idea to use the tabs. I had an unbound text box at the top of the tab to have the user select the matType.

This does not seem to be a tab problem, unless there is something not disclosed in the problem. Using an unbound textbox to select the tab (by typing the tab name into the textbox) isn't the way tabs should be used. Hiding the unused tabs then doesn't even allow the user to see their choices when entering into the textbox. Note: The code in Post #8 will hide ALL TABS after 2 calls to the subroutine.

But there are many forms here. What is the difference, and why multiple forms. How do they relate to the tabs.

So the text box's default value already represents the matTYpeID. The user does not have to input.

Huh? If 5 types, it should be right 1/5 of the time. Or is it bound to a table.

I want to know the table structures, and the controls on the form that control the data to be displayed. Whether the data display is on the main form, 5 additional forms, on subforms, or in the tab control is not important yet.

[csolomon]

You are trying to code a problem that has not been clearly defined, and the rules seem to change with each post.

How do the rules change w/ each post? IMO, Chip has been giving me info about what works and what doesn't, in addition to assisting me with figuring out what is wrong...although we haven't got there yet.

Note: The code in Post #8 will hide ALL TABS after 2 calls to the subroutine.

This explains why only one tab shows up!

  Within each category is a material type, then a material.  2 tables?

Yes, two tables One material type will have many materials

tblMatType
matTypeID
matType

tblMaterials
materialID
matTypeID
material

tblMixDesign: (One Mixdesign will have many materials and material types in the MixSample, which are all the ingredients which make up a sample of concrete)
DM_Mix
DM_SampleNo
DM_Date
jobNumber

tblMixSample
DM_MaterialNo
DM_Mix
materialID
matTypeID
matBatchWeight
pigPercent

I want to know the table structures, and the controls on the form that control the data to be displayed.  Whether the data display is on the main form, 5 additional forms, on subforms, or in the tab control is not important 

I have a main form, F_Test2_MixDesign
I have a tabControl, tabCtl1 that has 5 tabs that represent each category of material type. Each tab has a subform that represents one of the 5 categories.
subform 1, SF_CementType. subform 2, SF_CoarseType, subform3, SF_Fine, etc for Pigments and Chemicals (4 and 5).

Here are the controls on the form for the data to be displayed:
MaterialID - combo box
matBatchWeight, txt box
matGrav: txtbox
matPRice: txtbox
calculated fields: text boxes
unbound combo box, representing the matTypeID that I would like my tabs driven by
The controls used to display them are bound to the table(s).

But there are many forms here.  What is the difference, and why multiple forms.  How do they relate to the tabs.    Huh?  If 5 types, it should be right 1/5 of the time.  Or is it bound to a table.

My ultimate goal is to have the user input the materials and information related to it, specifically in areas for each material type. The difference in the forms is that the record source differs by material Type. I tried to do it using just one form but it didn't work, so I thought I needed a subform that represents each matType

Here is the record source:

SELECT MixSample.DM_Mix, MixSample.DM_MaterialNo, MixSample.matTypeID, MixSample.materialID, MixSample.matBatchWeight, MatType.matType, Material.material, Material.materialGrav, GetYield([MixSample].[matTypeID],[matBatchWeight],[Material].[materialGrav]) AS DMYield, MixSample.pigPercent, DLookUp("matPrice","MatPrices","materialID = " & [MixSample].[materialID] & " AND matPriceActive = True") AS _matPrice, GetMixCost([MixSample].[matTypeID],[matBatchWeight],[_matPrice]) AS DMMixCost FROM Material INNER JOIN (MixSample INNER JOIN MatType ON MixSample.matTypeID=MatType.matTypeID) ON Material.materialID=MixSample.materialID WHERE (((MixSample.DM_Mix)=Forms!F_Test2_MixDesign!DM_Mix) And ((MixSample.matTypeID)=Forms!F_Test2_MixDesign!SF_CementType.Form!Combo10)); 

Using an unbound textbox to select the tab (by typing the tab name into the textbox) isn't the way tabs should be used.

I meant unbound combo box, not text box

@OldBirdman

[ChipR]

Why did you change your record source to include the combo value? The record source for each subform should not change. You are only changing which one is visible.

You can show the appropriate subform either by stacking all the subforms on each other and show/hiding them, or by putting them on the tabs and showing the appropriate tab. This is controlled by the combo box the user can make a selection from.

This is actually much simpler than you think, but I don't really know how to explain it any differently.

Edit: combo box is bound to category types, not unbound

[csolomon]

I am absolutely sure this isn't difficult, I was am just having issues.
OK. I chose the method of using a subform on each tab.

My control sources for each subform are the same. It does not include the combo value
The combox controlling the tabs is bound to matTypeID

@ChipR

[ChipR]

Ok. Your record source in your previous post had Combo10 in it, so I'm assuming you fixed that. Your control sources for each subform should not be the same. What would be the point of having 5? There should be one subform for each category, and each has it's control source showing records of that category. You don't change the subforms' data, you change which is visible.

You should have code in the after update event of the combo box controlling the tabs and select case on the value of the combo box.

[csolomon]

Chip, I misunderstood this part:

Why did you change your record source to include the combo value? The record source for each subform should not change

So I changed it from what it was (each subform source had the appropriate matTypeid it represented).

Here is what is in the after event of the combo box:

Private Sub matTypeID_AfterUpdate()
Select Case matTypeID
   Case 1
      Me.materialID.Requery
TabCtl1.Pages(1).Visible = False
' TabCtl1.Pages(2).Visible = False
' TabCtl1.Pages(3).Visible = False
' TabCtl1.Pages(4).Visible = False
TabCtl1.Pages(0).Visible = True
   Case 2
      Me.materialID.Requery
TabCtl1.Pages(1).Visible = True
' TabCtl1.Pages(2).Visible = False
' TabCtl1.Pages(3).Visible = False
' TabCtl1.Pages(4).Visible = False
TabCtl1.Pages(0).Visible = False
   'Case 3
    '  Me.materialID.Requery
   'Case 4
    '  Me.materialID.Requery
   'Case 5
    '  Me.materialID.Requery
End Select
End Sub

@ChipR

[OldBirdman]

How do the rules change w/ each post?

Post #1 has a material category

I have 5 categories of material types

, but this went away, to return in post #8.

so now where I use to have 1 form, I have 5, representing one of the 5 categories of materials

Has 5 forms, not one as in Post #1.

Moving forward. Yes, ChipR was giving good code ideas. I posted because the thread didn't seem to be moving forward.

I guess you cannot hide all tabs individually on a tab control, so you are correct, one tab still shows. You would have to hide the tab control itself to hide all tabs.

The last post, #19,

I have a tabControl, tabCtl1 that has 5 tabs that represent each category of material type.

I am still not sure category is in the table structure. Are you using "material type" and "category of material type" to mean the same thing?

Not to deal with the display of data yet, I see a form, F_Test2_MixDesign, with a tab control, tabCtl1, on that form. The tabs would be labeled "CementType", "CoarseType",... "Chemicals". Selecting one of these tabs would display the data, for that material type, on the tab chosen.

I see an event subroutine tabCtl1_Change() that will eventually process your data, but for now might be

Private Sub tabCtl1_Change()
    MsgBox "Tab chosen is " & tabCtl1.Pages(tabCtl1).Name
End Sub
 

Am I correct so far?

[csolomon]

I am still not sure category is in the table structure. Are you using "material type" and "category of material type" to mean the same thing?

Yes I was...it's actually called matType, for material Type

I tried to use that Change routine you sent, but unfortunately, my matTypeID combo box is not allowing me to change it.
You are correct so far.

@OldBirdman

[OldBirdman]

I know it's difficult to communicate with 2 people simultaneously, but ChipR is working from a different perspective, so one of us will help get to a solution.

Not to deal with the display of data yet, I see a form, F_Test2_MixDesign, with a tab control, tabCtl1, on that form. The tabs would be labeled "CementType", "CoarseType",... "Chemicals". Selecting one of these tabs would display the data, for that material type, on the tab chosen. Am I correct so far?

You are correct so far.

I'm not sure why you need this combobox. Why can't you use the tabs of tabCtl1 and eliminate the combobox?

[csolomon]

You are right OldBirdman, it is but I am sure we will arrive at a viable solution.

Since matTypeID is a field in the table, wouldnt' I need to somehow connect the tab with what the matTypeID it represents??? If not, then the user will have to select the material type so the record in the table will be filled in. I think that would be redundant since the tabs suggest the matType, even though it's just a label?


@OldBirdman

[OldBirdman]

I would expect that the tab Captions have meaningful (to your users) names, such as Cement Type, Coarse Type, Fine, Pigments, and Chemicals. If the user wants "Pigments", they select the tab with that name.

Connecting this to a table comes later. For now, the form needs to determine what the user wants. If it cannot do that, you cannot proceed to the code to display data.

This is why I said "STOP". Step 1 is to determine what you need from your user, and how to get it. That is the purpose of your primary form. You can use a textbox, a combobox, a listbox, or a tab control to select a material type. That is your choice. You do not want both a combobox and a tab control to make your choice.

You wanted to display your data on a tab page. This is good. It means that your user makes a choice, and the name they clicked (the tab caption) is displayed as a heading for the data.

I still don't get why for the moment your form doesn't have 1 control, a tab control. This would have 5 tabs. For now, tab 0 would have a caption of "Cement Type" and a label on that tab page with a caption "Data for Cement Type will go here".

[ChipR]

@csolomon
The subform on each tab is set to show only the records with the matTypeID for that tab. It's hard coded, and doesn't change.

[csolomon]

I have cleared out all code telling my tabs to appear visible or invisible, yet when I open my form, it still only shows the one cement tab.

fixed this piece!

I still don't get why for the moment your form doesn't have 1 control, a tab control. This would have 5 tabs. For now, tab 0 would have a caption of "Cement Type" and a label on that tab page with a caption "Data for Cement Type will go here"

It does have 1 control, a tab control. it has 5 tabs. On each tab is a subform

I named my tabs: pgeOne, pgeTwo, pgeThree, pgeFour, pgeFive

@OldBirdman

[OldBirdman]

I have cleared out all code telling my tabs to appear visible or invisible, yet when I open my form, it still only shows the one cement tab.

I would guess that the .visible property has been saved as No. In design view for the form, check this property FOR EACH PAGE.

I named my tabs: pgeOne, pgeTwo, pgeThree, pgeFour, pgeFive

If the Caption for a page is null, the name is displayed. You don't expect your user to know what "pgeFour" is, so you think you need a combobox. Just add captions to your tab pages.

The code I supplied earlier can then be changed to

MsgBox "Tab chosen is " & tabCtl1.Pages(tabCtl1).Caption
 

[csolomon]

The caption for each page is not null. The names I provided are located in the property name section...the Caption says the matType.

I like that idea using the message box, bc it will let my users know (again) what they are putting in. Additionally, for the matTypeID, I just made the default value, the number representing the matTYpeID in each individual form.

As far as the footer in the form, if I want to refer to controls in the main form, would I do that this way:

[Form].[Parent]![Name of field]

with the instance above, the name of the field is Text28, but the field it represents in the form record source is called DM_waterRatio. I have tried it using

[Form].[Parent]![DM_waterRatio]

and

[Form].[Parent]![Text28]

Neither works.

Also, my form works with the exception of some of the materialIDs now being blank. what could be the cause of this?


@OldBirdman

[ChipR]

To refer to a control on the parent form, it should be Me.Parent!Text28

[csolomon]

I am on the parent form trying to refer to a control in the footer of the subform, in the expression builder. I didn't think the Me operator was used there.

This:

=[SF_CementType].[Form]![txtWaterBW]

Does not work in this form, although in the initial form it did.


@ChipR

[ChipR]

Try
= Me.SF_CementType.Form!txtWaterBW

[OldBirdman]

The caption for each page is not null. The names I provided are located in the property name section...the Caption says the matType

OK, now I don't understand this problem at all. I thought that each tab represented one material type. If true, how can a tab be "matType"?

I don't know what "the property name section" means in respect to the caption. In the Properties dialog for each tab control page, the "Format" tab has a "Caption" row. The "Other" tab has a "Name" row.

I like that idea using the message box, bc it will let my users know (again) what they are putting in.

Message boxes become annoying. Quickly. Although they never quit being annoying, they become ignored. The click-a-tab / click-a-OK becomes automatic. But it is your program.

Additionally, for the matTypeID, I just made the default value, the number representing the matTYpeID in each individual form.

Are we dealing with the display of data here? What is the "default value" of an item of data? Are we back discussing the 5 forms?

Programming requires careful accuracy. Discussion of a program must also be precise to avoid confusion. This discussion keeps changing the way things are identified. "material type" and "category of material type" mean the same thing in this discussion, but imply that the material types can be classified within categories. You made the default value for "matTypeID" to?

tblMatType
matTypeID
matType

Isn't matTypeID your primary key (PK)? How can it have a default value?

I have something I would like to do and I was wondering if tab controls was the best route to accomplish it.

This thread started with the idea of using a Tab Control to display and/or edit data. Are you abandoning this idea, or do you want to pursue it? To pursue it with me, I need to follow you, and you need to follow me. If you want to go it alone, fine with me.

[csolomon]

I apologize BirdMan, I meant each tab represents a matType, not each one says MatTypeID. I was referring to the property window...I think I have it set up correctly. When the msgbox comes up, it shows the Other name in the property window, which is the caption on the tab.

Message boxes become annoying.  Quickly.  Although they never quit being annoying, they become ignored.  The click-a-tab / click-a-OK becomes automatic.  But it is your program.

I can agree with you there...the problem is my job :) that's what they want. This form had to be re-designed (dumbed down is what they wanted) so that everything was entered separately, as opposed to how I had it, where the user just (using cascading combos) selected his matTypeID and then materialID and so on.

You made the default value for "matTypeID" to?    Isn't matTypeID your primary key (PK)?  How can it have a default value?
 
This thread started with the idea of using a Tab Control to display and/or edit data.  Are you abandoning this idea, or do you want to pursue it?

I was actually talking about the default value in the record source for the form. In the query I just for each form, specify

And MatTypeID = X

. I am still using the forms on each individual tab, so no, that method is not abandoned.

Are we back discussing the 5 forms?

I am using 5 forms currently. If there is a way that I can do this using one form, I am completely open to hear it...I just wasn't successful at it using one form.


@OldBirdman

[OldBirdman]

OK - Why don't we keep each step short and easy? That way we don't have to keep looking at all 30+ posts to compose a response?

If this is OK, I would like to start with the tab control, as this is the place your users select what they want to see/edit/work with.

Each tab has a Caption and a Name. The name should make it easy for YOU to use to reference your tables. mat1, mat2, comes to mind, where the digit is the same as a matTypeID in table = tblMatType. Each caption should be meaningful to a new user, such as Cement Type, Coarse Type, Fine, Pigments, and Chemicals. The word "Material" is redundant here.

You might consider renaming the tab control to something better, like tabMaterials, but that is up to you.

For the moment, the MsgBox is for debugging, but you can modify it later for confirmation later, as you require.

 MsgBox "Tab Name=" & tabCtl1.Pages(tabCtl1).Name & " Caption=" & tabCtl1.Pages(tabCtl1).Caption

Can we get this much working?

[csolomon]

OK. That much is working.

[OldBirdman]

Good!
So we have a tab control, and we know, in our code, the matTypeID, which we can use in table = tblMaterials.

As this is the simplist of the tables, except tblMatType, lets just se what we can do with it.

For the moment, can you create a subform, on your form but NOT in the tab control, that displays this data. I think you are probably using datasheet view, but maybe not. I will need to know that.

With our new one-step-at-a-time policy, don't consider any filtering. Just put a subform on your form that displays ALL of this table, unsorted.

Let me know!

[csolomon]

OK...one step at a time.

I have created the form as you asked, unfiltered and on the parent form, with out the tab. I am using a continuous form, but I may have to change it to datasheet view since the job wants it to look like an excel sheet.

[OldBirdman]

I'm having trouble with my Office programs at the moment, so any code/sql I post may be unchecked for syntax. You may have to debug syntax, but not logic.

Private Sub tabCtl1_Change()
Dim strSQL as String
Dim iTab as Integer
    MsgBox "Tab chosen is " & tabCtl1.Pages(tabCtl1).Name 
    iTab = Mid(tabCtl1.Pages(tabCtl1).Name, 4, 1)
    strSQL = "SELECT * FROM tblMaterials WHERE matTypeID=" & iTab & " ORDER BY material;"
    Me!YourSubformName.Form.RecordSource = strSQL
End Sub 

I'm assuming that your tab page names now contain the matTypeID in them. The above code assumes in position 4, i.e. "mat3".

[csolomon]

I'm assuming that your tab page names now contain the matTypeID in them.  The above code assumes in position 4, i.e. "mat3"

Yes I have relabeled them. Should this include a line for the other 4 tabs?

Additionally, what you said works, however in my original record source, I had some calculations being performed. those now have errors. How can I include those calculations in the str SQL?

@OldBirdman

[OldBirdman]

Yes I have relabeled them.

ReNAMED them?

... in my original record source, I had some calculations being performed. those now have errors. How can I include those calculations in the str SQL?

One step at a time, remember?

Should this include a line for the other 4 tabs?

You now have a subform that responds to your tab control? Yes or No? If you click the Pigments tab, does your subform displays the records from tblMaterials that refer to Pigments? Please ignore all controls except the new subform and the tab control. Does the data in this subform change when the tab page changes?

To make communication easier, I would like to know:

Tab Control Name:
Tab page 1 Name:
Tab page 2 Name:
Tab page 3 Name:
Tab page 4 Name:
Tab page 1 Caption:
Tab page 2 Caption:
Tab page 3 Caption:
Tab page 4 Caption:
Main Form Name:
SubForm Name:
 

[csolomon]

# Tab Control Name: tabctl1
# Tab page 1 Name:mat1
# Tab page 2 Name:mat2
# Tab page 3 Name:mat3
# Tab page 4 Name:mat4
# Tab page 5 Name:mat5
# Tab page 1 Caption:Cement
# Tab page 2 Caption: Fine
# Tab page 3 Caption: Course
# Tab page 4 Caption: Pigments
# Tab page 5 Caption: Chemicals
# Main Form Name:F_Test2_MixDesign
# SubForm Name:SF_Test_MixSample

You now have a subform that responds to your tab control? Yes or No? If you click the Pigments tab, does your subform displays the records from tblMaterials that refer to Pigments? Please ignore all controls except the new subform and the tab control. Does the data in this subform change when the tab page changes?

No. when I switch tabs, the only thing that changes are the records within the tab control. The new subform does not ever change

[OldBirdman]

After the program is running, put a breakpoint on the "End Sub" (line 8) of the tabCtl1_Change subroutine, and determine iTab, strSQL, and Me!SF_Test_MixSample.Form.RecordSource

This can be done by entering ?iTab into the immediate window. Then do ?strSQL Then do ?Me!SF_Test_MixSample.Form.RecordSource

What do you get?

[csolomon]

After the program ran, I put a breakpoint on line 3 of the tabCtl1_change subroutine. Here's what happened in the immediate window:
?iTab: 2
?strSQL:

SELECT * FROM Material WHERE matTypeID=2 ORDER BY material;

?Me!SF_Test_MixSample.Form.RecordSource:

SELECT MixSample.DM_Mix, MixSample.DM_MaterialNo, MixSample.matTypeID, MixSample.materialID, MixSample.matBatchWeight, MatType.matType, Material.material, Material.materialGrav, GetYield([MixSample].[matTypeID],[matBatchWeight],[Material].[materialGrav]) AS DMYield, MixSample.pigPercent, DLookUp("matPrice","MatPrices","materialID = " & [MixSample].[materialID] & " AND matPriceActive = True") AS _matPrice, GetMixCost([MixSample].[matTypeID],[matBatchWeight],[_matPrice]) AS DMMixCost FROM Material INNER JOIN (MixSample INNER JOIN MatType ON MixSample.matTypeID=MatType.matTypeID) ON Material.materialID=MixSample.materialID WHERE (((MixSample.DM_Mix)=[Forms]![F_Test2_MixDesign]![DM_Mix])); 

@OldBirdman

[OldBirdman]

So you didn't use my code, line 7, because otherwise Me!SF_Test_MixSample.Form.RecordSource should be identical to strSQL

What is the complete code for Private Sub tabCtl1_Change() ?

[csolomon]

You are right BirdMan, I had the subform on the tab control name as opposed to the new subform name.

Now, when I select a tab, the subform changes.

Private Sub tabCtl1_Change()
Dim strSQL As String
Dim iTab As Integer
MsgBox "Tab chosen is " & TabCtl1.Pages(TabCtl1).Name
iTab = Mid(TabCtl1.Pages(TabCtl1).Name, 4, 1)
strSQL = "SELECT * FROM Material WHERE matTypeID=" & iTab & " ORDER BY material;"
Me!SF_Test_MixSample.Form.RecordSource = strSQL
End Sub

@OldBirdman

[OldBirdman]

OK, Good!

One step at a time:
1) Make a copy of this database.
2) Remove all controls except tabctl1 and Name:SF_Test_MixSample
a) Be sure that ALL tab pages are blank (NO CONTROLS)
3) Set tabctl1.BackStyle = Transparent
4) Resize tabctl1 so it is wide enough for SF_Test_MixSample
5) Move SF_Test_MixSample to be on top of tabctl1
a) Do not Cut/Paste SF_Test_MixSample into tabctl1 or onto a page
b) Use drag/drop only
6) Switch to Form View, and click one of your tabs
a) Can you see your subform?
b) Does it change correctly?
c) Can you select a record in the subform?
d) Can you edit a field in a record in the subform?

Let me know. If any problem, which step fails?