Struggling with Linked Tables

[NKA]

I think I am in deeper water than I should be here and am hoping some smart cookie can help!

Without wanting to confuse anyone, I have tried to create a picture to show what I need to do:

> Open Database (display switchboard)
> Select A. or B.

Both of these selections open the same form - FORM_1

> Select and item from combo box
> Click toggle button to display (if A. selected above) FORM_2 or (if B. selected) FORM_3 (and hide FORM_1)

> Select an item from combo box (similar to FORM_1)
> Display filtered records in FORM_2.1 or FORM_3.1

FORM_2 and FORM_3 could be the same. It is only FORM_2.1 and FORM_3.1 which are differnt

So far, I have created 2 toggle buttons which point to the respective forms. I thought that by creating a variable to decide which form was to be applied, I could then use the same code as it would insert the form name for me. However, when I select either of the toggle buttons, I get an error message "Microsoft Access can't find the form 'FrmName' referred to in a macro expression or Visual Basic Code.

'FrmName' is the name of my variable - so I am really confused now!!

I hope this makes sense to someone and that they can help a damsel in distress!

NKA

The answer's always easy - if you know it! ;-)

 

[jgoodman00]

Can you post the code you are using. The error you have given suggests you have placed the FrmName variable inside quotes, meaning the FrmName is being interpreted as text rather than a variable...

James Goodman MCP

http://www.james-goodman.co.uk

 

[NKA]

Hi James,

The code behind the first form is as follows:

***Start of code*****************************

Option Compare Database
Option Explicit

Dim FrmName As String

Private Sub CBO_Con_ID_new_AfterUpdate()

'Find the record that matches the control
Me.RecordsetClone.FindFirst "[Con_ID] = " & Me![CBO_Con_ID_new]
Me.Bookmark = Me.RecordsetClone.Bookmark

'Show Contract details after selecting contract
Me!Contract.Visible = True
Me!Manager_Name.Visible = True

If MySel = "SCH" Then
Me!ToggleSchedule.Visible = True
ElseIf MySel = "TSK" Then
Me!ToggleTask.Visible = True
End If

End Sub

------------------------------------------
Sub Form_Current()
On Error GoTo Form_Current_Err

If ChildFormIsOpen() Then FilterChildForm

Form_Current_Exit:
Exit Sub

Form_Current_Err:
MsgBox Error$
Resume Form_Current_Exit

End Sub

------------------------------------------

End Sub
Private Sub FilterChildForm()

If MySel = "SCH" Then
FrmName = "SUB_FRM_SEL_Asset"
Forms!FrmName!CBO_Asset_ID.Requery
ElseIf MySel = "TSK" Then
FrmName = "SUB_FRM_Contract_Asset"
Forms!FrmName!CBO_Asset_ID.Requery
End If

If Me.NewRecord Then
Forms!FrmName.DataEntry = True
Else
Forms!FrmName.Filter = "[Con_ID] = " & Me![Con_ID]
Forms!FrmName.FilterOn = True
End If

End Sub

------------------------------------------
Private Sub OpenChildForm()

If MySel = "SCH" Then
FrmName = "SUB_FRM_SEL_Asset"
DoCmd.OpenForm FrmName
If Not Me![ToggleSchedule] Then Me![ToggleSchedule] = True
ElseIf MySel = "TSK" Then
FrmName = "SUB_FRM_Contract_Asset"
DoCmd.OpenForm FrmName
If Not Me![ToggleTask] Then Me![ToggleTask] = True
End If

End Sub

------------------------------------------
Private Sub CloseChildForm()

If MySel = "SCH" Then
FrmName = "SUB_FRM_SEL_Asset"
DoCmd.Close acForm, FrmName
If Me![ToggleSchedule] Then Me![ToggleSchedule] = False
ElseIf MySel = "TSK" Then
FrmName = "SUB_FRM_Contract_Asset"
DoCmd.Close acForm, FrmName
If Me![ToggleTask] Then Me![ToggleTask] = False
End If

End Sub

------------------------------------------
Private Function ChildFormIsOpen()

If MySel = "SCH" Then
FrmName = "SUB_FRM_SEL_Asset"
ElseIf MySel = "TSK" Then
FrmName = "SUB_FRM_Contract_Asset"
End If

ChildFormIsOpen = (SysCmd(acSysCmdGetObjectState, acForm, FrmName) And acObjStateOpen) <> False

End Function

------------------------------------------
Private Sub ToggleSchedule_Click()
On Error GoTo ToggleSchedule_Click_Err

