Creating continuous forms

[bibek24]

I have a main form which has a Comment Area Section whose source object is a form fsubCaseDetails.
fsubCaseDetails has a textbox.The main form which when viewed in form view displays the fsubcaseDetails form in the Comments Area section.
I need to enter comments into the form fsubCaseDetails by clicking on the AddComments button which opens a form frmZoom.When a user clicks on AddComments button frmZoom is opened and he/she enters the comments into the textbox of frmZoom and finally the comments entered in the frmZoon will get transfered to form fsubcasedetails on clicking save button of frmZoom.
When the text get transferred into fsubcasedetails form,it will get locked and wiil not allow any further comments to be entered.
When some text get transferred to fsubcasedetails form,another fsubcasedetails will get opened in the Comment Area section below the first form.
If now a user wants to add some comments by opening the frmZoom,the text comments should get transferred to the 2nd fsubcasedetails form which got created.Similarly the process wll go on.

Could anyone please help me in the above problem.
Specifically how continious forms will be created and the text will get transfered to the next next forms.
This is very urgent.

Thanks.

[NeoPa]

This is really quite unclear. Items are not introduced in any understandable order.

The best I can do with this is to provide a possibly helpful link on Referring to Items on a Sub-Form.

[bibek24]

This was not really helpful.Let me explain the scenario more clearly.

Logging into my application brings me into frmMain where it displays certain cases.
Clicking on any particular case,"frmCase" is opened.
This form frmCase has many tabs among which "File Details" is one.
Clicking on "File Details" tab, a screen appears which has a "Comment Area" section inside which there is continuous form "fsubCaseDetails".

I need to enter comments to the form "fsubCaseDetails" through a form frmZoom.frmZoom will get open on clicking a button "Add Comments" which is present in frmCase.

What I need to do so that the comments entered in the frmZoom will get transferred to fsubcasedetails on clicking "save" button of frmZoom.

[FishVal]

Hello, bibek.

You may read the similar threads:
Pop Up Form to Enter Data, then Close and Requery Main Form
Returning Data from Modal Form
make one form wait for another

Regards,
Fish

[NeoPa]

This was not really helpful.Let me explain the scenario more clearly.

That would be a good starting point yes.

Now, I get that when you say tab you're referring to a Page control, but would I be right in guessing that your "Comment Area" section refers to a SubForm control (as opposed to the form contained within it - see earlier link for details)?

fsubCaseDetails must be the name of a form which is displayed within the SubForm control in continuous mode. frmZoom is an independent form which is merely linked logically by code.

Can you please confirm this understanding, and possibly post the code you use ( The full procedure please) to open frmZoom.

[bibek24]

What you have understood is perfectly correct.
Let me give you more inputs as well.

Here fsubCaseDetails(Row Source is a query) is a subform in frmCase.
The textbox in fsubCaseDetails is locked that means user doesn't have the ability to directly enter into it.

A user can only enter text to it if he/she clicks on "Add Comments" button (present in frmCase) which opens frmZoom(a zoom view to fsubCaseDetails).
Anything entered in the frmZoom shoud get transfer and saved in fsubCaseDetails when the "Save" button of frmZoom is clicked.

After clicking Save, if the user wants to add some more comments then he/she has to click again the "Add Comments" button which would pop up the frmZoom and the text entered should get transfer to the 2nd row of fsubCaseDetails.
The problem is that after writing some text to frmZoom and clicking Save button,its not getting transfer to fsubCaseDetails.I might be missing some code in events of both of the forms such as open event,Activate event,Load event etc.

I need to know what code would solve my requirement.

[NeoPa]

That is a lot clearer, but I would still like to see your code for this.

This means that I can see better what you already understand, and concentrate on the gaps.

[bibek24]

Code for Save button in frmZoom:
========================

txtEntered = Me.txtZoom.Value
Forms![frmCase]![fsubCaseDetail].Form![txtDetails] = txtEntered
DoCmd.Close acForm, Me.Name

where txtEntered is a global variable.

I am new to VB Access and need to know on a high level what all codes are required for my requirement.

[NeoPa]

This code isn't perfect, but it's pretty adequate. If it's executed it should at least update your calling form with the latest data from frmZoom.

Is this not happening at all?

[NeoPa]

Some better code, if you want to add data in to subsequent lines if frmZoom is repeated :

With Forms![frmCase].[fsubCaseDetail].Form
  .[txtDetails] = .[txtDetails] & vbCrLf & Me.txtZoom
End With
DoCmd.Close

This assumes txtDetails can expand to show multiple lines. I hope at this stage you're not referring to multiple records on your main form ([frmCase]), that would change the question entirely and to have left out explaining that would be, well, certainly not clever.

[bibek24]

Hey I would just like to know what code would pass the text from frmZoom to fsubCaseDetails on clicking the "Save" button of frmZoom and after doing it ,the form should exit.

My code is not passing the value and not closing the form as well.

[FishVal]

Hey I would just like to know what code would pass the text from frmZoom to fsubCaseDetails on clicking the "Save" button of frmZoom and after doing it ,the form should exit.

My code is not passing the value and not closing the form as well.

