Strange stLinkCriteria behaviour on command button

[Anthony Dowd]

Hi

I have two forms which are related by a field called "PatientID". The first
form (Patient Details) has a command button on it that opens the other form
(Operation) when clicked. Below is the code for this form.

***************************************************
Private Sub NextPage2_Click()
On Error GoTo Err_NextPage2_Click

Dim stDocName As String
Dim stLinkCriteria As String


stDocName = "Operation"
stLinkCriteria = "[PatientID]=" & Me![PatientID]

DoCmd.Save acForm, "Patient Details"
If Me.RecordsetClone.RecordCount = 0 Then

MsgBox "You can not open the next form when there are no records in
this one"

Else
DoCmd.Close
DoCmd.OpenForm stDocName, , , stLinkCriteria
DoCmd.Maximize

End If
Exit_NextPage2_Click:
Exit Sub

Err_NextPage2_Click:
MsgBox Err.Description
Resume Exit_NextPage2_Click

End Sub
**************************************************
Other information:

1. Row Source of Combo Box PatientID in tblOperation
SELECT [Patient Details].[PatientID] FROM [Patient Details]; .



2. Row source of PatientId in Operation Form

SELECT [Patient Details].[PatientID] FROM [Patient Details];



3. Default value of PatientID in Operation Form

=DMax("[PatientID]","Patient Details")



So...When the command button on the first form (Patient Details) is clicked,
the second form (ie operation) opens at the correct "PatientID" when:

1. there is an existing record in the second form.
2. I enter the data in the first form and immediately open the second form.

However...The second form does not open at the correct PatientID when I
enter several records in the first form without going to the second form.
This is a requirement of the application. When I do go back to enter the
data in the second form for a particular patient, this form opens at
PatientID = DMax instead of the correct PatientID. If I delete the DMax
function in the default value of PatientID in the Operation Form, then I get
an error in PatientID on the Operation form and must select the value for
PatientID. I want this value to be selected automatically.

How can i open the Operation form at the correct PatientId, even when
entering several records in the first form then going back to enter related
records in the second form some time later?

Thanks in advance
Anthony

 

[Van T. Dinh]

1. Are you aware that the statement

DoCmd.Save acForm, "Patient Details"

saves the *design* of the Form, not the data?

If you want to save the CurrentRecord on the Form, use:

DoCmd.RunCommand acCmdSaveRecord

or simply:

Me.Dirty = False

2. If you use the above, then there *always* exists a Record, i.e. the
CurrentRecord you have just updated into the Table so you don't need the If
statement. However, you DO need to make sure the user enters some data (so
that you can save the Record) before clicking the CommandButton.

3. After saving the Record with 1 above, put the statement:

DBEngine.Idle dbRefreshCache

to make sure the Record is actually updated into the Table rather than
waiting in cache.

4. The second Form (Operation) will open with the *Current Patient* in the
Form "Patient Details". I am not sure what you described in "when I enter
several records in the first form without going to the second form."

5. I think you misunderstood the DefaultValue Property. DefaultValue is
only used in creating NewRecord. In your case, you call the Form Operation
with WhereCondition , i.e. the strLinkCriteria, so you are not creating
NewRecord in the Form Operation.

Check Access Help on DefaultValue Property.

--
HTH
Van T. Dinh
MVP (Access)

Hi

I have two forms which are related by a field called "PatientID". The first
form (Patient Details) has a command button on it that opens the other form
(Operation) when clicked. Below is the code for this form.

***************************************************
Private Sub NextPage2_Click()
On Error GoTo Err_NextPage2_Click

Dim stDocName As String
Dim stLinkCriteria As String


stDocName = "Operation"
stLinkCriteria = "[PatientID]=" & Me![PatientID]

DoCmd.Save acForm, "Patient Details"
If Me.RecordsetClone.RecordCount = 0 Then

MsgBox "You can not open the next form when there are no records in
this one"

