Moving from Switchboard to Switchboard

C

Chip Coutts

I have an Access 2000 database, split between front end and back end, which
has four switchboards. Each switchboard has the same three fields in the
Header Section at the top:

Combo12: Unbound, Row Source: SELECT DISTINCTROW
[PatientsByLastName].SocSecNumber, [PatientsByLastName].LastName & ", " &
[PatientsByLastName].FirstName & " " & [PatientsByLastName].MiddleName FROM
[PatientsByLastName];
Combo22: Control Source: SocSecNumber Row Source: SELECT DISTINCTROW
[PatientsByLastName].SocSecNumber, [PatientsByLastName].LastName & ", " &
[PatientsByLastName].FirstName & " " & [PatientsByLastName].MiddleName FROM
[PatientsByLastName];
SocSecNumber: Control Source: SocSecNumber
---------------------------------------------
The user selects the patient using Combo12...

Sub Combo12_AfterUpdate()
' Find the record that matches the control.
Me.RecordsetClone.FindFirst "[SocSecNumber] = '" & Me![Combo12] & "'"
Me.Bookmark = Me.RecordsetClone.Bookmark
End Sub

Private Sub Combo12_Enter()
Combo12.Requery
End Sub

Private Sub Combo12_GotFocus()
' Find the record that matches the control.
Me.RecordsetClone.FindFirst "[SocSecNumber] = '" & Me![Combo12] & "'"
Me.Bookmark = Me.RecordsetClone.Bookmark
End Sub

....then clicks on buttons in the Detail Section to open forms or print
letters or whatever for that patient based on SocSecNumber.
---------------------------------------------
To navigate from one switchboard to the next, without the user having to
re-select the patient each time, I have buttons at the bottom of the
switchboard:
(It has needed the {F6} command to move the focus from the Detail section to
the Header)

Private Sub GoToSwitchboard2_Click()
On Error GoTo Err_GoToSwitchboard2_Click

Dim stDocName As String

stDocName = "Switchboard2"

DoCmd.OpenForm stDocName
[Forms]![Switchboard2]![Combo12] = [Forms]![Switchboard1]![SocSecNumber]
SendKeys "{F6}"
SendKeys "{ENTER}"
[Forms]![Switchboard2]![Combo12].Requery

stDocName = "Switchboard1"

DoCmd.Close acForm, stDocName

Exit_GoToSwitchboard2_Click:
Exit Sub

Err_GoToSwitchboard2_Click:
MsgBox Err.Description
Resume Exit_GoToSwitchboard2_Click

End Sub
---------------------------------------------
For several years, running directly over the network, this has worked fine.
We are moving toward using Terminal Server (Microsoft Windows Server 2003
Standard Edition) running Access 2003.
Under Terminal Server, when moving from switchboard to switchboard, it works
as intended most of the time, but often it will open the new switchboard to
the first record in the pulldown,
almost as if the connection is running a bit slower, and instructions are
being executed out of order?
I have tried migrating the front end to Access 2003, and that did not seem
to make a difference. My master plan is to migrate the back end to Access
2003 then SQL Server,
but that may be months off. Due to remote sites coming on board, the
Terminal Server solution will be implemented, I just need to make it work.
Any insight would be greatly appreciated.
 
M

Martin

Beware of SendKeys - it's rough-and-ready and can behave quite unpredictably.
However, you can force it to execute each keystroke fully before moving on
to the next line of code by making the following changes:

SendKeys "{F6}", True
SendKeys "{ENTER}", True

If this doesn't work, try and eliminate your SendKeys statements: there's
got to be a better way...here it is, a far more "accessy" way to move between
sections:
Me.Section(acHeader).Controls("Text6").SetFocus
to move to a control called "Text6" in the report header and:
Me.Section(acDetail).Controls("Text1").SetFocus
to move to a control in the detail.

Good luck!



Chip Coutts said:
I have an Access 2000 database, split between front end and back end, which
has four switchboards. Each switchboard has the same three fields in the
Header Section at the top:

Combo12: Unbound, Row Source: SELECT DISTINCTROW
[PatientsByLastName].SocSecNumber, [PatientsByLastName].LastName & ", " &
[PatientsByLastName].FirstName & " " & [PatientsByLastName].MiddleName FROM
[PatientsByLastName];
Combo22: Control Source: SocSecNumber Row Source: SELECT DISTINCTROW
[PatientsByLastName].SocSecNumber, [PatientsByLastName].LastName & ", " &
[PatientsByLastName].FirstName & " " & [PatientsByLastName].MiddleName FROM
[PatientsByLastName];
SocSecNumber: Control Source: SocSecNumber
---------------------------------------------
The user selects the patient using Combo12...

Sub Combo12_AfterUpdate()
' Find the record that matches the control.
Me.RecordsetClone.FindFirst "[SocSecNumber] = '" & Me![Combo12] & "'"
Me.Bookmark = Me.RecordsetClone.Bookmark
End Sub

Private Sub Combo12_Enter()
Combo12.Requery
End Sub

Private Sub Combo12_GotFocus()
' Find the record that matches the control.
Me.RecordsetClone.FindFirst "[SocSecNumber] = '" & Me![Combo12] & "'"
Me.Bookmark = Me.RecordsetClone.Bookmark
End Sub

...then clicks on buttons in the Detail Section to open forms or print
letters or whatever for that patient based on SocSecNumber.
---------------------------------------------
To navigate from one switchboard to the next, without the user having to
re-select the patient each time, I have buttons at the bottom of the
switchboard:
(It has needed the {F6} command to move the focus from the Detail section to
the Header)

Private Sub GoToSwitchboard2_Click()
On Error GoTo Err_GoToSwitchboard2_Click

