Is this a Sound Database Design?

Nevermind, I figured out you can do it & got it to work....

Thanks
Curtis

So is it even possible to have one form with text boxes in it pointing to
fields from multiple tables? It isn't right?

Rather than using just one form that gets its data from many tables (or
globbing all the data into one table when it should be seperated), most
database solutions use a mainform/subform scenario.

In my opinion, it is best to have just one form or subform to fill data
in each table. So, if you have 10 tables, you would have 10 data forms
-- then perhaps a "switchboard" form to decide which form to open and a
ReportMenu form for processing report requests.



Warm Regards,
Crystal
*
(: have an awesome day
*
MVP Access
Remote programming and Training
strive4peace2006 at yahoo.com
*



Curtis Stevens wrote:
Crystal,

If I'm not mistaken, you can't have all these different forms, add them all
to the same query and use one main form, right? You will have to use
subforms for the additonal tables right? If so, then I can't go splitting it
up as I can't go spliting up the form that much, just wouldn't work.

Curtis

I'm just going to stick to tab control and use multiple tables in one query,
etc....


I'm still not able to get this code to work! Here is what I have exactly!

Created a form called TEST. Inserted a command button named SwitchTabs.
Inserted a subform named TabSubform.

I pasted this code as an OnClick for the command button. It comes up with
that same error. What do I do???? Potential & Current are the names of my
forms I want to be displayed in TabSubform.

==================CODE==============

Private Sub SwitchTabs_Click()
Private Function SwitchTabs(pIndex As Integer)
On Error GoTo SwitchTabs_error
'9 is Notes -- no code, not launched

'crystal
'strive4peace2006 at yahoo.com

Me.TabNumber = pIndex
Dim mNumTabs As Integer, i As Integer, mboo As Boolean
Dim Fore1 As Long, Fore2 As Long, Back1 As Long, Back2 As Long

Fore1 = 16777215
Back1 = 11220286
Fore2 = 8388608
Back2 = 16644084

mNumTabs = 14

For i = 1 To mNumTabs
If pIndex = i Then
Me("tab" & i).BackColor = Back1
Me("tab" & i).ForeColor = Fore1
Else
Me("tab" & i).BackColor = Back2
Me("tab" & i).ForeColor = Fore2
End If
Next i

Select Case pIndex
Case 1, 2, 3, 4, 5, 14
Me.TabSubform.Visible = True
Me.TabSubform.SetFocus
End Select
Select Case pIndex
Case 1
Me.TabSubform.SourceObject = "Potential"
Case 2
Me.TabSubform.SourceObject = "Current"
Case 3:
Me.TabSubform.SourceObject = "Potential"
Case 4:
Me.TabSubform.SourceObject = "Current"
Case 5:
Me.TabSubform.SourceObject = "Potential"
Case 14:
Me.TabSubform.SourceObject = "Current"
Case Else
Me.TabSubform.SourceObject = ""
Me.Name1.SetFocus
Me.TabSubform.Visible = False
End Select

Proc_Exit:
Exit Function

Proc_Err:
MsgBox Err.Description, , "ERROR " & Err.Number & " SwitchTabs"
'press F8 to step through lines of code to see where problem is
'comment next line after debugged
Stop: Resume

Resume Proc_Exit

End Function
End Function
==================CODE==============

Thanks
Curtis

Hi Curtis,

Try something like this:

'Private Sub SwitchTabs_Click() -- Remove this line!

Private Function SwitchTabs(pIndex As Integer)
On Error GoTo Proc_Err

'crystal
'strive4peace2006 at yahoo.com

Me.TabNumber = pIndex
Dim mNumTabs As Integer, i As Integer, mboo As Boolean
Dim Fore1 As Long, Fore2 As Long, Back1 As Long, Back2 As Long

Fore1 = 16777215
Back1 = 11220286
Fore2 = 8388608
Back2 = 16644084

mNumTabs = 2

For i = 1 To mNumTabs
If pIndex = i Then
Me("tab" & i).BackColor = Back1
Me("tab" & i).ForeColor = Fore1
Else
Me("tab" & i).BackColor = Back2
Me("tab" & i).ForeColor = Fore2
End If
Next i

Me.TabSubform.Visible = True
Me.TabSubform.SetFocus

Select Case pIndex
Case 1
Me.TabSubform.SourceObject = "Potential"
Case 2
Me.TabSubform.SourceObject = "CurrentForm"

End Select

Proc_Exit:
Exit Function

Proc_Err:
MsgBox Err.Description, , "ERROR " & Err.Number & " SwitchTabs"
'press F8 to step through lines of code to see where problem is
'comment next line after debugged
Stop: Resume

Resume Proc_Exit

End Function
'End Function -- Remove this line!

~~~~~~~~~~~~~~~~~

make sure you have these buttons

Name -- Tab1
Caption -- Potential
Onclick -- =SwitchTabs(1)

Name -- Tab2
Caption -- CurrentForm '-- changed name from Current
Onclick -- =SwitchTabs(2)

and this textbox:

Name -- TabNumber
~~~~~~~~~~~~~~~~~~

you are going to run into problems with a form name of Current -- you
need to change that, so I changed it in the example

Warm Regards,
Crystal
*
(: have an awesome day
*
MVP Access
Remote programming and Training
strive4peace2006 at yahoo.com
*



Curtis Stevens wrote:
Crystal,

I'm still not able to get this code to work! Here is what I have exactly!

Created a form called TEST. Inserted a command button named SwitchTabs.
Inserted a subform named TabSubform.

I pasted this code as an OnClick for the command button. It comes up with
that same error. What do I do???? Potential & Current are the names of my
forms I want to be displayed in TabSubform.

==================CODE==============

Private Sub SwitchTabs_Click()
Private Function SwitchTabs(pIndex As Integer)
On Error GoTo SwitchTabs_error
'9 is Notes -- no code, not launched

'crystal
'strive4peace2006 at yahoo.com

Me.TabNumber = pIndex
Dim mNumTabs As Integer, i As Integer, mboo As Boolean
Dim Fore1 As Long, Fore2 As Long, Back1 As Long, Back2 As Long

Fore1 = 16777215
Back1 = 11220286
Fore2 = 8388608
Back2 = 16644084

mNumTabs = 14

For i = 1 To mNumTabs
If pIndex = i Then
Me("tab" & i).BackColor = Back1
Me("tab" & i).ForeColor = Fore1
Else
Me("tab" & i).BackColor = Back2
Me("tab" & i).ForeColor = Fore2
End If
Next i

Select Case pIndex
Case 1, 2, 3, 4, 5, 14
Me.TabSubform.Visible = True
Me.TabSubform.SetFocus
End Select
Select Case pIndex
Case 1
Me.TabSubform.SourceObject = "Potential"
Case 2
Me.TabSubform.SourceObject = "Current"
Case 3:
Me.TabSubform.SourceObject = "Potential"
Case 4:
Me.TabSubform.SourceObject = "Current"
Case 5:
Me.TabSubform.SourceObject = "Potential"
Case 14:
Me.TabSubform.SourceObject = "Current"
Case Else
Me.TabSubform.SourceObject = ""
Me.Name1.SetFocus
Me.TabSubform.Visible = False
End Select

Proc_Exit:
Exit Function

Proc_Err:
MsgBox Err.Description, , "ERROR " & Err.Number & " SwitchTabs"
'press F8 to step through lines of code to see where problem is
'comment next line after debugged
Stop: Resume

Resume Proc_Exit

End Function
End Function
==================CODE==============

Thanks
Curtis

Hi Crystal,

Ok, so where does this code go? On an onclick of what button or form or???
I don't know where to put it!

Curtis


Try something like this:

'Private Sub SwitchTabs_Click() -- Remove this line!

Private Function SwitchTabs(pIndex As Integer)
On Error GoTo Proc_Err

'crystal
'strive4peace2006 at yahoo.com

Me.TabNumber = pIndex
Dim mNumTabs As Integer, i As Integer, mboo As Boolean
Dim Fore1 As Long, Fore2 As Long, Back1 As Long, Back2 As Long

Fore1 = 16777215
Back1 = 11220286
Fore2 = 8388608
Back2 = 16644084

mNumTabs = 2

For i = 1 To mNumTabs
If pIndex = i Then
Me("tab" & i).BackColor = Back1
Me("tab" & i).ForeColor = Fore1
Else
Me("tab" & i).BackColor = Back2
Me("tab" & i).ForeColor = Fore2
End If
Next i

Me.TabSubform.Visible = True
Me.TabSubform.SetFocus

Select Case pIndex
Case 1
Me.TabSubform.SourceObject = "Potential"
Case 2
Me.TabSubform.SourceObject = "CurrentForm"

End Select

Proc_Exit:
Exit Function

Proc_Err:
MsgBox Err.Description, , "ERROR " & Err.Number & " SwitchTabs"
'press F8 to step through lines of code to see where problem is
'comment next line after debugged
Stop: Resume

Resume Proc_Exit

End Function
'End Function -- Remove this line!

~~~~~~~~~~~~~~~~~

make sure you have these buttons

Name -- Tab1
Caption -- Potential
Onclick -- =SwitchTabs(1)

Name -- Tab2
Caption -- CurrentForm '-- changed name from Current
Onclick -- =SwitchTabs(2)

and this textbox:

Name -- TabNumber
~~~~~~~~~~~~~~~~~~

you are going to run into problems with a form name of Current -- you
need to change that, so I changed it in the example

Warm Regards,
Crystal
*
(: have an awesome day
*
MVP Access
Remote programming and Training
strive4peace2006 at yahoo.com
*



Curtis Stevens wrote:
Crystal,

I'm still not able to get this code to work! Here is what I have exactly!

Created a form called TEST. Inserted a command button named SwitchTabs.
Inserted a subform named TabSubform.

I pasted this code as an OnClick for the command button. It comes up with
that same error. What do I do???? Potential & Current are the names of my
forms I want to be displayed in TabSubform.

==================CODE==============

Private Sub SwitchTabs_Click()
Private Function SwitchTabs(pIndex As Integer)
On Error GoTo SwitchTabs_error
'9 is Notes -- no code, not launched

'crystal
'strive4peace2006 at yahoo.com

Me.TabNumber = pIndex
Dim mNumTabs As Integer, i As Integer, mboo As Boolean
Dim Fore1 As Long, Fore2 As Long, Back1 As Long, Back2 As Long

Fore1 = 16777215
Back1 = 11220286
Fore2 = 8388608
Back2 = 16644084

mNumTabs = 14

For i = 1 To mNumTabs
If pIndex = i Then
Me("tab" & i).BackColor = Back1
Me("tab" & i).ForeColor = Fore1
Else
Me("tab" & i).BackColor = Back2
Me("tab" & i).ForeColor = Fore2
End If
Next i

Select Case pIndex
Case 1, 2, 3, 4, 5, 14
Me.TabSubform.Visible = True
Me.TabSubform.SetFocus
End Select
Select Case pIndex
Case 1
Me.TabSubform.SourceObject = "Potential"
Case 2
Me.TabSubform.SourceObject = "Current"
Case 3:
Me.TabSubform.SourceObject = "Potential"
Case 4:
Me.TabSubform.SourceObject = "Current"
Case 5:
Me.TabSubform.SourceObject = "Potential"
Case 14:
Me.TabSubform.SourceObject = "Current"
Case Else
Me.TabSubform.SourceObject = ""
Me.Name1.SetFocus
Me.TabSubform.Visible = False
End Select

Proc_Exit:
Exit Function

Proc_Err:
MsgBox Err.Description, , "ERROR " & Err.Number & " SwitchTabs"
'press F8 to step through lines of code to see where problem is
'comment next line after debugged
Stop: Resume

Resume Proc_Exit

End Function
End Function
==================CODE==============

Thanks
Curtis

Hi Curtis,

I realize you have been tossed into the deep end of the pool and all of
this code is spinning in your head... but try and get past the confusion
of code to the LOGIC...

in my previous post, I told you how the code would be launched:

make sure you have these (command) buttons

Name -- Tab1
Caption -- Potential
OnClick event -- =SwitchTabs(1)

Name -- Tab2
Caption -- CurrentForm '-- changed name from Current
OnClick event -- =SwitchTabs(2)

The OnClick event of the command button will launch the code... this
will replace the SourceObject in your subform control according to the
parameter (in your case, 1 or 2) that is sent to the function (whose
code is behind your form)


Warm Regards,
Crystal
*
(: have an awesome day
*
MVP Access
Remote programming and Training
strive4peace2006 at yahoo.com
*



Curtis Stevens wrote:
Hi Crystal,

Ok, so where does this code go? On an onclick of what button or form or???
I don't know where to put it!

Curtis


Try something like this:

'Private Sub SwitchTabs_Click() -- Remove this line!

Private Function SwitchTabs(pIndex As Integer)
On Error GoTo Proc_Err

'crystal
'strive4peace2006 at yahoo.com

Me.TabNumber = pIndex
Dim mNumTabs As Integer, i As Integer, mboo As Boolean
Dim Fore1 As Long, Fore2 As Long, Back1 As Long, Back2 As Long

Fore1 = 16777215
Back1 = 11220286
Fore2 = 8388608
Back2 = 16644084

mNumTabs = 2

For i = 1 To mNumTabs
If pIndex = i Then
Me("tab" & i).BackColor = Back1
Me("tab" & i).ForeColor = Fore1
Else
Me("tab" & i).BackColor = Back2
Me("tab" & i).ForeColor = Fore2
End If
Next i

Me.TabSubform.Visible = True
Me.TabSubform.SetFocus

Select Case pIndex
Case 1
Me.TabSubform.SourceObject = "Potential"
Case 2
Me.TabSubform.SourceObject = "CurrentForm"

End Select

Proc_Exit:
Exit Function

Proc_Err:
MsgBox Err.Description, , "ERROR " & Err.Number & " SwitchTabs"
'press F8 to step through lines of code to see where problem is
'comment next line after debugged
Stop: Resume

Resume Proc_Exit

End Function
'End Function -- Remove this line!

~~~~~~~~~~~~~~~~~

make sure you have these buttons

Name -- Tab1
Caption -- Potential
Onclick -- =SwitchTabs(1)

Name -- Tab2
Caption -- CurrentForm '-- changed name from Current
Onclick -- =SwitchTabs(2)

and this textbox:

Name -- TabNumber
~~~~~~~~~~~~~~~~~~

you are going to run into problems with a form name of Current -- you
need to change that, so I changed it in the example

Warm Regards,
Crystal
*
(: have an awesome day
*
MVP Access
Remote programming and Training
strive4peace2006 at yahoo.com
*



Curtis Stevens wrote:
Crystal,

I'm still not able to get this code to work! Here is what I have exactly!

Created a form called TEST. Inserted a command button named SwitchTabs.
Inserted a subform named TabSubform.

I pasted this code as an OnClick for the command button. It comes up with
that same error. What do I do???? Potential & Current are the names of my
forms I want to be displayed in TabSubform.

==================CODE==============

Private Sub SwitchTabs_Click()
Private Function SwitchTabs(pIndex As Integer)
On Error GoTo SwitchTabs_error
'9 is Notes -- no code, not launched

'crystal
'strive4peace2006 at yahoo.com

Me.TabNumber = pIndex
Dim mNumTabs As Integer, i As Integer, mboo As Boolean
Dim Fore1 As Long, Fore2 As Long, Back1 As Long, Back2 As Long

Fore1 = 16777215
Back1 = 11220286
Fore2 = 8388608
Back2 = 16644084

mNumTabs = 14

For i = 1 To mNumTabs
If pIndex = i Then
Me("tab" & i).BackColor = Back1
Me("tab" & i).ForeColor = Fore1
Else
Me("tab" & i).BackColor = Back2
Me("tab" & i).ForeColor = Fore2
End If
Next i

Select Case pIndex
Case 1, 2, 3, 4, 5, 14
Me.TabSubform.Visible = True
Me.TabSubform.SetFocus
End Select
Select Case pIndex
Case 1
Me.TabSubform.SourceObject = "Potential"
Case 2
Me.TabSubform.SourceObject = "Current"
Case 3:
Me.TabSubform.SourceObject = "Potential"
Case 4:
Me.TabSubform.SourceObject = "Current"
Case 5:
Me.TabSubform.SourceObject = "Potential"
Case 14:
Me.TabSubform.SourceObject = "Current"
Case Else
Me.TabSubform.SourceObject = ""
Me.Name1.SetFocus
Me.TabSubform.Visible = False
End Select

Proc_Exit:
Exit Function

Proc_Err:
MsgBox Err.Description, , "ERROR " & Err.Number & " SwitchTabs"
'press F8 to step through lines of code to see where problem is
'comment next line after debugged
Stop: Resume

Resume Proc_Exit

End Function
End Function
==================CODE==============

Thanks
Curtis

I thought I was suppose to put =SwitchTabs(1) in the onclick event window
field box...... Didn't realize you were talking about the code, I thought
that it should, but based on what you said, I thought it was suppose to say
=SwitchTabs(1)........

Curtis


I realize you have been tossed into the deep end of the pool and all of
this code is spinning in your head... but try and get past the confusion
of code to the LOGIC...

in my previous post, I told you how the code would be launched:

make sure you have these (command) buttons

Name -- Tab1
Caption -- Potential
OnClick event -- =SwitchTabs(1)

Name -- Tab2
Caption -- CurrentForm '-- changed name from Current
OnClick event -- =SwitchTabs(2)

The OnClick event of the command button will launch the code... this
will replace the SourceObject in your subform control according to the
parameter (in your case, 1 or 2) that is sent to the function (whose
code is behind your form)


Warm Regards,
Crystal
*
(: have an awesome day
*
MVP Access
Remote programming and Training
strive4peace2006 at yahoo.com
*



Curtis Stevens wrote:
Hi Crystal,

Ok, so where does this code go? On an onclick of what button or form or???
I don't know where to put it!

Curtis


Try something like this:

'Private Sub SwitchTabs_Click() -- Remove this line!

Private Function SwitchTabs(pIndex As Integer)
On Error GoTo Proc_Err

'crystal
'strive4peace2006 at yahoo.com

Me.TabNumber = pIndex
Dim mNumTabs As Integer, i As Integer, mboo As Boolean
Dim Fore1 As Long, Fore2 As Long, Back1 As Long, Back2 As Long

Fore1 = 16777215
Back1 = 11220286
Fore2 = 8388608
Back2 = 16644084

mNumTabs = 2

For i = 1 To mNumTabs
If pIndex = i Then
Me("tab" & i).BackColor = Back1
Me("tab" & i).ForeColor = Fore1
Else
Me("tab" & i).BackColor = Back2
Me("tab" & i).ForeColor = Fore2
End If
Next i

Me.TabSubform.Visible = True
Me.TabSubform.SetFocus

Select Case pIndex
Case 1
Me.TabSubform.SourceObject = "Potential"
Case 2
Me.TabSubform.SourceObject = "CurrentForm"

End Select

Proc_Exit:
Exit Function

Proc_Err:
MsgBox Err.Description, , "ERROR " & Err.Number & " SwitchTabs"
'press F8 to step through lines of code to see where problem is
'comment next line after debugged
Stop: Resume

Resume Proc_Exit

End Function
'End Function -- Remove this line!

~~~~~~~~~~~~~~~~~

make sure you have these buttons

Name -- Tab1
Caption -- Potential
Onclick -- =SwitchTabs(1)

Name -- Tab2
Caption -- CurrentForm '-- changed name from Current
Onclick -- =SwitchTabs(2)

and this textbox:

Name -- TabNumber
~~~~~~~~~~~~~~~~~~

you are going to run into problems with a form name of Current -- you
need to change that, so I changed it in the example

Warm Regards,
Crystal
*
(: have an awesome day
*
MVP Access
Remote programming and Training
strive4peace2006 at yahoo.com
*



Curtis Stevens wrote:
Crystal,

I'm still not able to get this code to work! Here is what I have exactly!

Created a form called TEST. Inserted a command button named SwitchTabs.
Inserted a subform named TabSubform.

I pasted this code as an OnClick for the command button. It comes up with
that same error. What do I do???? Potential & Current are the names of my
forms I want to be displayed in TabSubform.

==================CODE==============

Private Sub SwitchTabs_Click()
Private Function SwitchTabs(pIndex As Integer)
On Error GoTo SwitchTabs_error
'9 is Notes -- no code, not launched

'crystal
'strive4peace2006 at yahoo.com

Me.TabNumber = pIndex
Dim mNumTabs As Integer, i As Integer, mboo As Boolean
Dim Fore1 As Long, Fore2 As Long, Back1 As Long, Back2 As Long

Fore1 = 16777215
Back1 = 11220286
Fore2 = 8388608
Back2 = 16644084

mNumTabs = 14

For i = 1 To mNumTabs
If pIndex = i Then
Me("tab" & i).BackColor = Back1
Me("tab" & i).ForeColor = Fore1
Else
Me("tab" & i).BackColor = Back2
Me("tab" & i).ForeColor = Fore2
End If
Next i

Select Case pIndex
Case 1, 2, 3, 4, 5, 14
Me.TabSubform.Visible = True
Me.TabSubform.SetFocus
End Select
Select Case pIndex
Case 1
Me.TabSubform.SourceObject = "Potential"
Case 2
Me.TabSubform.SourceObject = "Current"
Case 3:
Me.TabSubform.SourceObject = "Potential"
Case 4:
Me.TabSubform.SourceObject = "Current"
Case 5:
Me.TabSubform.SourceObject = "Potential"
Case 14:
Me.TabSubform.SourceObject = "Current"
Case Else
Me.TabSubform.SourceObject = ""
Me.Name1.SetFocus
Me.TabSubform.Visible = False
End Select

Proc_Exit:
Exit Function

Proc_Err:
MsgBox Err.Description, , "ERROR " & Err.Number & " SwitchTabs"
'press F8 to step through lines of code to see where problem is
'comment next line after debugged
Stop: Resume

Resume Proc_Exit

End Function
End Function
==================CODE==============

Thanks
Curtis

Hi Curtis,

sorry for the confusion... did you get it to work?

The actual function goes in the module sheet behind your form.

'~~~~~~~~~ Compile ~~~~~~~~~

Whenever you change code or references, your should always compile
before executing.

from the menu in a module window: Debug, Compile

fix any errors on the yellow highlighted lines

keep compiling until nothing happens (this is good!)


Warm Regards,
Crystal
*
(: have an awesome day
*
MVP Access
Remote programming and Training
strive4peace2006 at yahoo.com
*



Curtis Stevens wrote:
I thought I was suppose to put =SwitchTabs(1) in the onclick event window
field box...... Didn't realize you were talking about the code, I thought
that it should, but based on what you said, I thought it was suppose to say
=SwitchTabs(1)........

Curtis


I realize you have been tossed into the deep end of the pool and all of
this code is spinning in your head... but try and get past the confusion
of code to the LOGIC...

in my previous post, I told you how the code would be launched:

make sure you have these (command) buttons

Name -- Tab1
Caption -- Potential
OnClick event -- =SwitchTabs(1)

Name -- Tab2
Caption -- CurrentForm '-- changed name from Current
OnClick event -- =SwitchTabs(2)

The OnClick event of the command button will launch the code... this
will replace the SourceObject in your subform control according to the
parameter (in your case, 1 or 2) that is sent to the function (whose
code is behind your form)


Warm Regards,
Crystal
*
(: have an awesome day
*
MVP Access
Remote programming and Training
strive4peace2006 at yahoo.com
*



Curtis Stevens wrote:
Hi Crystal,

Ok, so where does this code go? On an onclick of what button or form or???
I don't know where to put it!

Curtis


Try something like this:

'Private Sub SwitchTabs_Click() -- Remove this line!

Private Function SwitchTabs(pIndex As Integer)
On Error GoTo Proc_Err

'crystal
'strive4peace2006 at yahoo.com

Me.TabNumber = pIndex
Dim mNumTabs As Integer, i As Integer, mboo As Boolean
Dim Fore1 As Long, Fore2 As Long, Back1 As Long, Back2 As Long

Fore1 = 16777215
Back1 = 11220286
Fore2 = 8388608
Back2 = 16644084

mNumTabs = 2

For i = 1 To mNumTabs
If pIndex = i Then
Me("tab" & i).BackColor = Back1
Me("tab" & i).ForeColor = Fore1
Else
Me("tab" & i).BackColor = Back2
Me("tab" & i).ForeColor = Fore2
End If
Next i

Me.TabSubform.Visible = True
Me.TabSubform.SetFocus

Select Case pIndex
Case 1
Me.TabSubform.SourceObject = "Potential"
Case 2
Me.TabSubform.SourceObject = "CurrentForm"

End Select

Proc_Exit:
Exit Function

Proc_Err:
MsgBox Err.Description, , "ERROR " & Err.Number & " SwitchTabs"
'press F8 to step through lines of code to see where problem is
'comment next line after debugged
Stop: Resume

Resume Proc_Exit

End Function
'End Function -- Remove this line!

~~~~~~~~~~~~~~~~~

make sure you have these buttons

Name -- Tab1
Caption -- Potential
Onclick -- =SwitchTabs(1)

Name -- Tab2
Caption -- CurrentForm '-- changed name from Current
Onclick -- =SwitchTabs(2)

and this textbox:

Name -- TabNumber
~~~~~~~~~~~~~~~~~~

you are going to run into problems with a form name of Current -- you
need to change that, so I changed it in the example

Warm Regards,
Crystal
*
(: have an awesome day
*
MVP Access
Remote programming and Training
strive4peace2006 at yahoo.com
*



Curtis Stevens wrote:
Crystal,

I'm still not able to get this code to work! Here is what I have exactly!

Created a form called TEST. Inserted a command button named SwitchTabs.
Inserted a subform named TabSubform.

I pasted this code as an OnClick for the command button. It comes up with
that same error. What do I do???? Potential & Current are the names of my
forms I want to be displayed in TabSubform.

==================CODE==============

Private Sub SwitchTabs_Click()
Private Function SwitchTabs(pIndex As Integer)
On Error GoTo SwitchTabs_error
'9 is Notes -- no code, not launched

'crystal
'strive4peace2006 at yahoo.com

Me.TabNumber = pIndex
Dim mNumTabs As Integer, i As Integer, mboo As Boolean
Dim Fore1 As Long, Fore2 As Long, Back1 As Long, Back2 As Long

Fore1 = 16777215
Back1 = 11220286
Fore2 = 8388608
Back2 = 16644084

mNumTabs = 14

For i = 1 To mNumTabs
If pIndex = i Then
Me("tab" & i).BackColor = Back1
Me("tab" & i).ForeColor = Fore1
Else
Me("tab" & i).BackColor = Back2
Me("tab" & i).ForeColor = Fore2
End If
Next i

Select Case pIndex
Case 1, 2, 3, 4, 5, 14
Me.TabSubform.Visible = True
Me.TabSubform.SetFocus
End Select
Select Case pIndex
Case 1
Me.TabSubform.SourceObject = "Potential"
Case 2
Me.TabSubform.SourceObject = "Current"
Case 3:
Me.TabSubform.SourceObject = "Potential"
Case 4:
Me.TabSubform.SourceObject = "Current"
Case 5:
Me.TabSubform.SourceObject = "Potential"
Case 14:
Me.TabSubform.SourceObject = "Current"
Case Else
Me.TabSubform.SourceObject = ""
Me.Name1.SetFocus
Me.TabSubform.Visible = False
End Select

Proc_Exit:
Exit Function

Proc_Err:
MsgBox Err.Description, , "ERROR " & Err.Number & " SwitchTabs"
'press F8 to step through lines of code to see where problem is
'comment next line after debugged
Stop: Resume

Resume Proc_Exit

End Function
End Function
==================CODE==============

Thanks
Curtis

I did exactly what you said and it comes up with this error:

Error 438 SwitchTabs - Object doesn't support this propery or method

Points to this line of code: Stop: Resume

Hi Curtis,

'press F8 to step through lines of code to see where problem is
'comment next line after debugged
Stop: Resume

the code will STOP on the Stop statement

press F8 and it will go to the Resume statement

press F8 again and it will go to the actual offending line...


Warm Regards,
Crystal
*
(: have an awesome day
*
MVP Access
Remote programming and Training
strive4peace2006 at yahoo.com
*



Curtis Stevens wrote:
I did exactly what you said and it comes up with this error:

Error 438 SwitchTabs - Object doesn't support this propery or method

Points to this line of code: Stop: Resume