Tracking historical asset movements

FYI, when you doing a "follow on" question, it's best to use the "related question" button when doing the new question.  That way, you won't re-hash what you've already done.
For the inqurying minds, look at the last half dozen comments in this thread so you can see where this came from:
http://www.experts-exchange.com/Microsoft/Development/MS_Access/Q_24766447.html
JimD.
PS.  Let's continue here since you opened a new question.

Where is the related question button?  I looked for it because you had mentioned it but I didn't see it.  

Also, as you can see, I'm still having trouble.  Can you point me to a resource where I can get help, this is sort of the last step in setting up this DB and I'd like to get it done soon so that I can start utilizing it.  thanks.

<<Where is the related question button?  I looked for it because you had mentioned it but I didn't see it.  >>
Sorry for the confusion; you only get it from the old question and it's a link, not a button.  If you click on the link, it gives you a slightly different wizard with which you can ask the a new question and it will automatically include a link to the old question.
I've attached a screen shot of the link.
JimD.

Region-Capture.jpg

<<Also, as you can see, I'm still having trouble.  Can you point me to a resource where I can get help, this is sort of the last step in setting up this DB and I'd like to get it done soon so that I can start utilizing it.  thanks. >>
  understood, but I'm not clear on exactly what it is you need help with; ie. writting the code to save the record into tbl_Asset_Locations?
JimD.

I've changed the db design based on the recommendations you gave on the last thread (removing the Pick_Up_Location Field, redesigning the relationships, etc.)  I've attached a copy of the relationship view below, could you confirm that it follows what you had suggested.  

Below is some info on what a user would do and what I'd like to be able to do with the data:

-Enter a new asset in frmAsset.
-Be able to view and edit other asset information in frmAsset.
-Be able to record asset movements in frmAsset_Movements (not yet created)
-If an Assets location has been changed in  tblAsset_Location by way of frmAsset_Movements, I'd like the new location to be reflected in frmAsset when a user views or edit's an asset.
-Be able to run reports showing asset information including current location.
-Be able to run reports showing historical asset movement.

So, that's what I'd ultimately like to be able to do...but I'm not sure where I should start.  So, I suppose writing the code to save the info into tblAsset_Location would be a good place, do you agree?
screenshot.jpg

<<I've changed the db design based on the recommendations you gave on the last thread (removing the Pick_Up_Location Field, redesigning the relationships, etc.) I've attached a copy of the relationship view below, could you confirm that it follows what you had suggested. >>
Right. And in the other thread, the changes you made looked good, which I said.  Although I didn't check the entire design, just what was involved with the asset movement. Taking a quick glance at the rest, I don't see anything that pops out.
<<Below is some info on what a user would do and what I'd like to be able to do with the data:
-Enter a new asset in frmAsset.
-Be able to view and edit other asset information in frmAsset.
-Be able to record asset movements in frmAsset_Movements (not yet created)
-If an Assets location has been changed in tblAsset_Location by way of frmAsset_Movements, I'd like the new location to be reflected in frmAsset when a user views or edit's an asset.
-Be able to run reports showing asset information including current location.
-Be able to run reports showing historical asset movement.>>
I start with the first frmAsset, which is probably the simplest. It will be bound directly to the asset table. It would be a main/subform combination (possibly multiple via tabs). I'd start with the asset table fields in the main part of the form, get all that working and then add the tabs/subforms as needed.
So what's not complete with that at this point? I've gotten the impression that the only thing that was left to complete was the asset movement.
JimD.

frmAsset is done.  All controls are bound to tblAssets.  The only thing on this form I'm iffy about is the 'Current Location' combo box.  it's bound to the field Asset_Location_ID in tblAssets.  You mention subforms for this form, what subforms would you attach to it, where you thinking the form for tblAsset_Location?

<<You mention subforms for this form, what subforms would you attach to it, where you thinking the form for tblAsset_Location>>
  Any of the child tables; locations, services, hrs/mileage, etc.  Maybe their read-only, maybe their done in other places.  But in general, when you look at something, you'd want to see all the associated info with it.  That's up to you.  frmAssets may just deal with the asset itself and the move and that's it.
<<The only thing on this form I'm iffy about is the 'Current Location' combo box.  it's bound to the field Asset_Location_ID in tblAssets. >>
  Well let's finish that then. Before we get started though I would rename the Location_ID field to Prior_Asset_LocationID.   You also need to add back in another instance of tblLocations and join it to Delivery_Location (Which I would rename to Delivery_Location_ID to be clear it's a key field).  Sorry I didn't catch that change from before.   This is all assuming you want to link each move to the next and not rely on the autonumber or  Deliver_date to order the move records.  
  I mentioned using the BeforeUpdate event of the form to check the oldvalue vs the current value of the combo.   So you'd start like this in the BeforeUpdate of frmAssets:
' Do any validy checks before this point.  We want to make sure we are not going to cancel the
' save of the asset record past this point.

' Check if the user has moved the asset
If Me.<MyLocationCombo>.OldValue <> Me.<MYlocationCombo>.Value then
    ' Use has changed the assets location.  Need to write a record into tblAssets_Locations.
   
    ' First the last move record to link to.

    ' Get data for fields in move record - How are we going to get Pick_Up_date, Delivery_Date, and Moved_By? Popup form?

    ' Save the move record

