Unbound combobox in header not obeying 3058 error handling

[mrmozambique]

Hi all. I haven't posted here for a while, but visit regularly. I'm hoping you might have some ideas about an issue I'm having with a subform.

I have a mainform and a continuous subform. The continuous subform has a header with two unbound comboboxes that are used to filter the results in the main detail section. This works fine.

I also have some error handling VBA code in case one of the required fields is left incomplete in the subform. It's pasted below. This code works fine if I try to click on another record in the continuous form. However, when I leave a required field blank and then use one of the header combos, it gives me the normal 3058 error and tries to debug. Why don't these comboboxes in the subform header obey the error handling I've set up?! It's driving me mad.

Thanks in advance for any help.

If DataErr = 3058 Then
    MsgBox "MAIN ERROR: You are missing one or more required disaggregations in this entry.  Please use the dropdowns to select gender, age, etc.  If the disaggregation does not apply, be sure to select N/A.", vbExclamation, "Required fields left blank!"
    Response = acDataErrCancel
    Me.cboother_disag_id.SetFocus
End If

 

[vbaInet]

What is the full text of the error?

 

[mrmozambique]

Hi VBAInet. I was able to sort out the 3058 error with some tricky If Thens under the GotFocus events of the unbound comboboxes in the header. However, I'm having the exact same problem if I enter a duplicate record (error 3022) in the continuous subform and then try to click on the header comboboxes.

I have error trapping set up for 3022 much like the code I posted above. However, it never gets to the error handling. Here's the message I get: "The changes you requested to the table were not successful because they would create duplicate values in the index, primary key, or relationship. Change the data in the field or fields that contain duplicate data, remove the index, or redefine the index to permit duplicate entries and try again." Then it gives me the standard End, Debug, etc. buttons that I definitely don't want appearing to the users.

This happens whenever I have a duplicate record and then update one of the unbound comboboxes that filter the continuous subform. Clicking anywhere else on the form besides these two buggers triggers the error handling just fine. Here's code from one of my unbound comboboxes.

    If IsNull(Me.cbosub_area_filter) Then
        Me.cbongi_filter.SetFocus
    Else
        Me.Filter = "sub_area_id = " & Me.cbosub_area_filter & " AND ngi_id = " & Me.cbongi_filter
        Me.FilterOn = True
        Me.cboother_disag_id.Requery
        Me.cbogender_id.Requery
        Me.cboage_id.Requery
    End If

 

[vbaInet]

What it's doing is attempting to save your record and because you have it as "Required" it's something you can't run away from, it becomes an untrappable error. It's eithe you you change the required property on the table, or handle the "Required" fields using code. You could also enter some text programatically and change the fore color to white on the Lost Focus event, and clear the box on got focus if the text box's forecolor is white.

My preferred option is removing the required property and handle it with code on the On Current event.

 

[mrmozambique]

They're actually not required fields. The primary key is actually made up of about five fields which always has to be unique. There's no way to get out of the primary key comibination. Does that make a difference?

 

[vbaInet]

Is there any particular reason why you want to enter duplicate records?

 

[DCrake]

You need to call a function on the before update of all the required fields to test for a duplicate entry in your table, if found warn user and undo changes.

David

 

[mrmozambique]

Ha! I don't want to enter duplicate records. However, the DB isn't just for me to use and you know what they say about software users...they're like water. If there's a hole, they'll find it.

It seems awfully complicated to have to check the combination of five records after each control's update. I don't have advanced VBA skills and the only way I can think to do this is to create a concatenated field in the table of the five primary key fields and Dcount against an unbound concatenated control on the form. Is that what you recommend, Dcrake?

I don't understand why it doesn't check for an error when you click on the header's controls. It checks if you select another record and is happy to give you the trapped error message. Is this really the only way out of it?

Thanks for your input so far, guys.

JP

 

[vbaInet]

Like I mentioned earlier, this seems to be one of those untrappable errors. Not all errors can be trapped using the On Error handler. I believe this is to safeguard the integrity of your database. If you want to have greater control then you should be thinking along the lines of using an unbound form and then using the INSERT/UPDATE query.

 

[vbaInet]

Actually, I've just thought about something. Use the form's On Error event to trap that error. This seems to be a form level error. That would work. Let us know?

 

[mrmozambique]

That's the thing. It seems like something that the form's On Error should catch, but for some reason it doesn't. I seem to have sorted it out using a control-level error handler to trap it. Since the unbound comboboxes won't accept an Undo command, I also had to create two unbound text controls to store the values of the unbound comboboxes after update because when the error happens, it leaves the main form unfiltered but allows the changes to the comboboxes in the header so they've lost synchronization. All a bit complex, but it appears to be working. Here's the code for the after update event of one of the header comboboxes.

