Solved

subform is processing before parent form finishes loading (ADP)

Posted on 2004-09-13
10
435 Views
Last Modified: 2008-02-01
This particular quirk hasn't happened to me before.  I have a form that has a subform.  The subform's record source is a stored procedure and there are two input parameters.  There are no child/master links between the two forms, but the parameters for the subform are pulled from the main form.  The Input parameters line looks like this:

@Date smalldatetime = [Forms]![frmOrders]![tbxDate], @Crop nvarchar(25) = [Forms]![frmOrders]![tbxCurrentCropID]

The problem is that when I open the parent form, popups that ask for parameter inputs are appearing.  I was thinking that this was happening because the subform was trying to process the stored procedure before the form loaded.  So I make sure in my form's Load event that the two fields that control the parameters are set to something (the current date and the first crop of a drop down list).  Then I put in a couple test msgbox popups to show me these values.  

Well, the parameters popups are still popping up BEFORE my msgbox popups (which have the correct values), indicating to me that the subform is trying to process the data even before the Form_Load event?  I don't understand. I have other forms/subforms with this setup, and those are working fine so there must be some detail I am overlooking...

Thanks for any help.
0
Comment
Question by:rsoble
  • 6
  • 4
10 Comments
 
LVL 39

Expert Comment

by:stevbe
ID: 12045562
subforms do load before the parent ... try using code to set the subform's recordsource in the parent's Load event.

Steve
0
 
LVL 1

Author Comment

by:rsoble
ID: 12056333
Thanks Steve.  I did what you said and progress is being made.  However I think I have the syntax wrong for the InputParameters line below:
-----------
Private Sub Form_Load()

    Me.tbxDate.Value = dtGetCurrentDate()  'this sets the date parameter
    Call cmdNext_Click 'this sets the cropID parameter - goes to the first item in the drop down list.

    Me.subfrmOrders.Form.RecordSource = "spGrowerOrder"
    Me.subfrmGrowerOrders.Form.InputParameters = "@Date smalldatetime = " & Me.tbxDate.Value & ", @Crop nvarchar(25) = " & Me.tbxCurrentCropID

End Sub
-------------

Any corrections to be made?  Thanks!
0
 
LVL 39

Expert Comment

by:stevbe
ID: 12056621
when you refer to a subform from it's parent you need to use the subform control name and not the name of the subform itself ...

Me.subformControlName.Form.InputParameters ....

Steve
0
 
LVL 1

Author Comment

by:rsoble
ID: 12056906
Oh yes I had a typo there.  It should have been:

    Me.subfrmOrders.Form.InputParameters = "@Date smalldatetime = " & Me.tbxDate.Value & ", @Crop nvarchar(25) = " & Me.tbxCurrentCropID

I fixed that and still am having problems.  I load the form and I get popups asking for my parameters.
I still think I probably have the syntax of the variable wrong?

0
 
LVL 39

Expert Comment

by:stevbe
ID: 12057038
Can you get the InputParameters to work if you do this on a single form ... we can fix Parent/Child references once this part is straight.

Steve
0
Find Ransomware Secrets With All-Source Analysis

Ransomware has become a major concern for organizations; its prevalence has grown due to past successes achieved by threat actors. While each ransomware variant is different, we’ve seen some common tactics and trends used among the authors of the malware.

 
LVL 1

Author Comment

by:rsoble
ID: 12057110
With the subform alone, if I have the recordsource and the inputparameters set in the form properties, I then open the form in Form View and popups appear to put in the parameters.  I do so, and the subform appears populated with data as expected.  

If I open the main form in Form View and have the subform recordsouce set but the inputparameters blank, i am prompted for the two parameter value and after entering them, the form plus subform both load fine.  

So the problem is just trying to load the Parent+Subform with the input parameters being pulled from the parent rather than being input by hand.

Thanks.  I'll up the points to the max.
0
 
LVL 39

Accepted Solution

by:
stevbe earned 500 total points
ID: 12057379
ok ... try leaving the InputParamters and RecordSDource properties blank and then setting the inputparameter poperty first ...

from Parent ...

'using Load to m,ake sure data is available ...
Private Sub Form_Load()
    me.childForm.Form.InputParamters = _
"@Date smalldatetime = " & Me.tbxDate.Value & ", @Crop nvarchar(25) = " & Me.tbxCurrentCropID
me.childForm.Form.Recordsource = "sproc_MyStoredProc"

End Sub
0
 
LVL 1

Author Comment

by:rsoble
ID: 12066089
Ok, that seemed to work, setting the input parameters first.  
But I also had to reformat my InputParameters string to include the little single quote marks that SQL needed:
Here's the final version:

================
Private Sub Form_Load()
    Me.tbxDate.Value = dtGetCurrentDate()
    Call cmdNext_Click
    Me.subfrmGrowerOrders.Form.InputParameters = "@Date smalldatetime = '" & DateValue(Me.tbxDate) & "', @Crop nvarchar(25) = '" & Me.tbxCurrentCropID & "'"
    Me.subfrmGrowerOrders.Form.RecordSource = "spGrowerOrder"
End Sub
================

Thanks for your help!  
0
 
LVL 1

Author Comment

by:rsoble
ID: 12068398
Steve - I'm posting a followup question to this one because now I've got another problem with this subform.  Check it out if you're interested.
0
 
LVL 1

Author Comment

by:rsoble
ID: 12068612
Nevermind, I'm not posting the followup yet.  I want to do more experimentation on my own first...  sorry for the false alarm.
0

Featured Post

Backup Your Microsoft Windows Server®

Backup all your Microsoft Windows Server – on-premises, in remote locations, in private and hybrid clouds. Your entire Windows Server will be backed up in one easy step with patented, block-level disk imaging. We achieve RTOs (recovery time objectives) as low as 15 seconds.

Join & Write a Comment

This article is a continuation or rather an extension from Cascading Combos (http://www.experts-exchange.com/A_5949.html) and builds on examples developed in detail there. It should be understandable alone, but I recommend reading the previous artic…
It took me quite some time to sort out all the different properties of combo and list boxes available from Visual Basic at run-time. Not that the documentation is lacking: the help pages are quite thorough and well written. The problem was rather wh…
Familiarize people with the process of retrieving data from SQL Server using an Access pass-thru query. Microsoft Access is a very powerful client/server development tool. One of the ways that you can retrieve data from a SQL Server is by using a pa…
What’s inside an Access Desktop Database. Will look at the basic interface, Navigation Pane (Database Container), Tables, Queries, Forms, Report, Macro’s, and VBA code.

705 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

Need Help in Real-Time?

Connect with top rated Experts

18 Experts available now in Live!

Get 1:1 Help Now