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.

[csolomon]

I was able to do everything listed, with no issues. I only tried to edit a record, not add one...that works.

@OldBirdman

[OldBirdman]

Except that your subform is limited in the fields it displays, does your tab control work correctly as you wanted in post #1?

Also, do we meet the desire that there only be one subform?

I see 3 issues left:
1) Complete testing in this limited version
2) Convert to the full subform display
3) Add "Footer" and get it working

Do you have anything to add to this list?

[csolomon]

Yes it does! And using one form!!!

I noticed that the first time I go into design view, I see all my calculations, but when I change tabs, they say #NAME?

Those are the only issues left. The footer's calculations only are used for the cements values. I have a lot of unbound text boxes, representing different values that are used in calculations.

@OldBirdman

[csolomon]

OldBirdMan,

It's about that time! I really appreciate you taking the time out (one step at a time :)) to explain things to me and ensure that I got it right. Hopefully we can finish this tomorrow as my work day is over.

Thank you again. I will respond to you on tomorrow.

[ChipR]

Thanks for taking over OldBirdMan. I'm sorry I couldn't be of more help, but I'm knee deep in an urgent project, and can never seem to explain things clearly.

[OldBirdman]

#Name means that the control is not bound. The subform controls need to be bound to the query. This is done by seting the control's ControlSource property. If this works for testing, leave it alone, as we will be at item 2) next.

One step at a time:
1) Open the backup copy you made in Post #51, step 1. Copy ONE of your subforms, and then switch to your current database and paste it onto your form = F_Test2_MixDesign, not into your tab control
2) Drag your new subform into your tab control, as you did in post #51. Make sure your tab control is large enough for both subforms.
3) Next post, I need to know the name of your new subform
3) Between lines 7 & 8 of Private Sub tabCtl1_Change() you will need to assign a RecordSource to your new subform.
a) Line 7.1 strSQL = "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 "
b) Notice that all the double-quotes(") inside other double-quotes were changed to single-quotes(')
c) Line 7.2 strSQL = strSQL & "FROM Material INNER JOIN (MixSample INNER JOIN MatType ON MixSample.matTypeID=" & iTab & ") ON Material.materialID=MixSample.materialID "
d) Line 7.3 strSQL = strSQL & "WHERE (((MixSample.DM_Mix)=[Forms]![F_Test2_MixDesign]![DM_Mix])) "
e) Line 7.4 strSQL = strSQL & "ORDER BY ???;" if you need these in some order, otherwise the semicolon(;) goes at the end of the FROM Clause
f) Line 7.5 Me!YourSubformName.Form.RecordSource = strSQL
g) Test it to death

[csolomon]

Hi OldBirdman,

Again I appreciate your assistance. Back to work!

1) CHECK
2)CHECK

3) Next post, I need to know the name of your new subform

The name is SF_Test_TestMixSample
3) a. Here is what I have:

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;"
strSQL = "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"
strSQL = strSQL & "FROM Material INNER JOIN (MixSample INNER JOIN MatType ON MixSample.matTypeID=" & iTab & ") ON Material.materialID=MixSample.materialID "
strSQL = strSQL & "WHERE (((MixSample.DM_Mix)=[Forms]![F_Test2_MixDesign]![DM_Mix]);"
Me!SF_Test_TestMixSample.Form.RecordSource = strSQL
End Sub

The bold line is returning an error. The error is a syntax error


@OldBirdman