Link Child/Master fields 1

[ad2]

Right now I have a details data entry subform on the Main data entry form.

Because the subform takes up so much screen space (I want to add a second subform) I want to have the subforms open as pop-up forms from a command button.

Is there a way to create the Link Child and Link Master fields in code on the pop-up forms? I not sure how to link the forms as the Link field are only available in design view on a true subform.

 

[TheAceMan1]

How are ya ad2 . . . .

[blue]Is there [purple]a way to create the Link Child and Link Master fields[/purple] in code on the pop-up forms?[/blue]

No! Link Master/Child is for form with subform only.

For an Independent Form, [blue]to be dependent on another[/blue], it has to be synchronized by other means. There are several methods to do this. The one I present here allows [blue]synchronization even after both forms are open[/blue] (most other methods synchronize the Dependent form only on open). Goto to a record on the Independent Form and the Dependent Form synchronizes (follows along).

Now the process ([purple]don't forget to backup the database in case you want to come back to square one[/purple]).

Note: You substitute all names in [purple]purple[/purple].

[ol][li]Open the [blue]MainForm[/blue] and remove the subforms.[/li]
[li]Save/Close the form.[/li]
[li]Make a query for each [blue]Dependent Form[/blue] (your pop-ups) with the fields you desire. Be sure to [blue]include a field common to both forms[/blue] (main & pop-up . . . usually the PrimaryKey . . . in fact thats what I use in the example code). Stay in Query Design View.[/li]
[li]In the [blue]criteria for PrimaryKey[/blue] add the following:
Forms![purple]MainFormName[/purple]![purple]PrimaryKeyName[/purple][/li]
[li]Save & name the query.[/li]
[li]Open the [blue]Pop-up Form[/blue] in design view and in the [blue]RecordSource[/blue] select the query from the dropdown list.[/li]
[li]Save/Close the form.[/li]
[li]The MainForm needs to detect if the Pop-up Form is open. If it is, [blue]synchronization is performed by a simple Requery of the Pop-up![/blue] So in a module in the module window add the following code:

[blue]Function IsOpenFrm(frmName As String) As Boolean
   Dim cp As CurrentProject, Frms As Object
   
   Set cp = CurrentProject()
   Set Frms = cp.AllForms
   
   If Frms.Item(frmName).IsLoaded Then
      If Forms(frmName).CurrentView > 0 Then IsOpenFrm = True
   End If
   
   Set Frms = Nothing
   Set cp = Nothing

End Function[/blue]

[/li]
[li]Finally, in the [blue]On Current[/blue] event of the [blue]MainForm[/blue] add the following:

[blue]   If IsOpenFrm("[purple][b]Pop-upFormName[/b][/purple]") Then
      Forms!Pop-upFormName.Requery
   End If[/blue]

[/li][/ol]
However you decide to open the [blue]Pop-upForm[/blue], don't do anything special, just open it (like DoCmd.OpenForm "FormName"). It'll automatically synchronize on open!

[purple]Thats it. Give it a whirl & let me know . . . .[/purple]

See Ya! . . . . . .

 

[ad2]

Hi AceMan,

after I create the new module, how do I connect the Main form to the module?

Thanks,
Ad2

 

[TheAceMan1]

ad2 . . . .

There is no linking so to speak. A module simply holds routines & functions for your use. You call the routines & functions as you need them.

The [blue]OnCurrent[/blue] event of the mainform calls the[purple]IsOpenFrm[/purple] function to tell if the pop-up is already open.

So there's nothing for you to do (as far as the module is concerned)except copy the code to the module.
From here (if you've completed all the steps), just check your form/pop-up ops.

See Ya! . . . . . .

 

[ad2]

AceMan,

I've created quriers for the pop-ups and created the module.

I have this on the On Current event of the main form:

Private Sub Form_Current()
If IsOpenFrm("frmDetails") Then
Forms!frmDetails.Requery
End If
End Sub

But I get an error message when I click the button to open the pop-up, the debugger goes to the On Current event of the main form.

This is the error message:

Compile error: Expected variable or procedure, not module

 

[TheAceMan1]

OK ad2 . . .

Postback the following.
[ol][li]The names of the forms (identify main form).[/li]
[li]The code in your button[/li]
[li]The code you have in the module[/li]
[li]The SQL from the query for one of the pop-ups.[/li][/ol]
If you button is just opening the pop-ups, then the Current Event has nothing to do with it. The current event is for when you change records on the mainform (causes one/both pop-up to stay schronized).

See Ya! . . . . . .

 

[ad2]

Ah-ha! "The current event is for when you change records on the mainform (causes one/both pop-up to stay schronized). "

I want to change records on my pop-ups, as I would on a true subform.

 

[TheAceMan1]

ad2 . . . . .

You should be able to do that no problemo!

Let me explain how it works:

In a Form with subform, linked with [blue]Master/Child[/blue] properties (the forms here after referred to as Master & Child), the links allow the Child to synchronize with the Master. That is, [purple]the Master determines what records are revealed in the Child[/purple] (not the other way around). Change record on the Master and the Child syncs-up or follows along. [purple]Its like a query where the Child filters the records according to the link field of the master (usually the PK).[/purple] The user can navigate thru the Child Records same as any other form.

In your case, your [blue]synchronizing/linking with queries[/blue] as the rowsource of the pop-ups. [purple]The queries filter records according to the PK of the Master.[/purple] Only problem is, [blue]we have to tell the pop-ups when the master has changed record[/blue] (its done automatically with Master/Child when the child form is embedded on the master form). [purple]This is the purpose of the code in the OnCurrent event of the Master.[/purple] It Requeries the pop-ups so they synchronize to the Master.

So, the code for the [blue]OnCurrent event[/blue] belongs in the Master Form. If your pop-ups aren't Modal, you should be able to change record on the Master and see the pop-ups synchronize. Again . . .you navigate the pop-ups like any other form.

Hope this helps . . . . . .

See Ya! . . . . . .

 

[ad2]

Ok Here is what I have.
1.)

I have this on the On Current event of the main form (frmStaff):

Private Sub Form_Current()
If IsOpenFrm("frmDetails") Then
Forms!frmDetails.Requery
End If
End Sub

But I get an error message when I click the button to open the pop-up, the debugger goes to the On Current event of the main form.

This is the error message:

Compile error: Expected variable or procedure, not module

2.

I havve this module named IsOpenFrm

Function IsOpenFrm(frmDetails As String) As Boolean
Dim cp As CurrentProject, Frms As Object

Set cp = CurrentProject()
Set Frms = cp.AllForms

If Frms.Item(frmDetails).IsLoaded Then
If Forms(frmDetails).CurrentView > 0 Then IsOpenFrm = True
End If

Set Frms = Nothing
Set cp = Nothing

End Function

3. In the criterea of the primary key that is the source Query for frmDetails:

Forms!frmStaff!CourseID

Point of forms. List each staff record. For each staff record, in the details form, list the courses that staff member has taken. Works fine when frmDetails is a subform on the frmStaff, but the version with the frmDetails as a pop-up generates the error noted above.

 

[TheAceMan1]

ad2 . . . . .

[blue]I havve this module named IsOpenFrm[/blue]

The error you report & your quote above tells me you have a module with the same name as the function (no can do). [purple]A module is a container that holds Functions & Routines!, and cannot have the same name of any in the container.[/purple]
Follows these steps:
[ol][li]Copy the code below:

[blue]Function IsOpenFrm(frmName As String) As Boolean
   Dim cp As CurrentProject, Frms As Object
   
   Set cp = CurrentProject()
   Set Frms = cp.AllForms
   
   If Frms.Item(frmName).IsLoaded Then
      If Forms(frmName).CurrentView > 0 Then IsOpenFrm = True
   End If
   
   Set Frms = Nothing
   Set cp = Nothing

End Function[/blue]

[/li]
[li]In the modules window, delete the module you quoted in your last post.[/li]
[li]Still in the modules window, click [purple]New[/purple].[/li]
[li]Paste the code.[/li]
[li]Click [purple]Save[/purple]. Name the module [purple]modUtility[/purple].[/li]
[li]Press [blue]Ctrl + Q[/blue] to return to the module window.[/li][/ol]
[blue]Checkout your form operations! . . . . .[/blue]

See Ya! . . . . . .

 

[ad2]

AceMan,

Thanks for your help, I wasn't able to get you method to work, but you got me thinking.

I left the forms as true subforms on the main form, but when the main form opens, I have each set to Visible = False.

Then when I click the command button, each subform set to Visible = True.

Then I have a close button that when clicked resets each subform set to Visible = False.

Thanks for all your help!

 

[jflachman]

I think you have made this user interface much more difficult than you really needed.

It is much easier to have one subform on your main form, but display different data in the subform depending on what you want to see.

The way you do this is actually create several different forms for display in the one subform space.

Then, with a button click or radio dial or combo box or whatever, the user selects which data they want to see.

In code you would modify the "SourceObject" property of the subform on the main form to reference the different subform depending on what is needed.

This also works better than many different subforms on tab controls because they don't all try to open or get updated every time the main form data changes.

Jonathan
________________________________________
It is not fair to ask of others what you are unwilling to do yourself.
-Eleanor Roosevelt

 

[TheAceMan1]

ad2 . . . . .

In your post origination:

[blue][purple]Because the subform takes up so much screen space[/purple] (I want to add a second subform) [purple]I want to have the subforms open as pop-up forms[/purple] from a command button.[/blue]

Then in your latest post:

[blue][purple]I left the forms as true subforms on the main form[/purple], but when the main form opens, I have each set to Visible = False.[/blue]

[purple]If you say one thing but do another, how could you expect anything to work?[/purple]

[blue]I gave you what you asked for . . . code to synchronize Pop-Ups or external forms . . . . .[/blue]

See Ya! . . . . . .

 

[ad2]

AceMan,

my last post was the solution I had to implement because I could not get your method to work.

I did not say one thing but do another. I did try several times to use the solution you kindly provided, but I'm sure do to my lack of skills, I could not get it implemented, so I had to take another approach, though it is not the one I was originally trying for.

 

[TheAceMan1]

ad2 . . . . Alright . . . . .

The method I gave you is pretty much standard for synchronizing external forms, and I've done it a great many times. Earlier I asked for the following info:
[ol][li]The names of the forms (identify main form).[/li]
[li]The code in your button[/li]
[li]The code you have in the module.[/li]
[li]The SQL from the query for one of the pop-ups.[/li][/ol]

See Ya! . . . . . .

 

[ad2]

AceMan,

I'm going to have to revisit this issue at another time. I have to move on to other projects that have been stacking up. Thanks for your time, you've been great.

 

[Christineeve]

Hey,
I found this post very helpful, althought it doesn't relate to my current issue, it relates to something I'm doing down the line.

Thanks for posting this topic.