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

Run-time error 2115?

P: 675
I have a listbox named lbxSelect which has RowSource = strSQL set in VBA code. This displays correctly on my form (fAAA).

Once displayed, I want to use it to select a record, and display the selected record on the form (View=SingleForm).

Whether I use the Mouse to click in lbxSelect, a command button (cmdFirst, cmdNext, etc), or within VBA, I call the same subroutine to set iixFormKey, a global long variable.

I then call a subroutine to display the form, with the statement
Expand|Select|Wrap|Line Numbers
  1. Forms!fAAA.Filter = "Key=" & iixFormKey
This statement works correctly the first time. When strSQL is assigned to RowSource, I then call from VBA the subroutine to set iixFormKey to the first row, and display the form. It displays correctly.

When I attempt to change lbxSelect.RowSource and select the first row with VBA, it fails with:
2115 The macro or function set to the BeforeUpdate or ValidationRule property for this field is preventing Microsoft Office Access from saving the data in the field.
Removing the VBA code to select row 1, and using the "First" command button does not get an error.

What is going on?
Jul 6 '09 #1
Share this Question
Share on Google+
18 Replies

Expert 100+
P: 1,287
Is the line of code you displayed the one where the error occurs?
Jul 6 '09 #2

P: 675

I've Googled this to death. Can't seem to get any ideas here. Why/what would be being updated if I don't change anything. I've been thru all my tables, and made sure all Required have entries, etc. I don't use Access Validation field in this program.

The program has no messages when Debug->Compile, and I've tried Tools->Database Utilities->Compact and Repair Database. Same results Windows XP & Vista.
Jul 6 '09 #3

Expert 100+
P: 1,287
Do any fields on your form have default values? Could you give an example of strSQL?
Jul 6 '09 #4

Expert 100+
P: 1,287
I guess what I meant to ask was whether lbxSelect was unbound. strSQL shouldn't matter then.
Jul 6 '09 #5

P: 675
Yes, some fields have default values. I don't think that is the problem, because I can't easily answer your second request, and show sample value for strSQL.

ALL "filtering" is done here. The filter form is quite complex, with 12 tabs for 15 different fields. Also are a couple of checkboxes and listboxes unbound on the form itself to further filter what is displayed in lbxSelect. Changing these, and clicking "Create Filter" in the filter form builds a new strSQL. Changing a "Filter" field on the form causes an immediate rebuild and requery of lbxSelect. Nobody wants to debug that, except me.

So I created a very simple string as an illustration. I actually replaced the line of code:
Expand|Select|Wrap|Line Numbers
  1. Forms!fAAA.lbxSelect.RowSource = strSQL
Expand|Select|Wrap|Line Numbers
  1. Forms!fAAA.lbxSelect.RowSource = _
  2.    "SELECT Key, TitleName, FirstLetter " & _
  3.    "FROM tAAA " & _
  4.    "WHERE (((FirstLetter)='N')) " & _
  5.    "ORDER BY TitleName;"
The problems go away. Bingo! So there is something in the query that is wrong. I can start adding complexity to the constant string in my code until it fails, as one approach.
Jul 6 '09 #6

P: 675
In my attempt to simplify and test, I removed too much, and my project seemed to work. It didn't.

I have stripped this project to an absolute minimum, and still cannot figure out what is happening.

Expand|Select|Wrap|Line Numbers
  1. Table (tAAA)
  2.   Key (PK) AutoNumber
  3. Table (tTitles)
  4.   Key (PK) AutoNumber
  5.   ptr->tAAA (FK) Number(Long Integer)
  6.   Title Text(255) Indexed Yes (Duplicates OK) DefaultValue = ""  Required No
  8. Form (fAAA) .RecordSource = tAAA
  9.   lbxAlpha Unbound ListBox .RowSource (*;A;C;E)
  10.   lbxSelect Unbound ListBox .RowSource assigned in VBA code
  11.   txtTitleName Unbound TextBox .Value assigned in VBA code
  12.   txtKey Bound/Unbound (I have tried both) Textbox
