By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
424,836 Members | 2,011 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 424,836 IT Pros & Developers. It's quick & easy.

Control Tab Possibllities

P: 166

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:

Expand|Select|Wrap|Line Numbers
  1. 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


Case 2

etc...(I have not included the whole case statement). Neither format in case 1
or 2 works. I found this information here:

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.
Mar 25 '09
Share this Question
Share on Google+
56 Replies

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

Mar 26 '09 #51

P: 675
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?
Mar 26 '09 #52

P: 166
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.

Mar 26 '09 #53

P: 166

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.
Mar 26 '09 #54

Expert 100+
P: 1,287
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.
Mar 26 '09 #55

P: 675
#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
Mar 26 '09 #56

P: 166
Hi OldBirdman,

Again I appreciate your assistance. Back to work!

Expand|Select|Wrap|Line Numbers
  1. 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:

Expand|Select|Wrap|Line Numbers
  1. Private Sub tabCtl1_Change()
  2. Dim strSQL As String
  3. Dim iTab As Integer
  4. MsgBox "Tab chosen is " & TabCtl1.Pages(TabCtl1).Name
  5. iTab = Mid(TabCtl1.Pages(TabCtl1).Name, 4, 1)
  6. 'strSQL = "SELECT * FROM Material WHERE matTypeID=" & iTab & " ORDER BY material;"
  7. 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"
  8. strSQL = strSQL & "FROM Material INNER JOIN (MixSample INNER JOIN MatType ON MixSample.matTypeID=" & iTab & ") ON Material.materialID=MixSample.materialID "
  9. strSQL = strSQL & "WHERE (((MixSample.DM_Mix)=[Forms]![F_Test2_MixDesign]![DM_Mix]);"
  10. Me!SF_Test_TestMixSample.Form.RecordSource = strSQL
  11. End Sub
The bold line is returning an error. The error is a syntax error

Mar 30 '09 #57

56 Replies

Post your reply

Sign in to post your reply or Sign up for a free account.