Opening a related record in a linked form

  • Thread starter Thread starter Anthony
  • Start date Start date
A

Anthony

Hello

First problem:

I have a form called "Patient Details" with a command button, which is
supposed to act as a next button and open a second form called "Indication"
at a record with the same ID number (i.e., PatientID) as in the first form.

The form opens ok, but not at the record with the same PatientID as in the
previous form. The code used for this command button is:

Private Sub NextPage2_Click()
On Error GoTo Err_NextPage2_Click


DoCmd.Close

Dim stDocName As String
Dim stLinkCriteria As String

stDocName = "Indication"
DoCmd.OpenForm stDocName, , , stLinkCriteria
DoCmd.GoToRecord , , acNewRec

Exit_NextPage2_Click:
Exit Sub

Err_NextPage2_Click:
MsgBox Err.Description
Resume Exit_NextPage2_Click

End Sub

Also, the Row Source of PatientID in the "Indication" form is: SELECT
[Patient Details].[PatientID] FROM [Patient Details];

Second problem:

On the second form, "Indication", I also have a command button that is
supposed to act as a "back" button and take the user back to the "Patient
Details" form, again to the record with the same PatientID.

The correct form opens, but an error message appears:
"The expression you entered refers to an object that is closed or doesn't
exist" and the form doesn't open at the record with the PatientID that was
active on the previous form. The code for this command button is:

Private Sub Command17_Click()
On Error GoTo Err_Command17_Click


DoCmd.Close

Dim stDocName As String
Dim stLinkCriteria As String

stDocName = "Patient Details"
DoCmd.OpenForm stDocName, , , stLinkCriteria
stLinkCriteria = "[PatientID]=" & Me![PatientID]

Exit_Command17_Click:
Exit Sub

Err_Command17_Click:
MsgBox Err.Description
Resume Exit_Command17_Click

End Sub


Any help with where I am going wrong here would be greatly appreciated.
Thanks in advance
Anthony
 
You need to fill in stLinkCriteria before the OpenForm call. The stLinkCriteria is missing
completely from the first call (it is not being assigned a value). The syntax you have for
this in the second function should also work in the first, just remember to place it
before the OpenForm call. Also, you are closing the present form before you get the value
from it that you need for the stLinkCriteria. Close it after you get the value.

Unless you have GoTo statements or something else to change the flow of what is happening,
the code statements will be executed in order from top to bottom. If you get to a
statement that wants to use the value of a variable, such as the OpenForm statement trying
to use the value of stLinkCriteria, then you have to set that value before you get to the
statement that is trying to use it. In this case you won't get an error, the variables
value is simply "" (an empty string) if you haven't set it, which causes no problem at
all.

--
Wayne Morgan


Anthony said:
Hello

First problem:

I have a form called "Patient Details" with a command button, which is
supposed to act as a next button and open a second form called "Indication"
at a record with the same ID number (i.e., PatientID) as in the first form.

The form opens ok, but not at the record with the same PatientID as in the
previous form. The code used for this command button is:

Private Sub NextPage2_Click()
On Error GoTo Err_NextPage2_Click


DoCmd.Close

Dim stDocName As String
Dim stLinkCriteria As String

stDocName = "Indication"
DoCmd.OpenForm stDocName, , , stLinkCriteria
DoCmd.GoToRecord , , acNewRec

Exit_NextPage2_Click:
Exit Sub

Err_NextPage2_Click:
MsgBox Err.Description
Resume Exit_NextPage2_Click

End Sub

Also, the Row Source of PatientID in the "Indication" form is: SELECT
[Patient Details].[PatientID] FROM [Patient Details];

Second problem:

On the second form, "Indication", I also have a command button that is
supposed to act as a "back" button and take the user back to the "Patient
Details" form, again to the record with the same PatientID.

The correct form opens, but an error message appears:
"The expression you entered refers to an object that is closed or doesn't
exist" and the form doesn't open at the record with the PatientID that was
active on the previous form. The code for this command button is:

Private Sub Command17_Click()
On Error GoTo Err_Command17_Click


DoCmd.Close

Dim stDocName As String
Dim stLinkCriteria As String

stDocName = "Patient Details"
DoCmd.OpenForm stDocName, , , stLinkCriteria
stLinkCriteria = "[PatientID]=" & Me![PatientID]

Exit_Command17_Click:
Exit Sub

Err_Command17_Click:
MsgBox Err.Description
Resume Exit_Command17_Click

End Sub


Any help with where I am going wrong here would be greatly appreciated.
Thanks in advance
Anthony
Click to expand...
 