FrmName = &quot;SUB_FRM_SEL_Asset&quot;

If ChildFormIsOpen() Then
CloseChildForm
Else
OpenChildForm
FilterChildForm
End If

Me.Visible = False
DoEvents
While SysCmd(acSysCmdGetObjectState, acForm, FrmName) = acObjStateOpen
DoEvents
Wend
Me.Visible = True
DoEvents


ToggleSchedule_Click_Exit:
Exit Sub

ToggleSchedule_Click_Err:
MsgBox Error$
Resume ToggleSchedule_Click_Exit
End Sub

------------------------------------------
Private Sub ToggleTask_Click()
On Error GoTo ToggleTask_Click_Err

FrmName = &quot;SUB_FRM_Contract_Asset&quot;

If ChildFormIsOpen() Then
CloseChildForm
Else
OpenChildForm
FilterChildForm
End If

Me.Visible = False
DoEvents
While SysCmd(acSysCmdGetObjectState, acForm, FrmName) = acObjStateOpen
DoEvents
Wend
Me.Visible = True
DoEvents


ToggleTask_Click_Exit:
Exit Sub

ToggleTask_Click_Err:
MsgBox Error$
Resume ToggleTask_Click_Exit
End Sub

*** End of code **********************

NKA

The answer's always easy - if you know it! ;-)

 

[jgoodman00]

I presume it is one of the following subs which are failing?
Private Sub ToggleSchedule_Click()
On Error GoTo ToggleSchedule_Click_Err

FrmName = &quot;SUB_FRM_SEL_Asset&quot;

If ChildFormIsOpen() Then
CloseChildForm
Else
OpenChildForm
FilterChildForm
End If

Me.Visible = False
DoEvents
While SysCmd(acSysCmdGetObjectState, acForm, FrmName) = acObjStateOpen
DoEvents
Wend
Me.Visible = True
DoEvents


ToggleSchedule_Click_Exit:
Exit Sub

ToggleSchedule_Click_Err:
MsgBox Error$
Resume ToggleSchedule_Click_Exit
End Sub

------------------------------------------
Private Sub ToggleTask_Click()
On Error GoTo ToggleTask_Click_Err

FrmName = &quot;SUB_FRM_Contract_Asset&quot;

If ChildFormIsOpen() Then
CloseChildForm
Else
OpenChildForm
FilterChildForm
End If

Me.Visible = False
DoEvents
While SysCmd(acSysCmdGetObjectState, acForm, FrmName) = acObjStateOpen
DoEvents
Wend
Me.Visible = True
DoEvents


ToggleTask_Click_Exit:
Exit Sub

ToggleTask_Click_Err:
MsgBox Error$
Resume ToggleTask_Click_Exit
End Sub


Could you step through the code, & say where exactly the error is occuring...

James Goodman MCP

http://www.james-goodman.co.uk

 

[jgoodman00]

Looking at it, I think your problem is the existence of two toggle buttons. What is to stop the user selecting both forms?

Would it not be better to use two standard command buttons?

James Goodman MCP

http://www.james-goodman.co.uk

 

[NKA]

Arrrrggggh! This is driving me mad!

I have taken on board what you said about the 2 toggle buttons (the theory was that only one form could be opened as the buttons would by made visible based on selection from switchboard).

However, I thought I would try the command button tact as you suggested - but am still hitting a brick wall running.

The code I have behind a single command button is as follows:

[blue]Private Sub Cmd_OK_Click()

If MySel = &quot;SCH&quot; Then
FrmName = &quot;SUB_FRM_SEL_Asset&quot;
ElseIf MySel = &quot;TSK&quot; Then
FrmName = &quot;SUB_FRM_Contract_Asset&quot;
End If

DoCmd.OpenForm FrmName[/blue]
[red]With Forms!FrmName[/red]
[blue] !CBO_Asset_ID.Requery
End With


If Me.NewRecord Then
Forms!FrmName.DataEntry = True
Else
Forms!FrmName.Filter = &quot;[Con_ID] = &quot; & Me![Con_ID]
Forms!FrmName.FilterOn = True
End If

Me.Visible = False
DoEvents
While SysCmd(acSysCmdGetObjectState, acForm, FrmName) = acObjStateOpen
DoEvents
Wend
Me.Visible = True
DoEvents


End Sub[/blue]

I really would like to avoid having 2 command buttons, so I thought by using variables, I could get round this. Clearly not!

An error occurs on the red line above - saying that Microsoft Access cannot find 'FrmName' referred to. What am I doing wrong???



NKA

The answer's always easy - if you know it! ;-)