Carrying field content from one form to another (Access 2K)

Topic

New Reply

Does anyone know of a good and faultproof way to carry the field content of a form to another form that is based on diferent tables. For e.g. I have a frmCustomer where I store information on a customer (based on tblCustomers). How can I then carry over the CustomerID from the frmCustomer to frmTransactions (based on tblTransaction and is using CustomerID as a foreign key). I’ve been storing the currentcustid as global variable and using the OnCurrent event to copy customerID to currentcustid. It works but it is not always faultproof especially when a user click on the cmd button to open frmtransaction before frmCustomer is updated with the new customerid. I hope I am making sense to someone.

Reply | Quote

Replies

If you haven’t done so yet, create a relationship between tblCustomers and tblTransaction on the CustomerID field
Tick the ‘Enforce Referential Integrity’ and ‘Cascade Update Related Fields’ check boxes, then click OK.
This will ensure that if you modify a CustomerID in tblCustomers, it will also be modified in all related records in tblTransaction. No code needed.

Reply | Quote

If you haven’t done so yet, create a relationship between tblCustomers and tblTransaction on the CustomerID field
Tick the ‘Enforce Referential Integrity’ and ‘Cascade Update Related Fields’ check boxes, then click OK.
This will ensure that if you modify a CustomerID in tblCustomers, it will also be modified in all related records in tblTransaction. No code needed.

Reply | Quote

the scenario that i have in my database is slightly crazy. i have pets (tblpets) who don’t always have an owner (tblcustomers) but there are cases where a customer will own more than one pets or a customer may not even have a pet coz they called to complain about some else’s pet. so, there is no direct way for me to relate tblpets to tblcustomers. i am trying to relate tblcustomer to tblpet by using a middle table called tbltransaction. i can directly relate a pet to tbltransaction because a transaction will always involve a pet. but a customer may not always be involve in a transaction. when a transaction does involve a customer i would like for my users to transfer the customerid on frmcustomer to the customerid field on frmtransaction. there is a need for me to relate tblcustomers to tblpets in some way because my users would like to know which pet is owned by who if there is an owner. to a novice like me, this is scenario is driving me bonkers! is there a better way for me to design the database so i can easilt separate out the owners from the non-owners and still have my users filled out the customer info in one place?

Reply | Quote

I would include an OwnerID field in tblPets, linked to CustomerID in tblCustomers. If the pet has an owner, you fill in the OwnerID, otherwise leave it blank. I would include a CustomerID field in tblTransaction, linked to CustomerID in tblCustomers and a PetID field linked to PetID in tblPets. Set ‘enforce referential integrity’ and ‘cascade update related fields’ for all l relationships. Note that OwnerID in tblPets is NOT linked to CustomerID in tblTransaction, as explained by you.

You could make sure that the current record in the customers form has been saved before the transaction form is opened from the command button:

If Me.Dirty Then
RunCommand acCmdSaveRecord
End If
DoCmd.OpenForm “…”

Reply | Quote

I would include an OwnerID field in tblPets, linked to CustomerID in tblCustomers. If the pet has an owner, you fill in the OwnerID, otherwise leave it blank. I would include a CustomerID field in tblTransaction, linked to CustomerID in tblCustomers and a PetID field linked to PetID in tblPets. Set ‘enforce referential integrity’ and ‘cascade update related fields’ for all l relationships. Note that OwnerID in tblPets is NOT linked to CustomerID in tblTransaction, as explained by you.

You could make sure that the current record in the customers form has been saved before the transaction form is opened from the command button:

If Me.Dirty Then
RunCommand acCmdSaveRecord
End If
DoCmd.OpenForm “…”

Reply | Quote

the scenario that i have in my database is slightly crazy. i have pets (tblpets) who don’t always have an owner (tblcustomers) but there are cases where a customer will own more than one pets or a customer may not even have a pet coz they called to complain about some else’s pet. so, there is no direct way for me to relate tblpets to tblcustomers. i am trying to relate tblcustomer to tblpet by using a middle table called tbltransaction. i can directly relate a pet to tbltransaction because a transaction will always involve a pet. but a customer may not always be involve in a transaction. when a transaction does involve a customer i would like for my users to transfer the customerid on frmcustomer to the customerid field on frmtransaction. there is a need for me to relate tblcustomers to tblpets in some way because my users would like to know which pet is owned by who if there is an owner. to a novice like me, this is scenario is driving me bonkers! is there a better way for me to design the database so i can easilt separate out the owners from the non-owners and still have my users filled out the customer info in one place?

Reply | Quote