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]

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("pageNam e"), 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_MixDesi gn
Subform: SF_CementType
textbox name: txtWaterBW

Here is what I put in the immediate window:
=Form!F_Test2_M ixDesign!SF_Cem entType.txtWate rBW

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_CementTyp es.Form!txtWate rBW

[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 =.