By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
434,587 Members | 1,079 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 434,587 IT Pros & Developers. It's quick & easy.

Reset values on startup

P: 16
I am building a db, and I have 3 different tables/forms that I need to transfer records between. Current Clients, Past Clients, and Prospective. Now i have researched this for almost a week now, and i found similar posts, but no one every says how they resolved them.
What I am doing:
I have in each table a yes/no that is called Append. I made an append query, that upon Yes, it will append 1 record, then i have manually delete it from the originating source. My issue is I either need a more efficient way of doing this, or a way to reset the Append values when the form starts up.
Issue: After being appended the value is still yes, instead of going though, finding the record, resetting it after every time. Thanks in advance for any/all help.
Jan 30 '08 #1
Share this Question
Share on Google+
7 Replies


MindBender77
100+
P: 234
I am building a db, and I have 3 different tables/forms that I need to transfer records between. Current Clients, Past Clients, and Prospective. Now i have researched this for almost a week now, and i found similar posts, but no one every says how they resolved them.
What I am doing:
I have in each table a yes/no that is called Append. I made an append query, that upon Yes, it will append 1 record, then i have manually delete it from the originating source. My issue is I either need a more efficient way of doing this, or a way to reset the Append values when the form starts up.
Issue: After being appended the value is still yes, instead of going though, finding the record, resetting it after every time. Thanks in advance for any/all help.
I would try creating a Macro that runs the append query, then the delete query and finally a update query.. This should automate things much more efficiently.

Hope this Helps,
JS
Jan 30 '08 #2

P: 16
I apologize could you explain that a little more in detail. I do have a macro set to run the append query. also this is something that needs to be permanent.
Jan 30 '08 #3

MindBender77
100+
P: 234
I apologize could you explain that a little more in detail. I do have a macro set to run the append query. also this is something that needs to be permanent.
Using the same macro that runs the append query, on the next line, have it run a delete query that deletes the records from its originating source. On the line below the delete query, run an update query on the appended data to reset the values.

Basically, your running 3 queries in a specified order on the execution of this macro. This very well could be a permanent event.

JS
Jan 30 '08 #4

P: 16
I appreciate the quick responses! But I guess to re-phrase that, I know what you mean but in the create a macro, i dont see or know how to make the macro delete a record. btw i am using 03. I can delete object, and an entire table/query. I'm sorry, you must think im an idiot, im just new to access.
Jan 30 '08 #5

MindBender77
100+
P: 234
I appreciate the quick responses! But I guess to re-phrase that, I know what you mean but in the create a macro, i dont see or know how to make the macro delete a record. btw i am using 03. I can delete object, and an entire table/query. I'm sorry, you must think im an idiot, im just new to access.
My apologies, everyone was new at one point or another.
I'll try to clarify. First, design a delete query using the query designer that deletes the the records from their source of origin. Next, design an update query that updates the yes/no property of the appended data, again using the query designer.

As for the Macro, this simply just tells each query to run. So you'll want the macro to be designed like this (Macro Design view):

OpenQuery - Append query
OpenQuery - Delete query
OpenQuery - Update query

Let me know if you need further assistance,
JS
Jan 30 '08 #6

P: 16
Holy crap! I am a moron!!! I seen the delete/update, but I never decided to look at them because I didnt think they could be use to me! Thanks a lot!!
Im just going to say what I did because I hate searching for things and people just end by saying thanks and not what they did, also you can tell me if this is right.

Create an append query to add only the selected records ([append]-yes/no) then a macro to run all three querys.
The delete one will remove it from my past form.
The update one will set all Appends to no.

Thanks again!
Jan 30 '08 #7

MindBender77
100+
P: 234
Create an append query to add only the selected records ([append]-yes/no) then a macro to run all three querys.
The delete one will remove it from my past form.
The update one will set all Appends to no.

Thanks again!
Yes, you are correct.

Glad I could help and good luck,
JS
Jan 30 '08 #8

Post your reply

Sign in to post your reply or Sign up for a free account.