Trouble with Set Forms!frmVerGeneral.recordSet = rsttt

[Bresart]

Hi, in a form with editable records i have an image for changing to another similar form but with different features like uneditable records. For that i have the code:

Dim rsttt as Variant
Set rsttt = Me.recordSet
DoCmd.OpenForm "frmUneditable"
Set Forms!frmUneditable.recordSet = rsttt
DoCmd.Close acForm, "frmEditable"

This code opens the form frmUneditable and nothing appears in the browsing box and the browsing buttons Previous and Next are dissabled. For avoiding that, i have added to the end of the previous code:


Forms!frmUneditable.SetFocus
DoCmd.GoToRecord , , acGoTo, 1
DoCmd.Close acForm, "frmEditable"

But this makes that the focus goes 1. to the form frmUneditable, 2. to the form frmEditable, and 3. to the form frmUneditable.

I have tried this

Set rsttt = Me.RecordsetClone

instead of this

Set rsttt = Me.recordSet

but this causes another problem in the form frmUneditable: in its Form_Current event the code

Me.Id

gives the error "Can't go to the specified field", when Me.Id is a field without textbox, it is between the available fields but it isn't shown.

I would want to avoid the ugly method of adding a hidden textbox for that field.

Is there possible changes in the code that allow these purposes (transferring the recordset between forms and having enabled the browsing bar)?

Thanks in advance for any help given.

 

[TheAceMan1]

How are ya Bresart . . .

Are you sure its not the same [blue]RecordSource[/blue] your after?

[blue]   DoCmd.OpenForm "frmUneditable"
   DoEvents
   Forms!frmUneditable.RecordSource = Me.RecordSource[/blue]

See Ya! . . . . . .

Be sure to see thread181-473997
Also faq181-2886

 

[Bresart]

Thanks TheAceMan1.

Both forms have the same table as Recordsource, i think that's what you mean.

I think the only thing that the code you posted makes is setting the same recordsource in both forms, thinking that DoEvents does nothing different.

I haven't said that the trouble happens when inserting a new record and filling in the form for this new record hasn't been completed; in this case, there's a first line in the code above:

Me.Undo
Dim rsttt as Variant
Set rsttt = Me.recordSet
DoCmd.OpenForm "frmUneditable"
Set Forms!frmUneditable.recordSet = rsttt
DoCmd.Close acForm, "frmEditable"


I have also tried, instead of

Forms!frmUneditable.SetFocus
DoCmd.GoToRecord , , acGoTo, 1

this code:

Dim rst as Variant
Set rst = Me.RecordsetClone
rst.MoveFirst
Forms!frmUneditable.Bookmark = rst.Bookmark

and also:

Forms!frmUneditable.Requery


with the same result.

 

[MajP]

The way I would do this is not a seperate form, but open a second form instance of the original form and then set the properties.

Here is a demo using the "Orders" form from Northwind. I then open another form instance and set the properties.

Public frmOrders2 As Form_Orders

Public Sub openFormInstance()
  Set frmOrders2 = New Form_Orders
  With frmOrders2
    ' some properties
    .Caption = "Uneditable Instance of the Orders form"
    .AllowAdditions = False
    .AllowEdits = False
    .AllowAdditions = False
    ' must set it visible
    .Visible = True
  End With
 
End Sub

Your thoughts.

 

[MajP]

If you do not want to use a form instance this should be still doable.

You should be able to set the recordsource, pass the recordset, or pass the recordsetclone without any problem. You need to probably post more of your code for the other problems.

I am not sure why you are doing this so I assume you can enter frmUneditable from different forms and each time you want to base frmUneditable's recordset on the calling form's recordset.

I very simple way is to define a public variable.

public rsUneditable as dao.recordset

In frmUneditable

Private Sub Form_Load()
Set Me.Recordset = rsUneditable
End Sub

in the calling form
set rsUneditable = me.recordsetclone
docmd.openform "frmUneditable

in frmUneditable on close
set rsUneditable = nothing

 

[Bresart]

Thanks MajP.

I can't use a form instance because there are elements like images different in both forms.

The other problems you refer to is, in the Form_Current event of the form frmUneditable i have:

If IsNull(Me.Id) = False Then
idSpecie = Me.Id
End If

and if in the form frmEditable i use

Set rsttt = Me.RecordsetClone

instead of

Set rsttt = Me.recordSet

the first line gives the error:

The DB can't find the field 'Id' which its expression refer to.

The purpose of doing this, like you say, is to open the form frmUneditable from the form frmEditable transfering the recordset from the form frmEditable to the form frmUneditable, keeping any applied filter.

The public variable has the problem that sometimes the form frmUneditable isn't opened from the form frmEditable, for example when opening the DB.



In lack in a better solution, i have avoided the problem by browsing to an existing record before opening the form frmUneditable:

Dim rsttt as Variant
Set rsttt = Me.recordSet
DoCmd.GoToRecord , , acFirst
DoCmd.OpenForm "frmUneditable"
Set Forms!frmUneditable.recordSet = rsttt
DoCmd.Close acForm, "frmEditable

 

[MajP]

The public variable has the problem that sometimes the form frmUneditable isn't opened from the form frmEditable, for example when opening the DB

Not really:

Private Sub Form_Load()
If Not rsNonEdit Is Nothing Then
Set Me.Recordset = rsNonEdit
End If
End Sub


Here is my test. I have two forms from the Northwind Db. I copied the Orders form and modified it to make Orders2.

Public Sub openOrders2()
DoCmd.OpenForm "Orders"
Set rsNonEdit = frm1.Recordset
DoCmd.OpenForm "Orders2"
Set rsNonEdit = Nothing
End Sub

But I can also open the Orders2 form without the Orders form open.

In regards to the difference between using the recordset or the recordsetclone, I can not understand how that will cause the error. But a likely solution is to replace
Me.ID with Me!ID
Or delete the current ID control on the form and replace it with a new control.
The only thing I think could be happening is somehow the current event is firing before the recordset is fully set on the frmUneditable

 

[MajP]

Let me explain this one
"replace Me.ID with Me!ID"

This should not matter except some weird cases. Sometimes when using dot notation you add a control to a form with a different name and then rename to the field name. For some reason (sometimes) you lose the field as a property of a form. Although I have seen this with dot I have never seen it with bang.

I doubt that is your problem, my guess is somehow the current event is triggered before your recordset is set. You could test that by putting something like this in the on current revent:
msgbox me.recordset.name

 

[Bresart]

Thanks MajP.

By now i will leave the form as it is. About the global variable, i think that as the problem happens when i am adding a new and not completed record, it will keep happening.

And in the other hand, roughly like you say i have had rare behaviours with catching values of fields without linked controls in the form, so i'll keep the code like it is though it isn't as smart as it could be.

 

[MajP]

Your original code should work (with some tweaking)

Dim rsttt as Variant
Set rsttt = Me.recordSet

That should throw an error. You are trying to set a variant equal to a dao.recordset. I am not sure if this has anything to do with your problem, because I am suprised it does not error.

This should read
dim rsttt as dao.recordset
set rsttt = me.recordset

but get rid of rsttt it is a waste of code
It should read simply

DoCmd.OpenForm "frmUneditable"
Set Forms!frmUneditable.recordSet = me.recordset
DoCmd.Close acForm, "frmEditable"