How to correct runtime error 2046?

[drd466]

Question: When I add code to change the record source my sub routine [ATTACHMENTS_DblClick(Cancel As Integer)]
returns a runtime error 2046 Edit hyperlink isn't available now.

The following is provides a little details

I have a form with a subform with the following Sub routine
Which works well, however. I have a like database and I want to use the same forms but just change the record source through code which also works.

Public Sub ATTACHMENTS_DblClick(Cancel As Integer)
  On Error GoTo error_fileclick 
  If IsNull(Me.ATTACHMENTS.Value) Then
    DoCmd.RunCommand acCmdEditHyperlink
  Else
    DoCmd.RunCommand acCmdOpenHyperlink
  End If
Exit_FileLink:
  Exit Sub
error_fileclick:
  MsgBox "No file has been linked."
  Resume Exit_FileLink
Exit Sub

[Stewart Ross]

It's hard to guess from what you've posted, but the most likely explanation I can think of is that the recordsource you have set using VBA code is not updateable - that is, you cannot add or modify records using that recordsource. This would prevent you from being able to run the edit hyperlink command in line 4 above.

It could also be the form's own settings, but I'd assume you would have checked the allow edits and allow additions properties yourself anyway.

You should be able to determine whether or not this is a recordsource issue fairly easily, by opening the recordsource outside of the form itself and attempting to change any field. If you cannot do so the recordsource is not updateable. If you can, then it may be a form property setting.

There is not really enough in what you've posted so far to give any firmer advice until you've checked out the status of your recordsource and form properties.

-Stewart

[drd466]

Stewart Thanks for the reply.

I'll try brief and clear as possible.
In Access 2007 I have form1 and a Subform2 (which is on a tab control). Form1 and subform2 record source type are dynaset. Form1 default view is single form, while subform2 is datasheet.

All the forms record source are ODBC to Oracle. I have checked the allow additions and edits and they are correct and the recordsource is updateable.

The problem begins when I try to combine the two separate databases into one. All I've done was ODBC to Oracle the like tables, add an unbound text box to the forms which gets its value based from an unbound column on the main menu thats always open.

The forms recordsource is changed by the following code

Private Sub Form_Current()
'FAPC
 If Me.BusinessUnit.Value = "FAPC" Then
    Me.RecordSource = "FAPC_WO_CODE_DATA"
 End If
 
'FTOL
 If Me.BusinessUnit.Value = "FTOL" Then
    Me.RecordSource = "FTOL_WO_CODE_DATA"
 End If
End Sub

As mentioned subform2 is on a tab control. On another tab of the same control is subform3 which also runs off the Public subroutine above on the original post and works.

I've noticed on the first line of subform2 (datasheet view) the code works. When I double click on subsequent lines in the datasheet view the runtime error 2046 appears.

I hope this provides more clarity.

[Stewart Ross]

The source of the problem is not at all obvious. One or two questions arise from what you've posted, though; the recordsource change is in the form's Current event, which is fired whenever the form moves from record to record. You will be changing the recordsource of the main form every time you move from one record to the next; is this intentional?

If you want to change recordsource according to the value of, say, an unbound combo on a form it is more usual to do so using the AfterUpdate event of the combo concerned to trigger the change, not on the Current event of the form. If the control you are testing, BusinessUnit, is bound to the recordsource itself then I think you could end up in difficult territory of recordsource changes occurring when you don't expect them to, which in turn affects any subforms related to the main recordsource.

Speaking of which, do the same field parent/child relationships apply from the main to the subform when you've change the recordsource?

Another possibility raised by what you've posted is the ODBC refresh interval set for the database, which you will find under Access Options, Advanced. This is set to 1500ms by default; you may wish to reduce this value to see if this may have some contribution to what is going on.

More questions than answers I guess.

One way to approach the systematic elimination of possibilities in the meantime is to verify that the updates work or do not work for both recordsources set WITHOUT any dynamic changes to the form's recordsource. In other words simply comment out the recordsource change in Form Current, and try out the form with both recordset choices set by yourself manually to rule out problems with each in turn.

-Stewart

[drd466]

Stewart,
Thanks for staying with me. I was away for a few days and the reason for a slow reply.

And you're right I believe the after update event is better. I push the record source to my FormA prior to its opening through an on click event from the main menu, which actually is a submenu of the main menu.

However, FormA has several subforms (on a tab control) in which the recordsource is set on each of the subforms current event. And each subform has the unbound BusinessUnit control which is set on the same on click event that opens FormA.

Is it be better to set the record source for each subform on the same on click event that opens FormA just as I do with BuisnessUnit?

My thinking, this will eliminate the on current event when moving from record to record.

I tried the after update event of the unbound BuisnessUnit control (hidden), but since it is set when FormA is opened there is no after update event that triggers.

The BusinessUnit is an unbound control.

Each tab, on the tab control is linked to a different table and therefore the parent/child relationships changes but the parent (master) is the same for each tab. The child is the only change.

I haven't had a chance to verify the refresh interval of the ODBC but 1500ms sounds correct.

As suggested, I commented out the on current event of the subform and manually set the recordsource and the Public Sub ATTACHMENTS_DblClick event code works fine.

If you think it would help I could post the record source used when the form is opened.

Thanks
David