Solved Conflict in code for two controls

[Jake2]

I have a continuous form that I can filter using a combo box and a textbox that reveals the current record. Individually, the code for the individual controls work fine. But because the two cause a conflict that I am trying to eliminate. Any help will be appreciated.

Cheers,
Jake

Note: I know that cross-posting is negative, but I had no results from the only other forum in which I posted — apologies for any offense.

---

Here is the code and the error message:


Private Sub Form_Current()
txtRecdCt2.SetFocus txtRecdCt2.Text = Form.CurrentRecord
End Sub

Private Sub cboLUTCategory_AfterUpdate()
Me.Filter = "Category = '" & Me.cboLUTCategory & "'" Me.FilterOn = True
End Sub


Error Message:

Run-time error '2115':

The macro or function set to the BeforeUpdate or ValidationRule
property for this field is preventing HOUSEHOLD INVENTORY from
saving the data in the field.

 

[arnelgp]

they should be on each Line:

Private Sub Form_Current()
txtRecdCt2.SetFocus
txtRecdCt2.Text = Form.CurrentRecord
End Sub

Private Sub cboLUTCategory_AfterUpdate()
Me.Filter = "Category = '" & Me.cboLUTCategory & "'"
Me.FilterOn = True
End Sub

 

[Jake2]

they should be on each Line:

Private Sub Form_Current()
txtRecdCt2.SetFocus
txtRecdCt2.Text = Form.CurrentRecord
End Sub

Private Sub cboLUTCategory_AfterUpdate()
Me.Filter = "Category = '" & Me.cboLUTCategory & "'"
Me.FilterOn = True
End Sub

Apologies, as you are correct, I also have it correct in the actual event. The mistake occurred when I copied/pasted. The past put all the code in one paragraph and I had to put it on the proper lines. I apparently missed that line. But the code in my app is as you detail.

Thanks, however, for the reply.

 

[Mike Krailo]

Error Message:

Run-time error '2115':

The macro or function set to the BeforeUpdate or ValidationRule
property for this field is preventing HOUSEHOLD INVENTORY from
saving the data in the field.

So what is the validation or code in your BeforeUpdate event? That's error message is pretty clear. Show us all the code in your before update event and verify if there is any table level validation rules in effect.

The second sub is just for filtering and does not modify any records. The first sub targets the text in the control only. I assume it is an unbound control so that won't modify or try to save any records. There must be something else going on other than the code you are showing.

 

[Jake2]

