Allocating records to resources in Access

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

** = 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!
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

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.

Jaguar5150Author Commented:
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.
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?

Acronis True Image 2019 just released!

Create a reliable backup. Make sure you always have dependable copies of your data so you can restore your entire system or individual files.

Jaguar5150Author Commented:
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!

I will take a look at this later this evening.
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.

Jaguar5150Author Commented:
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.
Jaguar5150Author Commented:
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?
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...


Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Jaguar5150Author Commented:
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.
Glad to help out :-)
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Access

From novice to tech pro — start learning today.