End If
  So the questions:

1. Do you want to link the move records to one another to track the last move as we discussed in the other thread?
2. How do we plan to get Pick_Up_date, Delivery_Date, and Moved_By?  
It would be quite helpful if you could upload a sample db with the frmAssets and the tables envolved (empty ones are fine) so I can see all the field/control naming.  It will help to make any examples I come up with clearer if I can use actual names.
JimD.

<1. Do you want to link the move records to one another to track the last move as we discussed in the other thread?>  Yes

<2. How do we plan to get Pick_Up_date, Delivery_Date, and Moved_By?>   I would say a pop up form.

I can't email the file because it's an .accdb extension.  I tried to convert it to an earlier version but was unable to. Can I email it?

<<I can't email the file because it's an .accdb extension.  I tried to convert it to an earlier version but was unable to. >>
Bummer; I don't have Access 2007.  I will find someone else to help.
JimD.

I've posted an alert for other experts to take a look at this and hopefully speed up the process for you.  I have no quams about continuing to work through this, but without being able to look at the DB, it would be a lot of back and forth between us.
I know you said you need to get this done.
If someone else hasn't picked this up in a couple of hours, I'll jump back in and we'll see if we can't finish this any way.
JimD.

How did you try to convert it?  Try clicking File | Save Av | Access 2002 - 2003 Database, then zip and attach the MDB if it is large.

For support using the .accdb file, you can rename it to:

your_database.accdb.txt

It will upload and we can simply trim the .txt to use it on our end.

M-1

I went to save-as and tried to save it as an 02-03 db but was unable to because it told me it couldn't because I'm utilizing features not compatible with previous versions.

I've added the .txt to the end instead.  Thanks for your help.
To-send.accdb.txt

Thoughts?

Sorry, I got side-tracked yesterday.  Since Jim was helping and fully understands where you are heading, I thought it would be best to first try to convert to MDB for you to keep things moving with him.

Since the tblAssets and tblContacts didn't seem to actual have any attachment data, I removed those columns so Jim can look at the MDB.  Just note that those columns are valid for 2007.
To-send.mdb

K, thanks.  So it's the ability to store attachments is only available in '07?

<<K, thanks.  So it's the ability to store attachments is only available in '07?>>
  Yes.   BTW, I just downloaded the MDB that posted mwvisa1 posted and I'm looking at it now.
JimD.

Cool, thanks.

Sorry for the delay , but I have gotten swamped with work (it's month end and one of my clients just shifted all their shipping to a new warehouse in CA).  
I expect to get back to this in a couple of hours when things die down a bit.
JimD.

That's fine.  That's for letting me know, I appreciate it.

Please forgive me if I sound like a pest, but have you had an opportunity to look at the DB yet?

I don't think your being a pest. In fact I think you've been quite patient and I'm sorry this has dragged out so long. I really didn't expect this question to turn into this.
Normally I try as best as possible to only answer questions that I think will fit my free time available. Every once in a while I get caught like this and as I am a one man shop, so when problems crop up there is only me<g>
As I said, one of my clients just moved their warehouse/shipping to another provider (100,000 sqft worth) and this was the first month end with them (a few things cropped up<g>).
In any case, that's behind me at present and I'm working on your DB now.
I'll be getting something to your shortly.
JimD.

Great, thanks.  I certainly can appreciate and fully understand the fact that you've been under-the-gun and that it takes priority.  Thanks again.

Quick update; finially got to it but I still have a few things to finish.  I'm attaching what I've setup so far so you can make comments in case you want it different.
On frmAssets, I will be adding a button "Move Asset", which will simply call up the frmAssetMove in dialog mode.
frmAssetMove just needs the save logic on the save button and I also want to add a subform to display the previous moves at the bottom.
 Optionally, you can allow deletes on that subform.  I will leave it as a read only view.
I simplified the table setup that we had discussed previously.  Although the setup I was talking about would give you better tracking, it would also be much more of a rigid system, so I'm depending on the delivery date to sequence the records.
Look it over and comment and I'll see if I can't finish this off tonight.
JimD.

To-send.mdb

Well here it is finally!  While I would not considered it "polished", you should have everything you need at this point.
I tried to use several different techniques so you can see how things are  done, but keep in mind that the way I did them is by no means the only way.  For example, on the assets form, the button to do a move could pass the asset ID using the OpenArgs argument when it opens frmAssetMove.
Instead I currently have some code in the OnLoad of frmAssetMove that says if the frmAsset is open, set the Asset_ID based on what's there.
 And as I mentioned, I changed the table layout in terms of the asset move tracking.  While the other method we were talking about would probably be superior, it would also be a lot more difficult to develop.  From the sounds of things, I wasn't sure if you'd be up to that or not.
  It also keeps the move records simple in that if you mess up on one, it can simply be deleted and new ones added.  If we had done it the other way (linking each move to the prior one), you would not be able to fix mistakes easily.  There are others in and outs to that that once I considered all the different things that needed to be done, I didn't think you'd want to deal with all that vs the benefit you would get.
If you have any questions I what I did, just holler.
and again, sorry for the delay.   I didn't mention this yesterday, but I had a business trip to Buffalo yesterday on top of everything else going on this week.  All I can say is; I'm glad it's over!
JimD.

This is great.  Thank you very much!  I greatly appreciate it!