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?

One thing you can do is set

Tabctl1.Pages(x ).Visible = False

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

[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?

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.

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

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]

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.

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]

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.

Always have a

Case Else
MsgBox "Unexpected Case"

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

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.

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