Dim stDocName As String

stDocName = "Switchboard2"

DoCmd.OpenForm stDocName
[Forms]![Switchboard2]![Combo12] = [Forms]![Switchboard1]![SocSecNumber]
SendKeys "{F6}"
SendKeys "{ENTER}"
[Forms]![Switchboard2]![Combo12].Requery

stDocName = "Switchboard1"

DoCmd.Close acForm, stDocName

Exit_GoToSwitchboard2_Click:
Exit Sub

Err_GoToSwitchboard2_Click:
MsgBox Err.Description
Resume Exit_GoToSwitchboard2_Click

End Sub
---------------------------------------------
For several years, running directly over the network, this has worked fine.
We are moving toward using Terminal Server (Microsoft Windows Server 2003
Standard Edition) running Access 2003.
Under Terminal Server, when moving from switchboard to switchboard, it works
as intended most of the time, but often it will open the new switchboard to
the first record in the pulldown,
almost as if the connection is running a bit slower, and instructions are
being executed out of order?
I have tried migrating the front end to Access 2003, and that did not seem
to make a difference. My master plan is to migrate the back end to Access
2003 then SQL Server,
but that may be months off. Due to remote sites coming on board, the
Terminal Server solution will be implemented, I just need to make it work.
Any insight would be greatly appreciated.
Click to expand...
 
C

Chip Coutts

Martin:

I have tried both of your solutions, and I get the same results. I am
beginning to think my flaw lies elsewhere...

Martin said:
Beware of SendKeys - it's rough-and-ready and can behave quite unpredictably.
However, you can force it to execute each keystroke fully before moving on
to the next line of code by making the following changes:

SendKeys "{F6}", True
SendKeys "{ENTER}", True

If this doesn't work, try and eliminate your SendKeys statements: there's
got to be a better way...here it is, a far more "accessy" way to move between
sections:
Me.Section(acHeader).Controls("Text6").SetFocus
to move to a control called "Text6" in the report header and:
Me.Section(acDetail).Controls("Text1").SetFocus
to move to a control in the detail.

Good luck!



Chip Coutts said:
I have an Access 2000 database, split between front end and back end, which
has four switchboards. Each switchboard has the same three fields in the
Header Section at the top:

Combo12: Unbound, Row Source: SELECT DISTINCTROW
[PatientsByLastName].SocSecNumber, [PatientsByLastName].LastName & ", " &
[PatientsByLastName].FirstName & " " & [PatientsByLastName].MiddleName FROM
[PatientsByLastName];
Combo22: Control Source: SocSecNumber Row Source: SELECT DISTINCTROW
[PatientsByLastName].SocSecNumber, [PatientsByLastName].LastName & ", " &
[PatientsByLastName].FirstName & " " & [PatientsByLastName].MiddleName FROM
[PatientsByLastName];
SocSecNumber: Control Source: SocSecNumber
---------------------------------------------
The user selects the patient using Combo12...

Sub Combo12_AfterUpdate()
' Find the record that matches the control.
Me.RecordsetClone.FindFirst "[SocSecNumber] = '" & Me![Combo12] & "'"
Me.Bookmark = Me.RecordsetClone.Bookmark
End Sub

Private Sub Combo12_Enter()
Combo12.Requery
End Sub

Private Sub Combo12_GotFocus()
' Find the record that matches the control.
Me.RecordsetClone.FindFirst "[SocSecNumber] = '" & Me![Combo12] & "'"
Me.Bookmark = Me.RecordsetClone.Bookmark
End Sub

...then clicks on buttons in the Detail Section to open forms or print
letters or whatever for that patient based on SocSecNumber.
---------------------------------------------
To navigate from one switchboard to the next, without the user having to
re-select the patient each time, I have buttons at the bottom of the
switchboard:
(It has needed the {F6} command to move the focus from the Detail section to
the Header)

Private Sub GoToSwitchboard2_Click()
On Error GoTo Err_GoToSwitchboard2_Click

Dim stDocName As String

stDocName = "Switchboard2"

DoCmd.OpenForm stDocName
[Forms]![Switchboard2]![Combo12] = [Forms]![Switchboard1]![SocSecNumber]
SendKeys "{F6}"
SendKeys "{ENTER}"
[Forms]![Switchboard2]![Combo12].Requery

stDocName = "Switchboard1"

DoCmd.Close acForm, stDocName

Exit_GoToSwitchboard2_Click:
Exit Sub

Err_GoToSwitchboard2_Click:
MsgBox Err.Description
Resume Exit_GoToSwitchboard2_Click

End Sub
---------------------------------------------
For several years, running directly over the network, this has worked fine.
We are moving toward using Terminal Server (Microsoft Windows Server 2003
Standard Edition) running Access 2003.
Under Terminal Server, when moving from switchboard to switchboard, it works
as intended most of the time, but often it will open the new switchboard to
the first record in the pulldown,
almost as if the connection is running a bit slower, and instructions are
being executed out of order?
I have tried migrating the front end to Access 2003, and that did not seem
to make a difference. My master plan is to migrate the back end to Access
2003 then SQL Server,
but that may be months off. Due to remote sites coming on board, the
Terminal Server solution will be implemented, I just need to make it work.
Any insight would be greatly appreciated.
Click to expand...
Click to expand...
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Access 2007 Switchboard buttons stop working 3
Make Switchboard the active window? 1
Problem with Parameter Query from a Form 5
Printing from a form 2
Error running code from Switchboard 4
Open selection from query ComboDropdownList Box on Switchboard 0
Click to Open Record from an Unbound Text Box 1
Error 2585 1

Top