Hi.

The code you've posted in msg #8 is expected to do something or at least fail with error message. If nothing happens, then I would like to ask:
- Do you have any error handling code?
- Are you sure the code runs?

Regards,
Fish.

[bibek24]

Ya the code is showing the following error:
Access cann't find the field fsubCaseDetails reffered to in your expression.

Let me tell the you the existing scenario as well.
In the existing scenario the frmZoom is opened by double clicking the text box of fsubCaseDetails.
But in the TO-BE scenario the text box in the form fsubCaseDetails should be locked and the user should only add comments to it by clicking on "Add Comments" button which would open frmZoom.

Can we modify the following existing code and meet our requirement?
One more thing there is no code associated with the "Save" button of frmZoom which could ensure the value passing from it to fsubCaseDetails.
I think the data is also passed through the following code.I am not getting the code clearly.If you can explain it and find out a way, then it would be great.

Opening of frmZoom on double clicking the text box of fsubCaseDetails:
================================================== =

Private Sub txtCaseDetails_DblClick(Cancel As Integer)
   On Error Resume Next
   gfncZoomWindow (Me.NewRecord = False)
End Sub

Function to open Zoom window:
=======================

Function gfncZoomWindow(Optional blnLock As Boolean)
   'Open Zoom Window With Contents Of Active Text Box Control
   On Error Resume Next
   Dim frm As Form      'Active Form
   Dim strFormName As String
   Dim blnLockEdit As Boolean
 
   If Not IsMissing(blnLock) Then blnLockEdit = blnLock
 
   If IsError(Screen.ActiveForm) = False Then
      Set frm = Screen.ActiveForm
 
      If frm.ActiveControl.ControlType = acSubform Then
         If frm.ActiveControl.Form.ActiveControl.ControlType = acTextBox Then
            'Tack . At End To Denote Subform
            DoCmd.OpenForm "frmZoom", , , , IIf(blnLock = True, acFormReadOnly, acFormPropertySettings), acDialog, frm.Name & "."
         End If
      ElseIf frm.ActiveControl.ControlType = acTextBox Then
         DoCmd.OpenForm "frmZoom", , , , IIf(blnLock = True, acFormReadOnly, acFormPropertySettings), acDialog, frm.Name & "."
      End If
   End If
End Function

[FishVal]

Ok.

That is half of the puzzle. Could you post all the code in [frmZoom] module?

[bibek24]

Here it is:

Option Compare Database
Option Explicit
 
Dim mfrm As Form              'Active Form
Dim mblnCancel As Boolean     'Indicates Cancel Pressed
 
Private Sub cmdCancel_Click()
   On Error Resume Next
   DoCmd.SetWarnings False
   DoCmd.RunCommand acCmdUndo
   DoCmd.SetWarnings True
   mblnCancel = True
   DoCmd.Close acForm, Me.Name
End Sub
 
Private Sub cmdSave_Click()
   On Error GoTo Err_cmdSave_Click
 
   txtEntered = Me.txtZoom
   Forms.fsubCaseDetails.txtDetails = txtEntered
   DoCmd.Close acForm, Me.Name
 
Exit_cmdSave_Click:
   Exit Sub
Err_cmdSave_Click:
   MsgBox Error$, vbCritical, "Error: cmdSave_Click"
   Resume Exit_cmdSave_Click
End Sub
 
 
Private Sub Form_Open(Cancel As Integer)
   On Error GoTo Err_Form_Open
   Dim varOpenArg As Variant        'Value Of Open Argument
   Dim ctl As Control
   Dim lngAddHeight As Long         'Add Height Of Detail Section
   Dim lngPos As Long
 
   'Set Initial Height
   DoCmd.Restore
   DoCmd.RunCommand acCmdSizeToFitForm
 
   'Set Module Form Variable
   varOpenArg = Me.OpenArgs
   If Not IsNull(varOpenArg) Then
      If InStr(1, varOpenArg, ".") Then
         lngPos = InStr(1, varOpenArg, ".")
         If lngPos > 0 Then
            Set mfrm = Forms(Left(varOpenArg, lngPos - 1))
            Set mfrm = mfrm.ActiveControl.Form
         Else
            Set mfrm = Forms(varOpenArg)
         End If
      Else
         Set mfrm = Forms(varOpenArg)
      End If
 
      Me.txtZoom = mfrm.ActiveControl.Value
 
      'Set The Locked Property To Match The ActiveControl Locked Property
      If mfrm.ActiveControl.ControlType = acTextBox Then
         Me.txtZoom.Locked = mfrm.ActiveControl.Locked
      End If
      SendKeys "{F2}"
   End If
 
Exit_Form_Open:
   Exit Sub
Err_Form_Open:
   MsgBox Error$, vbCritical, "Error: Form_Open"
   Resume Exit_Form_Open
End Sub
 
Private Sub Form_Unload(Cancel As Integer)
   On Error Resume Next
   If mblnCancel = False Then mfrm.ActiveControl.Value = Me.txtZoom
End Sub

[NeoPa]

Hey I would just like to know what code would pass the text from frmZoom to fsubCaseDetails on clicking the "Save" button of frmZoom and after doing it ,the form should exit.

