Run-time error 2115?

[OldBirdman]

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

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?

[ChipR]

Is the line of code you displayed the one where the error occurs?

[OldBirdman]

Yes!

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.

[ChipR]

Do any fields on your form have default values? Could you give an example of strSQL?

[ChipR]

I guess what I meant to ask was whether lbxSelect was unbound. strSQL shouldn't matter then.

[OldBirdman]

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:

Forms!fAAA.lbxSelect.RowSource = strSQL

with:

Forms!fAAA.lbxSelect.RowSource = _
   "SELECT Key, TitleName, FirstLetter " & _
   "FROM tAAA " & _
   "WHERE (((FirstLetter)='N')) " & _
   "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.

[OldBirdman]

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.

Table (tAAA)
  Key (PK) AutoNumber
Table (tTitles)
  Key (PK) AutoNumber
  ptr->tAAA (FK) Number(Long Integer)
  Title Text(255) Indexed Yes (Duplicates OK) DefaultValue = ""  Required No
 
Form (fAAA) .RecordSource = tAAA
  lbxAlpha Unbound ListBox .RowSource (*;A;C;E)
  lbxSelect Unbound ListBox .RowSource assigned in VBA code
  txtTitleName Unbound TextBox .Value assigned in VBA code
  txtKey Bound/Unbound (I have tried both) Textbox
 

The code has been ALL moved to the form class module. It is:

Option Compare Database
Option Explicit
 
'Select Variables
Dim iixSelectCount As Long
Dim iixSelectedRow As Long
 
'Form Data Variables
Dim iixFormKey As Long
 
 
 
 
Private Sub lbxAlpha_Click()
    Call QuerySelect(lbxAlpha)
End Sub 'lbxAlpha_Click
 
Private Sub lbxSelect_Click()
    Call SelectOneTitle
End Sub 'lbxSelect_Click
 
 
 
Private Sub Form_Load()
'Initialize Form Controls & Properties
    If Key.ControlSource = "" Then
        lblKey.Caption = "Key (UnBound && Blank)"
    Else
        lblKey.Caption = "Key (Bound Showing Key)"
    End If
 
    Me.FilterOn = True
 
'Initialize Global Variables
    'Form Data Variables
    iixFormKey = 0
 
    'Select Variables
    Call QuerySelect("*") 'This will initialize all Select Variables and lbxSelect
 
End Sub 'Form_Load
 
 
 
Public Sub QuerySelect(strLetter As String)
Dim strSELECT As String
Dim strFROM As String
Dim strWHERE As String
Dim strORDERBY As String
Dim strSQL As String
 
    'SELECT Clause
    strSELECT = "SELECT [ptr->tAAA], Title "
 
    'FROM Clause
    strFROM = "FROM tTitles "
 
    'WHERE Clause
    Select Case strLetter
    Case "*"
        strWHERE = ""
    Case Else
        strWHERE = "WHERE Title LIKE """ & strLetter & "*"" "
    End Select
 
    'ORDERBY Clause
    strORDERBY = "ORDER BY Title;"
 
    strSQL = strSELECT & strFROM & strWHERE & strORDERBY
    lbxSelect.RowSource = strSQL
    iixSelectCount = lbxSelect.ListCount
 
    lbxSelect.Selected(0) = True
    Call SelectOneTitle
End Sub 'QuerySelect
 
 
Public Sub SelectOneTitle()
    iixSelectedRow = lbxSelect.ListIndex     'Save selected Row
    iixFormKey = lbxSelect.Column(0)         'Save selected Key
    txtTitleName = iixFormKey & " " & lbxSelect.Column(1)
    Filter = "Key=" & iixFormKey
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

[ChipR]

In my code, trying to use Filter rather than Me.Filter didn't produce an error, it just didn't do anything.

[OldBirdman]

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.

[OldBirdman]

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.

[OldBirdman]

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.

Table = tNames
Key PK Autonum
Name Text All properties default
 
Key   Name
 1    A 222
 2    B 111
 3    C 333
 4    C 111
 5    B 333
 6    B 222
 7    A 333
 8    A 111
 9    C 222
 
Form = fAAA
Default View = Single Form
Everything else is default when form created in Design View
 
lbxSelect  ListBox Created with Wizard
Column Count = 2
Column Widths = 0";1";
Row Source Type = Table/Query
Row Source =
Bound Column = 1
Enabled = Yes
Locked = No
 
lbxAlpha  ListBox Created with Wizard
Column Count = 1
Column Widths = 1";
Row Source Type = Value List
Row Source = "A";"B";"C"
Bound Column = 1
Enabled = Yes
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:

Option Compare Database
Option Explicit
 
Private Sub lbxAlpha_Click()
    Call QuerySelect(lbxAlpha)
End Sub 'lbxAlpha_Click
 
Private Sub Form_Load()
    Me.FilterOn = True
    lbxSelect.RowSource = _
            "SELECT Key, Name FROM tNames ORDER BY Name;"
    lbxSelect.Selected(0) = True    'Select the first row of ListBox
End Sub 'Form_Load
 
Sub QuerySelect(strLetter As String)
    lbxSelect.RowSource = "SELECT Key, Name " & _
            "FROM tNames " & _
            "WHERE Name LIKE """ & strLetter & "*"" " & _
            "ORDER BY Name;"
    lbxSelect.Selected(0) = True    'Select the first row of ListBox
    Filter = "Key=" & lbxSelect.Column(0)
End Sub 'QuerySelect
 
Private Sub lbxSelect_Click()
    Filter = "Key=" & lbxSelect.Column(0)
End Sub 'lbxSelect_Click

Issues:
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:

Control (lbxSelect)  Bound Column = 0
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!

[ChipR]

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.

[OldBirdman]

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?

[ChipR]

I've neglected to actually test it. I'll try it out and get back to you.

[OldBirdman]

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.

[ChipR]

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.

[OldBirdman]

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.

[ChipR]

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.

[OldBirdman]

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