How do I View a Table in VBA

  • Thread starter Thread starter AAVF IT
  • Start date Start date
A

AAVF IT

This may sound a daft question, but I am new to VBA and have not yet got
hold of a decent manual.

I just want to use a module to open a table (TEST1) and examine a record
(REC_COUNT) and then move on to the next record. I will eventually be
wanting to act on content of the record but I cannot even open the damn
thing!

I think if I get this far, I can work out the rest.

I will eventually be running this code from a macro. At present, I am trying
to run it from the VBA editor and it keeps reporting errors.

Can somebody tell me the code for this please?

Many thanks

Regards

Tom
 
First of all, forms are better than direct table access to view and
manipulate records.

DoCmd.OpenTable tablename, view, datamode - will do this for you. See the
help file the settings you can use. Once open though, there is not much you
can do to them through code.

Mike Storr
www.veraccess.com
 
Thanks for your advice. Unfortunately, what I am trying to do here goes a
lot further than I have outlined in the posting. The table in question holds
an autocounter column to ensure correct sorting, but it also contains a
field that shows the quantity of new work to be done that day (in units).
Another column shows the maximum units that can be handled that day. I need
to be able to create a running balance of work to be done. I got this to
work, to a degree, using DSum. The idea being that a new column would
contain the running total that would sum up the amount of work outstanding.
All was well until I started to try to subtract the amount of work that is
assumed to be done in a day (the capacity of the work area) from it. It was
fine until there was no new work to add for a few days and the 'running sum'
of work to do went negative, ie there were 50 units left to make on a
particular day but the capacity was 170. The 'running sum' of outstanding
work at the start of the next day is now -120 - quite correct, but useless,
as the next day's new work was added to -120, not to zero. This is because I
was creating running sums based on the table at that point. I feel that VBA
is probably the only way I can truly act on a sum accrued row by row, rather
than the whole table up to that point.

I hope this makes sense.

Regards

Tom
 
AAVF said:
Thanks for your advice. Unfortunately, what I am trying to do here goes a
lot further than I have outlined in the posting. The table in question holds
an autocounter column to ensure correct sorting, but it also contains a
field that shows the quantity of new work to be done that day (in units).
Another column shows the maximum units that can be handled that day. I need
to be able to create a running balance of work to be done. I got this to
work, to a degree, using DSum. The idea being that a new column would
contain the running total that would sum up the amount of work outstanding.
All was well until I started to try to subtract the amount of work that is
assumed to be done in a day (the capacity of the work area) from it. It was
fine until there was no new work to add for a few days and the 'running sum'
of work to do went negative, ie there were 50 units left to make on a
particular day but the capacity was 170. The 'running sum' of outstanding
work at the start of the next day is now -120 - quite correct, but useless,
as the next day's new work was added to -120, not to zero. This is because I
was creating running sums based on the table at that point. I feel that VBA
is probably the only way I can truly act on a sum accrued row by row, rather
than the whole table up to that point.
Click to expand...

Sounds like you need a report with a running sum in it based on
whatever your "new work" field or whatever is. Then you might be able
to subtract on a record-by-record basis in your report... But I think
you need a report.... and why would you calculate this and shove the
info back in a table? Once someone adds a record it screws up your
calculations, right?
 
Back
Top