The proper way to abort or cancel ?

[back2basic]

This one is a bit difficult for me to figure and understand how to solve. Please keep in mind, I am doing all I describe below in effort to handle an "escape" by the user. If the user "escapes" I want to clear the form and close it which I can do fine with the error handler and the following code. The problem is I can't handle the weird stuff the Combo Box does?

Handler:
DoCmd.CancelEvent
DoCmd.Close

What is happening: I have an unbound Combo Box which the information is used to populate unbound fields on a form ( for user information only). The Combo Box triggers an event code I have tried this both ways with the following results:

BeforeUpdate - This makes it very easy to handle the escape as no data is written to the table but after the user selects a record in the Combo Box the code runs twice? Once after the user selects the information in the CB and then again when the user hits enter. It seems the forms does not advance to the next field after the CB data selection?

AfterUpdate - This makes it very hard to handle the data entry after the CB because I automatically move to the next field but no matter what I do, a record gets created ( even though except for the date it is blank)

What is the best way to do this and stop the non-sense I must be creating?

Thank you all in advance.

 

[missinglinq]

To undo changes or a New Record that has been started you'd simply use

Me.Undo

then close the Form.

Sorry, but I have no idea what you mean by "after the user selects a record in the Combo Box the code runs twice."

Linq ;0)>

 

[back2basic]

To undo changes or a New Record that has been started you'd simply use

Me.Undo

then close the Form.

Sorry, but I have no idea what you mean by "after the user selects a record in the Combo Box the code runs twice."

Linq ;0)>

First Thank you, Me.Undo does the job and the form works great. Kinda feel like your logo. In any event, if you have an unbound Combo Box as the first field in a form which is triggered by a BeforeUpdate event, after you select a record from the Combo Box, the code runs however the cursor remains at the Combo Box and upon hitting enter to move to the next field the code runs the same routine again ?? Unless you move with your mouse ( which I guess your should not have to do). to the next field.

 

[missinglinq]

Check that you don't have code behind more than one event of the Combobox, and show all code that you do have behind it.

Linq ;0)>

 

[back2basic]

Check that you don't have code behind more than one event of the Combobox, and show all code that you do have behind it.

Linq ;0)>

No only one routine running that I can see for this Combo Box by tabbing through view code window. Unless there is a way to see everything, this all I can see.

 

[missinglinq]

And that code is?

 

[back2basic]

And that code is?

Well, there are two Subs called by Combo Boxes's on this form. Do you really want to see my code?....... I'm sure you'll be surprised by how sophisticated it is?

Missinglinq, More important question, Why is it after I select a record in a Combo Box, and perform my AfterUpdate Sub routine does the cursor not automatically advance to the next field on the form? This is very important as selection has been made and I do not want the user to have to enter again. Can I put code in my routing to advance to the next field?

Also, If I ( the user) hit escape at some points in the form i.e., ( after the Combo Box has been updated) only one field clears, which is the bound field, and nothing else happens. I can't at this point get the form to "undo" and close it as if I am in Limbo. I think at this point we are in the form properties but can't figure out how to undo and close.

 

[missinglinq]

...Why is it after I select a record in a Combo Box, and perform my AfterUpdate Sub routine does the cursor not automatically advance to the next field on the form...

Perhaps if we could see the code you're using we can determine the reason it's doing this!

...If I ( the user) hit escape at some points in the form i.e., ( after the Combo Box has been updated) only one field clears...

If I remember correctly, hitting <Esc> once clears the last Field entered, hitting <Esc> twice clears them all.

...I can't at this point get the form to "undo"...

You can 'undo' everything by using...Me.Undo!

Linq ;0)>

 

[back2basic]

Have been in the field all day. Wish I could have seen this earlier. Anyhow here is my code. I know it's crude.

Option Compare Database
Dim QTY_Being_Returned As Integer
Dim QTY_Returned_to_Date As Integer
Dim Asset_ID As Variant
Dim Mat_ID As Variant
Dim Record_Stat As String

Private Sub cboAsset_ID_AfterUpdate()
Dim Quantity_issued As Integer

'Populate the form with data from the existing record
Me.Last_Name = Me.cboAsset_ID.Column(9) 'Update the form to show the users Last name.
Me.First_Name = Me.cboAsset_ID.Column(10) 'Update the form to show the users First name.
Me.Qty_issued = Me.cboAsset_ID.Column(4) 'Update the form to show the existing quantity issued.
Me.Qty_Return = Me.cboAsset_ID.Column(5) 'Update the form to show the current quantity returned.
Me.Date_checked_out = Me.cboAsset_ID.Column(0) 'Update the form to show the date quantity issued.
Me.Mat_Name = Me.cboAsset_ID.Column(2) 'Update the form to show the material name the product.
Asset_ID = Me.cboAsset_ID.Column(6) 'Get the existing Asset_ID of this record.
Mat_ID = Me.cboAsset_ID.Column(3) 'Get the existing Material_ID of this record.
Employee_ID = Me.cboAsset_ID.Column(8) 'Get the existing Employee_ID of this record.

