Form Instance problems

[newfrontiers]

Hello, I only post items here after spinning wheels for more than 3 days. And boy am I spinning them this time.

I have a form that when a specific textbox (txt0) is doubled clicked it opens an instance of another form and sets the recordsource using a number from textbox (txt1).

The problem I have is that I cannot get the number from txt1 into the form instance textbox called (txtINo).

I am trying to reference the form instance txtINo as Me.txtIno. Is this the problem?

Thanks.

 

[jmgaddis]

Maybe instead of using the me.txtino

try directly referencing the orginal form name directly

Forms!OriginalFormName!txtino

Hope this helps.

Thanks,
John

 

[MajP]

I am trying to reference the form instance txtINo as Me.txtIno. Is this the problem?

Yes, of course.

You do not show your code, but you should have code something like

public frm2 as Form_YourFormName

private sub someControl_someEvent()
'some code
set frm2 as new Form_YourFormName
frm2.visible = true
frm2.txtNo = me.txt1
'some code
end sub

 

[MajP]

Here is an example I posted the other day to show someone how to open an instance with different properties

Public frmOrders2 As Form_Orders


Public Sub openFormInstance()
  Set frmOrders2 = New Form_Orders
  With frmOrders2
    ' some properties
    .Caption = "Uneditable Orders"
    .AllowAdditions = False
    .AllowEdits = False
    .AllowAdditions = False
    ' must set it visible
    .txt1 = Me.txt1
    .cmdNewInstance.visible = false
    .Visible = True
 End With
  
End Sub
Private Sub cmdNewInstance_Click()
  openFormInstance
End Sub

 

[MajP]

One more thing. John's suggestion will not work. You can not reference a form instance using the form's collection. You can only do as I showed using the instantiated object. All form instances share the same name and only the original instance is a member of the forms collection the Forms collection. You can prove this by opening multiple instances of a form and set some property of the form using the forms collection. Only the original will change.

 

[newfrontiers]

MajP and John,

Thank you for your help. MajP I think my code is written similar to your code, but I'm getting an error. Thanks for letting me know where I'm going wrong.

Here is the code:

Public frmNewform As Form

Private Sub txtBusName_DblClick(Cancel As Integer)
Dim strSQL As String
Dim rst1 As New ADODB.Recordset

Set frmNewform = New Form_frmProspecting

intInstanceNum = intInstanceNum + 1

strSQL = "Select * from CHAPMAN_NATIONWIDE WHERE [No]
= " & Me.txtNo & ";"

Debug.Print "frmProspectingResults | The recordsource
for the form instance is: " & strSQL

frmNewform.RecordSource = strSQL

EntityNo = Me.txtNo

Debug.Print "frmProspectingResults | Passing No: " & Me.txtNo & " to frmProspecting instance"

frmNewform.SetFocus

frmNewform.txtNo.Value = Me.txtNo

Debug.Print "txtNo of frmProspecting instance = " & frmNewform.txtNo.Value

frmNewform.Caption = Me.txtBusName & ": " & intInstanceNum
clnClient.Add Item:=frmNewform, Key:=CStr(frmNewform.hWnd)

End Sub
=====
Here is the code that gets fired when the form instance is opened:

Private Sub Form_Open(Cancel As Integer)
Dim strSQL As String
Dim rst1 As New ADODB.Recordset

Me.Requery
Debug.Print "frmProspecting " & Me.txtNo & ", " & Me.txtBusName & ", " & Me.RecordSource


EntityNo = Me.txtNo (I GET AN ERROR HERE BECAUSE Me.txtNo IS NULL; ENTITYNO IS A PUBLIC VARIABLE)
Debug.Print "frmProspecting instance initial EntityNo = " & EntityNo

strSQL = "SELECT CHAPMAN_NATIONWIDE.[No] FROM (CHAPMAN_NATIONWIDE " & _
" INNER JOIN tblComments ON CHAPMAN_NATIONWIDE.[No] = tblComments.[No]) INNER JOIN " & _
" tblDealProgress ON tblComments.intSeq = tblDealProgress.intSeq WHERE (((CHAPMAN_NATIONWIDE.[No])= " & Me.txtNo & "));"
Debug.Print "frmProspecting | check for deal: " & strSQL

rst1.ActiveConnection = CurrentProject.Connection
rst1.open strSQL, , adOpenKeyset, adLockOptimistic

If rst1.EOF Then
Me.cboLevel.Enabled = False
Me.cboInterested.Enabled = False

Else
Me.cboLevel.Enabled = True
Me.cboInterested.Enabled = True
End If

rst1.Close

End Sub

John

 

[MajP]

Here is my guess.
Order of operations
a. Form is called to open the instance
b. Then the instance opens and runs the on open event
c. Then the code tells it to set the value of the text box

therefore you are referencing the control value before it is set.

Why do you even have this code?
"EntityNo = Me.txtNo" in the on open

Because As far as I can tell it already equals that value.
Then you try to reset it again to a textbox value that has not been set yet.

'You set the variable here
EntityNo = Me.txtNo
Debug.Print "frmProspectingResults | Passing No: " & Me.txtNo & " to frmProspecting instance"
frmNewform.SetFocus
'then you set frmNewform.txtNo to me.txtNo
frmNewform.txtNo.Value = Me.txtNo

All you need is to get rid of this
EntityNo = Me.txtNo

And then I would simply make a function

public function getEntityNo()
getEntityNo = EntityNo
end function

now use that function in your query in

((CHAPMAN_NATIONWIDE.[No])= " & getEntityNo & "));"

 

[MajP]

Better explanation of the problem

Set frmNewform = New Form_frmProspecting
'Your instance is opened above and therefore
'The on open event fires trying to set
'the public variable equal to txtNo. However
'its value is set later.
intInstanceNum = intInstanceNum + 1
strSQL = "Select * from CHAPMAN_NATIONWIDE WHERE [No]= " & Me.txtNo & ";"
frmNewform.RecordSource = strSQL
EntityNo = Me.txtNo
'Your instance is already open
'and the on open event fired before the below code happens
frmNewform.txtNo.Value = Me.txtNo

 

[YoungManRiver]

All,

Much easier to call the value of the first form Me![txt1] in the second form and process there. However you must following the VBA/Access naming rules so code should go something like this:

Sub Form_Load()
   Dim OFrm_val
   OFrm_val = Form_myoldFormname![txt1]
   Me![ntxt1] = OFrm_val      ' Even if this (Me![ntxt1]) is a hidden field
   Call MyFormQuery_Proc      ' Fill the other fields
End Sub

This should work and processing won't error.

YMR

 

[newfrontiers]

Thank you everyone for the suggestions. YMR, if I have multiple instances of the myoldFormname open that call the newform would the reference Me![ntxt1] work?

Thanks.