Else
DoCmd.Close
DoCmd.OpenForm stDocName, , , stLinkCriteria
DoCmd.Maximize

End If
Exit_NextPage2_Click:
Exit Sub

Err_NextPage2_Click:
MsgBox Err.Description
Resume Exit_NextPage2_Click

End Sub
**************************************************
Other information:

1. Row Source of Combo Box PatientID in tblOperation
SELECT [Patient Details].[PatientID] FROM [Patient Details]; .



2. Row source of PatientId in Operation Form

SELECT [Patient Details].[PatientID] FROM [Patient Details];



3. Default value of PatientID in Operation Form

=DMax("[PatientID]","Patient Details")



So...When the command button on the first form (Patient Details) is clicked,
the second form (ie operation) opens at the correct "PatientID" when:

1. there is an existing record in the second form.
2. I enter the data in the first form and immediately open the second form.

However...The second form does not open at the correct PatientID when I
enter several records in the first form without going to the second form.
This is a requirement of the application. When I do go back to enter the
data in the second form for a particular patient, this form opens at
PatientID = DMax instead of the correct PatientID. If I delete the DMax
function in the default value of PatientID in the Operation Form, then I get
an error in PatientID on the Operation form and must select the value for
PatientID. I want this value to be selected automatically.

How can i open the Operation form at the correct PatientId, even when
entering several records in the first form then going back to enter related
records in the second form some time later?

Thanks in advance
Anthony

 

[Anthony Dowd]

Hi Van

Thanks for your response.

I was not aware that DoCmd.Save acForm "Form Name" only saved the design of
the form. I have removed this line of code and replaced it with
Me.Dirty = False
DBEngine.Idle dbRefreshCache
as you suggested.

After having added these lines of code, I then entered data in the first
form, then clicked on the command button to open the second form
"Operation", the appropriate value for the field "PatientID" appeared in the
drop down list of the combo box. However, I still had to actually select the
value from the drop-down list. Once the value is selected from the drop-down
list, I can go back and forth between forms and the value of "PatientID"
stays the same. I actually want the value for "PatientID" to appear
automatically upon opening the "Operation" form. I even tried changing the
combo box to a text box, but I still had to enter the value of "PatientID"
into the "Operation" form so as to navigate between the forms with the same
value for "PatientID".

I achieved the desired result by adding a macro that uses the setvalue
command for new records (ie when "PatientID" IsNull in the "Operation"
form), but there must be a more efficient way of achieving the same result.

With regards to your Point No. 4, what I mean by entering several records in
the first form without going to the second form is that I want to be able to
enter the details for several patients in the "Patient Details" form without
navigating to the next form (Operation) to enter data. The data in the
"Patient Details" form will be entered before the operation and the data in
the "Operation" form will then be entered after the operation is performed.

Thanks again
Anthony

1. Are you aware that the statement

DoCmd.Save acForm, "Patient Details"

saves the *design* of the Form, not the data?

If you want to save the CurrentRecord on the Form, use:

DoCmd.RunCommand acCmdSaveRecord

or simply:

Me.Dirty = False

2. If you use the above, then there *always* exists a Record, i.e. the
CurrentRecord you have just updated into the Table so you don't need the If
statement. However, you DO need to make sure the user enters some data (so
that you can save the Record) before clicking the CommandButton.

3. After saving the Record with 1 above, put the statement:

DBEngine.Idle dbRefreshCache

to make sure the Record is actually updated into the Table rather than
waiting in cache.

4. The second Form (Operation) will open with the *Current Patient* in the
Form "Patient Details". I am not sure what you described in "when I enter
several records in the first form without going to the second form."

5. I think you misunderstood the DefaultValue Property. DefaultValue is
only used in creating NewRecord. In your case, you call the Form Operation
with WhereCondition , i.e. the strLinkCriteria, so you are not creating
NewRecord in the Form Operation.

Check Access Help on DefaultValue Property.

--
HTH
Van T. Dinh
MVP (Access)

Hi

