Solved

subform is processing before parent form finishes loading (ADP)

Posted on 2004-09-13
10
475 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
Migrating Your Company's PCs

To keep pace with competitors, businesses must keep employees productive, and that means providing them with the latest technology. This document provides the tips and tricks you need to help you migrate an outdated PC fleet to new desktops, laptops, and tablets.

 
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
 
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

Free Tool: SSL Checker

Scans your site and returns information about your SSL implementation and certificate. Helpful for debugging and validating your SSL configuration.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

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

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.
Access developers frequently have requirements to interact with Excel (import from or output to) in their applications.  You might be able to accomplish this with the TransferSpreadsheet and OutputTo methods, but in this series of articles I will di…
Familiarize people with the process of utilizing SQL Server views from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Microsoft Access…
In Microsoft Access, when working with VBA, learn some techniques for writing readable and easily maintained code.

713 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