assigning values

[Mike]

This may be a big question so I thank anyone in advance
for any help that they might be able to give. I have built
a database for tracking laboratory reagents. I would like
to add the ability in the database to track the locations
of these reagents. They are stored in 9x9 bozes and
locations would be assingned by Box:Row:Column. I would
like this field to be automatically assigned to each new
record for the next availiable location. Finally, I would
like the option to create a report which shows a map of
each box. Using the report tools, I have been unable to
make a report that reflects a 9x9 grid. Any suggestions
for part or all of this problem would be greatly
apprecated. Thanks.

 

[Kevin McBrearty]

Mike,

In order to assign the next available location you will need to add VBA Code behind your form. You will need to somehow write the code to locate and fill in an available location for the box.

If you are not familiar with VBA I might be able to help you. Unfortunately, this will be a little difficult for me with work, school, and my family. I don't really have a whole lot of free time on my hands. I would need to know more information on your database structure (Tables and Fields).

As for creating a report to show a map of the location I don't think you will be able to do it with the report tools in access. However you might be able to use VBA to create the report. I have currently used VBA for a number of things, but have yet to use it for creating reports.

With that said, you may be able to find someone who can help you with creating the report using VBA.

Thank you,
Kevin McBrearty

 

[Tim Ferguson]

I would
like this field to be automatically assigned to each new
record for the next availiable location. Finally, I would
like the option to create a report which shows a map of
each box.

I would seriously consider using Excel to do this:-

1) it's obviously ideally suited to grid-type problems, and printing,
sorting, searching etc becomes a cinch.

2) the underlying VBA is exactly the same as in Access, so there is no new
language to learn. Once you have used CreateObject to start up a DBEngine
object, all the recordsets and queries are the same as what you use anyway.

3) there are methods built in to the Excel model (like Sum and Range.Find
and so on) that will come in useful. You can format spreadsheet cells more
easily than controls on reports.

4) there is no reason to have to use one application for all processes on a
database. You can use Access in the office for data entry, Excel in the lab
for moving and tracking the reagents themselves, VBScript at night for
maintenance and backup etc.

Just a thought...


Tim F