I have two forms which are related by a field called "PatientID". The first
form (Patient Details) has a command button on it that opens the other form
(Operation) when clicked. Below is the code for this form.

***************************************************
Private Sub NextPage2_Click()
On Error GoTo Err_NextPage2_Click

Dim stDocName As String
Dim stLinkCriteria As String


stDocName = "Operation"
stLinkCriteria = "[PatientID]=" & Me![PatientID]

DoCmd.Save acForm, "Patient Details"
If Me.RecordsetClone.RecordCount = 0 Then

MsgBox "You can not open the next form when there are no records in
this one"

Else
DoCmd.Close
DoCmd.OpenForm stDocName, , , stLinkCriteria
DoCmd.Maximize

End If
Exit_NextPage2_Click:
Exit Sub

Err_NextPage2_Click:
MsgBox Err.Description
Resume Exit_NextPage2_Click

End Sub
**************************************************
Other information:

1. Row Source of Combo Box PatientID in tblOperation
SELECT [Patient Details].[PatientID] FROM [Patient Details]; .



2. Row source of PatientId in Operation Form

SELECT [Patient Details].[PatientID] FROM [Patient Details];



3. Default value of PatientID in Operation Form

=DMax("[PatientID]","Patient Details")



So...When the command button on the first form (Patient Details) is clicked,
the second form (ie operation) opens at the correct "PatientID" when:

1. there is an existing record in the second form.
2. I enter the data in the first form and immediately open the second form.

However...The second form does not open at the correct PatientID when I
enter several records in the first form without going to the second form.
This is a requirement of the application. When I do go back to enter the
data in the second form for a particular patient, this form opens at
PatientID = DMax instead of the correct PatientID. If I delete the DMax
function in the default value of PatientID in the Operation Form, then I get
an error in PatientID on the Operation form and must select the value for
PatientID. I want this value to be selected automatically.

How can i open the Operation form at the correct PatientId, even when
entering several records in the first form then going back to enter related
records in the second form some time later?

Thanks in advance
Anthony

 

[Van T. Dinh]

See comments in-line.

HTH
Van T. Dinh
MVP (Access)

Thanks for your response.

I was not aware that DoCmd.Save acForm "Form Name" only saved the design of
the form. I have removed this line of code and replaced it with
Me.Dirty = False
DBEngine.Idle dbRefreshCache
as you suggested.

After having added these lines of code, I then entered data in the first
form, then clicked on the command button to open the second form
"Operation", the appropriate value for the field "PatientID" appeared in the
drop down list of the combo box.

(as expected)

However, I still had to actually select the value from the drop-down list.
Once the value is selected from the drop-down
list, I can go back and forth between forms and the value of "PatientID"
stays the same.

This is a different question and it relates to the code you use to (re-)
open the Form "Patient Details". I bet you use the value of ComboBox in the
strLinkCriteria of the OpenForm Method. You can simply use the value of the
Field "PatientID" rather than the ComboBox. (See later note.).

I actually want the value for "PatientID" to appear
automatically upon opening the "Operation" form. I even tried changing the
combo box to a text box, but I still had to enter the value of "PatientID"
into the "Operation" form so as to navigate between the forms with the same
value for "PatientID".

I achieved the desired result by adding a macro that uses the setvalue
command for new records (ie when "PatientID" IsNull in the "Operation"
form), but there must be a more efficient way of achieving the same result.

In the Form_Load Event of the Form "Operation", set the value of the
ComboBox like:

Me!ComboBox.Value = Me.Fields("PatientID").Value

With regards to your Point No. 4, what I mean by entering several records in
the first form without going to the second form is that I want to be able to
enter the details for several patients in the "Patient Details" form without
navigating to the next form (Operation) to enter data. The data in the
"Patient Details" form will be entered before the operation and the data in
the "Operation" form will then be entered after the operation is performed.

That's no problem provided that when you switch Forms, you always want the
Form you switch to shows the Current Record of the Form you switch from.