creating a macro to move data form to a query and delete from curr

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

Hello,

I have created a database and now I have been asked if it's possible that
once an entry form has been marked 'complete' to purge it into a query. I've
looked for a command, or something similiar, but it seems the 2003 version is
limited.

What I want this command to do is once an 'entry form' has been completed by
a USER, to have that particular entry removed and filed to a query, without
removing the incomplete forms.

What function or command do I use to get this done? I was thinking of
placing a 'button' command labeled 'completed' for the USER to press once
the entry is ready to be filed, and having the command execute the macro, but
I don't seem to find a command for that to happen. I'm not very good at
creating macros if this is what I need to do.

Could you please help?

Thanks,--
Maria
 
Maria,

It will help you a lot, I feel, if you can remember this - your data is
stored in Tables. Forms don't have data. They provide a user-friendly
avenue for interacting with the data, but the data itself is in the
tables. Queries don't have data. They provide a way of modulating and
organising the data, but the data itself is in the tables. The idea of
"purge data into a query" doesn't really make sense.

I am not sure what you mean by "the 2003 version is limited". Certainly
Access provides richer functionality that other database development
tools, and Access 2003 is the most feature-rich of all Access versions
except 2007.

So, sorry, but need to get the basic concepts cleared first! :-)

Nor is it really a job for a macro either, probably.

Here's an idea... In the table that the form is based on, add another
field, let's say it's named Completed, Yes/No data type. And then on
the form, you add a checkbox bound to this new field. So the user of
the database will tick this checkbox when the record is completed. Ok.
And then what you do depends whether the existing Record Source of the
form is a table or a query. If it's a query, you add the Completed
field to the design view of the query, and enter this in the Criteria of
this column:
0
If the Record Source for the form is currently a table, then you make a
new query, based on the table, add all fields to it, including the
Completed field, enter 0 in the criteria as mentioned above, and then
you set the Record Source property of the form to be this query instead
of the table.

Now what this will do is every time the form is opened, it will only
show uncompleted records. When a record is marked as Completed, it will
still be visible via the form until the form is closed, then it won't
show next time the form is opened.

How does that sound? Do you think that will serve the purpose?
 
Steve thanks for the reply, I found it after I reposted this same question
again today.

I guess I mispoke about the form vs the table--I do know the difference, I
was trying to get a comman function to actually work on the FORM itself. I'm
basically self taught, and I've noticed some 'runcommands' don't apply to my
database I get an error that says I may have an outdated version, etc., hence
my comment about 2003 being limited.

I've read what you've suggested. The record source for the form is --->YES
a table. What you've suggested sounds like it might work, however could I
make the completed record go to a query or a report, or vice versa for the
outstanding reports to go to a query or report?


Thanks,
 
Maria,

Yes, at any time you will be able to use the Completed field within a
query to select either the completed records, or the outstanding ones,
no problem, and then these queries can be used as the Record Source of
your reports. So all you would need is to use the Criteria in the query
to select which records you want to see, depending on the value Yes/No
of the Completed field.
 
Back
Top