There is no "BeforeUpdate" code on any of my controls. One of the reasons I am confused (realize that I'm just a hair past novice and that line is blurred) is that when I hit debug, it highlights the form OnCurrent. Am I right in guessing that is why you suspect some BeforeUpdate code? The text control is, in fact, unbound.

The error does not trigger until after I make a selection from the combobox. My initial thought is that perhaps I am out of my element and reaching too far since the OnCurrent takes the focus away from the combobox focus - or vice versa. I hope that's not the case.

 

[Pat Hartman]

Private Sub Form_Current()
txtRecdCt2.SetFocus
txtRecdCt2.Text = Form.CurrentRecord
End Sub

Controls on a form have three buffers:
Me.txtRecdCt2.OldValue = the value from the table. If this is a new record, the .OldValue property will always be null. If this is an existing record, the value will be whatever is in the record as it exists in the table.
Me.txtRecdCt2.Text = the typing buffer. This property is available ONLY when the control has the focus - hence the need to use .SetFocus to reference this property UNLESS you are referencing it from an event that runs while the control has the focus such as the on change event.
Me.txtRecCT2.Value or Me.txtRecCt2 = the current value of the control. This is either the saved value if the control has not been altered or it is the changed value which has not yet been saved.

Note that the .Value property is the default property so developers rarely type it. Me.txtRecCt2 is the prefered way to reference the value in a control. The .Text property is irrelevant unless you are in the on change event and trying to validate keystroke by keystroke. In the old VB, the .Text property is the default property and this confuses people since this is different from VBA.

The form's Current event runs each time you navigate to a new record. Your code makes no sense. Please tell us in words what you think this code will show you.

Keep in mind that if the txtRecdCt2 control is bound, YOU are dirtying the record which is really poor practice. If the control is unbound, this would show the ordinal record number in the bound recordset but it would not identify a specific record since it would change if you resorted the recordset. If you use this in an unbound control on a continuous or ds view form, all rows would show the value of the current record whatever that is.

 

[pbaldy]

Is txtRecdCt2 bound (does it have a field in its control source)? If so, that could cause this error. The current code would be dirtying the record, then the combo code trying to go to a different record.

By the way, you don't need to set focus to the textbox to set its value. Just don't use the .Text property, which requires it:

txtRecdCt2= Form.CurrentRecord

 

[Mike Krailo]

The text control is, in fact, unbound.

If this is true, there is more involved than what you are showing us. I'm also not sure what the purpose or intention is with the code in the on current event. You mentioned it reveals the current record. How are you actually using this? There are reasons why I might want to store the current record id of a sub form in an unbound control on the main form. Not sure why you would do this otherwise.

Is your continuous form actually a subform on a main form?

 

[Pat Hartman]

Form.CurrentRecord does not return the PK of a record, it returns its sequence in the recordsource. This is a changeable value and should never be stored. Depending how the recordset is sorted, PK 211399 could be the first record in the record set or the 201st. It all depends on what else is selected and what the sort order is.

You might want to read the last paragraph of my previous post.

 

[Jake2]

OK, folks, I will explain this the best I can. To begin, the code for this operation was copied from a couple of the many Access support websites (not a forum), online. The code works, except for the error message. This is proven by the fact that after I close the error message, the filter from the combo box performs the filtering, and the txtRecdCt2 displays the current record.

txtRecdCt2 is an unbound control as well as another textbox named txtCounter. txtCounter displays the number of records displayed after the filter is applied. These two text boxes replace the onboard record information displayed at the bottom of the continuous form (not a subform). The control txtCounter Control Source = Count([DOCNO]). DOCNO is a field that is a primary key. It is the default ID when the primary key is created when creating the table. So txtRecdCt2 works alongside txtCounter to show the current record and total records displayed, such as [txtRecdCt2] of [txtCounter] or Record # of x Records.

If I were to remove the .SetFocus, txtRecdCt2 does not display.

When I am referring to the fact that txtRecdCt, txtCounter and cboLUTCategory work after closing the error message, this was determined after running the form by actually running the form in Form View and actually selecting an item from cboCategory. These tests were attempted several times with the same results. Closing the error message does not crash the form.

I hope I have answered all the questions. However, I am not familiar with the term "PK" since my experience is lacking. Thanks for all the effort thus far and hopefully this explanation will help in revealing the source/resolution of the problem.

Cheers.

 

[arnelgp]

simplify your form current to this and it will work:

Private Sub Form_Current()
[txtRecdCt2] = Form.Recordset.RecordCount
End Sub

or use the code in post#7

Private Sub Form_Current()
txtRecdCt2= Form.CurrentRecord
End Sub

 

[CJ_London]

I would have thought you would want

Private Sub Form_Current()

txtRecdCt2 = Form.CurrentRecord

End Sub

perhaps @Jake2 can provide a link to where the code came from, perhaps it is doing something different from that required

 

[Jake2]

txtRecdCt2= Form.CurrentRecord

Yup! This worked fine!! Thank You so very much.
And thanks to all who invested their time to help a VBA dummy! ; -)

 

[Pat Hartman]

the code for this operation was copied from a couple of the many Access support websites (not a forum), online.

Given the misuse of the .text property, I would avoid this website like the plague.

If I were to remove the .SetFocus, txtRecdCt2 does not display.

Did you read my lengthy explanation of the three control buffers and when/why to use each????

I am not familiar with the term "PK"

PK = Primary Key another you will see is FK = Foreign Key. The Foreign Key is the other side of a relationship defined in the Relationship window.

 

[Jake2]

Given the misuse of the .text property, I would avoid this website like the plague.

Did you read my lengthy explanation of the three control buffers and when/why to use each????

PK = Primary Key another you will see is FK = Foreign Key. The Foreign Key is the other side of a relationship defined in the Relationship window.

I did read that explanation of the buffers and it kind of makes sense, but after a bit more experience, I think it will all come together. Even building an expression has me a bit dubious when it comes to text vs value. Part of my difficulty is that I am pretty much self-taught - except when I run into concepts that are new. I experiment with some new ideas, fail and they lead me to others. But I do appreciate the patience from all, given these deficiencies.

I know about Primary/Foreign Key but I just didn't put the abbreviation together.
And I will also need to learn who is reliable and who is not. That wasn't even a forum, it was some kind of teaching site. Arrghhh...

 

[Pat Hartman]

I've been working with Access for close to 30 years and I don't think I've had to use the .text property twice in all that time. It is relevant when you want to validate an entry one keystroke at a time as the user is typing. Pretty rare situation. Usually used when the input format is too complex and variable for an input mask to be useful.