Control Tab Possibllities

[OldBirdman]

After the program is running, put a breakpoint on the "End Sub" (line 8) of the tabCtl1_Change subroutine, and determine iTab, strSQL, and Me!SF_Test_MixS ample.Form.Reco rdSource

This can be done by entering ?iTab into the immediate window. Then do ?strSQL Then do ?Me!SF_Test_Mix Sample.Form.Rec ordSource

What do you get?

[csolomon]

After the program ran, I put a breakpoint on line 3 of the tabCtl1_change subroutine. Here's what happened in the immediate window:
?iTab: 2
?strSQL:

SELECT * FROM Material WHERE matTypeID=2 ORDER BY material;

?Me!SF_Test_Mix Sample.Form.Rec ordSource:

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]));

After the program is running, put a breakpoint on the "End Sub" (line 8) of the tabCtl1_Change subroutine, and determine iTab, strSQL, and Me!SF_Test_MixS ample.Form.Reco rdSource

This can be done by entering ?iTab into the immediate window. Then do ?strSQL Then do ?Me!SF_Test_Mix Sample.Form.Rec ordSource

What do you get?

[OldBirdman]

So you didn't use my code, line 7, because otherwise Me!SF_Test_MixS ample.Form.Reco rdSource should be identical to strSQL

What is the complete code for Private Sub tabCtl1_Change( ) ?

[csolomon]

You are right BirdMan, I had the subform on the tab control name as opposed to the new subform name.

Now, when I select a tab, the subform changes.

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;"
Me!SF_Test_MixSample.Form.RecordSource = strSQL
End Sub

So you didn't use my code, line 7, because otherwise Me!SF_Test_MixS ample.Form.Reco rdSource should be identical to strSQL

What is the complete code for Private Sub tabCtl1_Change( ) ?

[OldBirdman]

OK, Good!

One step at a time:
1) Make a copy of this database.
2) Remove all controls except tabctl1 and Name:SF_Test_Mi xSample
a) Be sure that ALL tab pages are blank (NO CONTROLS)
3) Set tabctl1.BackSty le = Transparent
4) Resize tabctl1 so it is wide enough for SF_Test_MixSamp le
5) Move SF_Test_MixSamp le to be on top of tabctl1
a) Do not Cut/Paste SF_Test_MixSamp le into tabctl1 or onto a page
b) Use drag/drop only
6) Switch to Form View, and click one of your tabs
a) Can you see your subform?
b) Does it change correctly?
c) Can you select a record in the subform?
d) Can you edit a field in a record in the subform?

Let me know. If any problem, which step fails?

[csolomon]

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

OK, Good!

One step at a time:
1) Make a copy of this database.
2) Remove all controls except tabctl1 and Name:SF_Test_Mi xSample
a) Be sure that ALL tab pages are blank (NO CONTROLS)
3) Set tabctl1.BackSty le = Transparent
4) Resize tabctl1 so it is wide enough for SF_Test_MixSamp le
5) Move SF_Test_MixSamp le to be on top of tabctl1
a) Do not Cut/Paste SF_Test_MixSamp le into tabctl1 or onto a page
b) Use drag/drop only
6) Switch to Form View, and click one of your tabs
a) Can you see your subform?
b) Does it change correctly?
c) Can you select a record in the subform?
d) Can you edit a field in a record in the subform?

Let me know. If any problem, which step fails?

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

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]

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_MixDesi gn, 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_Mi x, MixSample.DM_Ma terialNo, MixSample.matTy peID, MixSample.mater ialID, MixSample.matBa tchWeight, MatType.matType , Material.materi al, Material.materi alGrav, GetYield([MixSample].[matTypeID],[matBatchWeight],[Material].[materialGrav]) AS DMYield, MixSample.pigPe rcent, DLookUp('matPri ce','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.matTy peID=" & iTab & ") ON Material.materi alID=MixSample. materialID "
d) Line 7.3 strSQL = strSQL & "WHERE (((MixSample.DM _Mix)=[Forms]![F_Test2_MixDesi gn]![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!YourSubformN ame.Form.Record Source = 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_TestMix Sample
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"[B])[/B] 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

#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_MixDesi gn, 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_Mi x, MixSample.DM_Ma terialNo, MixSample.matTy peID, MixSample.mater ialID, MixSample.matBa tchWeight, MatType.matType , Material.materi al, Material.materi alGrav, GetYield([MixSample].[matTypeID],[matBatchWeight],[Material].[materialGrav]) AS DMYield, MixSample.pigPe rcent, DLookUp('matPri ce','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.matTy peID=" & iTab & ") ON Material.materi alID=MixSample. materialID "
d) Line 7.3 strSQL = strSQL & "WHERE (((MixSample.DM _Mix)=[Forms]![F_Test2_MixDesi gn]![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!YourSubformN ame.Form.Record Source = strSQL
g) Test it to death