'Move data from one table to another' Button? 1

[PaulChilds]

Hi,

I have two forms (based on two tables). One for existing clients and one for prospective clients.

When a prospective client signs up to the company, I need a button or somnething that deletes the record from the Prospective CLients tables/form and moves it over to the existing clients table.

Any ideas?

The tables/forms are called: Clients & Prospective Clients.

I'd appreciate some help on this one!

Cheers.

PC

http://www.worship-leader.co.uk

http://www.liverpooldream.co.uk

http://www.ormskirkparish.co.uk

http://www.cadetsltd.co.uk

 

[KenReay]

Hi

First Question, why have two tables?, why not just have one table, with an indicator in each row for Prospective/Actual, then you would not need to copy the record, but simply the indicator

but to answer yor question

In the button on click event somethin like:

DoCmd.SetWarnings False
DoCmd.RunSQL "INSERT INTO tblActual SELECT * FROM tblProspect WHERE Id = " & Me.Id & ";"
DoCmd.RunSQL "DELETE FROM tblProspect WHERE Id = " & Me.Id & ";"
DoCmd.SetWarnings True

You need to adjust the table names, and the key field name, also if you key field is a string you need ..WHERE Id = '" & Me.Id & "';"

Regards

Ken Reay
Freelance Solutions Developer
Boldon Information Systems Ltd
Website needs upgrading, but for now -

http://www.kenneth.reay.btinternet.co.uk

UK

 

[PaulChilds]

There's probably a really simple way to do this that I forgot:

"Prospective Clients" table has a space in the title, and it's causing errors. I though underscore _ would work in place of the space but it didn't.

What do i do?

http://www.worship-leader.co.uk

http://www.liverpooldream.co.uk

http://www.ormskirkparish.co.uk

http://www.cadetsltd.co.uk

 

[KenReay]

Hi

[Prospective Clients]

and do not use embedded spaces in table names or column names, or control names in future (Joke) Regards

Ken Reay
Freelance Solutions Developer
Boldon Information Systems Ltd
Website needs upgrading, but for now -

http://www.kenneth.reay.btinternet.co.uk

UK

 

[PaulChilds]

I get

Run-Time error 3131

Syntax error in from clause

Any ideas what that is all about?

http://www.worship-leader.co.uk

http://www.liverpooldream.co.uk

http://www.ormskirkparish.co.uk

http://www.cadetsltd.co.uk

 

[KenReay]

Hi

Not unless you post the code you are using Regards

Ken Reay
Freelance Solutions Developer
Boldon Information Systems Ltd
Website needs upgrading, but for now -

http://www.kenneth.reay.btinternet.co.uk

UK

 

[PaulChilds]

'Identifier' is the primary key field in both tables:

DoCmd.SetWarnings False
DoCmd.RunSQL "INSERT INTO tblClients SELECT * FROM tbl[Prospective Clients] WHERE Id = '" & Me.Identifier & "';"
DoCmd.RunSQL "DELETE FROM tbl[Prospective Clients] WHERE Id = '" & Me.Identifier & "';"
DoCmd.SetWarnings True

http://www.worship-leader.co.uk

http://www.liverpooldream.co.uk

http://www.ormskirkparish.co.uk

http://www.cadetsltd.co.uk

 

[KenReay]

Hi

Assuming the table name is "tblProspective Clients" not "Prospective Clients"

DoCmd.SetWarnings False
DoCmd.RunSQL "INSERT INTO tblClients SELECT * FROM [tblProspective Clients] WHERE Id = '" & Me.Identifier & "';"
DoCmd.RunSQL "DELETE FROM [tblProspective Clients] WHERE Id = '" & Me.Identifier & "';"
DoCmd.SetWarnings True Regards

Ken Reay
Freelance Solutions Developer
Boldon Information Systems Ltd
Website needs upgrading, but for now -

http://www.kenneth.reay.btinternet.co.uk

UK

 

[PaulChilds]

The tables don't have tbl, so i deleted that from the code, and tried it.

No errors came up this time but nothing happened.

http://www.worship-leader.co.uk

http://www.liverpooldream.co.uk

http://www.ormskirkparish.co.uk

http://www.cadetsltd.co.uk

 

[KenReay]

Hi

Ok, so no error means the SQL 'reads' OK, but maybe it cannot fine anything with Id to match the id you gave it.

Suggest you put abreakpoint on the first d0cmd.runsql

Run

Whenit stops examine the value of Me.Identifier and make sure there is a matching key in Table [Prospective Clients] Regards

Ken Reay
Freelance Solutions Developer
Boldon Information Systems Ltd
Website needs upgrading, but for now -

http://www.kenneth.reay.btinternet.co.uk

UK

 

[PaulChilds]

I didn't get a chance to do the above, because Now i get a box coming up asking me to enter and id.

http://www.worship-leader.co.uk

http://www.liverpooldream.co.uk

http://www.ormskirkparish.co.uk

http://www.cadetsltd.co.uk

 

[KenReay]

Hi

Is Id the name of the column in the tables Clients and [Prospective Clients] which uniquely identifies the client?

Regards

Ken Reay
Freelance Solutions Developer
Boldon Information Systems Ltd
Website needs upgrading, but for now -

http://www.kenneth.reay.btinternet.co.uk

UK

 

[PaulChilds]

No - it's identifier

http://www.worship-leader.co.uk

http://www.liverpooldream.co.uk

http://www.ormskirkparish.co.uk

http://www.cadetsltd.co.uk

 

[KenReay]

Hi

Well that is your problem you need:

DoCmd.SetWarnings False
DoCmd.RunSQL "INSERT INTO tblClients SELECT * FROM [Prospective Clients] WHERE Identifier = '" & Me.Identifier & "';"
DoCmd.RunSQL "DELETE FROM [Prospective Clients] WHERE Identifier = '" & Me.Identifier & "';"
DoCmd.SetWarnings True

Regards

Ken Reay
Freelance Solutions Developer
Boldon Information Systems Ltd
Website needs upgrading, but for now -

http://www.kenneth.reay.btinternet.co.uk

UK

 

[PaulChilds]

That works great now thanks except...

It leaves the page of the form with ##DELETED## in all the fields.

Any way to stop this?

http://www.worship-leader.co.uk

http://www.liverpooldream.co.uk

http://www.ormskirkparish.co.uk

http://www.cadetsltd.co.uk

 

[KenReay]

Hi

Requery the recordsource of the form so

Me.Requery

after the DELETE SQL Regards

Ken Reay
Freelance Solutions Developer
Boldon Information Systems Ltd
Website needs upgrading, but for now -

http://www.kenneth.reay.btinternet.co.uk

UK

 

[PaulChilds]

SMASHING!!!

Thanks for all your help!

I'll give you lots of stars!

http://www.worship-leader.co.uk

http://www.liverpooldream.co.uk

http://www.ormskirkparish.co.uk

http://www.cadetsltd.co.uk