Problem with multiple event procedures

[back2basic]

I have a simple form with 3 fields. I am having a problem as the form will not run the AfterUpdate event code triggered in the last field "Date_entered"

I suspect the code of the first field "Material_ID" triggered by BeforeUpdate may be prematurely exiting the form??? I can continue entering the data in the form. Don't know what is going on. Will someone help?

Material_ID
Employee_ID
Date_entered

 

[pr2-eugin]

Hello back2basic.. Description does not seem to be specifying what problem you are having or what you are trying to do.. Could you explain a little bit more in detail? Along with some Code of what the code does?

 

[back2basic]

OK, please allow me to explain what each field does:

Material_ID - This field is bound to a ComboBox which calls the sub cboMaterial_ID_BeforeUpdate. I know my code is crude (still refining) but this Sub works as planned.

Employee_ID _ This field is bound to a ComboBox but calls no Code

Date_entered - This is a date field which calls the sub Date_allocated_AfterUpdate(). The user is prompted in this sub if he wants to save the record, then all data is written to the appropriate fields in foreign tables and the record is saved.

Problem is that the Date_allocated_AfterUpdate() event does not run.

Is there something wrong perhaps with the way I am returning to the form after the first Sub cboMaterial_ID_BeforeUpdate runs?

Option Compare Database
Public serialnum As String
Public QTY_Requested As Integer

Private Sub cboMaterial_ID_BeforeUpdate(Cancel As Integer)
Dim QTY_Remaining As Integer
Dim QTY_Available As Integer
Dim QTY_Issue As Integer
Dim secured As String
Dim MsgResponse
Dim AnswerResponse
cancelresponse = "Data entry Cancel"

'This Sub routine first checks to see if an item is secured. If the item is secured check for an existing serial number.
'If no serial number entered, prompt for one.  Only one secured iyem may be checked out at a time.
' If the item is not secure,  prompt the user for the quantity of materials desired,  check that quantity desired
' of a material to be sure materials are availabe

On Error GoTo Handler:

If Me.cboMaterial_ID.Column(4, cboMaterial_ID.ItemsSelected) Then ' Check if the item is secured
   ' Check if a serial number exists
   If Len(Me.cboMaterial_ID.Column(8, cboMaterial_ID.ItemsSelected) & vbNullString) = 0 Then
        ' No serial number exists,  Prompt for a serial number.
        serialnum = InputBox(" What is the units serial number")
   End If
   Me.Quantity_issued = 1 'Update the text box in the form to show how many units requested.
    QTY_Requested = 1 ' Set quantity requested = 1. Note only one secure item at a time can be checked out.
Else
' item is not secured
' Get the quantity available by subtracting Stock_Quantity column(3) from Issued_Quantity column(7).
QTY_Available = Me.cboMaterial_ID.Column(3) - [cboMaterial_ID].Column(7)
Line1:
'Ask user how much of this material is needed. Handle the error on cancel
QTY_Requested = InputBox("Enter the Quantity, " & QTY_Available & " Units are available?")
        If QTY_Requested > QTY_Available Then ' Test to see if user has entered more then is available
        MsgResponse = MsgBox("Only have  " & QTY_Available & " Available, do you want continue?", vbRetryCancel)
            If MsgResponse = vbRetry Then
                GoTo Line1
            End If
            Exit Sub
     Else
     Me.Quantity_issued = QTY_Requested 'Update the text box in the form to show how many units requested.
     End If
End If
Exit Sub
Handler:
MsgBox cancelresponse
End Sub

Private Sub Date_allocated_AfterUpdate()
strSQL As String
Dim MsgResponse2
' This sub routine first prompts the user if they wish to save the current transaction.  If so, it
' updates the materials table for the quantity of stock requested.
    
    'prompt user if He wants to save the record
     MsgResponse2 = MsgBox("Do you want Save this transaction?", vbYesNo)
        If MsgResponse2 = vbYes Then
        'Save and write all Data
            QTY_Issue = Me.[cboMaterial_ID].Column(7) + QTY_Requested ' Increase Quantity issued (column 7) by Quantity Requested
            QTY_Remaining = Me.[cboMaterial_ID].Column(5) - QTY_Requested 'Quantity remaining = Current quantity - quantity issed
            strSQL = "UPDATE Materials SET Issued_Quantity = " & QTY_Issue & " WHERE Material_ID = " & Me.cboMaterial_ID ' Update Issued quantity
            CurrentDb.Execute strSQL, dbFailOnError
            strSQL = "UPDATE Materials SET Remaining_Quantity = " & QTY_Remaining & " WHERE Material_ID = " & Me.cboMaterial_ID ' Update Remaining quantity
            CurrentDb.Execute strSQL, dbFailOnError
            
            'Write seial number data below
            strSQL = "UPDATE Materials SET Serial_Number = '" & serialnum & "' WHERE Material_ID = " & Me.cboMaterial_ID
            CurrentDb.Execute strSQL, dbFailOnError
            strSQL = "UPDATE Materials SET Issued_Quantity = 1 WHERE Material_ID = " & Me.cboMaterial_ID ' Update Issued quantity
            CurrentDb.Execute strSQL, dbFailOnError
            strSQL = "UPDATE Materials SET Remaining_Quantity = 0 WHERE Material_ID = " & Me.cboMaterial_ID ' Update Remaining quantity
            CurrentDb.Execute strSQL, dbFailOnError
            'Debug.Print strSQL
           
        Else
        ' User has elected not to save data
        Me.cboMaterial_ID.Requery
        Me.Requery
        End If
