Copying Data from tables

  • Thread starter Thread starter Neil Greenough
  • Start date Start date
N

Neil Greenough

Whilst I'm on a role and getting answers to many of my questions, thought I
would try this one too:-

I have two different tables - "Clients" and "Potential Clients." Both tables
have 10 fields, with 5 of these field being the same within each table;
name, address, telephone, contact, email.

Now, when a potential client gets back to me to confirm they would like a
job doing, I would like to be able to click on a button on my "potential
client" form and this will in turn, copy the data from the potential client
table to the client table, that is to say, the data that they share (name,
address etc....). Likewise, once done, I would then like a continuation in
the code that will delete the whole entry in the potential client table, so
that there details only exist in one table.

Again, thanks all for your help :)
 
Why not use one table with a field to indicate whether or not the client is
a potential client?

To make the transfer, run an Append Query to add the record to the Clients
table then run a Delete query to remove the record from the Potential
Clients table. Verify the existence of the new record in the Clients table
first.

strSQL = "INSERT INTO Clients(Name, Address, Telephone, Contact, Email)
SELECT Name, Address, Telephone, Contact, Email FROM PotentialClients WHERE
ContactID = " & Me.txtContactID
'you could also just pass the current values of the controls of the form
instead
CurrentDb.Execute strSQL, dbFailOnError
If Not IsNull(DLookup("[Name]", "[Clients]", "[Name]=""" & Me.txtName & """
And [Address]=""" & Me.txtAddress & """)
'add other criteria as necessary to verify that the check is unique
'so that you know the record go there
'you could either delete the current record of the form here or run a
delete query using the method above
RunCommand acCmdDeleteRecord
End If

Also, Name isn't a good name for a field, it is a reserved word. ClientName
would be better. Also, if this is a person's name, you may find it better to
have separate first name and last name fields. You could concatenate them
together whenever you needed them that way.
 
Fantastic Reply Wayne.

Will look into all of this soon :)

Thanks

Wayne Morgan said:
Why not use one table with a field to indicate whether or not the client
is a potential client?

To make the transfer, run an Append Query to add the record to the Clients
table then run a Delete query to remove the record from the Potential
Clients table. Verify the existence of the new record in the Clients table
first.

strSQL = "INSERT INTO Clients(Name, Address, Telephone, Contact, Email)
SELECT Name, Address, Telephone, Contact, Email FROM PotentialClients
WHERE ContactID = " & Me.txtContactID
'you could also just pass the current values of the controls of the form
instead
CurrentDb.Execute strSQL, dbFailOnError
If Not IsNull(DLookup("[Name]", "[Clients]", "[Name]=""" & Me.txtName &
""" And [Address]=""" & Me.txtAddress & """)
'add other criteria as necessary to verify that the check is unique
'so that you know the record go there
'you could either delete the current record of the form here or run a
delete query using the method above
RunCommand acCmdDeleteRecord
End If

Also, Name isn't a good name for a field, it is a reserved word.
ClientName would be better. Also, if this is a person's name, you may find
it better to have separate first name and last name fields. You could
concatenate them together whenever you needed them that way.

--
Wayne Morgan
MS Access MVP


Neil Greenough said:
Whilst I'm on a role and getting answers to many of my questions, thought
I would try this one too:-

I have two different tables - "Clients" and "Potential Clients." Both
tables have 10 fields, with 5 of these field being the same within each
table; name, address, telephone, contact, email.

Now, when a potential client gets back to me to confirm they would like a
job doing, I would like to be able to click on a button on my "potential
client" form and this will in turn, copy the data from the potential
client
table to the client table, that is to say, the data that they share
(name, address etc....). Likewise, once done, I would then like a
continuation in the code that will delete the whole entry in the
potential client table, so that there details only exist in one table.

Again, thanks all for your help :)
Click to expand...
Click to expand...
 
Back
Top