Results 1 to 8 of 8
  1. #1
    Lounger
    Join Date
    Dec 2003
    Posts
    38
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Populate control in another form (Access 2000)

    My task is to place a button in the form clients,with which to open the form Customers, go to a new record, and populate this new customer
    with the data from the client. This client should be then deleted, since he is becoming customer already.

    With the following code i open the new form,go to a new record, but then the data are not transferred :


    Dim stDocName As String
    Dim stLinkCriteria As String
    stDocName = "FCustomers"
    DoCmd.OpenForm stDocName, , , stLinkCriteria
    DoCmd.GoToRecord , "", acNewRec
    DoCmd.GoToControl "customerid"
    Dim f As Form
    Set f = Forms![FCustomers]
    f![CompanyName] = Me![CompanyName]
    f![City] = Me![city]
    f![phone] = Me![phone]


    RunCommand acCmdDeleteRecord

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

    Re: Populate control in another form (Access 2000)

    Your code could be made slightly more efficient: you can specify that you want to go to a new record in DoCmd.OpenForm. Furthermore, you must make sure that the Clients record has been saved, and you must return to the Clients form to delete the current record, otherwise, you will delete the record that has just been added to the Customers form.

    Private Sub cmdTransfer2Clients_Click()
    ' Save record if edited
    If Me.Dirty Then
    RunCommand acCmdSaveRecord
    End If

    ' Open other form
    DoCmd.OpenForm FormName:="FCustomers", DataMode:=acFormAdd

    ' Copy some fields from current record
    With Forms!FCustomers
    !CompanyName= Me!CompanyName
    !City= Me!City
    !Phone= Me!Phone
    End With

    ' Back to first form
    Me.SetFocus

    ' Delete current record
    RunCommand acCmdDeleteRecord
    End Sub

  3. #3
    Lounger
    Join Date
    Dec 2003
    Posts
    38
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Populate control in another form (Access 2000)

    Thank you !!!

  4. #4
    Lounger
    Join Date
    Dec 2003
    Posts
    38
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Populate control in another form (Access 2000)

    Dear Sir,

    I revert to your suggestion, which works so excellent.I came accros however another problem. Unfortunately my table Clients is related with another form called CallsClients. Therefore, if the client has made a call,it is reflected in the table CallsClients ,and then i cannot delete this client,.and i get the message
    "The record cannot be deleted or changed becasue table CallsClients includes related records"
    Can i delete also the records in the table CallsClients? CallsClients has an autonumber CallsID and also contains the field ClientID,
    connected with the field ClientID in the table Clients in one-to-many relationship

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

    Re: Populate control in another form (Access 2000)

    Select Tools | Relationships...
    Double click the line joining Clients and CallsClients.
    Tick the Enforce Referential Integrity and Casecade Delete Related Records check boxes.
    Click OK.
    If you get an error message, you have records that violate the relationship; you will have to find out what's wrong before continuing.
    Otherwise, deleting a record in Clients will now also delete related records in CallsClients.

    Note: wouldn't you want to transfer the CallsClients records to a CallsCustomers table, or is that nonsense?

  6. #6
    Lounger
    Join Date
    Dec 2003
    Posts
    38
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Populate control in another form (Access 2000)

    Dear Sir,

    Thank you for your reply. I am sending you the small db.
    Just to explain our approach. We divide our clients to contacts and customers.
    If a contact has become a customer, he must be deleted and converted into a customer.
    Also, we pay visits, or calls both to the contacts and the customers.
    First you must open the form frmClients. If you want to convert a contact
    into a customer, with the help of your code, then the error appears.:
    error number 3230, the record cannot be deleted or changed because
    table CallsClients includes related records.Therefore i think i should at first delete the related records in the table CallsClient and just then proceed with your code.
    But how can i achieve that ?
    Also, could you please have a look at my db. It is not my own development i have copied it, i think it is very clever.But if you have any notes of irmprovement
    please let me know.

    Regards
    Attached Files Attached Files

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

    Re: Populate control in another form (Access 2000)

    <img src=/S/whisper.gif border=0 alt=whisper width=29 height=17> We're rather informal here in the Lounge, there is no need to address me as Sir. My name is Hans. <img src=/S/smile.gif border=0 alt=smile width=15 height=15>

    I would choose a much simpler structure:
    - Use a single table for clients and customers, with a Yes/No field (check box) to indicate who is a customer.
    - Use a single table for calls
    - Create a relationship between these tables on ClientID, with referential integrity and cascading deletes.
    - Use a main form for clients and customers.
    - Use a subform on this main form for calls; the subform is linked to the main form by ClientID.

    No code needed at all. See the attached version.
    Attached Files Attached Files

  8. #8
    Lounger
    Join Date
    Dec 2003
    Posts
    38
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Populate control in another form (Access 2000)

    Dear Hans,


    I want to thank you for your nice help. I thought that i had a very clever solution
    for the calls on the customers and clients, but what you have ofered is much better,
    it nicely fits to our requirements, it simplifies our work and we
    have now a beter visual grasp for any customer

    Regards

Posting Permissions

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