?
Solved

Allocating records to resources in Access

Posted on 2008-11-04
11
Medium Priority
?
245 Views
Last Modified: 2013-11-29
I am in the process of designing a simple database for tracking our backup tapes which case they are in.

Below is the basic design, showing the primary keys and the relationships;

   Table1(Tapes)              Table2(Cases)
**BARCODE        -------**CASEID
   CASEID--------//

** = Primary Key

What I'm trying to do is track the location of the individual tape to the case it is in.  Our storage facility uses the case number to locate the tape.  We recycle the tapes after a certain retention period, and those tapes do not usually go back into the same case they were in to begin with.

We use a barcode scanner to enter data.  Essentially what I'd like to have happen is we would pull up the case that we are inventorying, and then scan each tape and have it be updated to reflect its new case location.  The problem I am running into is that since the tape barcode is the primary key, adding it to a new case creates a duplicate (which I understand), however, I'd like that tape to be automatically removed from its original case and then reflected in its new location.  We have no need to record history of the tape.  I know that I could go to each tape in the table and update its CASEID, but there has to be an easier way.

Please understand that if any VB code is required, I may need a little extra explanation as to where that code goes.

Thank you very much in advance!
0
Comment
Question by:Jaguar5150
  • 6
  • 5
11 Comments
 
LVL 61

Expert Comment

by:mbizup
ID: 22876934
Is this going to be a bulk update (all tapes in a given case being moved to the same new case)?

UPDATE YourTable
SET CaseID = NewCaseID
WHERE CaseID = YourOriginalCaseID


or are you updating tapes individually (tapes in the original case being moved to multiple new cases)?

UPDATE YourTable
SET CaseID = NewCaseID
WHERE Barcode = YourScannedBarcode

These update statements reflect the general syntax you will need. If you post some more detail, we can figure out how to implement this.


0
 
LVL 2

Author Comment

by:Jaguar5150
ID: 22877114
Thank you for your response!

Actually, it doesn't seem to fit either of those.  We pull tapes from our tape library when they are ejected and those tapes (from multiple prior cases), go into a single new case.

So for example:
Tape1   From Case 100
Tape2   From Case 101
Tape3   From Case 102
Tape4   From Case 102
are all now being placed in  Case 103

Hope that helps explain  :)

The syntax you refer to, where does that go?  Sorry, I am rather new at advanced Access functions.
0
 
LVL 61

Expert Comment

by:mbizup
ID: 22877417
That would actually fit in with my second example - each tape needs to be updated individually (because they are coming from seperate cases).

I'm not 100% clear on how this is done from a user's standpoint, but it sounds something like:

- Scan tape
- update case number
- repeat

so each time a tape is scanned, that tape's record gets updated to reflect the new case number:

UPDATE YourTable
SET CaseID = NewCaseID
WHERE Barcode = YourScannedBarcode

In VBA, this would look like:

Dim strSQL as string
strSQL = "UPDATE YourTable SET CaseID = " & NewCaseID & " WHERE Barcode = '" & YourScannedBarcode & "'"
currentdb.execute strSQL, dbfailonerror

This code would need to be event based. We need more details about the user interface to figure out where to place the code and how to apply it to your database.
- Will the update occur when the user clicks a command button?
- What do your relevant forms look like?
- When a tape gets scanned, where does the barcode of that scanned tape go? is there a field on a form that displays it?
- How does the user enter the new caseID  into the database?
- How do I (or the code) know what the current barcode and new caseID are?





0
Transaction-level recovery for Oracle database

Veeam Explore for Oracle delivers low RTOs and RPOs with agentless transaction log backup and transaction-level recovery of Oracle databases. You can restore the database to a precise point in time, even to a specific transaction.

 
LVL 2

Author Comment

by:Jaguar5150
ID: 22878760
Maybe it would help if I attached the database I am building.  I believe most, if not all of your questions will be answered by looking at the setup.

While I don't have the final UI built yet, you will see my initial attempt at the form in the database.  Ultimately I would like to open the form and from there, open(lookup) the case item, and input the barcodes by case using a subform.  

If you open the form, you will see a list of case numbers that have been populated in the Cases table under CaseID.  Below that is the subform.  I would like to enter a series of barcodes in the subform and then have them now show as residing in the new case.  Whether that be a button click or something else automatic, either would work (the most automated would be preferrable, however).

**As a side note, while toying with the form and inputting initial data, I've noticed a problem with the form where, if I pull up a case number and add new tape items, those tape items are added to the next CaseID in record, not the case I selected initially.  I will increase the point value to max if you are able to offer a solution to that problem.