On Error GoTo Handler:

Quantity_issued = Me.cboAsset_ID.Column(4) ' Get the number of materials assigned to this employee
Material_ID = Me.cboAsset_ID.Column(3)
QTY_Returned_to_Date = Me.cboAsset_ID.Column(5) ' Get the quantity of this material returned to date.

If Quantity_issued >= QTY_Returned_to_Date Then
Line1:
'Ask user how much of this material is being returned.
QTY_Being_Returned = InputBox("Enter the Quantity being returned, " & (Quantity_issued - QTY_Returned_to_Date) & " Remain to be Returned?")
        If (QTY_Returned_to_Date + QTY_Being_Returned) > Quantity_issued Then ' Test to see if user has entered more then is available
        MsgResponse = MsgBox("Returning more then checked out  " & (Quantity_issued - QTY_Returned_to_Date) & " Remain to be Returned. Do you want continue?", vbRetryCancel)
                If MsgResponse = vbRetry Then
                GoTo Line1
                Else
                ' The user has elected to abort.
                Me.Undo
                DoCmd.CancelEvent
                DoCmd.Close
                Exit Sub
                End If
            
        Else ' Quantity returned is not greater then Quantity issued
            If (QTY_Returned_to_Date + QTY_Being_Returned) = Quantity_issued Then 'Test if Quantity returned = Quantity issued
                Record_Stat = "yes"
            Else
            Record_Stat = "no"
            End If
        Me.Text6 = QTY_Being_Returned 'Update the text box in the form to show how many units returned.
        End If
Else
MsgBox " Item already returned"
Me.Undo
DoCmd.CancelEvent
DoCmd.Close
End If
Exit Sub

Handler:
MsgBox "An error ocurred in cbo Asset_ID"
Me.Undo
DoCmd.CancelEvent
DoCmd.Close
End Sub

Private Sub Form_Error(DataErr As Integer, Response As Integer)
Me.Undo
DoCmd.CancelEvent
DoCmd.Close
End Sub

Private Sub Text14_AfterUpdate()
Dim MsgResponse2
Dim varX As Integer
' 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 returned.
On Error GoTo Handler:
    'prompt user if He wants to save the record
     MsgResponse2 = MsgBox("Do you want Save this transaction?", vbYesNo)
        'Save and write all Data
        If MsgResponse2 = vbYes Then
            If Record_Stat = "yes" Then 'The quantity returned is equal to the quantity checked out.  Close the record
                Me.Record_closed = "yes" ' Set this return record to closed
                strSQL = "UPDATE Material_Assignment SET Record_closed = " & "yes" & " WHERE Assets_ID = " & Asset_ID ' Update Issued quantity
                CurrentDb.Execute strSQL, dbFailOnError
                strSQL = "UPDATE Material_Assignment SET Quantity_Returned = " & QTY_Being_Returned + QTY_Returned_to_Date & " WHERE Assets_ID = " & Asset_ID ' Update Issued quantity
                CurrentDb.Execute strSQL, dbFailOnError
            
            Else
                'The quantities are not equal,  close the return record and keep the issued record open
                Me.Record_closed = "yes" ' Set this return record to closed
                varX = DLookup("[Quantity_Returned]", "Material_Assignment", "Assets_ID =" & Asset_ID)
                strSQL = "UPDATE Material_Assignment SET Quantity_Returned = " & QTY_Being_Returned + varX & " WHERE Assets_ID = " & Asset_ID ' Update Issued quantity
                CurrentDb.Execute strSQL, dbFailOnError
            End If
                
        varX = DLookup("[Issued_Quantity]", "Materials", "Material_ID =" & Mat_ID)
        strSQL = "UPDATE Materials SET Issued_Quantity = " & varX - QTY_Being_Returned & " WHERE Material_ID = " & Mat_ID ' Update Issued quantity
        CurrentDb.Execute strSQL, dbFailOnError
        varX = DLookup("[Remaining_Quantity]", "Materials", "Material_ID =" & Mat_ID)
        strSQL = "UPDATE Materials SET Remaining_Quantity = " & QTY_Being_Returned + varX & " WHERE Material_ID = " & Mat_ID ' Update Issued quantity
        CurrentDb.Execute strSQL, dbFailOnError
        
        '  Clear the form and get ready for the next transaction
        Me.Last_Name = ""
        Me.First_Name = ""
        Me.Qty_issued = ""
        Me.Qty_Return = ""
        Me.Date_checked_out = ""
        Me.Mat_Name = ""
        Me.cboAsset_ID.Requery
        Me.Requery
        Exit Sub
        Else ' User does not want to save the transaction
            Me.Undo
            DoCmd.CancelEvent
            DoCmd.Close
        Exit Sub
        End If


