Updating parent table based on child results

[rnpIII]

Hello All,

I would like to start by recognizing the knowledge on this forum. I see some major potential for learning here.
Like I just read from someone, better than going to a class and learning from a book and probably not getting as much out of it.

Again, thanks to all for your responses.

Ok, here we go.
I am reinventing the wheel here. Have made a Licensing database to track our software licenses and am not real quick with the VB code in some areas.
Probably don't need to do it in code in some areas but Access is not my strong point, was weened from dBase and Paradox several years ago.

I have an issue with updating the parent when the child has been adjusted.
The scenario:

Add info to child to choose SomeSoftware, this updates the parent showing that piece of software has been issued or one or more of that piece of software has been issued.

I have it updating the parent for this, I have it deleting ONE piece of software from the child and updating the parent, like so:

delqry = "DELETE [License User].AssetNum, [License User].IDNum," & _
"[License User].SoftUserID, [License User].DateIssued" & _
" FROM [License User]" & _
" WHERE ((([License User].SoftUserID)=[RecDel]))"

issqry = "UPDATE UserAssignLicQry SET UserAssignLicQry.Issued = [issued]-1" & _
" WHERE (((UserAssignLicQry.SoftUserID)=[recdel]))"

Set Mydb = CurrentDb
Set Myrst = Mydb.OpenRecordset(sqlstr)
Set rstTemp = Mydb.OpenRecordset("UserAssignLicQry&quot
Set subform = Form_Form_SUB_UserAssignLicQry

tagno = subform.AssetNum.Value
Recdel = tagno & InputBox("Enter the software id to delete." & vbCr & _
" i.e. 10" & vbCr & _
"Where 10 is the Software ID", "Software Deletion Confirmation&quot

DoCmd.SetWarnings False
DoCmd.RunSQL issqry
DoCmd.RunSQL delqry
Recdel = ""
DoCmd.SetWarnings True

It runs nicely and will do exactly what I want. If any of you have a better suggestion for this code, I will be more than willing to listen/read.

The problem is when I have to delete multiple entries based on the same assetnum.
Can't seem to get it quite right. Can delete all of the records related to the asset in the child with the delete qry, but can't get the update to update but one of the entries in the parent. It is just supposed to add one to the [issued] field.

Any suggestions will be greatly, immensely in appreciation.
rnpIII

 

[rnpIII]

Thought I might add the current code I have set up for the above to happen.
Hope it will help build an answer.

FullDel = subform.AssetNum.Value
Recdel = SoftUserID.Value

FullDelqry = "DELETE [License User].AssetNum, [License User].IDNum," & _
"[License User].SoftUserID, [License User].DateIssued" & _
" FROM [License User]" & _
" WHERE ((([License User].AssetNum)=[FullDel]))"

Do While AssetNum.Value = FullDel
With Myrst
.MoveFirst
.Edit
Form_Form_SUB_UserAssignLicQry.Issued.Locked = False
Form_Form_SUB_UserAssignLicQry.Issued.Value = Form_Form_SUB_UserAssignLicQry.Issued.Value - 1
Form_Form_SUB_UserAssignLicQry.Issued.Locked = True
.MoveFirst
.Close
End With
Loop

'DoCmd.RunSQL issqry
DoCmd.RunSQL FullDelqry

rnpIII

 

[MichaelRed]

Well, I'm not going to attempt to crack this as it stands. Form your initial statement, there could be some un-necessary storage - which is geberating hte issue t begin with. I do not know the schema for the db, however A point of RELATIONAL db is to not have the problem of the tail wagging the dog. If the app needs to display the count of licenses, there should be a very strainght forward which simply counts the liscenses in the 'child' database. Again, without knowing the current schema (and probably sone of the logic) it is not pratical for me to offer detailed advice, however storage of the number of liscenses 'in use' as a field in a table appears to be un-necessqary and inefficient. For each transaction, you need to do two operations, where as if you just counted the liscenses, you save the storage and the necessity of updating it for each issus/recall. Overall, the db NUST be sadly de-normalized, or this would not be happening.

MichaelRed
mred@att.net

There is never time to do it right but there is always time to do it over

 

[rnpIII]

Hi MichaelRed,

Point well taken. I feel you are correct on the normalization, although I don't feel it is "sadly" denormalized.

I may be approaching this the wrong way and will accept any suggestions offered.
The "Issued" field is not a formula. It is actually updated manually by the clerk when entering licensing information from another form (not related to this form or its subform accepting for the tables).

Normalization has never been a really strong area for me. I have considered a count of the field for the updates, but can't quite seem to get it right. I can't just start from scratch, too much time has been spent on it, I will have to adjust some things possibly.

I do feel the storage of the "Issued" number is necessary (in this case), due to the way the tables and queries are set up.

I started this as a project for me to play with, my boss found out what I was doing and told her boss, now they want to see what it will do. Well, I wanted a challenge!!

Again, any assistance or advice will be greatly appreciated.

rnpIII