Thank you so much for your continued support on this issue!
Tape-Archive-Database.mdb
0
 
LVL 61

Expert Comment

by:mbizup
ID: 22879727
Hi,

I will take a look at this later this evening.
0
 
LVL 61

Expert Comment

by:mbizup
ID: 22885265
Okay,  I looked at your database last night and got a better picture of what you are trying to do.

It looks like you will be dealing with  a given case in the mainform and handling the tapes in the subform records.  Since the tapes are going to be moved from a single case to potentially multiple cases, you will have to make these updates on a tape by tape (record by record) basis.

With this scenario you really don't save any time or effort by using an update query as I suggested earlier.  This would really be beneficial if you were moving all or a group of tapes into a single new case.

What I suggest doing is:

- Create a popup form bound to the table that contains the tape information.
- Open this popup form on the double-click event of the barcodes in your subform to allow the user to edit the caseID and any other information for a selected tape.
- Include Cancel and Update buttons on the popup for to allow the user to save or discard the changes.

If this sounds good to you, go ahead and create a small popup form to allow the user to edit data pertaining to the tapes.

Once you do that, post back and I will help you out with the code (minimal) to make this work.

0
 
LVL 2

Author Comment

by:Jaguar5150
ID: 22885641
My intention was to use the form to do just that, move a group of tapes which may have originated in multiple other cases, to a single new case.  Maybe the form isn't the answer and there could be a better way, but from what you described:

"This would really be beneficial if you were moving all or a group of tapes into a single new case."

I am trying to do just that.  And since it would be myself and a couple of other backup administrators using this database, it isn't all that necessary to have a pretty form to use.  I would be just as happy editing in the table.
0
 
LVL 2

Author Comment

by:Jaguar5150
ID: 22885738
Now that I reread your statement, I think I understand what you are saying.

Maybe this would work as a work-around:

When our tapes are returned from the storage facility, they get reviewed to be certain that they are indeed scratch tapes.  Those tapes are all moved from their original case to a case called "scratch"

Once they are used and have valid data written on them, they are moved from scratch to their numbered case.

The question I have with this is, there could be say 200 tapes in "Scratch".  Does your method require that all 200 tapes be moved to a numbered case at once, or can 24 of them be updated to reflect their new numbered case?
0
 
LVL 61

Accepted Solution

by:
mbizup earned 1600 total points
ID: 22886496
I think this may answer some of your questions, and also demonstrate how a "bulk update" can be beneficial in some cases.

This sample adds a boolean (yes/no) field to your tapes table to indicate pending moves to a (single) new case.

The user can select multiple tapes, and then choose a case to move these tapes to.

An update query as I described in my first post moves the tapes as a group to that new case.  This is how a bulk update can save you time.

The code needed to actually accomplish this is minimal.  However if you add more end users at a later time, it can be embellished to make things more user-friendly.

Take a look at the forms and tables.  And try to get a feel for how the code works behind the dialog box (which does the bulk update) and the Clear button on the main form ("updates" the table by clearing the user selections).

If this looks like what you need, try to apply it to your own database.  You will need to change any relevant table and field names to make this work.

Fire back with any questions...




tapes.mdb
0
 
LVL 2

Author Comment

by:Jaguar5150
ID: 22886896
Thank you so much for your efforts!  I've looked it over, and it may work, at least, it will be better than nothing!

I'll study this and see if I can apply it to my scenario and/or build from your example.
0
 
LVL 61

Expert Comment

by:mbizup
ID: 22886982
Glad to help out :-)
0

Featured Post

How to Use the Help Bell

Need to boost the visibility of your question for solutions? Use the Experts Exchange Help Bell to confirm priority levels and contact subject-matter experts for question attention.  Check out this how-to article for more information.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Explore the ways to Unlock VBA Project Password Excel 2010 & 2013 documents. Go through the article and perform the steps carefully to remove VBA Excel .xls file.
Windows Explorer let you handle zip folders nearly as any other folder: Copy, move, change, and delete, etc. In VBA you can also handle normal files and folders, but zip folders takes a little more - and that you'll find here.
In Microsoft Access, learn the trick to repeating sub-report headings at the top of each page. The problem with sub-reports and headings: Add a dummy group to the sub report using the expression =1: Set the “Repeat Section” property of the dummy…
Visualize your data even better in Access queries. Given a date and a value, this lesson shows how to compare that value with the previous value, calculate the difference, and display a circle if the value is the same, an up triangle if it increased…
Suggested Courses

864 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question