Need help designing a stock/asset tracking database (1 Viewer)

[fat controller]

If you Check Out an available item to a vehicle(contact) then open the Contact Details Form and navigate to that particular vehicle, then click on the tab for the Current Equipment sub-form (this was called Current Lending in the template, all I have done is change the caption), you should see the equipment allocated (or currently on loan to) that vehicle - however, what it actually shows is all equipment that has been issued and not just the items that are specific to that vehicle/borrower.

I have tried it on an untouched version of the template, and the same thing happens - is there any way of getting that sub-form to show only the items that are currently allocated to that specific vehicle/borrower?

Thanks for your patience and help

 

[HiTechCoach]

Remeber we know niothign about how thisz datsbe works or thhow you are doing things.

If you Check Out an available item to a vehicle(contact) ...

How exact are you doing that?

Note: There are generally multiple ways to get something done it a database. You have mouse clicks (left and right) and keyboard keystrokes. I could look under the hood and figure out some way to do it . But if I am not doing the exact same mouse clicks and/or keystrokes then I may not be able to reproduce the exact same issue. This is what makes debugging software so hard to do. Without watching the user to see exactly what they are doing you may not be able to reproduce the issue.

 

[fat controller]

I'll try and post screen grabs of the process I am going through (hope this works). I start off on the Asset Details form which I have renamed to On Bus Equipment:

