Error Check and Add Record that has not been commited when user double clicks
I have a form that allows the user to either add or update an invoice on a front end form. User enters various data fields to create a new invoice (invoice date, customer, purchase order number). These fields are mandatory and I error check them to make sure there is data in the fields by using the before update event on the record. The user can double click on the invoice number to open a form that allows them to add products to the invoice.
The problem I am running into is when a user is adding a new record and then double clicks the invoice number BEFORE the record has been added to the table. At that point, the code needs to check the mandatory fields to make sure there is data and that the data meet error checking criteria. If all mandatory data has been entered, the record should be added to the invoice master table and the second form for adding invoice detail should be displayed.
I have place this code in the double click event
If Me.Dirty = True Then
Me.Dirty = False
End If
When the user double clicks, the before update does fire and the data is error checked. However, even if an error is found the "dirty" code above fires. I suppose I could create a Public boolean variable and change it's value to true if no errors are found in the before update event. Then, I would only fire the "dirty" event if that variable value is True. Is there a better method to accomplish this task?
By the way, when my "dirty" code does fire, I get a run time error 3270, "Property not found" on the Me.Dirty = False statement.
Any ideas?
The problem I am running into is when a user is adding a new record and then double clicks the invoice number BEFORE the record has been added to the table. At that point, the code needs to check the mandatory fields to make sure there is data and that the data meet error checking criteria. If all mandatory data has been entered, the record should be added to the invoice master table and the second form for adding invoice detail should be displayed.
I have place this code in the double click event
If Me.Dirty = True Then
Me.Dirty = False
End If
When the user double clicks, the before update does fire and the data is error checked. However, even if an error is found the "dirty" code above fires. I suppose I could create a Public boolean variable and change it's value to true if no errors are found in the before update event. Then, I would only fire the "dirty" event if that variable value is True. Is there a better method to accomplish this task?
By the way, when my "dirty" code does fire, I get a run time error 3270, "Property not found" on the Me.Dirty = False statement.
Any ideas?
ASKER
Thanks for your prompt reply. I am using the BeforeUpdate event to error check the record. The problem is the record is not written to the table until the operator moves to the next record.
If they double click the invoice field BEFORE they move to the next record, the BeforeUpdate event does fire. It finds the errors. It displays the error message. Then the double click event takes over and runs any code assigned.
What I want to happen is this: If no errors are found, the record will be written to the table (using the "dirty" event) and the next form will be displayed. If errors are found, the code to write the record to the table should not fire.
This is happening when the operator is entering a new record and then double clicks the invoice number field before the record has been written to the table. There are other fields that are not mandatory that the operator may or may not key data into. I need to error check data and base writing changes to the table on the absence or presence of errors.
If they double click the invoice field BEFORE they move to the next record, the BeforeUpdate event does fire. It finds the errors. It displays the error message. Then the double click event takes over and runs any code assigned.
What I want to happen is this: If no errors are found, the record will be written to the table (using the "dirty" event) and the next form will be displayed. If errors are found, the code to write the record to the table should not fire.
This is happening when the operator is entering a new record and then double clicks the invoice number field before the record has been written to the table. There are other fields that are not mandatory that the operator may or may not key data into. I need to error check data and base writing changes to the table on the absence or presence of errors.
If errors are found, the code to write the record to the table should not fire.Setting Cancel = True in the Before_Update event will stop the Save from happening, and would require the user to either comply with the validation, or undo the changes.
You mention that you have code in the Before_Update event - do you also CANCEL the event if the validation fails? This is critical ...
Also, what code are you running in the Dirty event?
ASKER
Below is some of the code from the BeforeUpdate event
Private Sub Form_BeforeUpdate(Cancel As Integer)
Dim strFormName, strOpenArgs As String
'Is the invoice number empty
If IsNull(Me.txtInvoice) Or Me.txtInvoice = 0 Then
strFormName = "frmErrorOKOnly"
strOpenArgs = "You did not enter an INVOICE number. " _
& vbCrLf _
& "You must do so before you can continue. " _
& ""
DoCmd.OpenForm strFormName, , , , , acDialog, strOpenArgs
Cancel = True
Me.txtInvoice.SetFocus
Exit Sub
End If
I am not using the Dirty Event. I have this code attached to the double click event on the bound field txtInvoice
Private Sub txtInvoice_DblClick(Cancel
Dim strFormName, strOpenArgs As String
'Save changes
If Me.Dirty = True Then
Me.Dirty = False
End If
'Setup variable data for opening Invoice Detail edit form
strOpenArgs = Me.txtInvoice
strFormName = "frmInvoiceNew"
strWhere = "[Invoice]=" & Me.txtInvoice
'Close invoice selection form
DoCmd.Close
'Open invoice detail edit form
DoCmd.OpenForm strFormName, acNormal, , strWhere, , acDialog
Private Sub Form_BeforeUpdate(Cancel As Integer)
Dim strFormName, strOpenArgs As String
'Is the invoice number empty
If IsNull(Me.txtInvoice) Or Me.txtInvoice = 0 Then
strFormName = "frmErrorOKOnly"
strOpenArgs = "You did not enter an INVOICE number. " _
& vbCrLf _
& "You must do so before you can continue. " _
& ""
DoCmd.OpenForm strFormName, , , , , acDialog, strOpenArgs
Cancel = True
Me.txtInvoice.SetFocus
Exit Sub
End If
I am not using the Dirty Event. I have this code attached to the double click event on the bound field txtInvoice
Private Sub txtInvoice_DblClick(Cancel
Dim strFormName, strOpenArgs As String
'Save changes
If Me.Dirty = True Then
Me.Dirty = False
End If
'Setup variable data for opening Invoice Detail edit form
strOpenArgs = Me.txtInvoice
strFormName = "frmInvoiceNew"
strWhere = "[Invoice]=" & Me.txtInvoice
'Close invoice selection form
DoCmd.Close
'Open invoice detail edit form
DoCmd.OpenForm strFormName, acNormal, , strWhere, , acDialog
ASKER CERTIFIED SOLUTION
membership
Create a free account to see this answer
Signing up is free and takes 30 seconds. No credit card required.
ASKER
I tried your suggestions to no avail. Finally, I added the error checking code to the double click event on the invoice number. This code will be run twice as it will run on the double click event and prior to the record being updated. However, it does work. If an error is not found, I set me.dirty to false, writing changes to table. If an error is found, I present an error screen with associated error message. I want to award you the points. However, I am not sure which answer I should accept as solution. All information was benefical and taught me a few things. However, it did not solve the problem. Thanks again for your help and insight.
This post is just meant as additional information and should not be considered for any points:
<The user can double click on the invoice number to open a form that allows them to add products to the invoice.>
I always avoid doing this (double click on the invoice number) ...it seems like it is a "slick" thing to do, but remember that this functionality has to be "told" to a new user.
Also note that then users are always double clicking other things to see if something else will happen.
When not keep it simple and just have a "Add Items to invoice" button?
This way you have the button disabled until all fields are filled in and the record is added/updated.
In other words, perhaps you are trying to do too much with this one form.
<I have a form that allows the user to either add or update an invoice on a front end form.>
Why not create a form that selects the "Mode" the form will open in?
(Add, Delete, Update, View)
Then you can put a combobox on this form to find a certain Invoice to: view, edit or delete...
Then this problem (and all the associated machinations needed to workaround it) goes away.
The "One form that does multiple things" design approach, seem like a convenient thing to do, but many times it only complicates things unnecessarily, and creates issues like the one you are having here.
...Then when you (or the users) eventually want other "features" like filtering and sorting, it can really get messy.
JeffCoachman
<The user can double click on the invoice number to open a form that allows them to add products to the invoice.>
I always avoid doing this (double click on the invoice number) ...it seems like it is a "slick" thing to do, but remember that this functionality has to be "told" to a new user.
Also note that then users are always double clicking other things to see if something else will happen.
When not keep it simple and just have a "Add Items to invoice" button?
This way you have the button disabled until all fields are filled in and the record is added/updated.
In other words, perhaps you are trying to do too much with this one form.
<I have a form that allows the user to either add or update an invoice on a front end form.>
Why not create a form that selects the "Mode" the form will open in?
(Add, Delete, Update, View)
Then you can put a combobox on this form to find a certain Invoice to: view, edit or delete...
Then this problem (and all the associated machinations needed to workaround it) goes away.
The "One form that does multiple things" design approach, seem like a convenient thing to do, but many times it only complicates things unnecessarily, and creates issues like the one you are having here.
...Then when you (or the users) eventually want other "features" like filtering and sorting, it can really get messy.
JeffCoachman
ASKER
In the end, I took some of the advice of JeffCoachman. I was trying to make the invoice selection screen do more than it was designed for, select an invoice to work with.
I kept the "doubleclick" option to change an invoice. I did this because it seem intuitive to "doubleclick" on an invoice to change it. If a button is used, the user has to select a record and then click a button. Then, from the "single form", the operator can make desired changes to the invoice. This form will not allow additions, user will be able to modify only. I removed the ability to browse thru records. User is restricted to the record they selected.
I added a button to add an invoice. User enters the invoice number and then click the button to add a new invoice. System looks for existing invoice and if it does not exist, it calls the form to add the invoice.
I kept the "doubleclick" option to change an invoice. I did this because it seem intuitive to "doubleclick" on an invoice to change it. If a button is used, the user has to select a record and then click a button. Then, from the "single form", the operator can make desired changes to the invoice. This form will not allow additions, user will be able to modify only. I removed the ability to browse thru records. User is restricted to the record they selected.
I added a button to add an invoice. User enters the invoice number and then click the button to add a new invoice. System looks for existing invoice and if it does not exist, it calls the form to add the invoice.
Sub Before_Update(Cancel As Integer)
If Nz(Me.Control1, "") = "" Then
Msgbox "You must enter a value in Control1"
Cancel = True
End If
If Nz(Me.Control2, "") = "" Then
Msgbox "You must enter a value in Control2"
Cancel = True
End If
End Sub
You can also use the Tag property of the controls and loop through the Controls collection, if that fits better with what you're doing. To do that, add a value to the Tag control for each Control you wish to check (for example, add the word 'req' to each), then loop through the Controls and check any of them with the word 'req' there:
Dim ctl As Control
Dim s As String
For Each ctl In Me.Controls
If ctl.Tag = "req" Then
If Nz(ctl.Value, "") = "" Then
s = s & vbCrLf & ctl.Name & " is required"
End If
End If
Next ctl
If Len(s) > 0 Then
'/ one or more did not pass validation
Msgbox "Validation failed: " & vbCrLf & s
Else
'/ save the data
End If