End Sub

Private Sub Form_Undo(Cancel As Integer)
strSQL As String
cancelresponse2 = "Test Data entry Cancel"

'The user has cancelled the transaction reverse, all Data  entered  from Sub cboMaterial_ID_BeforeUpdate
'main Sub Routine.

MsgBox cancelresponse2

End Sub

 

[pr2-eugin]

After Update is triggered only after a specific change is made to the field.. Manually overwriting the existing value/editing the date from 23/10/2012 to 03/10/2013.. Deleting the already present date.. Are you making any of the mentioned changes? If not the Event will not be called..

If you wish to UPDATE tables, why not use a Button? Why the choice of the method AfterUpdate?

 

[back2basic]

No patronization intended here but you are a genius and very helpful. You are correct, I put a default date in the field "=Date()" and there are no changes most of the time. I was wondering why the Sub was intermittent? Thank you.

If you wish to UPDATE tables, why not use a Button? Why the choice of the method AfterUpdate?

To answer your question: Didn't think about a button. Simply, thought it was best to prompt for save conformation after the last field. This way a simple enter on the keyboard (default) would confirm and save without having to move to a mouse click after every record is entered.

Is this normal thinking or do you think a button is necessary?

 

[pr2-eugin]

It is not a Strict rule you have to use a Button, I would do that, so it would be have a smooth logical flow, not normally people will follow the intended sequence.. Specially in cases like this; where sometime events might not be called.. Which would put the whole coding useless..

But it is your design.. You can have it as such.. Just make sure that is called..

 

[back2basic]

Hmmm, having another problem..

Does, "DoCmd.save", only save the current form? Even though the data is correct in the foreign table (after changes are made), one of the criteria I have in an expressions is not being executed. Each time I enter a record in this form I also need to save the foreign table....Materials. Is there another command I need to use other then DoCmd?

The name of the table is Materials

 

[pr2-eugin]

If the two tables are related, you can (should) use the Main Form SubForm structure.. this will avoid all unnecessary code that is involved in Updating multiple tables..

The Main form would be the table that is in the ONE side of the relationship and the ChildForm/SubForm will be the MANY side of the relationship..

 

[back2basic]

If the two tables are related, you can (should) use the Main Form SubForm structure.. this will avoid all unnecessary code that is involved in Updating multiple tables..

The Main form would be the table that is in the ONE side of the relationship and the ChildForm/SubForm will be the MANY side of the relationship..

Got it, and understand, the one to many relationship is
Materials >>>>>>Assigned_assets
Where Materials is the ONE side and Assigned_assets is the many.

However, This form, which is frmAssigned_assets, via VBA, is modifying data in the Materials table directly. In the frmAssigned_assets VBA code, I need to save the Materials table after each record entry. Can I do this without leaving the form?

 

[pr2-eugin]

In the frmAssigned_assets VBA code, I need to save the Materials table after each record entry. Can I do this without leaving the form?

Sounds like there is some calculation that is involved.. Storing calculate values in tables is a very bad design, look into "Calculated values" by Allen Browne to understand why it is a bad idea..

I think a quick reorganizing of the Table structure is highly essential here.. It will help you a lot of trouble in the future, if you take the time to do this now.

 

[back2basic]

Sounds like there is some calculation that is involved.. Storing calculate values in tables is a very bad design, look into "Calculated values" by Allen Browne to understand why it is a bad idea..

I think a quick reorganizing of the Table structure is highly essential here.. It will help you a lot of trouble in the future, if you take the time to do this now.

I hope I am not conflicting in what I say but what I am doing is updating the amount of a issued quantity of product. The Materials table has three fields pertaining to stock:
Materials_Table:
QTY_Stock - never changes
QTY_issued - Updated each time a user checks out an item
QTY_Remaining - Updated each time a user checks out an item

