Need a code to move a record from one table to another arfer updat

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

Guest

I had a list of checks and will like to move the items from one table will
all the checks to another table with the cashed checks for reconciliation
purposes. It could be using a check box or a command bottom to update.

Help me please !

You are the best ! :-)
 
Jannette,

This doesn't seem like a very good idea. In general, you should not have
more than one tables with the same structure; you should have one table
with all the records in it. Let me explain: what you are doing by moving
records from one table to another, is essentially storing information
(cheque is cashed) not in a table field, but in the fact that the record
is in the particular table. This can result in anumber of problems,
like, for instance:
(a) if you are looking for a particular cheque, how do you know which
table it is in?
(b) if you need to report on all cheques, you need to employ a union
query (plus other workarounds, possibly) in order to bring together the
records form both tables
etc.
My proposed solution: keep all records in one table; use a yes/no field
or a DateCashed field to store the information that a particular cheque
has actually been cashed or not.
To separate the cashed from the non-cashed ones, use queries with the
appropriate criteria on the Yes/No or DateCahsed field.

HTH,
Nikos
 
Thanks for the info Nikos but let me ask you something else. The yes/no
field will help me too if I can make this "yes field" hidden to the user of
the form. This is because the user will use a bound form to see all the non
cash checks and mark the ones show in the Bank Statement. For the next month
those checks marked should not appear or show in the form whenever the new
statement arrive.

This is the reason to move the checks from a table to another, but if there
is a better way to do it, I will be glad to know and will greatly appreciate
your help.

Thanks for everything :-)
Jeannette
 
If I understand this correctly, then all you need to do is make a query
on the table, set a criterion to select only the records where the
Yes/No field is false, and use that query as your form's recordsource,
as opposed to using the table itself.
This way, checks that are marked as cashed will never appear in the form
again.

HTH,
Nikos
 
Hi Nikos:

You're the master. You were so right. It works as you said. But I had
another question. When I close the form and open it back it is updated but
how can I add a control to the form to update it without closing the form.
Refresh, re-open, something like that.

The data is show in a Subform.

Thank you, your help is really appreciated.
 
Jeannette,

Open your form in design view, and add a command button (to do the
refreshing whenever you want). When the command button wizard comes on,
click Cancel (so you don't go through the rest of it).

Open the properties window (and select the command button, if
deselected). Select tab Format, and change the Caption property, to set
the text 9on the button (Refresh or something like that, I guess). Then
select tab Other, and change the name property (name by which Access
recognizes your command button) to something meaningful, like
cmdRefresh. Next, select tab Events, and put the cursor in the Click
event box; click on the little button that appears on the right, and
select Code builder n the pop-up. You will be taken to a VBA editor
screen, and the cursor will be in between to lines like:

Private Sub cmdRefresh_Click()

End Sub

Just paste the following line of code between them:

Me.[The name of the subform here].Requery

(keep the square brackets if your cubform name contains spaces or other
"funny" characters, otherwise you can drop them).

Close the VBA window, save the form and you are done!

Alternatively, you could choose to requery automatically everytime a
record is checked, but that might be inconvenient, as it will (a)
requery too often (?), and (b) won't give you the chance to uncheck an
accidental check in the wrong record.

HTH,
Nikos
 
Back
Top