Thanks for you help Wayne. By moving the Close command below the OpenForm
command and also by removing the GoToRecord,,acNewRec command, I am now able
to move between forms displaying the records relating to the same
PatientID.However, I am still having problems when entering data for a new
patient.

Once I enter the data for the new patient and click on the command button to
go to the next form, a zero is displayed in the "PatientID" field where the
PatientID for the new record should be displayed. Having said that, the new
PatientID is present on the drop-down list.

How do I get this new value to display as the default?

Thanks once again,
Anthony


Wayne Morgan said:
You need to fill in stLinkCriteria before the OpenForm call. The stLinkCriteria is missing
completely from the first call (it is not being assigned a value). The syntax you have for
this in the second function should also work in the first, just remember to place it
before the OpenForm call. Also, you are closing the present form before you get the value
from it that you need for the stLinkCriteria. Close it after you get the value.

Unless you have GoTo statements or something else to change the flow of what is happening,
the code statements will be executed in order from top to bottom. If you get to a
statement that wants to use the value of a variable, such as the OpenForm statement trying
to use the value of stLinkCriteria, then you have to set that value before you get to the
statement that is trying to use it. In this case you won't get an error, the variables
value is simply "" (an empty string) if you haven't set it, which causes no problem at
all.

--
Wayne Morgan


Anthony said:
Hello

First problem:

I have a form called "Patient Details" with a command button, which is
supposed to act as a next button and open a second form called "Indication"
at a record with the same ID number (i.e., PatientID) as in the first form.

The form opens ok, but not at the record with the same PatientID as in the
previous form. The code used for this command button is:

Private Sub NextPage2_Click()
On Error GoTo Err_NextPage2_Click


DoCmd.Close

Dim stDocName As String
Dim stLinkCriteria As String

stDocName = "Indication"
DoCmd.OpenForm stDocName, , , stLinkCriteria
DoCmd.GoToRecord , , acNewRec

Exit_NextPage2_Click:
Exit Sub

Err_NextPage2_Click:
MsgBox Err.Description
Resume Exit_NextPage2_Click

End Sub

Also, the Row Source of PatientID in the "Indication" form is: SELECT
[Patient Details].[PatientID] FROM [Patient Details];

Second problem:

On the second form, "Indication", I also have a command button that is
supposed to act as a "back" button and take the user back to the "Patient
Details" form, again to the record with the same PatientID.

The correct form opens, but an error message appears:
"The expression you entered refers to an object that is closed or doesn't
exist" and the form doesn't open at the record with the PatientID that was
active on the previous form. The code for this command button is:

Private Sub Command17_Click()
On Error GoTo Err_Command17_Click


DoCmd.Close

Dim stDocName As String
Dim stLinkCriteria As String

stDocName = "Patient Details"
DoCmd.OpenForm stDocName, , , stLinkCriteria
stLinkCriteria = "[PatientID]=" & Me![PatientID]

Exit_Command17_Click:
Exit Sub

Err_Command17_Click:
MsgBox Err.Description
Resume Exit_Command17_Click

End Sub


Any help with where I am going wrong here would be greatly appreciated.
Thanks in advance
Anthony
Click to expand...
Click to expand...
 
How many columns are in the drop down list (visible or hidden)? If there is more than one,
what is the bound column?
 
I am not sure how I should go about determining how many columns are in the
drop down list (after the fact)using the "properties" box. However, I can
tell you that this drop-down list is a combo box in the underlying table
which has one bound column called "PatientID", the Row Source Type set as
"Table/Query" and the Row Source as:
SELECT [Patient Details].[PatientID] FROM Forms![Patient Details];

This must(could?) be the source of my problem, because I get an error
message saying, "Syntax error in FROM clause". Any suggestions on how I
should proceed?

Anthony
 
Problem solved!!!

I corrected the statement for the bound column by removing "Forms!". I also
inserted a statement for the default value (i.e.,
=DMax("[PatientID]","Patient Details")) and everything is working as it
should.

Wayne, thank you very much for all of your assistance.
Anthony
Anthony said:
I am not sure how I should go about determining how many columns are in the
drop down list (after the fact)using the "properties" box. However, I can
tell you that this drop-down list is a combo box in the underlying table
which has one bound column called "PatientID", the Row Source Type set as
"Table/Query" and the Row Source as:
SELECT [Patient Details].[PatientID] FROM Forms![Patient Details];

This must(could?) be the source of my problem, because I get an error
message saying, "Syntax error in FROM clause". Any suggestions on how I
should proceed?

Anthony
Click to expand...
 
Back
Top