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.

 

[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?

 

[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! ; -)

 

[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...