Populate control in another form (Access 2000)

Topic

New Reply

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

Reply | Quote

Replies

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

Reply | Quote

Thank you !!!

Reply | Quote

Thank you !!!

Reply | Quote

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

Reply | Quote

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?

Reply | Quote

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

Reply | Quote

We’re rather informal here in the Lounge, there is no need to address me as Sir. My name is Hans.

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.

Reply | Quote

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

Reply | Quote

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

Reply | Quote

We’re rather informal here in the Lounge, there is no need to address me as Sir. My name is Hans.

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.

Reply | Quote

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

Reply | Quote

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?

Reply | Quote

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

Reply | Quote