Requery form using option buttons

[bllgnn]

I'm having trouble trying to set a new RecordSource and Requery my continuous popup form called pufrmOrderParts. It's a simple form listing PartNumber, Part Description, etc. from tblParts.

I'm using 3 option buttons and each one has a different query as a RecordSource for the form. The only difference in each query is that the records on the form are sorted in a different order.

All the fields are locked except a bound Yes/No checkbox. The error occures whether or not a record is selected.

When I run it, I get a Runtime error 2115 "The macro or function set to the BeforeUpdate or Validation Rule property for this field is preventing MyApplication from saving the data in the field."

Here is my code:

Public Sub SortOptions()
Select Case Me.fraSortOptions
Case 1 'Part #
Forms!pufrmOrderParts.RecordSource = "qryPartsOrderNumber"
Case 2 'Part Group
Forms!pufrmOrderParts.RecordSource = "qryPartsOrderGroup"
Case 3 'Part Description
Forms!pufrmOrderParts.RecordSource = "qryPartsOrderDescription"
End Select

Forms!pufrmOrderParts.Requery
End Sub

The underlying table does not have any validation rules. Can anyone tell me what is happening here?

 

[Akart]

Is the sort option code placed in a BeforeUpdate event?

If so, this is your problem. You are requerying the data you are trying to update.

If not then you have some other code in a BeforeUpdate event which is interferring. You should change or remove it.

Hope this helps.

Akart

 

[bllgnn]

No, I do not have any code in a BeforeUpdate Event.

 

[databasedemon2002]

First, the option buttons should not be a bound control when all you are using them for is to set the record source of the form. In other words, there is no need for you to store the choice the user makes in a table. To unbind the option buttons, just remove the field that the option group is bound to.

Second, the Before Update event is not the appropriate place to be doing this. Try the After Update event of the option group to change the record source. Also, I am not exactly sure off the top of my head, but if you change the record source of the form dynamically, Access will automatically requery the form for you so there is no need to do so explicitly.

Good Luck!

 

[Akart]

I seem to have re-created your problem.

The form is not bound to anything to start with when you open it.

If you select yes or no from the option box, a new unbound record is created and when you run your code your error appears.

However if you open the form and select one of the option groups and run your code. A recordset is added to the form recordsource and once records are there you can chenge the yesno field no worries.

Perhaps you could apply a recordsource to the form on open that way you wont be changing data on an unbound form.

Hope this helps...

Regards,

Akart

 

[bllgnn]

Sorry, my fault. Somehow, I had put my procedure SortOptions() into the BeforeUpdate event of the option box frame instead of the in the OnClick event. I should have noticed this earelier. It's working fine now.

Thank you both for your help,