Run-time error 2115?

OldBirdman · #1 July 6th, 2009, 06:04 PM

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

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:

Quote:

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 · #2 July 6th, 2009, 07:06 PM

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

OldBirdman · #3 July 6th, 2009, 07:24 PM

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 · #4 July 6th, 2009, 07:34 PM

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

ChipR · #5 July 6th, 2009, 07:48 PM

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

OldBirdman · #6 July 6th, 2009, 08:08 PM

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

Forms!fAAA.lbxSelect.RowSource = strSQL

with:

Expand|Select|Wrap|Line Numbers

 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 · #7 July 7th, 2009, 09:32 PM

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

 
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:

Expand|Select|Wrap|Line Numbers

 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.

Quote:

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 · #8 July 8th, 2009, 12:48 PM

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

OldBirdman · #9 July 8th, 2009, 02:15 PM

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 · #10 July 8th, 2009, 02:29 PM

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 · #11 July 10th, 2009, 12:55 AM

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

 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:

Expand|Select|Wrap|Line Numbers

 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:

Expand|Select|Wrap|Line Numbers

 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 · #12 July 10th, 2009, 02:44 PM

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 · #13 July 10th, 2009, 04:15 PM

Quote:

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.

Quote:

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 · #14 July 10th, 2009, 04:59 PM

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

OldBirdman · #15 July 10th, 2009, 05:24 PM

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 · #16 July 10th, 2009, 05:29 PM

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 · #17 July 10th, 2009, 05:56 PM

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 · #18 July 10th, 2009, 06:46 PM

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 · #19 July 10th, 2009, 08:48 PM

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

Run-time error 2115?

re: Run-time error 2115?

re: Run-time error 2115?

re: Run-time error 2115?

re: Run-time error 2115?

re: Run-time error 2115?

re: Run-time error 2115?

re: Run-time error 2115?

re: Run-time error 2115?

re: Run-time error 2115?

re: Run-time error 2115?

re: Run-time error 2115?

re: Run-time error 2115?

re: Run-time error 2115?

re: Run-time error 2115?

re: Run-time error 2115?

re: Run-time error 2115?

re: Run-time error 2115?

re: Run-time error 2115?

/bytes/development

/bytes/it

/bytes/about