Pulling data depending what form is open (1 Viewer)

dmorgan20 · Apr 11, 2018

Morning

I have multiple userforms - From all the form users can click a button that will open another form called frm_complaints_manager

When the frm_complaints_manager form opens I want it to detect which form is open and pull some data from it.

I had no idea how to achieve this so came up with the following that doesn't appear to work:

Code:

On Error GoTo Step2:
If Forms![frm_Manager_Stats_NEW_Appts].Visible = True Then
    Forms![frm_complaints_manager]![CDP1] = Forms![frm_Manager_Stats_NEW_Appts]![Text285]
        complaints.Requery
Else
    'do nothing
End If
On Error GoTo Step3:
Step2:
If Forms![frm_Manager_Stats_NEW_Finals].Visible = True Then
    Forms![frm_complaints_manager]![CDP1] = Forms![frm_Manager_Stats_NEW_Finals]![Text285]
        complaints.Requery
Else
    'do nothing
End If
Step3:
If Forms![frm_Manager_Stats_NEW].Visible = True Then
    Forms![frm_complaints_manager]![CDP1] = Forms![frm_Manager_Stats_NEW]![Text285]
        complaints.Requery
Else
    'do nothing
End If

Any help is appreciate - I would imagine I have taken completely the wrong approach to this

isladogs · Apr 11, 2018

Initially, I was going to suggest using Screen.ActiveForm.Name but that would just get the currently loaded form name (frm_complaints_manager)

The error loops aren't necessary IMO
If complaints is a control on the current form, use Me. prefix

This should work:

Code:

If Forms![frm_Manager_Stats_NEW_Appts].Visible = True Then
    Me.CDP1 = Forms![frm_Manager_Stats_NEW_Appts].Text285     
ElseIf Forms![frm_Manager_Stats_NEW_Finals].Visible = True Then
    Me.CDP1 = Forms![frm_Manager_Stats_NEW_Finals].Text285    
ElseIf Forms![frm_Manager_Stats_NEW].Visible = True Then
    Me.CDP1 = Forms![frm_Manager_Stats_NEW].Text285
Else 'not actually needed - for info only	
    'do nothing
End If

Me.complaints.Requery

I suspect it can be improved further into just one line + requery line!

CJ_London · Apr 11, 2018

doesn't appear to work

what does this mean?
your approach is ok - the forms collection only contains open forms. So an error is generated on your is visible line if it is not open

suspect it is the ordering of the code - would have thought the On Error GoTo Step3: should be after Step2:, not before

and lets say the first form is open and visible, so a value is assigned so it goes to the next step - again visible, so value assigned again - and on to third step etc.

dmorgan20 · Apr 11, 2018

ridders said:
Initially, I was going to suggest using Screen.ActiveForm.Name but that would just get the currently loaded form name (frm_complaints_manager)

The error loops aren't necessary IMO
If complaints is a control on the current form, use Me. prefix

This should work:

Code:

If Forms![frm_Manager_Stats_NEW_Appts].Visible = True Then Me.CDP1 = Forms![frm_Manager_Stats_NEW_Appts].Text285 ElseIf Forms![frm_Manager_Stats_NEW_Finals].Visible = True Then Me.CDP1 = Forms![frm_Manager_Stats_NEW_Finals].Text285 ElseIf Forms![frm_Manager_Stats_NEW].Visible = True Then Me.CDP1 = Forms![frm_Manager_Stats_NEW].Text285 Else 'not actually needed - for info only 'do nothing End If Me.complaints.Requery

I suspect it can be improved further into just one line + requery line!

Thank you for this - I have tried it but its currently throwing out the same error I was previously getting which was:

Microsoft Access cannot find the referenced form 'frm_Manager_Stats_NEW_Appts'.

This form does exist - Baffled

Minty · Apr 11, 2018

I would be very careful of calling your forms frm_ as Access stores internal references to objects by pre-fixing them with form_ .

Although its a naming convention that works for you, I would remove the underscores, they are difficult to see in certain situations and could trip you up.

Back to your problem, If you passed the form name to your manager stats form using the openargs property you wouldn't have any issues in knowing which form it was opened from.

On form load use something like

Code:

Select Case Me.OpenArgs 
    Case "Form1"
         Do some stuff
    Case "Form2"
         Do Some Other stuff
etc

On your open form button on each form simply add Me.Name as the OpenArgs property

dmorgan20 · Apr 11, 2018

Minty said:
I would be very careful of calling your forms frm_ as Access stores internal references to objects by pre-fixing them with form_ .

Although its a naming convention that works for you, I would remove the underscores, they are difficult to see in certain situations and could trip you up.

Back to your problem, If you passed the form name to your manager stats form using the openargs property you wouldn't have any issues in knowing which form it was opened from.

On form load use something like

Code:

Select Case Me.OpenArgs Case "Form1" Do some stuff Case "Form2" Do Some Other stuff etc

On your open form button on each form simply add Me.Name as the OpenArgs property

Thank you for taking the time to reply

Can you explain in a little more detail please as this is a tad over my head at the minute, sorry.

Minty · Apr 11, 2018

I'll try - on the forms that you have the command button on that opens the form you want to change things on you can pass information to it when it is opened.

so if your command button on your forms was called cmdOpenMyManagerForm then the on click event would be something like;

Code:

Private sub cmdOpenMyManagerForm_Click()
    Dim sArgs As String

    sArgs = Me.Name

    DoCmd.OpenForm "frm_complaints_manager", acNormal, , , acFormEdit, acWindowNormal, sArgs

End Sub

Now on the frm_complaints_manager in the OnLoad event do the following

Code:

Msgbox "This form was opened from the " & Me.OpenArgs & " form"

This will demonstrate how to get the information from one form to the other. From there it's simple to do the various setting of controls / values based on the calling form.

dmorgan20 · Apr 12, 2018

Thank you

Took 5 minutes to understand it, works a treat. Thank you

Pulling data depending what form is open (1 Viewer)

dmorgan20

Registered User.

isladogs

MVP / VIP

CJ_London

Super Moderator

dmorgan20

Registered User.

Minty

AWF VIP

dmorgan20

Registered User.

Minty

AWF VIP

dmorgan20

Registered User.

Similar threads

Users who are viewing this thread