Howdy, Stranger!

It looks like you're new here. If you want to get involved, click one of these buttons!

Categories

Updating Access db

Is it possible to update two linked Access database fields using an ADODC control for each field?

I get a message informing me that I cannot change a record as a related record is required in another table. The code I am using is below. It works if the tables aren't linked, but that's not much use. I have tried hours to get this to work but to no avail.

REM Updates the first table
Adodc1.Recordset.AddNew
Adodc1.Recordset!A = Text3.Text
Adodc1.Recordset!As = Text4.Text
Adodc1.Recordset!BH = Text5.Text
Adodc1.Recordset!BB = Text6.Text

REM Updates the second table
Adodc2.Recordset.AddNew
Adodc2.Recordset!CN = Text30.Text
Adodc2.Recordset!F = Text31.Text
Adodc2.Recordset!S = Text32.Text
Adodc2.Recordset!De = Text33.Text

Adodc1.Recordset.Update
Adodc2.Recordset.Update

Comments

  • jlegjleg Member Posts: 237
    : Is it possible to update two linked Access database fields using an ADODC control for each field?
    :
    : I get a message informing me that I cannot change a record as a related record is required in another table. The code I am using is below. It works if the tables aren't linked, but that's not much use. I have tried hours to get this to work but to no avail.
    :
    : REM Updates the first table
    : Adodc1.Recordset.AddNew
    : Adodc1.Recordset!A = Text3.Text
    : Adodc1.Recordset!As = Text4.Text
    : Adodc1.Recordset!BH = Text5.Text
    : Adodc1.Recordset!BB = Text6.Text
    :
    : REM Updates the second table
    : Adodc2.Recordset.AddNew
    : Adodc2.Recordset!CN = Text30.Text
    : Adodc2.Recordset!F = Text31.Text
    : Adodc2.Recordset!S = Text32.Text
    : Adodc2.Recordset!De = Text33.Text
    :
    : Adodc1.Recordset.Update
    : Adodc2.Recordset.Update
    :
    Are the recordsets you are sourcing related to each other. If so is it possible to create one recordset including the related data and change that record. You would only need one Ado data control. Or find the relationship and delete it. If it is not required. If there is a relationship and it is required are you trying the change the key on which they are related. You would need to change the parent and cascade the changes to the related child.

    You may be trying to create a record in the parent and child before the the parent is updated with a new record. So then the child has nothing to create the relationship to. Try putting the update for Adodc1 before the Adodc2.Recordset.Add

    This is making the assumption that Adodc1 recordset is the parent of adodc2 and there is a relationship of one to many or one to one.

    May give you more to work on.

    John ;-)
  • akf1akf1 Member Posts: 4
    : : Is it possible to update two linked Access database fields using an ADODC control for each field?
    : :
    : : I get a message informing me that I cannot change a record as a related record is required in another table. The code I am using is below. It works if the tables aren't linked, but that's not much use. I have tried hours to get this to work but to no avail.
    : :
    : : REM Updates the first table
    : : Adodc1.Recordset.AddNew
    : : Adodc1.Recordset!A = Text3.Text
    : : Adodc1.Recordset!As = Text4.Text
    : : Adodc1.Recordset!BH = Text5.Text
    : : Adodc1.Recordset!BB = Text6.Text
    : :
    : : REM Updates the second table
    : : Adodc2.Recordset.AddNew
    : : Adodc2.Recordset!CN = Text30.Text
    : : Adodc2.Recordset!F = Text31.Text
    : : Adodc2.Recordset!S = Text32.Text
    : : Adodc2.Recordset!De = Text33.Text
    : :
    : : Adodc1.Recordset.Update
    : : Adodc2.Recordset.Update
    : :
    : Are the recordsets you are sourcing related to each other. If so is it possible to create one recordset including the related data and change that record. You would only need one Ado data control. Or find the relationship and delete it. If it is not required. If there is a relationship and it is required are you trying the change the key on which they are related. You would need to change the parent and cascade the changes to the related child.
    :
    : You may be trying to create a record in the parent and child before the the parent is updated with a new record. So then the child has nothing to create the relationship to. Try putting the update for Adodc1 before the Adodc2.Recordset.Add
    :
    : This is making the assumption that Adodc1 recordset is the parent of adodc2 and there is a relationship of one to many or one to one.
    :
    : May give you more to work on.
    :
    : John ;-)
    :
    I have an autonumber in the parent field, Adodc1 (primary key) and a number field in the child table, Adodc2 (foreign key). The relationship is set as one to many, enforcing referential integrity. Should I include cascade update related fileds & cascade delete related records? Do you think this is the problem? If so, how do I rectify it?

    Thanks for your help
  • jlegjleg Member Posts: 237
    : : : Is it possible to update two linked Access database fields using an ADODC control for each field?
    : : :
    : : : I get a message informing me that I cannot change a record as a related record is required in another table. The code I am using is below. It works if the tables aren't linked, but that's not much use. I have tried hours to get this to work but to no avail.
    : : :
    : : : REM Updates the first table
    : : : Adodc1.Recordset.AddNew
    : : : Adodc1.Recordset!A = Text3.Text
    : : : Adodc1.Recordset!As = Text4.Text
    : : : Adodc1.Recordset!BH = Text5.Text
    : : : Adodc1.Recordset!BB = Text6.Text
    : : :
    : : : REM Updates the second table
    : : : Adodc2.Recordset.AddNew
    : : : Adodc2.Recordset!CN = Text30.Text
    : : : Adodc2.Recordset!F = Text31.Text
    : : : Adodc2.Recordset!S = Text32.Text
    : : : Adodc2.Recordset!De = Text33.Text
    : : :
    : : : Adodc1.Recordset.Update
    : : : Adodc2.Recordset.Update
    : : :
    : : Are the recordsets you are sourcing related to each other. If so is it possible to create one recordset including the related data and change that record. You would only need one Ado data control. Or find the relationship and delete it. If it is not required. If there is a relationship and it is required are you trying the change the key on which they are related. You would need to change the parent and cascade the changes to the related child.
    : :
    : : You may be trying to create a record in the parent and child before the the parent is updated with a new record. So then the child has nothing to create the relationship to. Try putting the update for Adodc1 before the Adodc2.Recordset.Add
    : :
    : : This is making the assumption that Adodc1 recordset is the parent of adodc2 and there is a relationship of one to many or one to one.
    : :
    : : May give you more to work on.
    : :
    : : John ;-)
    : :
    : I have an autonumber in the parent field, Adodc1 (primary key) and a number field in the child table, Adodc2 (foreign key). The relationship is set as one to many, enforcing referential integrity. Should I include cascade update related fileds & cascade delete related records? Do you think this is the problem? If so, how do I rectify it?
    :
    : Thanks for your help
    :
    Yes, this is most likely the problem. Although I would still try and move the update to adodc1 before you try and add a record to adodc2. With referencial integrity cascade update means that if you change the value in the primary key field, the related foreign key value will change as well. Cascade delete means that if the record in the primary table is deleted the related records in the table with the foreign key will be deleted. There for ensuring referential integrity.

    Your initial comment was that you were changing an existing record. But your code indicates that you are adding records. If you are adding are you making sure that the value of the primary key (autonumber) is in the placed in the related tables foreign key? I can't actually see it in your code.

    John ;-)
  • akf1akf1 Member Posts: 4
    : : : : Is it possible to update two linked Access database fields using an ADODC control for each field?
    : : : :
    : : : : I get a message informing me that I cannot change a record as a related record is required in another table. The code I am using is below. It works if the tables aren't linked, but that's not much use. I have tried hours to get this to work but to no avail.
    : : : :
    : : : : REM Updates the first table
    : : : : Adodc1.Recordset.AddNew
    : : : : Adodc1.Recordset!A = Text3.Text
    : : : : Adodc1.Recordset!As = Text4.Text
    : : : : Adodc1.Recordset!BH = Text5.Text
    : : : : Adodc1.Recordset!BB = Text6.Text
    : : : :
    : : : : REM Updates the second table
    : : : : Adodc2.Recordset.AddNew
    : : : : Adodc2.Recordset!CN = Text30.Text
    : : : : Adodc2.Recordset!F = Text31.Text
    : : : : Adodc2.Recordset!S = Text32.Text
    : : : : Adodc2.Recordset!De = Text33.Text
    : : : :
    : : : : Adodc1.Recordset.Update
    : : : : Adodc2.Recordset.Update
    : : : :
    : : : Are the recordsets you are sourcing related to each other. If so is it possible to create one recordset including the related data and change that record. You would only need one Ado data control. Or find the relationship and delete it. If it is not required. If there is a relationship and it is required are you trying the change the key on which they are related. You would need to change the parent and cascade the changes to the related child.
    : : :
    : : : You may be trying to create a record in the parent and child before the the parent is updated with a new record. So then the child has nothing to create the relationship to. Try putting the update for Adodc1 before the Adodc2.Recordset.Add
    : : :
    : : : This is making the assumption that Adodc1 recordset is the parent of adodc2 and there is a relationship of one to many or one to one.
    : : :
    : : : May give you more to work on.
    : : :
    : : : John ;-)
    : : :
    : : I have an autonumber in the parent field, Adodc1 (primary key) and a number field in the child table, Adodc2 (foreign key). The relationship is set as one to many, enforcing referential integrity. Should I include cascade update related fileds & cascade delete related records? Do you think this is the problem? If so, how do I rectify it?
    : :
    : : Thanks for your help
    : :
    : Yes, this is most likely the problem. Although I would still try and move the update to adodc1 before you try and add a record to adodc2. With referencial integrity cascade update means that if you change the value in the primary key field, the related foreign key value will change as well. Cascade delete means that if the record in the primary table is deleted the related records in the table with the foreign key will be deleted. There for ensuring referential integrity.
    :
    : Your initial comment was that you were changing an existing record. But your code indicates that you are adding records. If you are adding are you making sure that the value of the primary key (autonumber) is in the placed in the related tables foreign key? I can't actually see it in your code.
    :
    : John ;-)
    : I am attempting to add new records to a database.
    I have realised that I have no reference to the auto number in the code. The reason is that I am unsure how to do this as I have tried so many things and failed,
    Adodc1.Recordset!Autonumber = ????? (what should this reference to allow the update???) I cannot work out what to do! Would the adodc2.recordset!number (foreign key) need to be referenced to Adodc1.Recordset!Autonumber (primary key)?
  • jlegjleg Member Posts: 237
    : : : : : Is it possible to update two linked Access database fields using an ADODC control for each field?
    : : : : :
    : : : : : I get a message informing me that I cannot change a record as a related record is required in another table. The code I am using is below. It works if the tables aren't linked, but that's not much use. I have tried hours to get this to work but to no avail.
    : : : : :
    : : : : : REM Updates the first table
    : : : : : Adodc1.Recordset.AddNew
    : : : : : Adodc1.Recordset!A = Text3.Text
    : : : : : Adodc1.Recordset!As = Text4.Text
    : : : : : Adodc1.Recordset!BH = Text5.Text
    : : : : : Adodc1.Recordset!BB = Text6.Text
    : : : : :
    : : : : : REM Updates the second table
    : : : : : Adodc2.Recordset.AddNew
    : : : : : Adodc2.Recordset!CN = Text30.Text
    : : : : : Adodc2.Recordset!F = Text31.Text
    : : : : : Adodc2.Recordset!S = Text32.Text
    : : : : : Adodc2.Recordset!De = Text33.Text
    : : : : :
    : : : : : Adodc1.Recordset.Update
    : : : : : Adodc2.Recordset.Update
    : : : : :
    : : : : Are the recordsets you are sourcing related to each other. If so is it possible to create one recordset including the related data and change that record. You would only need one Ado data control. Or find the relationship and delete it. If it is not required. If there is a relationship and it is required are you trying the change the key on which they are related. You would need to change the parent and cascade the changes to the related child.
    : : : :
    : : : : You may be trying to create a record in the parent and child before the the parent is updated with a new record. So then the child has nothing to create the relationship to. Try putting the update for Adodc1 before the Adodc2.Recordset.Add
    : : : :
    : : : : This is making the assumption that Adodc1 recordset is the parent of adodc2 and there is a relationship of one to many or one to one.
    : : : :
    : : : : May give you more to work on.
    : : : :
    : : : : John ;-)
    : : : :
    : : : I have an autonumber in the parent field, Adodc1 (primary key) and a number field in the child table, Adodc2 (foreign key). The relationship is set as one to many, enforcing referential integrity. Should I include cascade update related fileds & cascade delete related records? Do you think this is the problem? If so, how do I rectify it?
    : : :
    : : : Thanks for your help
    : : :
    : : Yes, this is most likely the problem. Although I would still try and move the update to adodc1 before you try and add a record to adodc2. With referencial integrity cascade update means that if you change the value in the primary key field, the related foreign key value will change as well. Cascade delete means that if the record in the primary table is deleted the related records in the table with the foreign key will be deleted. There for ensuring referential integrity.
    : :
    : : Your initial comment was that you were changing an existing record. But your code indicates that you are adding records. If you are adding are you making sure that the value of the primary key (autonumber) is in the placed in the related tables foreign key? I can't actually see it in your code.
    : :
    : : John ;-)
    : : I am attempting to add new records to a database.
    : I have realised that I have no reference to the auto number in the code. The reason is that I am unsure how to do this as I have tried so many things and failed,
    : Adodc1.Recordset!Autonumber = ????? (what should this reference to allow the update???) I cannot work out what to do! Would the adodc2.recordset!number (foreign key) need to be referenced to Adodc1.Recordset!Autonumber (primary key)?
    :
    You would normally build a relationship between the tables. This may have been done at database level (in Access). They are made between the primary key and the foreign key. ADO and autonumbers can be a bit difficult at times because the value may not be available to use until the record is written to the database. Which means if I start to add a record to adodc1, I cannot use the autonumber value until adodc1 has been updated. Therefore I cannot place the value in adodc2 foreign key field. There is however some properties that can be set on the connection and recordset that will enable the use of the autonumber prior to adodc1 being updated.

    I normally do them in code rather than use the dataenvironment. I pretty sure by my memory that the recordset properties value for IRowsetIdentity allows you to access a value in a autonumber field prior to it being written to the database.

    eg:

    rs.ActiveConnection = cn
    rs.CursorLocation = adUseServer
    rs.CursorType = adOpenKeyset
    rs.LockType = adLockPessimistic
    rs.Properties("IRowsetIdentity") = True
    rs.Open strSQL,,,,adCmdText

    In this code I create a recordset which may be bound to an ADODC control. It allows for the fact that there may be an autonumber field that I may wish to know the value prior to an update. This is only an example and may not apply to your exact needs.

    John ;-)

Sign In or Register to comment.