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
|
|||
|
|||
unable to change record
I have a table called tblMemberData, which has an autonumber/primary field
called “ID” and several other fields. A second table, called tblDirections, has two fields, ID (number) and Directions (memo field). I have established a one-to-many relationship between tblMemberData and tblDirections based on the ID field. I have a form based on the first table, with all the fields visable. I have a second form (“frmDirections”), based on the second table, with the two fields showing (I have the ID field showing just for testing purposes now). On the first form, I have a button with the following “on click” event procedure (some lines are left out to conserve space here): stDocName = "frmDirections" stLinkCriteria = "[ID]=" & Me![ID] DoCmd.OpenForm stDocName, , , stLinkCriteria If I have the main form open to the record with, say an ID field of 2 and I click the button to open the directions form, the form opens, but it shows “0” in the ID field. When I try to close the form after adding some data I get the following error: “You cannot add or change a record because a related record is required in the table “tblMemberData.” I know it’s simple, but what did I leave out? |
#2
|
|||
|
|||
unable to change record
Two things I would add to the code
1. Save the record before the second form is open, and pass the Id using the OpenArgs If Me.Dirty Then Me.Dirty = False End If stDocName = "frmDirections" stLinkCriteria = "[ID]=" & Me![ID] DoCmd.OpenForm stDocName, , , stLinkCriteria , , , Me![ID] 2. On the load event of the second form check if any record is related to the Id, if no, assign the Id that you passed with the OpenArgs to the Id field in the form If Me.RecordsetClone.RecordCount = 0 Then Me.Id = Me.OpenArgs End If -- Good Luck BS"D "JWCrosby" wrote: I have a table called tblMemberData, which has an autonumber/primary field called “ID” and several other fields. A second table, called tblDirections, has two fields, ID (number) and Directions (memo field). I have established a one-to-many relationship between tblMemberData and tblDirections based on the ID field. I have a form based on the first table, with all the fields visable. I have a second form (“frmDirections”), based on the second table, with the two fields showing (I have the ID field showing just for testing purposes now). On the first form, I have a button with the following “on click” event procedure (some lines are left out to conserve space here): stDocName = "frmDirections" stLinkCriteria = "[ID]=" & Me![ID] DoCmd.OpenForm stDocName, , , stLinkCriteria If I have the main form open to the record with, say an ID field of 2 and I click the button to open the directions form, the form opens, but it shows “0” in the ID field. When I try to close the form after adding some data I get the following error: “You cannot add or change a record because a related record is required in the table “tblMemberData.” I know it’s simple, but what did I leave out? |
#3
|
|||
|
|||
unable to change record
Are there really "many" directions for each Member? Are you expecting the
WhereCondition to *create* a record with your ID, because it will not? JWCrosby wrote: I have a table called tblMemberData, which has an autonumber/primary field called “ID” and several other fields. A second table, called tblDirections, has two fields, ID (number) and Directions (memo field). I have established a one-to-many relationship between tblMemberData and tblDirections based on the ID field. I have a form based on the first table, with all the fields visable. I have a second form (“frmDirections”), based on the second table, with the two fields showing (I have the ID field showing just for testing purposes now). On the first form, I have a button with the following “on click” event procedure (some lines are left out to conserve space here): stDocName = "frmDirections" stLinkCriteria = "[ID]=" & Me![ID] DoCmd.OpenForm stDocName, , , stLinkCriteria If I have the main form open to the record with, say an ID field of 2 and I click the button to open the directions form, the form opens, but it shows “0” in the ID field. When I try to close the form after adding some data I get the following error: “You cannot add or change a record because a related record is required in the table “tblMemberData.” I know it’s simple, but what did I leave out? -- HTH - RuralGuy (RG for short) acXP WinXP Pro Please post back to this forum so all may benefit. Message posted via AccessMonster.com http://www.accessmonster.com/Uwe/For...forms/200608/1 |
#4
|
|||
|
|||
unable to change record
It worked! Thanks.
"Ofer Cohen" wrote: Two things I would add to the code 1. Save the record before the second form is open, and pass the Id using the OpenArgs If Me.Dirty Then Me.Dirty = False End If stDocName = "frmDirections" stLinkCriteria = "[ID]=" & Me![ID] DoCmd.OpenForm stDocName, , , stLinkCriteria , , , Me![ID] 2. On the load event of the second form check if any record is related to the Id, if no, assign the Id that you passed with the OpenArgs to the Id field in the form If Me.RecordsetClone.RecordCount = 0 Then Me.Id = Me.OpenArgs End If -- Good Luck BS"D "JWCrosby" wrote: I have a table called tblMemberData, which has an autonumber/primary field called “ID” and several other fields. A second table, called tblDirections, has two fields, ID (number) and Directions (memo field). I have established a one-to-many relationship between tblMemberData and tblDirections based on the ID field. I have a form based on the first table, with all the fields visable. I have a second form (“frmDirections”), based on the second table, with the two fields showing (I have the ID field showing just for testing purposes now). On the first form, I have a button with the following “on click” event procedure (some lines are left out to conserve space here): stDocName = "frmDirections" stLinkCriteria = "[ID]=" & Me![ID] DoCmd.OpenForm stDocName, , , stLinkCriteria If I have the main form open to the record with, say an ID field of 2 and I click the button to open the directions form, the form opens, but it shows “0” in the ID field. When I try to close the form after adding some data I get the following error: “You cannot add or change a record because a related record is required in the table “tblMemberData.” I know it’s simple, but what did I leave out? |
#5
|
|||
|
|||
unable to change record
On Tue, 29 Aug 2006 14:58:01 -0700, JWCrosby
wrote: On the first form, I have a button with the following on click event procedure (some lines are left out to conserve space here): stDocName = "frmDirections" stLinkCriteria = "[ID]=" & Me![ID] DoCmd.OpenForm stDocName, , , stLinkCriteria Do you have some particular reason not to use the powerful Subform capability? If you make the second form a subform of the first, with ID as the Master/Child Link Field, it will synch the data in the two forms and fill in the ID in new records, with no code at all. John W. Vinson[MVP] |
Thread Tools | |
Display Modes | |
|
|