Results 1 to 4 of 4
  1. #1
    Lounger
    Join Date
    Mar 2003
    Posts
    46
    Thanks
    0
    Thanked 0 Times in 0 Posts

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

    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.

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

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

    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.

  3. #3
    Lounger
    Join Date
    Mar 2003
    Posts
    46
    Thanks
    0
    Thanked 0 Times in 0 Posts

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

    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?

  4. #4
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

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

    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 "..."

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •