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_After Update()
Select Case matTypeID

Case 1

Me.materialID.R equery
Me!TabCtl1.Page s(0).SetFocus

Case 2
Me.materialID.R equery
Me.Tabctl1.Page s.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]

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 misunderstandin g 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...althoug h 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_MixDesi gn
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