Handler:
MsgBox "An error ocurred in Text14 AfterUdate"
DoCmd.CancelEvent
DoCmd.Close
End Sub

 

[missinglinq]

I understand your reluctance to post the code; I've got a headache from trying to schlepp through it! This is really going to require a laying on of hands, I think. If you could save the file in a 2007 or earlier format, Zip it up and attach it to a post, I'd be happy to look at it and see what I can figure out.

Just off hand, it would help if your OnError line were at the top of the Sub, directly beneath the Sub Header, and also have the Error routine give you an error description, rather than simply undoing everything and closing, if an error occurs.

Also, your use of Go To Line1 could be troublesome; we stopped using 'spaghetti code' years ago because it tends to have untoward effects, often leading to infinite loops, which your problem sort of sounds like.

You're sure you no longer have code in the BeforeUpdate event of the Combobox, as well as its AfterUpdate event?

Linq ;0)>

 

[back2basic]

I understand your reluctance to post the code; I've got a headache from trying to schlepp through it! This is really going to require a laying on of hands, I think. If you could save the file in a 2007 or earlier format, Zip it up and attach it to a post, I'd be happy to look at it and see what I can figure out.

I understand, this is a bit embarrassing to show the world of Access experts what I have done....Spaghetti code...I agree. Unfortunately, at this point, since I have not programmed in 15 years, I am not experienced enough or even know all the new functions of today's applications to be able to write any better. Doesn't mean I shouldn't try, it has been a good experience........I am learning and getting better?.

In any event, this application is not for a client or any other end user but soley for myself and the job I am trying to do...Perhaps, I may have an assistant but that's it. Eventually, this DB will end up as something I can be proud of.......I hope.

I have attached the DB as it is today. PLEASE don't be too critical. I know it is junk by your standards I am sure. Their are calculated fields and redundancy. But keep in mind, it works exactly as I want to. Ii it is a good model of what I need to do. I encourage you to use the Switch board and assign materials in various way...to the same employee...in various quantities....to different employees Return materials in various amounts untill all are returned. Hit escape and cancel and all that stuff. This is what will happen when this gets going.

FYI, The DB, at least for me, has been very complex, I simply am not able to use and understand Queries well enough to do it any other way then the way I have written. I do understand I should have used queries to calculated remaining or outstanding materials but they are simply too complex. In any event, I appreciate your advice even if you throw up your hands and say it's trash.

Just off hand, it would help if your OnError line were at the top of the Sub, directly beneath the Sub Header, and also have the Error routine give you an error description, rather than simply undoing everything and closing, if an error occurs.

...Agree

Also, your use of Go To Line1 could be troublesome; we stopped using 'spaghetti code' years ago because it tends to have untoward effects, often leading to infinite loops, which your problem sort of sounds like.

...Agree, should probably use a For ..While loop or something


You're sure you no longer have code in the BeforeUpdate event of the Combobox, as well as its AfterUpdate event?

To be honest how else would I be able to see any other code ( hidden or otherwise) then what is listed. There are no other events listed in the Combo Box or Text Box properties. I have selected view code. Have I missed something.
Linq ;0)>

Thank you Dale

 

[missinglinq]

Sorry, the attachment won't open in Access 2007. As I said, for me to use it it needs to be in 2007 or earlier format.

Linq ;0)>

 

[back2basic]

Sorry, the attachment won't open in Access 2007. As I said, for me to use it it needs to be in 2007 or earlier format.

Linq ;0)>

Even though I am using Access 2010, the file header says Database (Access 2007) ??
I try to save the DB "save type as" as an alternate or earlier version but the application only allows me to choose " Microsoft Access Database". Sooo..., what can I do?

 

[back2basic]

Even though I am using Access 2010, the file header says Database (Access 2007) ??
I try to save the DB "save type as" as an alternate or earlier version but the application only allows me to choose " Microsoft Access Database". Sooo..., what can I do?

Missinglinq, this is very important to me that you are willing to look at my work. Is this a typical issue with Access 2010 not being able to save in a earlier version or is my version missing something?

Perhaps we can get someone else on this board to open the DB and save it in the format you need? Just a thought.

Dale

 

[missinglinq]

Not running 2010, I have no idea! Maybe someone running 2010 will come along and have a look at your attachment. Sorry!

Linq ;0)>