The code has been ALL moved to the form class module. It is:
Expand|Select|Wrap|Line Numbers
  1. Option Compare Database
  2. Option Explicit
  4. 'Select Variables
  5. Dim iixSelectCount As Long
  6. Dim iixSelectedRow As Long
  8. 'Form Data Variables
  9. Dim iixFormKey As Long
  14. Private Sub lbxAlpha_Click()
  15.     Call QuerySelect(lbxAlpha)
  16. End Sub 'lbxAlpha_Click
  18. Private Sub lbxSelect_Click()
  19.     Call SelectOneTitle
  20. End Sub 'lbxSelect_Click
  24. Private Sub Form_Load()
  25. 'Initialize Form Controls & Properties
  26.     If Key.ControlSource = "" Then
  27.         lblKey.Caption = "Key (UnBound && Blank)"
  28.     Else
  29.         lblKey.Caption = "Key (Bound Showing Key)"
  30.     End If
  32.     Me.FilterOn = True
  34. 'Initialize Global Variables
  35.     'Form Data Variables
  36.     iixFormKey = 0
  38.     'Select Variables
  39.     Call QuerySelect("*") 'This will initialize all Select Variables and lbxSelect
  41. End Sub 'Form_Load
  45. Public Sub QuerySelect(strLetter As String)
  46. Dim strSELECT As String
  47. Dim strFROM As String
  48. Dim strWHERE As String
  49. Dim strORDERBY As String
  50. Dim strSQL As String
  52.     'SELECT Clause
  53.     strSELECT = "SELECT [ptr->tAAA], Title "
  55.     'FROM Clause
  56.     strFROM = "FROM tTitles "
  58.     'WHERE Clause
  59.     Select Case strLetter
  60.     Case "*"
  61.         strWHERE = ""
  62.     Case Else
  63.         strWHERE = "WHERE Title LIKE """ & strLetter & "*"" "
  64.     End Select
  66.     'ORDERBY Clause
  67.     strORDERBY = "ORDER BY Title;"
  69.     strSQL = strSELECT & strFROM & strWHERE & strORDERBY
  70.     lbxSelect.RowSource = strSQL
  71.     iixSelectCount = lbxSelect.ListCount
  73.     lbxSelect.Selected(0) = True
  74.     Call SelectOneTitle
  75. End Sub 'QuerySelect
  78. Public Sub SelectOneTitle()
  79.     iixSelectedRow = lbxSelect.ListIndex     'Save selected Row
  80.     iixFormKey = lbxSelect.Column(0)         'Save selected Key
  81.     txtTitleName = iixFormKey & " " & lbxSelect.Column(1)
  82.     Filter = "Key=" & iixFormKey
  83. End Sub 'SelectOneTitle
The error occurs on Line 82.
Run-time error '2115':
The macro or function set to the BeforeUpdate or ValidationRule property for this field is preventing Microsoft Office Access from saving the data in the field.
Line 81 works, and the data is valid for lbxSelect. lbxSelect is Unbound, and is already requeried by the time the error occurs. It displays correctly.

Summary of what this is supposed to do:
1) User selects a letter (or *=ALL) in lbxAlpha - This test only has *;A;C;E
2) lbxSelect displays all Titles/Names for that letter
3) The first displayed Title is highlighted, and the data displayed on the form
4) User can select another Title
4a) Form displays data for new choice
Jul 7 '09 #7

Expert 100+
P: 1,287
In my code, trying to use Filter rather than Me.Filter didn't produce an error, it just didn't do anything.
Jul 8 '09 #8

P: 675
Thanks ChipR for taking the time to actually try the code. Whether Me.Filter=, Forms!fAAA.Filter=, or just Filter=strSQL, I get the same error.

When you ran this with the Me.Filter, what happened?

In my actual project, the 2 subroutines will have to be in an external module. SelectOneTitle will be called from the OnClick of navagation buttons, some on a subForm. QuerySelect will also be moved to the external module for similar reasons. This will actually require the Forms!fAAA.Filter method. So I need to make that work.
Jul 8 '09 #9

P: 675
Removing Line 82 of the code, to avoid the error, produces a different kind of error. lbxSelect becomes "Locked" after the requery, It can be requeried, but a row cannot be selected with the mouse.
Jul 8 '09 #10

P: 675
Access 2003 Windows Vista
I've stripped my program to do less than I need it to do, and I still cannot get it to run correctly. Again, I started from scratch. Did complete Virus Scan, nothing found. Rebooted, started Access, new project.
Entered EVERYTHING from scratch, including typing all VBA code from keyboard, no cut/paste from any other source.

Expand|Select|Wrap|Line Numbers
  1. Table = tNames
  2. Key PK Autonum
  3. Name Text All properties default
  5. Key   Name
  6.  1    A 222
  7.  2    B 111
  8.  3    C 333
  9.  4    C 111
  10.  5    B 333
  11.  6    B 222
  12.  7    A 333
  13.  8    A 111
  14.  9    C 222
  16. Form = fAAA
  17. Default View = Single Form
  18. Everything else is default when form created in Design View
  20. lbxSelect  ListBox Created with Wizard
  21. Column Count = 2
  22. Column Widths = 0";1";
  23. Row Source Type = Table/Query
  24. Row Source =
  25. Bound Column = 1
  26. Enabled = Yes
  27. Locked = No
  29. lbxAlpha  ListBox Created with Wizard
  30. Column Count = 1
  31. Column Widths = 1";
  32. Row Source Type = Value List
  33. Row Source = "A";"B";"C"
  34. Bound Column = 1
  35. Enabled = Yes
  36. Locked = No