My code is not passing the value and not closing the form as well.

Hey, if you'd responded to post #9 then I'd have known the exact situation we're dealing with. If you leave me guessing instead of responding clearly, you can expect me to be unclear as to exactly what IS going on.

Even after your less than polite reply, you STILL haven't answered half the questions posed of you and your situation is STILL unclear.

[NeoPa]

Ya the code is showing the following error:
Access cann't find the field fsubCaseDetails reffered to in your expression.

You're telling us this in post #13!! Have you given ANY consideration to this question? I suggest if you want some help, you pay more attention to those trying to help you, rather than showing attitude when they can't read your mind.

[NeoPa]

Ya the code is showing the following error:
Access cann't find the field fsubCaseDetails reffered to in your expression.

This is very confusing as the code you posted earlier (post #8) is quite different from the code you subsequently posted (post #15 - lines #19 to #21). There is also no indication as to which line triggers the error message shown.

I shall will await your response.

[FishVal]

@bibek24

The code you have is kludgy and unreasonably complicated.
Since the question was several times asked on the forum I've written a simple howto concerning different approaches to the problem.

Regards,
Fish

[bibek24]

The code in #8 is showing the error which is my TO BE code as per my requirement.
All other subsequent codes are for the existing scenario.
My requirement is to enhance the existing scenario and what I have explained in #13 is the existing scenario.

[NeoPa]

@Fish, Did you mean to post that in VB / VBA HowTo section? I would have thought this was Access specific.

PS. Nice article :)

[FishVal]

@Fish, Did you mean to post that in VB / VBA HowTo section? I would have thought this was Access specific.

PS. Nice article :)

:)

I intended to post it in Access howtos section but accidentally posted it in VB/VBA one. Please, move it appropriately.

[NeoPa]

The code in #8 is showing the error which is my TO BE code as per my requirement.
All other subsequent codes are for the existing scenario.
My requirement is to enhance the existing scenario and what I have explained in #13 is the existing scenario.

Bibek,

As Fish has already posted a fairly comprehensive article on the subject I will assume all your questions are resolved for now. I will content myself with a comment I was leading up to before, depending on one of the answers I was anticipating :
Sometimes, code can be inactive (remain untriggered) even if the code is perfectly correctly done, simply because the property of the object (I'm referring to the On Click of a Command Button for example or the On Open of the form itself even) is not set to the value "[Event Procedure]". This is found when looking at the object in Design View and showing the Properties (Alt-Enter).

If you find, after going through Fish's article that you still have questions then please come back and post them.

[NeoPa]

:)

I intended to post it in Access howtos section but accidentally posted it in VB/VBA one. Please, move it appropriately.

Moved :)

[bibek24]

The approach looks good.Thanks for this.
But I have the following questions:

1) I am always getting a debugging error when am trying to refer a subform in the main form.

for exp
With Forms!frmChild1
.txb.ControlSource = Me!fsubCaseDetails.Form!txb.ControlSource

2) To overcome this I kept the "Add Comments" button in the subform itself and tried with the same code as posted

With Forms!frmChild1
.txb.ControlSource = Me.txb.ControlSource

It was working fine but this always update the first record in the form,the control is not moving to the 2nd record.
So how to move the control to 2nd record once the 1st record has been saved?

[FishVal]

....
It was working fine but this always update the first record in the form,the control is not moving to the 2nd record.
So how to move the control to 2nd record once the 1st record has been saved?

As soon as both forms are bound to the same Recordset object, they will be always pointed to the same record.

Regards,
Fish.

P.S. I didn't really understand your question.

[NeoPa]

As this refers directly to Fish's code (in the article) I will allow Fish to respond.

If you hear nothing after a couple of days then bump the thread and I will have a look into it myself.

PS. How did he do that :S

[bibek24]

I mean to sat how to point to the 2nd record of the form once the 1st record is saved.Suppose I want to enter something to the 2nd record then how to do that?

[FishVal]

As I've already said both main and child form being bound to the same Recordset object are pointed to the same record, no matter where do you click navigation buttons.

Option 1. (as in the example)
Select appropriate record on main form, open child form, alter data and either submit or cancel input.

Option 2.
Add navigation bar to the child form. When child form is opened you may use its navigation bar to walk through records.

Hope this helps.

[bibek24]

I am sorry but I am really not getting help for my requirement which is as follows:

As many number of times the child form will be populated with some text and clicked on "Submit" button that many number number of records would be created in the parent form.

i.e First text entered to the child form would make up the first record in the parent form,second would make up the 2nd record and so on.

[FishVal]

I am sorry but I am really not getting help for my requirement which is as follows:

As many number of times the child form will be populated with some text and clicked on "Submit" button that many number number of records would be created in the parent form.

i.e First text entered to the child form would make up the first record in the parent form,second would make up the 2nd record and so on.

Hello, bibek.

I thought it is quite straightforward - when you need to do the same on usual form, you simply go to new record, enter data, go to new record and so on.

That means, the only thing [Submit] button has to do is to point child form to new record. For example using DoCmd.GoToRecord method.

Kind regards,
Fish.