Appending multiple records to grand child table

[Russyfoot]

Hi.

I have a database based on Microsoft Assets Database template. I have three tables: tblBuildings; Parent. PK BuildingID
tblAssets; Child. PK AssetID, linked to parent via BuildingID
tblMaintenance.Child of tblAssets. linked via AssetID

Service reports are received by building. I want to be able to select a building to give a list of assets, then select those assets which have "passed" the service and then append records in the maintenance table based on the selection. I've tried form, sub-forms, radio buttons, sub forms without success & am now going round in circles. I suspect I need some code to achieve this but this is beyond my current knowledge. Any advice appreciated.

 

[Malcy]

Hi Russ
SOunds like you need to create a form with a combo box on to select the building.
Then display all the assets in that building (query based on tblAssets where buildingId = combo value. If you do this as a continuous form and also add a checkbox that would let you identify the assets that have passed.
Next you need to either code up to append relevant fields to tblMaintenance where there is a tick in the box or kick off an append query that does this for you - but I seem to recall there is an issue with the last record ticked not appending so you may want to go via a temporary table.
Does this help at all? If you need a bit more then shout and I will try to help more but sort of hectic here at the moment
Best wishes

 

[Russyfoot]

Hi Malcy.

Thanks for the reply.

Yes & No. I had created a form as you describe, however, when I select a check box it enters a tick in all of the records. I never thought of a temporary table..I'll work on that but how can I solve this form problem ??

 

[Malcy]

Have you set up the form as continuous?
If going the temporary table I always name mine tblTmpWhatever so I can easily see it is temporary. You need to remember to run a delete query before appending if you are using "fixed" temporary tables like this

 

[Russyfoot]

Hi Malcy.

yes it is continuous. I've attached db for you to have a look at. Form is frmAssets1

 

[Malcy]

Hi Russ
I have renamed the mdb file so you do not lose your stuff. Get what you mean about multiple checking. There may be better ways to do it but this seems to work and I have commented code so you should be able to follow.
I added a field to tblAssets called bTest (check out naming conventions for field names - it will save you a lot of grief later on). I also built a new query qryAsset1 that populates the form frmAsset1 - I find queries become more manageable than select statements especially as it gets complex.
I also split the building selection off from frmAsset1 so you now have frmSelectLocation with cboLocation populated from qcboLocation. You can cancel out or hit view (I should have added some validation code to make sure there was an entry in cboLocation but also configuring client laptop at same time so mind in too many places!)
When you hit view it runs a query (qupdAssetTestNo) which knocks all bTest to No.
You will see form has changed a little. Thinking about it you should lock down the assetID field I added and also the asset description field so they cannot get damaged. If you put tick in any of the fields and then click update records it will append those ticked records to tblMaintenance using qappMaintenance and then again run qupdAssetTestNo to clear the decks. The append query adds in any comment and also set text - see last record in table (asset ID 186) to see.

Hopefully gives you scope now to enhance and elaborate so that it starts doing precisely what you want - like add another yes/no field to tblAsset so one for pass and one for fail
Anyway hope it helps a bit
Good luck

 

[Russyfoot]

Malcy..words fail me ! Thankyou so much. This is exactly what I was trying to achieve. I was expecting help and guidance and you've gone and done the thing for me. If you're ever south of the border and near J24 of the M1 let me know, I'll buy you a well-deserved pint.

Once again thanks

 

[Malcy]

Hi Russ
Thanks for the kind words, but hey man I haven't done it for you .. I have provided you with a map and a guide book that you can follow.
You now need to use that to make it work as you need for you application and make the data sing to you
There is still a good way to go but when you have a path the route is easier to follow.
Good luck