I then left click the Check Out link that is shown toward the bottom of the form, which then opens the Check Out form (note that it also says #Error in the title bar at the top, don't know if that is related?):

I select the condition from the drop down list, and then left click on the vehicle ID from the list (the vehicle ID is called last name in the actual table that the form is linked to), and then click on the Check Out command button toward the bottom of that form - that closes the form.

If I then re-select the asset (10004 in this case) by scrolling through the records on the Asset Details form using the controls right at the bottom of the form (navigation arrows), and then click on the Current Status tab to open the sub-form, and it correctly shows that the asset has been allocated (borrowed) by SLE13, and that it is unavailable:

If I then open the Contact Details form (Vehicle Details), and navigate to the record for SLE13, then click on the Current Equipment (which was called current lending on the template) tab, it shows the following:

The item ID 10004 shows, but so does 10003 which is allocated to another vehicle/borrower.

If I return to the Asset Details/On Bus Equipment Form, and navigate to the record for 10003, then click the current status tab, it shows that it is allocated to a vehicle with the ID of SLE9:

If I was to continue to Check Assets out to various vehicles, no matter which one I selected in the Contact Details form, if I then subsequently clicked on the Current Equipment tab, the form would then show all the items that are on loan/allocated to all vehicles, and not only the items that relate to just that one vehicle.

In all other respects, it is working just about perfectly, and all reports will show the assets correctly allocated to the right vehicles.

 

[HiTechCoach]

Worked for me just fine in Access 2007 with SP2 and Acess 2010.

Have you installed all the Office 2007 updates to SP2?

See: Where is the About dialog for Office 2007 applications

 

[fat controller]

As far as I know it is - I have everything set to allow automatic updates, so sort of take it for granted that everything stays up to date.

I've taken this screen grab (cut off the licence number in case it can be nicked or anything - is that even possible?), and I assume that the SP2 means that it is Service Pack 2?

 

[fat controller]

Not that I am impulsive in the slightest, but I have just ordered Office Professional 2010, so I'll sit tight until I get that installed and see if it changes anything. Hopefully it will arrive during next week (although doubtful because of the Easter holidays and then the Royal Wedding holiday).

 

[HiTechCoach]

Yes, you do have the Offfice 2007 SP2 update isntalled.

Good thinking to cut off the licence number in case!

Well ... that database has some design. issues. For example the object names have spaces, etc.. Not a well design database. I think you will have more of issues with it.

I am not sure how it would ever work. I would set the properties for the Master/Child linking fields for the sub form to see if that fixes the issue. See attached.

 

[HiTechCoach]

About Access 2010:
I will not use it in production until SP1 is rlease some time later this year.

 

[fat controller]

Pressing the wee thanks button hardly seems enough! You sir, are not only a genius, but also a true gentleman. It now works exactly as I had hoped, having followed your advice - If you were here in London, I'd buy you a beer or three.

Shame that its not a well designed database though - I have seen mention of spaces in object names whilst looking around this site, but confess that I don't quite understand why it is a no-no; why does Access not like spaces?

Is Office 2010 going to be a bad move for me then? One of the main reasons that I went for it was because my Outlook stopped working correctly on my desktop (getting by with Thunderbird just now) and I have somewhat stupidly lost my Office 2007 disc - that, and my laptop only has Open Office at the moment, so the two machine licence on the 2010 would cure that too.

 

[HiTechCoach]

I would love to come to London for a visit. My wife is fascinated with Monarchy. For the past week all she watches is shows about Prince William and Kate. We hope to visit there in the next 12-24 months.

I actually love Outlook 2010. I use it all the time.

With Access 2010 that are some compatibility issue with prior versions. I really like the the way you can make forms look in 2010. Very nice UI stuff. After SP1 comes out later this year I will probably start convert my Access 2003 apps to 2010.

I have some links on the subject here: Access 2010
** scroll down to find the link for Backward Compatibility between Access 2010 and Access 2007

 

[fat controller]

I must confess that I am getting to the point where I am getting fed up hearing about the Royal Wedding at the moment, but I suppose thats the joys of living here - good luck to them and all that as they are a lovely couple, its more the media machine that has gone into overdrive. You must bring your wife over though, she will love it - I'm a grumpy Scotsman exiled in London, so not really a fan of the place, yet even I can't help but be impressed by the sights in Central London.

I'm just about to head off to bed, but will read your link tomorrow.

As a matter of interest, I was able to run my database on one of the work machines today by using a runtime environment for Access 2007 that I downloaded from Microsoft - - it would only run as intended for a user to put data in etc, but to be honest that is all I will want them to be able to do anyway - especially once I have split the database (I will probably be back to you at some point about that one!)

Thanks again for your support - it really is greatly appreciated.

 

[HiTechCoach]

Glad to hear that the database is working for you.

I will be here when you have any more questions.

PS: I am also tired of hearing about the Royal Wedding and I don;t even live there. It will be over soon!

 

[fat controller]

Glad to hear that the database is working for you.

I will be here when you have any more questions.

PS: I am also tired of hearing about the Royal Wedding and I don;t even live there. It will be over soon!

True!

When you do manage to get to London, let me know if you need any help with the transport system here - as you have probably gathered, public transport is my area of expertise.

Cheers!

 

[fat controller]

Told you I'd be back!

Hope everyone had a nice Easter.

I am still plodding away with this database, and have made a copy which has subsequently been filled up with as much info as it is ever going to hold when used in anger - so far, so good - however, doing this has also helped uncover another couple of issues.

The first one:
- Open the 'Contact Details' form, and then click on the Equipment History tab (this is called Lending History in the template, I have simply renamed it). There is one vital column missing from this page which is the Serial Number (Item). Now, I've managed to work it out a bit, but have gotten stuck.

So far, I managed to work out that I needed to open the Lending History Subform in design view, and then I opened it in datasheet view; from there I clicked 'Unhide Columns' which then allowed me to tick the box for Serial Number to have it display.

However, the number that displays in this column is not the Serial Number, but is the ID instead. I suspect the problem lies with the Control Source/Row Source, but I am unsure how to proceed to make it right.

It is currently:

Control Source - Asset
Row Source - SELECT [Assets].ID, [Assets].Item FROM Assets;

Now, the Item field is the one that I have renamed on the Assets Form as Serial Number (but it is only the label on the Assets Form that I have changed), so I am looking to have this display the number from the relevant Item field (anyone as confused as I am yet?)

Can anyone help me as to where I am going wrong?

Also, when I go to 'Check Out' an item, and the Check Out Form opens, I get #Error where the title should be. Opening the Check Out Form in design view and comparing it with the Check In Form in design view, I note that the text for both is near identical; the Check In Form's auto-title works fine, but the Check Out one does not? Again, thoroughly confused.

As always, I am grateful for any guidance that can be offered.

 

[fat controller]

I have had another try to resolve the problem, but it appears that I am missing whatever is causing the problem.

I have compared the current database with an earlier (only part done) copy, and on the earlier copy, the 'Serial Number' shows correctly on the Equipment History Sub-Form within the Contact Details Form. I methodically worked through the Property Sheet for the Form, Sub-Form, and then each field in turn, and they are identical - yet I still cannot get the column to show the Serial Number - it is instead showing the ID number. I have also tried opening the database on a different PC to see if its a problem with my laptop - no change; and finally, I have installed Office 2010 on both my machines, and it also made no difference.

When in Layout View, if I click the drop down arrow on the combo box, it shows that it is showing the ID number:

Yet if I go to Design View, and select the Asset field, it shows that the Row Source is "SELECT [Assets].ID, [Assets].Item FROM Assets;" - this is exactly the same as is stated on the earlier copy which displays the number correctly.

I even went as far as taking out the "[Assets].ID," bit from the above, which then did allow me to see the serial numbers in the drop down combo box, but the initial one displayed was still an ID number.

Have I broke it?

 

[fat controller]

I decided to have another go, and start again from scratch in case I introduced whatever is causing the problem.

The new database is slightly better than the first one, and in the main everything works, however I still cannot get the Lending History Subform that is contained within the Contact Details Form to show what items have been 'loaned' to a contact in the past.

Here is a copy of the database so far - if anyone can guide me to resolve my stupidity, I'd be grateful.

 

[fat controller]

Sorry to bump this thread again, but I have made some progress - I have now managed to get the Lending History Subform working in as much as it will show items that have been loaned to a contact (allocated to a vehicle in my case) in the past - however, it also shows the Current Lending (items currently allocated to the vehicle) also.

Now, my logic (feel free to tell me I am wrong!) is telling me that all I need do now is set a filter for the sub-form which applies whenever the sub-form is opened, and I am presuming that the filter will be to show only records that have a date in the 'Date Checked In' Field?

Actually achieving this however, is beyond my understanding at the moment, so I would be grateful for any guidance/education that can be offered

 

[HiTechCoach]

Greetings. Been away from the PC for a week.

I normally do not use filters. I use the WHERE clause of the form's or sub form's record source to select only the desired records.

Can you post a copy of your latest Database?

 

[fat controller]

Hope you had a pleasant week?

Its not all that far unchanged from the post above - I just worked out that the child field that was linked on the Lending History sub-form was the wrong one.

Here is where I am at now

EDIT - did your wife enjoy the Royal Wedding? I must admit that despite being a bit sick of hearing about it on the run up, it was a nice event to watch on the day.

 

[HiTechCoach]

On the Asset Details form for the sub form on the Lending History tab, try changing the record source from the just the table Transactions to the following SQL:

SELECT Transactions.* FROM Transactions WHERE (((Transactions.[Checked In Date]) Is Not Null));