So i have an order form, and have a button that if pressed will cancel the order and close the form, but its not working...
I have
Me.Undo
DoCmd.Close acForm, Me.Name
but if say i restarted to enter the order details (and a new record created), when press the "Cancel" Button the form closes but the record is still showing with just the field that was completed?
so i forgot to add that the form has a second button - which is called "saved" one its clicked the record is saved but then asks the user to complete the missing fields, so then pressing Cancel does not delete the record so i get a need to add if got a record ID then delete and if no ID undo?
so i forgot to add that the form has a second button - which is called "saved" one its clicked the record is saved but then asks the user to complete the missing fields, so then pressing Cancel does not delete the record so i get a need to add if got a record ID then delete and if no ID undo?
You should not save a record and then validate the missing fields. Does not make sense. In the forms before update event you validate the data and cancel the event if not valid.
Hi Guys, I am new to using access. I have finally after 5 hours created a database and a form. All my combo Box and other necessary buttons are all good. But I want the user form to have a save button. Please anyone can provide me the basic step. The user form feeds the database...
Hi Guys, I am new to using access. I have finally after 5 hours created a database and a form. All my combo Box and other necessary buttons are all good. But I want the user form to have a save button. Please anyone can provide me the basic step. The user form feeds the database...
You could, but would not be real easy. If important to you I could probably write it.
Here is the problem. When you loop the controls on a form they are in an order known as z-order. This has to do with when they were added to the form and other position factors. You cannot loop by other order such as tab order and you cannot alter or control the z-order.
What you could do is create a custom collection. When the form loads add the controls to the custom collection in your desired order. Then pass this collection to the validate function. Now you can loop the controls in the custom collection instead of the form's control collection.
You could, but would not be real easy. If important to you I could probably write it.
Here is the problem. When you loop the controls on a form they are in an order known as z-order. This has to do with when they were added to the form and other position factors. You cannot loop by other order such as tab order and you cannot alter or control the z-order.
What you could do is create a custom collection. When the form loads add the controls to the custom collection in your desired order. Then pass this collection to the validate function. Now you can loop the controls in the custom collection instead of the form's control collection.
Oh wow that does sound difficult to create i guess any help apricated as i got the customer name after the address lines 1 and 2 and then the town is before the address and the post code before the address it just looks messy
To demo. I named the new function ValidData2. In this version you need to first create a collection of the controls to validate and then pass them in. I do this with a variable at the top of the form module and then load the collection in the desired order in the forms load event. For demo purposes, I did it backwards to make it obvious.
Code:
Option Compare Database
Option Explicit
'add a ctls collection
Private ctls As New Collection
Private Sub cmdClose_Click()
Dim rtn As Long
If DataValid2(Me) Then
DoCmd.Close acForm, Me.Name
Else
rtn = MsgBox(" Select OK to Close without saving, or CANCEL to complete form.", vbOKCancel, "Validate Data")
If rtn = vbOK Then
DoCmd.Close acForm, Me.Name
End If
End If
End Sub
Private Sub Form_BeforeUpdate(Cancel As Integer)
If Not DataValid2(Me, ctls) Then
Cancel = True
End If
End Sub
Private Sub Form_Load()
'Add controls in order
ctls.Add Me.Job_Title
ctls.Add Me.Company
ctls.Add Me.First_Name
ctls.Add Me.Last_Name
End Sub
In this example I modified the function that resets the borders. Not sure the reason for the original design, but as soon as you re-enter one of the marked controls all the red borders are reset. I modified this to the after update of the specific field.
To demo. I named the new function ValidData2. In this version you need to first create a collection of the controls to validate and then pass them in. I do this with a variable at the top of the form module and then load the collection in the desired order in the forms load event. For demo purposes, I did it backwards to make it obvious.
Code:
Option Compare Database
Option Explicit
'add a ctls collection
Private ctls As New Collection
Private Sub cmdClose_Click()
Dim rtn As Long
If DataValid2(Me) Then
DoCmd.Close acForm, Me.Name
Else
rtn = MsgBox(" Select OK to Close without saving, or CANCEL to complete form.", vbOKCancel, "Validate Data")
If rtn = vbOK Then
DoCmd.Close acForm, Me.Name
End If
End If
End Sub
Private Sub Form_BeforeUpdate(Cancel As Integer)
If Not DataValid2(Me, ctls) Then
Cancel = True
End If
End Sub
Private Sub Form_Load()
'Add controls in order
ctls.Add Me.Job_Title
ctls.Add Me.Company
ctls.Add Me.First_Name
ctls.Add Me.Last_Name
End Sub
In this example I modified the function that resets the borders. Not sure the reason for the original design, but as soon as you re-enter one of the marked controls all the red borders are reset. I modified this to the after update of the specific field.
Ok thanks I have copied over this new code and module but i am getting error message Compile error: Argument not optional onthis code..
Private Sub cmdClose_Click()
Dim rtn As Long
If DataValid2(Me) Then
DoCmd.Close acForm, Me.Name
Else
rtn = MsgBox(" Select OK to Close without saving, or CANCEL to complete form.", vbOKCancel, "Validate Data")
If rtn = vbOK Then
DoCmd.Close acForm, Me.Name
End If
End If
End Sub
Ok thanks I have copied over this new code and module but i am getting error message Compile error: Argument not optional onthis code..
Private Sub cmdClose_Click()
Dim rtn As Long
If DataValid2(Me) Then
DoCmd.Close acForm, Me.Name
Else
rtn = MsgBox(" Select OK to Close without saving, or CANCEL to complete form.", vbOKCancel, "Validate Data")
If rtn = vbOK Then
DoCmd.Close acForm, Me.Name
End If
End If
End Sub