Cannot change a text box to blank with VBA

[JeremyI]

Stumped on another issue in Access 2003 (file format Access 2000) where code does not seem to do what it's supposed to based on other topics I have read.

I have a form that allows the user to select a record from the database's main list of properties when they determine that it matches a record on a new imported spreadsheet. (This requires a manual matching exercise, because several different data sources are to be blended together, and they do not all use the same names for the properties, etc. Needs to be rather user-friendly.)

Here is my code assigned to the On Change event for a combobox cboMatch:

Private Sub cboMatch_Change()

    ' Update the related field to the PropIndex of any selected property.
    
    If IsNull(cboMatch) Then       ' Deleting (un-matching) is an option.
        txtPropIndex = Null
        DoCmd.Save
        txtPropIndex.Requery
    Else
        txtPropIndex = cboMatch
        txtPropIndex.Requery
    End If

End Sub

The form is Single Form view and bound to the new table, tblImportedFrom AAPList(2). cboMatch is an unbound combobox and gets its row source from the main table, tblDCCPropertie s. txtPropIndex shows the value of PropIndex, the foreign key in the new table, which (after being matched) relates it to the main table.

The updating of txtPropIndex works absolutely fine if an option is selected from the list. However, I also want to make it possible to delete the value in cboMatch, which should then delete the value in txtPropIndex and the underlying table. (This option is a must in case an incorrect match is made.) And for some reason, txtPropIndex will not change to Null. Once a selection is made, it always shows a value; I cannot get it to delete, because it won't accept the Null from cboMatch and I have locked the control.

cboMatch is also programmed to show already-matched property names via On Current:

Private Sub Form_Current()

    ' If property is already matched when the record changes,
    ' show the matched property name from tblDCCProperties in the combo box.
    
    If Not IsNull(txtPropIndex) Then
        cboMatch = txtPropIndex
    Else
        cboMatch = ""                ' Otherwise, reset it to blank.
        cboMatch.Requery
    End If
    
    ' Regardless, the selectable field should be highlighted.
    
    cboMatch.SetFocus
    
End Sub

The second bit of code is working according to plan, as far as I can tell. I hope it isn't interfering with the problem code.

I've also tried "" rather than IsNull/= Null, and tried all of this with and without the DoCmd.Save, and even tested it out with On Dirty. The value in txtPropIndex will not ever go away.

Help? Please...?

[TheSmileyCoder]

DoCmd.Save will save your form not your record. You would need to use
docmd.RunComman d acCmdSaveRecord
to save the record.
Have you tried such a thing as:

    If IsNull(cboMatch) Then       ' Deleting (un-matching) is an option. 
       [I][B]msgBox "Running null match"[/B][/I]
        txtPropIndex = Null 
        DoCmd.Save 
        txtPropIndex.Requery 
    Else 
        txtPropIndex = cboMatch 
        txtPropIndex.Requery 
    End If

So that you atleast know that the code is running.

Also is your textbox named the same as the field in your table? That can sometimes cause a bit odd results.

[JeremyI]

Re: Cannot change a text box to blank with VBA

Hmm... Okay, thanks, that was a good idea, TheSmileyOne. No message box appears; I also tried it for On Not in List, and still nothing. So I guess the events are not being triggered by deleting the text that displays in cboMatch.

The combobox has 2 columns, with Bound Column being 1, and widths of 0cm;1cm so that only the property's name displays for the user. The bound column is PropIndex, primary key in the main table of the database, but the combobox itself is unbound. Do you know if there is any event that would detect a user's deleting the display text in this combobox?

Regarding the name of the textbox ("txtPropIndex" ), it is different from the field name ("PropIndex" ), so that does rule out that potential issue.

Maybe I'll drop in a command button to "un-match".

But deletion would seem to be a simpler and more elegant solution if it's possible, so looking forward to any other suggestions!

[TheSmileyCoder]

Have you tried using cmb_BeforeUpdat e instead?

Maybe (I don't know) the cmb_Change is not triggered for unbound comboboxes? For instance the "Dirty" property of a firm is not triggered for unbound controls.

[JeremyI]

Thanks again, TSO, but alas, still nothing happens. I suppose this is all complicated because of the unbound combobox.

I think it has something to do with the fact that it is Column(1) of cboMatch that can be edited by the user. For instance, this...

Private Sub cboMatch_Exit(Cancel As Integer)
    If IsNull(cboMatch.Column(1)) Then txtPropIndex = Null
End Sub

...does work as far as it goes. I.e., when I delete the text in cboMatch and move to a different control or click a navigation button, the value of txtPropIndex does get reset to Null.

However, there is an unfortunate side effect, in that I cannot navigate to another record while the focus is on cboMatch! I have to click on a different control before clicking any of the navigation buttons, or else the same record remains loaded. Seems very strange to me. But obviously I can't subject the data entry person to such nonsense.

Command button is now in place ("Remove Match") and although it seems a bit brutal, it does exactly what it needs to. I think I will stick with that option because as much as I personally would like to solve the problem properly, the needs of the project mean I mustn't dwell on cosmetics.

Appreciate your helpful thoughts very much!

[NeoPa]

As Smiley says, consider a different event.

BeforeUpdate & AfterUpdate events would seem to fit most closely, though I'd only use BeforeUpdate if you are considering cancelling the change in your code. Otherwise (as I suspect), use AfterUpdate.

[JeremyI]

AfterUpdate is the one, NeoPa--cheers! I'm filing away this whole thread for future reference.

[NeoPa]

Always a pleasure Jeremy.

Welcome to Bytes!