Solved

MS Access VBA

Posted on 2012-12-27
14
245 Views
Last Modified: 2012-12-28
I have inherited a mess.
A MS Access app (ADP) with many many nested forms.

In this wuestion I have

Parent Form
   Subform
      SubSubForm on SubForm

On the parent form I have a combobox (cmbVisits)

On the ParentForm cmbVisits_Change() event I want to
execute a public sub on SubForm
execute a public sub on SubSubForm

I'm able to execute the sub form public sub with this...
Me!ClientVisitContactEvent.Form.loadQuestions

I've tried all kinds of permutations of the sub sub form
Like this one...all with a object error message
Me!MarketingVisitationClient.MarketingVisitationClientContactsWorklist.Form.loadContactData
0
Comment
Question by:lrbrister
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 9
  • 5
14 Comments
 
LVL 48

Expert Comment

by:Dale Fye (Access MVP)
ID: 38724457
Try:

Me!clientVisitContactEvent.Form.MarketingVisitationClient.form.loadContactData

the syntax would normally be:

Me.subformControlName.Form.SubSubFormControlName.Form.ProcedureName
0
 

Author Comment

by:lrbrister
ID: 38724472
fyed

I received this error

Screenprint
0
 

Author Comment

by:lrbrister
ID: 38724482
fyed
This is the actual forms and path left to right...still getting same error messgage

Me!MarketingVisitationClient.Form.MarketingVisitationClientContactsWorklist.Form.loadContactData
0
Simplifying Server Workload Migrations

This use case outlines the migration challenges that organizations face and how the Acronis AnyData Engine supports physical-to-physical (P2P), physical-to-virtual (P2V), virtual to physical (V2P), and cross-virtual (V2V) migration scenarios to address these challenges.

 
LVL 48

Expert Comment

by:Dale Fye (Access MVP)
ID: 38724487
You will have to provide more details.

Is the SubSubforms actually nested inside the SubForm, or are there just two subforms on the main form?

What are the Control Names of each of the subform controls (don't confuse this with the value in the SourceObject property of each item.
0
 
LVL 48

Expert Comment

by:Dale Fye (Access MVP)
ID: 38724502
You might have to declare that function (in the code section of the SubSubForm) from Private to Public.

As mentioned above, make sure you are using the "control names" and not the "source Object" to refer to these subforms.
0
 

Author Comment

by:lrbrister
ID: 38724513
fyed
On the main (ClientVisits) form is the combobox
This same ClientVists form has 3 sub forms...and the first one has it's own sub form

1. MarketingVisitationClient
     a. MarketingVisitationClientContactsWorklist
2. ClientVisitContactEvent
3. ClientVisitsNearbyLV

I can execute the public sub on #2 with
Me!ClientVisitContactEvent.Form.loadQuestions

I am trying to execute the public sub on 1.a. named loadContactData
0
 

Author Comment

by:lrbrister
ID: 38724522
fyed...
It IS a public sub in the sub sub form.

Public Sub loadContactData()
        Dim cmd1 As New ADODB.Command
        Dim rst1 As ADODB.Recordset
        
        cmd1.ActiveConnection = CurrentProject.Connection
        cmd1.CommandType = adCmdStoredProc
        cmd1.CommandText = "usp_MarketingVisitContactsGet"
        cmd1.Parameters("@VisitID") = Nz(Me.Parent.cmbVisits, 0)
        cmd1.Parameters("@ClientID") = Me.Parent.txtClientID 'Me.Parent.Parent.getClientID()
        
        Set rst1 = cmd1.Execute()
End Sub

Open in new window

0
 

Author Comment

by:lrbrister
ID: 38724554
fyed,
  Also...to make sure I was able to see the first subform from the parent (ClientVisits) I ran a messagebox in the following call and it worked

Me!MarketingVisitationClient.Form.doMessage

I'm just not reaching 1.a from my list above
0
 
LVL 48

Accepted Solution

by:
Dale Fye (Access MVP) earned 500 total points
ID: 38724563
That error makes more sense now.

Your line 9 refers to "me.Parent.cmbVisits"  but that combo is not in the parent form, it is in the parents parent, so try:

cmd1.Parameters("@VisitID") = NZ(me.parent.parent.cmbVisits, 0)

Put a breakpoint in the LoadContactData subroutine and confirm that those parameters are being  set correctly.
0
 

Author Comment

by:lrbrister
ID: 38724610
fyed

I know I'm missing something obvious (Like I just missed the Parent path)
I'm frustrated with myself.

But it's still not getting to that breakpoint...tried that myself previousely

Getting this error screen now

screen
0
 
LVL 48

Expert Comment

by:Dale Fye (Access MVP)
ID: 38724661
1.  Are you certain it is running the other code (loadQuestions).  Have you put a breakpoint in that code and stepped through it?

2.  Are you certain that subform is actually loaded at the point where this occurs?  Instead of trying to run code on that subform, try popping up a message box with the value in one of the controls on that subform.

msgbox me.MarketingVisitationClient.form.MarketingVisitationClientContactsWorklist.form.txtSomeControl

I must admit that I have never felt the need to call a subroutine nested in the code module of a sub-sub form.  Is there a reason that this code could not go into a general code module, rather than a forms code module?

Is there a reason you could not call that procedure from within the LoadQuestions subroutine of the MarketingVisitationClient form?


Instead of
0
 

Author Comment

by:lrbrister
ID: 38724885
fyed,
Well...this entire forms set is part of a much larger Client Visits and Schedu;ing process.

I must admit, it's been years since I was in MS Access.  been in AJAX .Net Web Forms and WinForms until this recent job.

The forms are all datasheet views so it's functioning like a hierachy based set of GridViews

Without going into the gory details...
I can't just use a view etc to call in the data.

These forms are all filtered by the Combobox.

How would I do that on my sub_sub form based on the conbobox click...using a general code module?
0
 

Author Comment

by:lrbrister
ID: 38724922
fyed
If I go to the sub sub form and put in the stored procedure I can then open the form and (since there are two paramaters to the stored procedure  @ClientID and @VisitID) ...
I can manually enter a ClientID and a VisitID and get the data.

Is there any way to tie that stored proc to the Parent.Parent.Combobox?

screenprint
0
 

Author Closing Comment

by:lrbrister
ID: 38726985
fyed,
  Everything you've pointed out to me is correct.  

I know for a fact it works because I've done it in a second database with a similar setup.

There's just something in this particular database that isn't working.

Since your reply 38724563 was what worked in a second database I'll accept that.

Looks like I'll have to find a work around.
0

Featured Post

PeopleSoft Has Never Been Easier

PeopleSoft Adoption Made Smooth & Simple!

On-The-Job Training Is made Intuitive & Easy With WalkMe's On-Screen Guidance Tool.  Claim Your Free WalkMe Account Now

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Title # Comments Views Activity
Calculation in a Report 13 43
Error in Visual Project 10 49
MS Access Duplicate Data Assistance 9 32
Unidentified Function 2 32
Having trouble getting your hands on Dynamics 365 Field Service or Project Service trial? Worry No More!!!
In earlier versions of Windows (XP and before), you could drag a database to the taskbar, where it would appear as a taskbar icon to open that database.  This article shows how to recreate this functionality in Windows 7 through 10.
With Microsoft Access, learn how to specify relationships between tables and set various options on the relationship. Add the tables: Create the relationship: Decide if you’re going to set referential integrity: Decide if you want cascade upda…
In Microsoft Access, when working with VBA, learn some techniques for writing readable and easily maintained code.

752 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question