This form has 2 controls. Neither is bound to a field in the table tNames. The form inself is bound to that table. No other controls except Navigation Controls inserted at bottom by Access Wizard.

I only have 8 lines of actual code, plus the overhead of subroutines ...Sub....EndSub. That code is:
Expand|Select|Wrap|Line Numbers
  1. Option Compare Database
  2. Option Explicit
  4. Private Sub lbxAlpha_Click()
  5.     Call QuerySelect(lbxAlpha)
  6. End Sub 'lbxAlpha_Click
  8. Private Sub Form_Load()
  9.     Me.FilterOn = True
  10.     lbxSelect.RowSource = _
  11.             "SELECT Key, Name FROM tNames ORDER BY Name;"
  12.     lbxSelect.Selected(0) = True    'Select the first row of ListBox
  13. End Sub 'Form_Load
  15. Sub QuerySelect(strLetter As String)
  16.     lbxSelect.RowSource = "SELECT Key, Name " & _
  17.             "FROM tNames " & _
  18.             "WHERE Name LIKE """ & strLetter & "*"" " & _
  19.             "ORDER BY Name;"
  20.     lbxSelect.Selected(0) = True    'Select the first row of ListBox
  21.     Filter = "Key=" & lbxSelect.Column(0)
  22. End Sub 'QuerySelect
  24. Private Sub lbxSelect_Click()
  25.     Filter = "Key=" & lbxSelect.Column(0)
  26. End Sub 'lbxSelect_Click

1) Line 12 triggers OnClick and code goes to Line 25
2) Line 20 does not trigger OnClick and code continues sequentially
3) Click lbxAlpha "B"
Click lbxSelect "B 222"
Click lbxAlpha "C"
lbxSelect now locked

If I make the following changes:
Expand|Select|Wrap|Line Numbers
  1. Control (lbxSelect)  Bound Column = 0
  2. Line 12 and Line 20    lbxSelect = 0
then issues 1 & 3 go away. Program now works.

I need to know what is going on here!
Jul 9 '09 #11

Expert 100+
P: 1,287
lbxSelect should not be bound to column 1 because that would be the Name field. When you select C in step 3 and filter the form, lbxSelect is still bound to B. That may be why it gets locked. Setting the bound column to 0 should resolve that issue.
I would not expect line 20 to trigger a call to lbxSelect_Click(), but I haven't tested.
Jul 10 '09 #12

P: 675
lbxSelect should not be bound to column 1 because that would be the Name field.
In the properties form, Column 1 is the first, or leftmost, column. It is not zero-based. Therefore, the bound column is Key.

When you select C in step 3 and filter the form, lbxSelect is still bound to B.
I select "C" in a listbox which changes the selected row, and therefore the value, from "B" to "C". I then requery lbxSelect. These two listboxes are not linked to each other. An event in lbxAlpha requeries lbxSelect. lbxSelect now correctly displays those Names starting with "C". There is no reason this control should now be locked.

Can you elaborate some more?
Jul 10 '09 #13

Expert 100+
P: 1,287
I've neglected to actually test it. I'll try it out and get back to you.
Jul 10 '09 #14

P: 675
I'm posting the version of this program as last described (Post #11).

This should allow you to test easily.

Thank you for doing this for me, as I am ABSOLUTELY stumped by this.
Attached Files
File Type: zip (11.6 KB, 99 views)
Jul 10 '09 #15

Expert 100+
P: 1,287
I actually recreated it from scratch, and typed in my own code. The only difference I see is that I didn't use the keyword Name for a field name, and I had to use Me.FilterOn = True after each Me.Filter or it didn't set the filter. I couldn't recreate the locking of the list box. I'm in Access 2007 on XP though.
Jul 10 '09 #16

P: 675
I'm in XP, but Access 2003.

I have no controls on the form that actually show whether the correct record would be displayed or not. All I know is that the Access Navagation Control shows I am on Record 1 of 1 (Filtered), so I presume it is filtering to a single record.

This code is the 'essence' of what I want to do. I I can't debug these 8 lines of code, I can't move forward. That is why I want to know what is going on. Adding more controls or code gets me the Run-time error '2115'.

I posted the code and sample table in my previous post.
Jul 10 '09 #17

Expert 100+
P: 1,287
Took a look at your .zip and I didn't get any errors or locking. The form in the Sratch.mdb wasn't bound to the recordset, but once I set it, I was able to put 2 text boxes on the form and observe that the recordset wasn't filtering. I don't have 2003 anymore, but I think plenty of people here do. Maybe someone else can test your example.
Jul 10 '09 #18

P: 675
Interesting, because when I put a bound control on the form, it filters just fine, the first time. After that, I get error '2115'. Adding FilterOn = True before or after the Filter = statement has no effect. It is already on before the Filter statement, and too late afterwards.

Thanks for all your time and effort
Jul 10 '09 #19

Post your reply

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