Private Sub cbosub_area_filter_AfterUpdate()
On Error GoTo Err3022
Me.txtngi_save = Me.cbongi_filter.Column(0) ' store the dependent cascading combo value into an unbound text control for safe keeping
Me.cbongi_filter.Requery ' Requery the dependent combo
Me.cbongi_filter = Me.cbongi_filter.ItemData(0) 'Set it to the first value in the record set
'Me.txtsub_area_save = Me.cbosub_area_filter.Column(0) ' Store this control's value for safe keeping
    If IsNull(Me.cbongi_filter) Or IsNull(Me.cbosub_area_filter) Then 'If either are null, don't filter the subform
        Me.cbongi_filter.SetFocus
        Exit Sub
    Else
        On Error GoTo Err3022
        Me.Filter = "sub_area_id = " & Me.cbosub_area_filter & " AND ngi_id = " & Me.cbongi_filter
        Me.FilterOn = True
        Me.cboother_disag_id.Requery ' Requery some other combos in the subform that depend on the sub_area_filter control
        Me.cboage_id.Requery
        Me.cbogender_id.Requery
        Exit Sub
    End If
Err3022:
        Me.txtsub_area_save.Undo ' Revert back to previous value of the safekeeping text box for this control
        Me.cbosub_area_filter = Me.txtsub_area_save 'Mimic undo of unbound bombo
        Me.cbongi_filter.Requery 'Requery the dependent cascading combo so the original value will be available in the record set
        Me.cbongi_filter = Me.txtngi_save 'All this just to mimic an undo on the ngi_filter combo
        MsgBox "This combination of disaggregation objects already exists.  _
Click the ESC key to delete this entry or select another disaggregation.", vbExclamation, "Duplicate Data Entry Found"
        Response = acDataErrCancel
        Me.cboother_disag_id.SetFocus
End Sub

 

[Rich]

You haven't used the Forms OnError event though

 

[mrmozambique]

You haven't used the Forms OnError event though

I'm pretty sure I have. It catches all the other possible ways to create the 3022 and 3058 errors except when I use the unbound combos in the header. Is this not the form's OnError event?

Private Sub Form_Error(DataErr As Integer, Response As Integer)
If DataErr = 3022 Then
    MsgBox "This combination of disaggregation objects already exists.  You can only enter this combination of results one time.  For example, you may have already entered Partner X > 2010 > APR > Prevention > AB > Reached > An Giang > Male > IDU.  You can only enter this combination once for this reporting period.  If you wish to edit this entry, find it in this list and edit the existing entry.    Click the ESC key to delete this entry.", vbExclamation, "Duplicate Data Entry Found"
    Response = acDataErrCancel
    Me.cboother_disag_id.SetFocus
End If
If DataErr = 3058 Then
    MsgBox "You are missing one or more required disaggregations in this entry.  Please use the dropdowns to select gender, age, etc.  If the disaggregation does not apply, be sure to select N/A.  If you created this entry by mistake, click the ESC key and it will be removed.", vbExclamation, "Required fields left blank!"
    Response = acDataErrCancel
    Me.cboother_disag_id.SetFocus
End If
End Sub

 

[Rich]

Sorry, didn't see that part, too early after a Friday night

 

[vbaInet]

Hard to diagnose these sort of things without seeing the db. At least you found a way round it.

 

[mrmozambique]

Thanks for all your help. I'm sure I'll be back soon! Cheers

 

[gemma-the-husky]

MrMozambique

In some cases I DO check a number of fields, to validate input.

I have a common validation function that ensures all the important values are set to appropriate settings.

In the after update for each of the key values i call

(dim validated as boolean is already dimmed at the top of the form)
validated = callmyvalidationfunction

and then in the form beforeupdate event, I can just say something like this

if not validated then
msgbox "you cant save this yet. Certain important fields are incomplete"
cancel = true
end if


then you dont need any fancy error trapping at all

 

[vbaInet]

MrMozambique

In some cases I DO check a number of fields, to validate input.

I have a common validation function that ensures all the important values are set to appropriate settings.

In the after update for each of the key values i call

(dim validated as boolean is already dimmed at the top of the form)
validated = callmyvalidationfunction

and then in the form beforeupdate event, I can just say something like this

if not validated then
msgbox "you cant save this yet. Certain important fields are incomplete"
cancel = true
end if


then you dont need any fancy error trapping at all

Great advice there. In addition you could also highlight the fields by changing their backcolor to red as you step through each field.