If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
Strange stLinkCriteria behaviour on command button
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 |
#2
|
|||
|
|||
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) "Anthony Dowd" wrote in message ... 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 |
#3
|
|||
|
|||
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 "Van T. Dinh" wrote in message ... 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) "Anthony Dowd" wrote in message ... 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 |
#4
|
|||
|
|||
See comments in-line.
HTH Van T. Dinh MVP (Access) "Anthony Dowd" wrote in message ... 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. (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. Thanks again Anthony |
Thread Tools | |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Thread Starter | Forum | Replies | Last Post |
Strange Characters (looks like Chinese) in Access Form | Sam Berry | Setting Up & Running Reports | 1 | August 9th, 2004 05:29 PM |
Strange behaviour in Code | lalexander | New Users | 2 | August 6th, 2004 12:10 PM |
Strange behaviour in Code | lalexander | Running & Setting Up Queries | 2 | August 6th, 2004 12:10 PM |
Strange File behaviour | Wolfman | General Discussion | 0 | May 24th, 2004 02:36 PM |
strange SUM behaviour | Peo Sjoblom | Worksheet Functions | 5 | January 20th, 2004 05:59 PM |