By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
435,255 Members | 2,575 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 435,255 IT Pros & Developers. It's quick & easy.

Cannot change a text box to blank with VBA

P: 49
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:

Expand|Select|Wrap|Line Numbers
  1. Private Sub cboMatch_Change()
  2.  
  3.     ' Update the related field to the PropIndex of any selected property.
  4.  
  5.     If IsNull(cboMatch) Then       ' Deleting (un-matching) is an option.
  6.         txtPropIndex = Null
  7.         DoCmd.Save
  8.         txtPropIndex.Requery
  9.     Else
  10.         txtPropIndex = cboMatch
  11.         txtPropIndex.Requery
  12.     End If
  13.  
  14. End Sub
The form is Single Form view and bound to the new table, tblImportedFromAAPList(2). cboMatch is an unbound combobox and gets its row source from the main table, tblDCCProperties. 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:

Expand|Select|Wrap|Line Numbers
  1. Private Sub Form_Current()
  2.  
  3.     ' If property is already matched when the record changes,
  4.     ' show the matched property name from tblDCCProperties in the combo box.
  5.  
  6.     If Not IsNull(txtPropIndex) Then
  7.         cboMatch = txtPropIndex
  8.     Else
  9.         cboMatch = ""                ' Otherwise, reset it to blank.
  10.         cboMatch.Requery
  11.     End If
  12.  
  13.     ' Regardless, the selectable field should be highlighted.
  14.  
  15.     cboMatch.SetFocus
  16.  
  17. 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...?
Feb 17 '10 #1

✓ answered by TheSmileyCoder

DoCmd.Save will save your form not your record. You would need to use
docmd.RunCommand acCmdSaveRecord
to save the record.
Have you tried such a thing as:
Expand|Select|Wrap|Line Numbers
  1.     If IsNull(cboMatch) Then       ' Deleting (un-matching) is an option. 
  2.        msgBox "Running null match"
  3.         txtPropIndex = Null 
  4.         DoCmd.Save 
  5.         txtPropIndex.Requery 
  6.     Else 
  7.         txtPropIndex = cboMatch 
  8.         txtPropIndex.Requery 
  9.     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.

Share this Question
Share on Google+
7 Replies


TheSmileyCoder
Expert Mod 100+
P: 2,321
DoCmd.Save will save your form not your record. You would need to use
docmd.RunCommand acCmdSaveRecord
to save the record.
Have you tried such a thing as:
Expand|Select|Wrap|Line Numbers
  1.     If IsNull(cboMatch) Then       ' Deleting (un-matching) is an option. 
  2.        msgBox "Running null match"
  3.         txtPropIndex = Null 
  4.         DoCmd.Save 
  5.         txtPropIndex.Requery 
  6.     Else 
  7.         txtPropIndex = cboMatch 
  8.         txtPropIndex.Requery 
  9.     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.
Feb 17 '10 #2

P: 49
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!
Feb 18 '10 #3

TheSmileyCoder
Expert Mod 100+
P: 2,321
Have you tried using cmb_BeforeUpdate 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.
Feb 18 '10 #4

P: 49
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...

Expand|Select|Wrap|Line Numbers
  1. Private Sub cboMatch_Exit(Cancel As Integer)
  2.     If IsNull(cboMatch.Column(1)) Then txtPropIndex = Null
  3. 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!
Feb 18 '10 #5

NeoPa
Expert Mod 15k+
P: 31,494
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.
Feb 19 '10 #6

P: 49
AfterUpdate is the one, NeoPa--cheers! I'm filing away this whole thread for future reference.
Feb 19 '10 #7

NeoPa
Expert Mod 15k+
P: 31,494
Always a pleasure Jeremy.

Welcome to Bytes!
Feb 20 '10 #8

Post your reply

Sign in to post your reply or Sign up for a free account.