Table Query, update, and save to another table.

[PMF1957]

Morning all and Happy Holidays,

This should be a simple matter but for the life of me I just can't figure it out. I'm posting alot of information up front to save everyones time (I hope).
Knowledge Base: Basic access at best. I have what you may say "fiddled and diddled" in access. Simple systems created, nothing major or complicated.
Background: Warehouse inventory system in access 2003. I have set up two primary tbls: Equipment and ETO (Equipment transfer order) both have an autofill primary key and the equipmentID is part of the ETO tble. There is a one-to many realationship from the equipment to the ETO. There are other tables (4) in the system but they are used as look-up tables and i'm not concerned about those.
Equipment Table layout: EquipmentID, ModelNumber, SerialNumber, Mfg, ProductName, CurrentLocation, Category. This table is used as the repository for all of the equipment that needs to be tracked and inventoried. It will not change much with except the CurrentLocation changing as the stuff is shipped around (in Store, In Warehouse) and of course when new equipment is being added.
ETO table layout: ETOID, ETONumber, FromStore, ToStore, OriginStore, ETODate, ModelNumber, SerialNumber, MfgID, ProductName, LocationID, CategoryID, EquipmentID. This table will be used (I hope) to basicly track the equipment as it moves from store to store to warehouse and back out again.
Process: Locate a peice of equipment by either SerialNumber or ModelNumber in the Equipment Table, change the location depending on it's status (coming in, going out) then i need to somehow update the ETO table with that ModelNumber, SerialNumber, Mfg, category, etc..But put in the FromStore, Tostore, OriginStore, ETODate, etc..
I have tried both the update query and append query from both tables but I can't get the results i need.
Should be simple right?:banghead:
Any help would be greatly Appreciated!

 

[jdraw]

Best advice at this point is to review the links
http://forums.aspfree.com/attachment.php?attachmentid=4712&d=1201055452
and
http://www.rogersaccesslibrary.com/Tutorials/Entity_Relationship.zip

Good luck with your project

 

[plog]

I need to update the ETO table with that ModelNumber, SerialNumber, Mfg, category, etc.

This is not how a database works. You are storing to much information--redundant data at that. If your ETO table is to keep track of what is moving where, then it only needs one piece of data from the Equipment table--the primary key. Conversely, the Equipment table doesn't need to store where a piece of equipment is because that information should be in your ETO table, therefore the field 'CurrentLocation' is unnecessary in the Equipment field.

 

[PMF1957]

This is not how a database works. You are storing to much information--redundant data at that. If your ETO table is to keep track of what is moving where, then it only needs one piece of data from the Equipment table--the primary key. Conversely, the Equipment table doesn't need to store where a piece of equipment is because that information should be in your ETO table, therefore the field 'CurrentLocation' is unnecessary in the Equipment field.

Agreed. I had the redundant info in the eto just to see what it would look like. I have since pulled it out. The ETO now only contains the fields associated with the movement of equipment (plus the Equipment ID). I need to leave the location field in the equipment table as this field will be used for inventory counts.

 

[plog]

I need to leave the location field in the equipment table as this field will be used for inventory counts.

I don't believe you do. If you want to know where an item is currently you should be able to look at the ETO table to find the last date of the item.

 

[PMF1957]

plog...Thanks you have been a great help. It's been a long time since i played with this stuff. Rust is starting to come off the old noggin...

I don't know of another way. Let me explain:
The equipment table is a repository for the assets that the warehouse stores and records the movement of. These assets are moved form one location to another they never go away unless they are sold to someone outside the company, or trashed which is very rear.
Simple example: A freezer case can come into the warehouse from a store on a monday, the Equipment Transfer Order (ETO) slip comes in with the item, and will be used to update the system i'm trying to build.
The etonumber, from, to and date for the eto table and the model, serial, mfg, etc.. from the equipment table will be entered into the system to create the ETO record.
A week later we receive another ETO request to ship that same piece of equipment we just received back out to another store. So a new ETO order needs to be generated for this instance. So the process is done again with a new
ETO number, new to and from, new date, but same model, serial, mfg, etc... This process could occur for the same piece of equipment any number of times within a 30 day period and each occurrence needs to be recorded.

The ETO table will be the base for reporting that movement of equipment on a monthly schedule. Which equipment came in, went out, was transferred from one store to another, etc..

The equipment table i figured i could use for inventory purposes. Basically, "What's in the warehouse now".
This is why i thought the locationID should be located on the Equipment table and not the ETO table...

 

[plog]

What's in the warehouse now.

That can be answered by looking at your ETO table. Find the last ETO of every item and that's where it is. The current location shouldn't be stored anywhere, it should determined by your data, specifically by running a query.

 

[PMF1957]

Excellent... Works just fine. Created reports showing where a piece of equipment is at any given point in time. Thank you... I have another question for you (i'm going to search for a answer also). Is there a way to execute a form via a button or command from a form?

 

[plog]

execute a form

What do mean by that?

 

[PMF1957]

I should have said "re-execute" the same form once I'm done entering data for a specific item into it. I would like to be able to re-execute the same form i'm in after saving the information instead of having to go back to the switchboard to process another record. The process is query driven to find a specific record and update its movement.

 

[plog]

putting a 're-' in front of the term I don't know how you are using doesn't help my understanding.

What do you mean '"re-execute" the same form'?

 

[PMF1957]

OK. I'll walk through the process.
From my main switchboard I click on a button that executes a OpenForm macro that in turn opens up a query which ask to enter a like parameter "Enter serial Number". I enter the information I'm looking for and hit enter. This then opens to a form/subform containing the information that I was searching for. The "form" information contains the information for the piece of equipment i searched for and the subform contains the ETO information for that piece of equipment. I enter changes to the movement in the subform area which tracks the coming and going of the equipment.
So the information has been entered and now I need to process another piece of equipment in the same manner. Currently I have to "X" out of the form and start the process again from the switchboard. I would like to be able to start the process from the current form.

 

[plog]

Put a button on that form that executes the OpenForm macro that is on your switchboard.

 

[PMF1957]

Tried that. The button on the switchboard executes a openform macro called "ETOForm" and I placed that same macro on the "on Click" process on a button on the form. When I click it nothing happens.

Update: I can change it to execute any other query or report and it works. It will just not work for the same form. Does the process or form i'm in have to be exited some how before it can be executed again?

 

[StarGrabber]

Hi plog, I hope you don't mind for jumping in.

PMF1957: in Access normally you can open a form only once. To go to another serial number you can put an unbound combobox on your form, let's name it 'cboSerialNumber'.

Set the record source property to:

SELECT Equipment.EquipmentID, Equipment.SerialNumber FROM Equipment ORDER BY 2;

Then set the column count property to 2 and the column widths property to 0cm (or inches, or whatever).

Finally paste the following procedure in the code module of your main form:

Private Sub cboSerialNumber_AfterUpdate()

    Dim rs As DAO.Recordset
    Set rs = Me.RecordsetClone

    rs.FindFirst "SerialNumber = " & Str(cboSerialNumber.Value)
    
    Me.Bookmark = rs.Bookmark

    Set rs = Nothing
    
End Sub

From now on you don't have to enter a serial number into a parameter box anymore. Remove it. Open your form as before, select a value in the combobox and that's it.

 

[PMF1957]

Sorry to take so long to get back to you. Thanks for the reply. I'm going to give that a shot and see what happens. Just so you know I kinda found a work around for this. I placed a unbound button on the main form with instructions to click it then Press and Hold the Shift key then hit F9. It work, it drives the hole process from the start while still in the form...Clumsy...but what the heck...