unable to change record

[Guest]

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?

 

[Guest]

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

 

[ruralguy via AccessMonster.com]

Are there really "many" directions for each Member? Are you expecting the
WhereCondition to *create* a record with your ID, because it will not?

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?

 

[Guest]

It worked! Thanks.

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

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?

 

[John Vinson]

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]