Hi guys I hope you can offer me some advice.
I am an amateur enthusiast when it comes to MS Access. I have built some quite good datases over the years, but have not had any formal training, I use what I learn on forums such as this one, which are invaluable to people like me. So I thank you for participating in these forums.
My current dilema is this. I have a database with a section for assett control, the assetts are pieces of equipment with serial number. In the database I have a combo box which pulls out the EQUIP_ID, EquipmentName, and SerNo. from a table called Equipment. When I click on my choice in the combo box this runs a bit of code in the on click event which takes the equip_id, the EquipName and SerNo and puts them in another table called Assetts which is the source of the form. In addition there is a field in the Assets Table which is called Allocated. This is a Text Field but only accepts "Yes" or "No". After the data has been added to the Assets table an update query is run which updates the Equipment Table Field "Allocated" to "Yes" This has an effect on the combo box contents which will only display equipment which is not allocated. This all works fine and I am quite happy with the way I have built this, though I suspect there are easier ways to do it. What I am at a total loss with is: when a piece of equipment is returned and deleted from the subform, how do I get the form to update the equipment table "Allocated" field to "No". I do hope I have explained my dilema well enough for someone to suggest a solution.
Many thanks in advance.

Dave

Hi Dave, welcome to Utter Access Forums!

A few references in your post indicate a possible structure flaw ... identifying it and fixing it will also help with your current question...

Is the only different between table Equipment and table Assets the Allocated field? If yes, you do not need the Assets table... just another field in the Equipment table.

If there are other differences (which ones?), all you need is to store the Equip_ID value in Assets... not the whole record. Relating the records will enable you to pull the rest of the equipment information when you need to show it.

So, how about you describe the structure of the database (see here how)... or attach your db with some sample records (how to attach a file)

Hi Doug, many thanks for responding.

I can't attach the whole database it is quite large and I would also have to clean out all the company data.

I can see where you are coming from as soon as i saw "Structure Flaw" in your email. I confess to not having normalised these tables. The rest of the database has been normalised. the Asset Management part was an afterthought, which is why it is causing some problems. anyway the tables are structured as follows:

tblAssets

AssetId
Asset_ContactID (foreign key to main table containing People/Companies)
Equip_ID
Date
TypeOfAsset
SerialNumber
Remarks(text)
AssetNotes(memo)
Allocated

TblEquipment
EquipmentID
EquipmentName
EquipmentSerNo
Allocated

Yes, there is some uneccessary duplication. I am also considering adding a few more fields to the Asset table, i.e. DateLoaned and DateReturned and getting rid of the simple Date field.

Any help on my current problem though would be gratefully accepted.

Regards

Dave

Hi again Doug, one thing I forgot to mention is that the list of equipment needs to be user controlled. ie. the user must be able to add or delete assets as necessary. Currently I use a pop up form for them to Edit/Add/Delete items in the Equipment Table.

Regards
Dave

Hi Dave,

I modified the structure a bit... see if it makes sense:

TblEquipment
- EquipmentID [PK] (A#)
- EquipmentName
- EquipmentSerNo
- TypeOfEquipment

tblAssets -- can be called tblAllocated
- AssetId
- Asset_ContactID (foreign key to main table containing People/Companies)
- Equip_ID [FK] <- to TblEquipment
- AllocatedDate
- Remarks(text)
- AssetNotes(memo)
- ReturnedDate

To identify equipment that is loaned you will look for records with a null returned date. Since you can loan the same equipment multiple times, you will look to the latest loaned date for each piece (save it as qryOut):



To find the avaiable equipment for loans... left join tblEquipment and qryOut and look for null ID in qryOut (basically an unmatched query):



Makes sense?

Doug many thanks, I have not tried it yet, but it all makes perfect sense. Don't know why I couldn't come up with something similar. Woods and trees springs to mind.

Once again many thanks, I will get back to you once I have made the changes and tried it.


Regards
Dave

You are welcome Dave.

Once in a while we all benefit from another set of eyes to help us see the path in the woods...

Hi Doug, hope you are still willing to assist in my endeavours.

I have implemented what you suggested and it works fine, but- and it's a big but, when I use the combobox which is fed by the query you suggested, it always displays the very last piece of kit I allocated, allong with all the unallocated kit. If I open the query independantly, the piece of kit does not show. Any ideas what is causing this. I have tried the requery on the combo box and even refreshing, but nothing seems to work, it has me completely perplexed.

Regards

Dave

Hi Dave,

Can you post your db (leave some sample data; compact and repair; zip; file needs to be <500KB)? I'll take a look at it.

I have sorted it Doug, I think I was putting the cart before the horse. I used the on click event to select the data and to requery/refresh the records, once I had changed the refresh/requery part to the afterupdate event it worked fine, many thanks for your help and offers of help.

Regards

Dave

You are welcome Dave. Glad you sorted it out.

Good luck with the project.