Are you saying QTY_issued and QTY_Remaining are not needed and never done this way? and I should calculate these values based on the what.....the number of records per/material item?

 

[pr2-eugin]

Are you saying QTY_issued and QTY_Remaining are not needed and never done this way?

Kinda yes..

(I have not worked with this sort of DB, but a genral idea would dictate..)Your Material table should have only basic information like Material ID, Material Name, and Max Stock Quantity.. Since these information normally do not chnage they would be in this Main table..

QTY_issued - Updated each time a user checks out an item - Since this changes as per the material required.. this would basically be stored along side the assigned_asset..
QTY_Remaining - Updated each time a user checks out an item - This is what I meant by not needed.. This data is calculated, this can be obtained now and then and has no real need to be stored in the table..

When you create a Master-Child form setup, the details can be gathered in a simple calculation on an Unbound Text box..

Me.CalculatedControlName = Me.Parent!Qty_Stock - DSum("QTY_issued","assigned_asset", "Material_ID = " & Me.fkID)

 

[back2basic]

I agree, what I have read and what you are saying here makes good sense. Queries will do the job and I guess I will have to migrate the DB to that design..... Think about this for a moment:

I am creating this DB for a construction company. What we have is stuff in the 100's like:

Shovels, Rakes, Sledge hammers, Safety Road cones, Road signs, Sign stands. These materials are checked out then back in (at some later date) in blocks of 10,20,30 or more.

In order for me to keep an accurate count of what is available is to query every time that material has been checked out and then back in. If a mistake is made checking the item in, the query will be wrong. What will happen 6 months down the road and forever for that matter? I will have to query every record to find out what is available.

I am not trying to buck the system but does it not make sense to keep a running total of what has been checked out and match it against the assignment table?

Pr2 - for now I need to test what I have done and give my boss a report. Is there a way to save the Materials Table after I assign materials in the frmAssign_assets?

P.S. reading your blog.

 

[pr2-eugin]

This would be just a dummy Query, change it as per your requirement..

SELECT Materials_Table.MaterialID, Materials_Table.Qty_Stock, Sum([assigned_asset].[QTY_issued]) AS total_QTY_ISSUED
FROM assigned_asset JOIN Materials_Table ON Materials_Table.MaterialID = assigned_asset.MaterialID_FK
GROUP BY Materials_Table.MaterialID, Materials_Table.Qty_Stock;

The above Query will take in all the materials from the Material table and Find the total stock level (Material_Table.Stock_QTY) and Stock that has been sent out (total_QTY_ISSUED)..

The main reason why storing "running total" is a very bad idea is, sometime, somewhere, someone will not 'UPDATE' the table properly.. Say for example.. When I am entering the values say I sent out 100 (the stock record is 120), some error popped out.. So to escape embarrassment, I just close the form (remember this will not update the table).. So the system will think we have got 120 still available but in reality we only have 20 left, 100 has been sent out.. So this way, there will be a HUGE LOSS !!

If you change this NOW, there is very minimal possibility for this loss to happen..

 

[back2basic]

This would be just a dummy Query, change it as per your requirement..

SELECT Materials_Table.MaterialID, Materials_Table.Qty_Stock, Sum([assigned_asset].[QTY_issued]) AS total_QTY_ISSUED
FROM assigned_asset JOIN Materials_Table ON Materials_Table.MaterialID = assigned_asset.MaterialID_FK
GROUP BY Materials_Table.MaterialID, Materials_Table.Qty_Stock;

The above Query will take in all the materials from the Material table and Find the total stock level (Material_Table.Stock_QTY) and Stock that has been sent out (total_QTY_ISSUED)..

The main reason why storing "running total" is a very bad idea is, sometime, somewhere, someone will not 'UPDATE' the table properly.. Say for example.. When I am entering the values say I sent out 100 (the stock record is 120), some error popped out.. So to escape embarrassment, I just close the form (remember this will not update the table).. So the system will think we have got 120 still available but in reality we only have 20 left, 100 has been sent out.. So this way, there will be a HUGE LOSS !!

If you change this NOW, there is very minimal possibility for this loss to happen..

I agree and am going to have to go to the query all records method but keep in mind, the exact same thing will happen if the user escapes the Assignment form because I will not have that record to query against.

Truly Thank you very much for your time and code to help me get it straight.

 

[pr2-eugin]

the exact same thing will happen if the user escapes the Assignment form because I will not have that record to query against.

This will be minimal.. If you have the relationship properly set "Referential integrity"..

Truly Thank you very much for your time and code to help me get it straight.

Glad to help.. Good luck..

 

[back2basic]

This will be minimal.. If you have the relationship properly set "Referential integrity"..
Glad to help.. Good luck..

I'll be posting. Got a lot of work to do but learning happily.....I guess.