Query Question

[Guiseppe]

Hello,

I'm trying to move a record from one table (prospective client) to another (client). I understand the mechanics of using an append query (to move), then a delete query (to remove from prospective client table), then an update query to change the client type in the 'client' table. What I'm unclear on is how to execute this from the form itself - how do I specify to Access that I want to perform the queries on the active record (in the prospective client form)?



Thanks so much for your assist...and if I overlooked an obvious answer in my search, my apologies!

[Guiseppe]

Follow up - the form that I am using is called 'Prospective Clients', and the control on the form is simply a text box called 'ClientName'. It seems that I would pass the value of the current record to a variable that would then be pushed to my query. That would get me the current record of the form. Is this the way you experts would do this?

[SOS]

The way the Experts would do it is to not have two tables. You would instead have one table and a FLAG to mark a client as a current client or to have a flag to mark them as a prospective client. Then you just change that field to change them from one type to another.

[pbaldy]

I'm no expert, but I certainly agree with SOS about having one table. Inevitably you're going to move somebody over and then have to move them back. If you really want to stay with two tables, one solution would be to have saved queries with a criteria of:

Forms![Prospective Clients].ClientName

and run those queries from code. But I'd go with one table.

[Guiseppe]

Hmmm. I think I'm seeing the light...so i could simply create a query to identify my clients (versus prospectives) and base the record source on my 'clients' form on that query. That's what I get for one too many 'RockStar's this afternoon and too little sleep last night. Thanks!

(PS if it makes any sense, my entire IT background was as a SysAdmin, so forgive my naivety when asking DB questions!) Thanks.

[Gerry]

Don't worry - you're way ahead of where I started. My first Access db (16 years ago) managed to not contain a single query. That's right, I wrote code to do every step of analysis, concatenation, parsing, importing, moving, updating, calculating, and even populating the reports.

Oh, and at the time, I didn't know how to step through code in cycles, so each field on a report had a separate section of code. It took hours to produce the reports, once all the data was in.